3

In an extension to this question: Import Multiple CSV Files to SQL Server from a Folder

As the question states, I want to import multiple tables from CSV into SQL server from a folder. But the thing is in my case, is that I can have up to 17 different tables, that all have different layouts. Some have 2 columns, while others have 10 for instance.

Is it possible to create a script that takes into account the different tables setup based on the name of the CSV file, and import it correct if the table actually exist in the folder?

The good thing is that the CSV files never change names/layout. But not all 17 tables are used in every project.

I want the rough code along with proper comments so that I understand it.

Someone do please help me out on this one. Thanks a lot in advance :)

Community
  • 1
  • 1
KhalidN
  • 385
  • 1
  • 7
  • 13
  • If you want to bulk insert those csv's only when the destination table actually exists, just wrap it using http://stackoverflow.com/a/167680/509464 – Michiel Cornille Sep 30 '15 at 09:48
  • It is not so much the destination table that should be checked if it exists, but more if there is a CSV file in the folder that should be imported. And thereby create a new table based on the existing CSV file – KhalidN Sep 30 '15 at 09:51
  • Then try wrapping it in : http://stackoverflow.com/questions/11740000/check-for-file-exists-or-not-in-sql-server ... but ... I think you are trying to over-automate things here. How many projects are we talking about? Isn't it easier to just provide a script for every project? It looks to me like you need to look for a (deployment?) tool that does the heavy lifting for you. – Michiel Cornille Sep 30 '15 at 09:56
  • I might be over automating things. But I am not that aware of it, as I am not that experienced with SQL. My situation is that I will get a bunch of csv from an external source, sometimes I get 10 files, other 17 files, but the names never change on the files. As they all have different structures, it is a very tiredsome procedure to import every csv file into a table each. That is why I want to automate it so I can put the files into a folder, I execute a SQL script that checks what files are in the folder, and then imports them according to their respective structures @Mvision – KhalidN Sep 30 '15 at 10:04
  • I would be tempted to look into powershell for this. You may well be able to do it just using sql, but it doesn't mean that you should. – Alex Ayscough Sep 30 '15 at 11:05
  • How would it work with powershell? I am not familiar with powershell I must admit @AlexAyscough – KhalidN Sep 30 '15 at 11:18

1 Answers1

-2

This is a perfect situation for using SQL Server Integration Services (SSIS), provided you have it available in your working environment. (I will assume you do, or can get it installed.)

SSIS is a huge platform in its own right so I cannot explain everything about how to use it here. Some good places to start learning about it are:

There are plenty of books and other websites available too.

Broadly speaking, you first need to create an SSIS "package", which is a file of .dtsx extension. You create it on your local PC using Microsoft Visual Studio or Microsoft SQL Server Data Tools. It has a reasonably user-friendly graphical interface.

Within this package you will need to configure a data source for each of the 17 possible files, then "map" them to the corresponding tables in your target SQL database.

Provided you set it up appropriately, the package will work gracefully if some of the files don't actually exist - it will load whichever are present. This is the crux of why SSIS is a good option in your use-case.

After creating the package, there are multiple ways to use/deploy it. Based on your comments and question, I think the best way for you might be:

  1. Upload it to an SSIS Package Store on your SQL Server (using SSMS Object Explorer, connecting to an SSIS instance rather than a SQL Server instance). You'll need the SSIS components installed on the SQL Server platform for this.
  2. Create a SQL Job via SQL Agent, with a step that triggers the SSIS package. You might also want steps before or after that step, to run T-SQL code that prepares the SQL tables you will load data into, or does things to the data after loading (such as cleaning and validating it).
  3. Rather than running a SQL script to set this whole process off, you instead trigger the job to run, either manually (via SQL Agent again) or using the built-in scheduling facilities (e.g. it could run every night at 6pm).

Packages can also be saved as a kind of standalone executable, in any folder location, and run from within Windows with a double-click (without needing to open Visual Studio/SSDT again).

Stuart J Cuthbertson
  • 390
  • 5
  • 13