Upload files and include datestamp columns

Question

Hi,

I need to write out a process that can upload CSV files and add them into a table within my database. This will be a weekly process.

The files names will be called TMUY_Weekly_yyyymmdd (example for this week: TMUY_Weekly_20211220)

The field names in the CSV are:
Cust_ID
Amount
Volume_of_items

What I would like to do when importing the data is add a new field 'Date_Id' which reflects the date within the CSV file name. So an example would be:

The following week, I will have the latest data view for that week only:

Then my table (Cust_Trans) within the database would show the 2 weeks worth of data combined as it just appends each week.

Is there anyway to do this please?

Answer 1

Is there anyway to do this please?

There are more ways than you want to know. :-)

One way is to write a C# program to read the files and send them to SQL Server through a table variable. You could easily add the date in the C# program. I have an article for how to do this, and this includes an example of reading a CSV file: https://www.sommarskog.se/arrays-in-sql-2008.html.

You can also read them directly from SQL Server with OPENROWSET(BULK) and add the date to the SELECT statement. However, since your file name is variable, this gets messy when you have to use dynamic SQL. On the other hand, there are less moving parts than with a extra program.

I'll stop there. If you can add some indication of where you want to go, we may be able to advice your further.

Answer 2

Hi @Bone_12 ,

You can add a new column variable by Bulk insert with a variable which was mentioned by Erland:
https://stackoverflow.com/questions/3544526/how-do-i-bulk-insert-with-additional-column-showing-filename
I agree to use SSIS, which seems to be the more convenient method.
https://stackoverflow.com/questions/60422056/add-datetime-stamp-column-to-end-destination-table-using-ssis-package
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3a1ce47d-fefc-4637-a4b5-8a4aa146dd42/import-data-into-table-that-has-a-timestamp?forum=sqlintegrationservices

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.