Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2008
    Posts
    2

    Unanswered: Importing into Sql Server2000 using DTS from CSV file

    Hello Everybody,
    I am importing an csv file into Sql Server straight forward no data cleansing or anything and i noticed everyday it misses the last row in the CSV file.
    Has anybody come accross this if so is there anything that i can do to get that row.

    ANY HELP ON THIS IS GREATLY APPRECIATED.


    Thanks In advance....

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Not that I've ever encountered.

    Have you physically looked at the spreadsheet to see if something is amiss in that last row? Is it greater than 65535 rows?

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd be willing to bet that all you need is to add a "newline" at the end of the CSV file. Neither BCP nor BULK INSERT will import a text line that doesn't end with a CRLF pair to mark the end of the line.

    -PatP

  4. #4
    Join Date
    Feb 2008
    Posts
    2
    Pat..Should i be adding something in the csv at the end of the file,if so can you please let me know what would be that.

    I am doing BULK INSERT it works but...data comes into the table with double quotes..Like this.

    fname lname mnam city country
    "test","test","test","NY","US"

    What should i do so that those "" doesnt come into the table.

    Thanks Very Much!

  5. #5
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Have you tried this without DTS? Just a basic BULK INSERT?

    I have just tried this both ways and have no issues with extra quotes, or missing lines. (Even without an extra CR/LF at the end of the file.) Can you post a screen shot of the DTS Bulk Insert options you're using, or your T-SQL?

    Thanks.

    -D.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you are creating the CSV file, just add a CRLF (in SQL that would be Char(13) + Char(10) which is a carriage-return character followed by a linefeed) at the end of the file.

    My solution would be:

    1) create a DTS package that does the CSV import for you
    2) Make that DTS package part of a job
    3) Schedule the job as needed, or start it via a stored procedure call.

    You can certainly adjust the way that the CSV file is built to address the missing CRLF at the end, then build a FMT File to manage the quotes around the CHAR columns. That's just way more work, and I'm lazy!

    -PatP

Posting Permissions

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