0

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.

Joshua
  • 40,822
  • 8
  • 72
  • 132
ErickTreetops
  • 3,189
  • 4
  • 27
  • 37
  • 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 Sep 24 '19 at 07:29
  • IMO the best way to create a custom solution by using SQLCLR. –  Sep 24 '19 at 08:37
  • 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: https://johnnycode.com/2013/08/19/using-c-sharp-sqlbulkcopy-to-import-csv-data-sql-server/ – Steve Ford Sep 24 '19 at 09:09
  • 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 Sep 24 '19 at 09:49

1 Answers1

2

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.

miken32
  • 42,008
  • 16
  • 111
  • 154
ErickTreetops
  • 3,189
  • 4
  • 27
  • 37
  • Providing an explanation of the format file syntax (or even a link to such an explanation) would make this answer more helpful for future visitors. – miken32 Jun 10 '22 at 15:08