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

user_19190972 0
Viewed 54

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

bulkinsert powershell sql-server
1 Answers

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.

user_153326500
Related