Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2009
    Posts
    4

    Unanswered: Question regarding bulk insert with format file

    I have a test.csv file which containing the following:

    ID,NAME,PHONE
    1234,"MICKY, MOUSE",4258893111
    1235,"TEST, TEST",4255554646
    1236,"JOHN, DOE",1258887777

    I'm using the tst.txt file as the format file:

    8.0
    3
    1 SQLCHAR 0 6 ",\"" 1 empid SQL_Latin1_General_Cp437_BIN
    2 SQLCHAR 0 50 "\"," 2 name SQL_Latin1_General_Cp437_BIN
    3 SQLCHAR 0 10 "\r\n" 3 phone SQL_Latin1_General_Cp437_BIN

    Now when I ran the following command in sql query analyzer:

    BULK INSERT test
    FROM 'c:\TEst.csv'
    WITH
    (
    FIRSTROW = 2,
    FORMATFILE = 'c:\tst.txt'
    )

    Only the last two rows got inserted. The first row below the header was ignored.

    I've tried with different files, it all gave me the same result. Anybody seen this before? What am I missing?

    Thanks.

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    FIRSTROW=1 ? did you try that ?

  3. #3
    Join Date
    Feb 2009
    Posts
    4
    Yes. Then I got column mismatch error.

    If I change column header Name to "Name", then everythign works fine.

    So using format file, the bulk insert utility will read the column header and first row as line 1.

    Is there a work around for this?

    thanks.

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    BULK INSERT [test]
    FROM 'c:\TEst.csv'
    WITH (FIRSTROW = 2, FIELDTERMINATOR = ',')

    Did you try this instead of format file

  5. #5
    Join Date
    Feb 2009
    Posts
    4
    Yes. If I don't use a format file, then the double quote field won't be inserted correctly.
    Last edited by JustLearnSQL; 02-05-09 at 15:40.

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    did you try with BCP from xp_cmdshell or command line

    BCP databasename..test in c:\test.txt -T -q -t, -c -Sservername -F2

    may also have to add -r \n option too.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •