Brian Sandt
asked on
Inserting multiple CSV files into SQL server
I have about 1000 files that I need to import into a table in SQL. Are there any tools out there to make this easier?
If not, what's the best way?
Currently using SQL Express 2012.
If not, what's the best way?
Currently using SQL Express 2012.
ASKER
From what I've read, Express doesn't support SSIS.
![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
Hi,
Sorry for the copy'n'paste of external links, but I thought going straight to the "horse's mouth" may be better for your background reading:
"Import and Export Bulk Data by Using the bcp Utility (SQL Server)" (MS-SQL Server 2012)
[ https://msdn.microsoft.com/en-gb/library/aa337544(v=sql.110).aspx ]
"bcp Utility" (MS-SQL Server 2012)
[ https://msdn.microsoft.com/en-gb/library/ms162802(v=sql.110).aspx ]
Also, unless already set in your version of MS-SQL Server 2012 Express:
"View or Configure Remote Server Connection Options"
[ https://msdn.microsoft.com/en-us/library/ms179383(v=sql.110).aspx ]
Sorry for the copy'n'paste of external links, but I thought going straight to the "horse's mouth" may be better for your background reading:
"Import and Export Bulk Data by Using the bcp Utility (SQL Server)" (MS-SQL Server 2012)
[ https://msdn.microsoft.com/en-gb/library/aa337544(v=sql.110).aspx ]
"bcp Utility" (MS-SQL Server 2012)
[ https://msdn.microsoft.com/en-gb/library/ms162802(v=sql.110).aspx ]
Also, unless already set in your version of MS-SQL Server 2012 Express:
"View or Configure Remote Server Connection Options"
[ https://msdn.microsoft.com/en-us/library/ms179383(v=sql.110).aspx ]
Do all those files have the same data structure in them? If that is the case then it is easy:
1. Create the table with the corresponding structure where the data from the files will be imported. If you are not sure of the data types for the fields then using a generic NVARCHAR will make sure it will "swallow" anything you might encounter.
2. Create a list with all the files you have in the folder. If they are in different folders it is better to copy them in one.
3. Use the list to create the BCP commands that will take each file in the list and will import it into the table created at step 1. This can be done easily in a batch file that will be executed on any machine on the same network with the target SQL server that has BCP installed on it. Can be the SQL server itself. BCP is a command line utility that comes together with the SQL server installation and it is the fastest tool there is when it comes to importing files.
1. Create the table with the corresponding structure where the data from the files will be imported. If you are not sure of the data types for the fields then using a generic NVARCHAR will make sure it will "swallow" anything you might encounter.
2. Create a list with all the files you have in the folder. If they are in different folders it is better to copy them in one.
3. Use the list to create the BCP commands that will take each file in the list and will import it into the table created at step 1. This can be done easily in a batch file that will be executed on any machine on the same network with the target SQL server that has BCP installed on it. Can be the SQL server itself. BCP is a command line utility that comes together with the SQL server installation and it is the fastest tool there is when it comes to importing files.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@lcohan
It is better if you post any code within the code tag.
It is better if you post any code within the code tag.
This assumes that the 1000 files have exactly the same file specs.