Importing several CSV files and create separate tables SQL server tables based on each file

Question

I have no SQL experience and have have 70 + CSV files I need to import into an SQL server database. What I need to do is automate this or simplify this so I can run a script and import them all and have them create a new table for each csv file. Some of the files are huge so ideally need a large column width (1000 ) for each.... Some have hundreds of columns... Any assistance would be great... I have seen some scripts but none seem to do the job I need

Probably the easiest way would be to use openrowset. Plenty of examples exist demonstrating how. — Stu
– Stu, Commented May 24, 2022 at 16:57

Stu · Accepted Answer · 2022-05-24 17:19:22Z

1

If you use openrowset you can take advantage of the ability to to select into a destination table that's created automatically.

It's a process that's not free of various hurdles you may need to overcome however I've had to do this many times and have had the best luck with the following syntax:

select * /* into <destinationtable> */
from OpenRowset(
 'Microsoft.ACE.OLEDB.16.0',
 'Text;IMEX=1;MaxScanRows=0;Database=<path to folder containing file>',
 'select * from filename.csv'
)x

The OLEDB driver works best when you have a schema.ini file located in the same folder as the source file.

See how to create a schema.ini file for details.

Also obtain the latest oledb 16 drivers here.

edited May 24, 2022 at 17:19

answered May 24, 2022 at 17:11

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Importing several CSV files and create separate tables SQL server tables based on each file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related