Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Mar 2003
    Posts
    97

    Unanswered: CSV bulk insert with headers

    Hello,

    I have a .txt as such:

    fname,lname,email
    "Kelly","Reynold","kelly@reynold.com"
    "John","Smith","bill@smith.com"
    "Sara","Parker","sara@parker.com"

    When I run this:

    BULK INSERT TmpStList FROM 'D:\files\TxtFile1.txt' WITH (FIELDTERMINATOR = '","', FIRSTROW=2, ROWTERMINATOR = '\r')

    I only get the first 2 rows imported:

    "John Smith bill@smith.com"
    "Sara Parker sara@parker.com"

    How can I make it start the import on the second row?

    TIA

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Play around with the FIRSTROW option
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2003
    Posts
    97
    George,

    I've done that without avail.

    I think the issue is that the headers (first row) aren't enclosed in double quotes, so it's throwing things off. Is there a way to work around this? The file structure cannot be modified.

    Thanks

  4. #4
    Join Date
    Mar 2003
    Posts
    97
    Anyone else?

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What data gets loaded if you leave out the FIRSTROW option entirely?
    George
    Home | Blog

  6. #6
    Join Date
    Mar 2003
    Posts
    97
    I get a truncation error, since I believe MS SQL is trying to import the entire first row into the first column. This is because the first row does not use the "," field terminator.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    CREATE TABLE #TmpStList (
       fname varchar(50)
     , lname varchar(50)
     , email varchar(50)
    );
    
    BULK
    INSERT #TmpStList
    FROM   'C:\TxtFile1.txt'
    WITH   (
             FORMATFILE = 'C:\formatfile.txt'
           , FIRSTROW = 2
           )
    ;
    -- Format file definition
    /*
    9.0
    3
    1       SQLCHAR       0       100     ","      1     fname         ""
    2       SQLCHAR       0       100     ","      2     lname         ""
    3       SQLCHAR       0       100     "\r\n"   3     email         ""
    */
    
    SELECT *
    FROM   #TmpStList
    
    -- Strip out the quotes (i.e. remove first and last character)
    -- You might want to think about batching this up if you have a big file to laod
    UPDATE #TmpStList
    SET    fname = SubString(fname, 2, Len(fname) - 2)
         , lname = SubString(lname, 2, Len(lname) - 2)
         , email = SubString(email, 2, Len(email) - 2)
    ;
    
    SELECT *
    FROM   #TmpStList
    ;
    
    GO
    DROP TABLE #TmpStList
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Bad news. The manual for BULKINSERT has this to say:
    Quote Originally Posted by Microsoft
    The FIRSTROW attribute is not intended to skip column headers. Skipping headers is not supported by the BULK INSERT statement. When skipping rows, the SQL Server Database Engine looks only at the field terminators, and does not validate the data in the fields of skipped rows.
    Does this need to be done at the SQL layer, or can this be done with BCP from a SQLAgent job?

  9. #9
    Join Date
    Mar 2003
    Posts
    97
    I can't use a format file because the columns are dynamic (the table structure can change at any time).

    Since this is for an import utility accessed through a web page I'm afraid it has to be done at the MS SQL layer, unless I'm missing something.

    Thoughts?

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Well, if the import does not have to be done synchronously, then you may be able to get away with populating some metadata from the page, then letting a SQLAgent job come along later, and do the actual import.

  11. #11
    Join Date
    Mar 2003
    Posts
    97
    It does have to be done synchronously (user selects file from its hard drive, click import, import code runs, returns message back to user).

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why are you allowing users to use incorrectly formatted files?
    You could create the format file on the fly too?
    George
    Home | Blog

  13. #13
    Join Date
    Mar 2003
    Posts
    97
    I'm not allowing it, the client is imposing it, unfortunately.

    I'm trying my best to avoid creating a format file on the fly as that will significantly add more dev hours, etc.

    I'm amazed at how inflexible BULK INSERT is. I guess it's not possible to have column headers without double quotes while the data values are wrapped in double quotes?

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Have you considered using SSIS? It it made to handle "Delimited CSV" files like this more easily.

    Even the SSMS "Import File" (which uses SSIS under the covers) does this pretty well.

    Using BULK INSERT to do this kind of work seems like a poor choice of tools to me. I'm impressed that a BULK INSERT tool handles a Delimited CSV or an XML file at all.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SSIS would not (easily) import files of indeterminate schema.

    What he may have to do is load the data into a staging table with each record being a single column, and then parse the columns from there.

    The business requirements are unreasonable, and that is why you have to hack your way around this. "Build me a process that will import any file a user points to". Really?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

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