Importing Multiple .csv files into one database from network shared drive

Question

Importing Multiple .csv files into one database from network shared drive

wenger.noah

Mr or Mrs. 500

Points: 589
More actions
November 4, 2016 at 8:09 am

#318620

Hi,
I have a network share folder containing 99 .csv files, all with different headers and all with quoted identifiers which I want to load into a SQL Server 2012 database. I want to create a batch script (or something of that nature) that will load all 99 files into one database creating 99 tables using the file names as the table names.
I would like to import the files via a single script via command shell, SSIS, import/export wizard, the bcp command utility or any other method. I have all the necessary permissions.
Thank you.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76997 More actions · Answer 1

I don't see a question here. If you're asking what the best approach to use is, it really depends a lot on what skills you have.

Personally, I like SSIS, because you can have multiple connections and load multiple files at the same time, but the learning curve for SSIS is VERY steep.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

wenger.noah Mr or Mrs. 500 Points: 589 More actions · Answer 2

My question is how do I, in one script/package/execution, load the 99 .csv files into a SQL Server 2012 database which is already created but contains no tables, create 99 tables naming each table after the name of the file loaded given that each of the .csv files have different header rows and each of the .csv files have quoted identifiers?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

If you don't have the tables, you could use the import/export wizard. It will create the tables. Just be aware that the data types might not be the best choice.

I'm not sure if BIML can create tables automatically, you could check that option.

My suggestion is to avoid shortcuts and create a proper process if this is something you'll be doing multiple times. This will take longer, but will give you less headaches later on.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 4

wenger.noah (11/4/2016)
Hi,
I have a network share folder containing 99 .csv files, all with different headers and all with quoted identifiers which I want to load into a SQL Server 2012 database. I want to create a batch script (or something of that nature) that will load all 99 files into one database creating 99 tables using the file names as the table names.
I would like to import the files via a single script via command shell, SSIS, import/export wizard, the bcp command utility or any other method. I have all the necessary permissions.
Thank you.

Quick question, is this a one time operation or will you have different number of files and formats at any stage?

😎

It is straight forward to use various tools for the job and there are quite few available but all of those have pros and cons. A single script might not be the best option, a collection of scripts called by a format identifier is possibly better as it will reduce the complexity of each individual component. Another approach is to import everything into a single datatype agnostic table and process from there which is in a way almost an industry standard approach when dealing with multiple file and document types using a single load process.