How to automate importing 31 .csv files into SQL Server? (1 Viewer)

[ions]

Hello,

I have a zip file that contains 31 .csv files. Each .csv file contains 1 mil records and the totality of the files makes the entire table. (This dataset is provided by the US Government) How would you go about importing this data into SQL Server?

When I import one .csv file using the SSMS flat file wizard it takes around 5-10 minutes because I have to give each field the correct data type. (There are around 30 fields)

This data dump gets updated on a weekly basis every Monday. How would you automate this import?

Would you combine the 31 .csv files into 1 .csv file and import the single file? If so, how would you combine the .csv files into one file? The .zip file containing the .csv files 950 MB.

Thank you for your feedback.

 

[Gasman]

Never used SQL server.
I would unzip the file to a dedicated folder.
Then I would walk that folder processing each file in turn.
I would have specification saved for the import and use that each time to import to the table.
You could create one huge file in dos with something along the lines of

Full file = file1 + file2 + file3 etc I believe?

 

[theDBguy]

Just thinking out loud but maybe try BCP?

Sent from phone...

 

[GPGeorge]

Just thinking out loud but maybe try BCP?

Sent from phone...

What theDBGuy was referring to: https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver17&tabs=windows

 

[ions]

Just thinking out loud but maybe try BCP?

Sent from phone...

Thanks. BCP appears to be what I am looking for. Appreciate your response.

 

[isladogs]

For many years, I did exactly this process with about 30 CSV files each night at about 2 am, importing data into the SQL server BE for my main school database. The import files were also very large so the total process typically took around 45-60 minutes.

However, the process was completely automated and triggered by a scheduled task on the server:
1. On the unlikely event that any users were logged in via terminal server at 2 am, they were warned and then logged out.
2. The CSV files were exported from an external data source
3. The files were then in turn imported into temp tables in Access, processed then the related SQL Server tables were updated.
4. The whole process was logged and if any issues arose, details were also logged.
5. When complete, the DBA was automatically emailed with a summary of the log file which was one of the first items checked on arrival the next day.

The system has worked flawlessly for many years in multiple locations with the only blips being occasional network outages

I know nothing about BCP so can't comment on whether that would have been any better / easier to setup.
In any case, it appears it wasn't available when our system was first setup back around 2005!

 

[Isaac]

How would you go about importing this data into SQL Server?

No question about it, I would create an SSIS package. Who knows how often you'll have to repeat it in the future

 

[BlueSpruce]

No question about it, I would create an SSIS package. Who knows how often you'll have to repeat it in the future

The OP might not have SSIS, unless he's using a paid SQL Server edition that includes it. There's also a steep learning curve involved with setting up SSIS packages. BCP should be sufficient for accomplishing the OP's objective on a recurring basis. It also loads data a lot faster than using the SSMS wizard.

 

[Isaac]

Data tools is free and I was actually shocked at how small the learning curve is to learn how to set up an ssis package.. you can learn how to do a basic one in about 5 minutes