0

How can I import a comma delimited text file with the following example record: abc, def and ghi,jkl

such that I get the output as column1: abc, def and ghi column2: jkl

There is no text qualifier surrounding (abc, def and ghi), and if the comma and space (, ) is specified as the text qualifier in sql server import, it does not produce the above result.

user2302300
  • 1

1 Answers1

0

You could try the Bulk Insert MSDN: Bulk Insert

    USE tempdb;
    GO

    IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test;
    CREATE TABLE #test
    (
     col1 VARCHAR(50)
    ,col2 VARCHAR(50)
    )

    BULK INSERT #test
    From 'C:\Users\Brian\Desktop\test.csv'
    WITH
    (
     FIRSTROW = 2
    ,FIELDTERMINATOR = '|'
    ,ROWTERMINATOR = '\n'
    )

    Select  * 
    From    #test
    IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test;

Below is the layout of my csv file. Would it be possible to use pipe delimited or some other kind of delimiter like ™...My favorite ;)

col1|col2

abc, def and ghi| jkl

And the results of the import.

               col1                 col2
               abc, def and ghi     jkl
Brian Boyd
  • 272
  • 3
  • 15
  • Note that the OP needs the `,` embedded inside field1 to not be treated as a separator, so I am not sure your solution is going to work out of the box. – iruvar Apr 20 '13 at 17:18