Automate the Import of CSV file into SQL Server

Question

I am trying to import a number of different csv files into a SQL Server 2008R2 database

The data in the files is comma delimited. I have no say over the file format.

Some columns are text and are delimited with double quotes ("like in excel"). Those columns contain text that may have additional commas within the text ("However, it drives me crazy")..

I tried to use Bulk Insert to loaded the text files into a number of SQL tables. However, the embedded commas in the text columns cause it to crash. SQL Server 2017 includes the option to set FORMAT =CSV and FIELDQUOTE = '"' but I am stuck with SQL Server 2008R2.

I could use DTS/SSIS but it links a VS version to a SQL version. So if I write the SSIS in VS2012 or VS2010 it may not work with our SQL Server 2008R2. And I don't' think we have any VS2008 laying around.

So what is the next best way to import these CSV files.

Bulk upload is the cleanest method to uploading half a dozen different csv files into different tables.

Do I pre-process the csv files and replace commas with pipes. If so how do I know which commas to replace (Regex?)? Or do I do the entire importation in .Net? Which is messy and Time consuming.

SSIS packages created in different versions of VS seldom do not open in different versions, however a newer version of Visual Studio should work with an older database version. that should not be a problem. I would suggest to atleast try making a test package in VS2012 connecting to the DB and writing some sample data in file to verify. it won't take too much of your time. — Sudipta Mondal
IMO the best way to create a custom solution by using SQLCLR. — user11380812
If you are comfortable using C# then I would consider writing a program to read the csv file and use SQLBulkCopy to insert into the database: johnnycode.com/2013/08/19/… — Steve Ford
SQL Server is very bad at handling RFC4180-compliant CSV files. It took ten years for Microsoft to get CSV export working correctly in SSRS, for example. I'm with DarkoMartinovic and SteveFord - use SQL CLR or a C# client program using SQLBulkCopy. — AlwaysLearning
Does this answer your question? Import CSV file into SQL Server — miken32

ErickTreetops ErickTreetops · Accepted Answer · 2019-09-27T07:17:57

All you need is a SQL format file. And although there are a few links on how to use a format file I only found one which explained how it worked properly including text fields with commas in them.

Here is the syntax to use in the sql script

BULK INSERT raw.routes FROM N'C:\WhereMyTextFileis\Import.txt'  
WITH ( 
    FORMATFILE = N'C:\WhereMyFormatFileIS\MyFormat.fmt'
    ,FIRSTROW = 2

)

and here are the contents of my format file

10.0
8
1       SQLCHAR             0       50      ","         1     RouteID            ""
2       SQLCHAR             0       50      ",\""       2     RouteShortName     Latin1_General_CI_AS
3       SQLCHAR             0       100      "\","      3     RouteLongName      Latin1_General_CI_AS
4       SQLCHAR             0       200      ","        4     RouteDescription  ""
5       SQLCHAR             0       50      ","         5     RouteType         ""
6       SQLCHAR             0       2000      ","       6     RouteURL          ""
7       SQLCHAR             0       100      ","        7     RouteColour       ""
8       SQLCHAR             0       100      "\r\n"     8     RouteTextColour   ""

The weird looking ",""" is to remove the double quotes at the start of my quoted text column RouteShortName and the ""," removes the quotes at the end of the quoted text column RouteShortName. But the important point is that the commas are kept in the column data contents.

Automate the Import of CSV file into SQL Server

1 Answers