BULK INSERT csv data file with quotations

Posted on 2007-09-28
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.

Question by:Keyman
    LVL 8

    Expert Comment

    BULK INSERT tablename
       FROM 'c:\filename.csv'
             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

    Author Comment

    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.
    LVL 68

    Accepted Solution

    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:

    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

    - 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)

    Author Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now