?
Solved

BULK INSERT csv data file with quotations

Posted on 2007-09-28
4
Medium Priority
?
5,188 Views
Last Modified: 2012-12-03
Is it possible to import data from a CSV file using BULK INSERT and removing quotation marks around the data?

We have a application that exports to a cvs and I have no control over the formatting. Every data item is encased in quotaion marks and this is making it difficult to import the data using a stored procedure.

I realize that I can use DTS but I already have 12 different sprocs that import data and I don't really want to take take this one and only and "de-centralize" (so to speak) how data is imported into my database.

Thanks
0
Comment
Question by:Keyman
  • 2
4 Comments
 
LVL 8

Expert Comment

by:Julianva
ID: 19981332
BULK INSERT tablename
   FROM 'c:\filename.csv'
   WITH
      (
         FIELDTERMINATOR =','
         ROWTERMINATOR ='\n'
      )

this will import the csv file

open file in excel then find quotation marks and replace with nothing then save an import the file
0
 

Author Comment

by:Keyman
ID: 19981749
Well that certainly works but that is not exactly what I am looking for.

The csv is output automatically and I don't want to do any manual intervention with the file.

I am trying to keep everything as central as possible because I have so many different imports going on. I could have imported information in many different ways for each of the variouos datasources I must gather information from. Each one of the datasources may have had a more optimal way to get the data but I chose to use stored procedures that I call from Job in the sql server.

It is very centralized and calls for no manual intervention. It just makes things easier to troubleshoot when something goes wrong and requires someone less knowlegable than myself to potentially fix a problem when it happens.

That long winded story being said...I have to do the whole thing in a stored procedure.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 19994781
You have to use a format file, which is not fun or easy to get working, but once you do they usually work well.

The trick is that you have to define the delimiters to include the quotes; that way SQL will not treat them as data when loading the table.

>> Every data item is encased in quotaion marks <<

I'll take it, then, that *every* value on the line has (single) quotes around it with a comma in between each **and no extra spaces** (sadly, delimiters must be *exact*, and spaces are *not* ignored).

In Notepad or whatever text editor you prefer, create a file similar to the following:

8.0
5
1       SQLCHAR       0       100     "','"     1     FieldName1        SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       100     "','"     2     FieldName2        SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     "','"     3     FieldName3        SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       100     "','"     4     FieldName4        SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR       0       100     "'\r\n" 5     LastField5          SQL_Latin1_General_CP1_CI_AS


Where:
- the first line is the SQL version (7.0=SQL7; 8.0=2000; 9.0=2005)
- the second line is the total number of fields
- the rest of the values are the fields themselves (of course), with:
    column 4 = max length of data
    column 5 = delimiter between columns; for quoted values other than the last one, it is ',' (quote comma quote, enclosed in dbl quotes because that's the way a format file is constructed); for the last value, it is a single quote followed by new line (typically)

Again, it can be frustrating to get the format right so that SQL accepts it, so be patient when testing :-) !

Oh, yeah, the full load command then looks something like:

BULK INSERT tableName
FROM 'c:\full\path\to\data\file\datafile.csv'
WITH (FORMATFILE = 'c:\full\path\to\format\file\like\above\formatfile.txt')
--Naturally you can specify , ...other_with_options after the WITH EXCEPT for options
--FIELDTERMINATOR and ROWTERMINATOR (naturally, since those have already been defined in the format file)
0
 

Author Comment

by:Keyman
ID: 19995816
Thanks
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question