Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Posts
    74

    Unanswered: Bulk insert from a .csv file

    Hi folks,

    I have a small problem - I am unable to load data from a .csv file into a table in SQL Server. Here is the command I am running:
    BULK INSERT CCSProgramParticipation FROM 'c:\test.csv'
    WITH (
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
    )

    Data in test.csv is the following format: (date fields can be blank)
    NY580232,0,6/30/2006,3567,396,7/1/2005,9/9/2005
    NY580232,0,6/30/2006,14850,462,12/12/2005,
    ....
    ....

    What I see is the data does get loaded; however, data from the following row is getting inserted in the last field of a particular row (previous row) - it seems like the rowterminator is being ignored.

    Has anyone encountered this issue? Please let me know your thoughts on this.

    Thanks so much!

    -Parul

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Try inserting ROWTERMINATOR ='\r\n'
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Oct 2002
    Posts
    74
    I have the following command now:

    BULK INSERT CCSTest FROM 'c:\test.csv'
    WITH (
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR ='\r\n'
    )

    But this is erroring out with the following message:
    Bulk Insert fails. Column is too long in the data file for row 1, column 7. Make sure the field terminator and row terminator are specified correctly.

    I am not sure why...

    Thanks!

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    because exactly what the message says...your column is too small for the data...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Oct 2002
    Posts
    74
    I understand, but I am not sure why it is erroring out in the first place - the fields are large enough for the data that they are supposed to hold.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How can you be so sure?

    Wht not DTS the file to a staging table and see what it builds, or import it into access

    In any event, trying to troubleshoot this without more details is difficult.

    Read the hint sticky at the top of the forum and post what it asks for

    You may need to attach a sample load file as well
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Oct 2002
    Posts
    74
    Here is the file attached...as you try to load this file you will see that some rows are getting clubbed into a single field in the row preceding them...
    I really think the file has invalid characters in the last column (date).
    Attached Files Attached Files

Posting Permissions

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