locked
Importing CSV Errors RRS feed

  • Question

  • I have spent the last full two days on this.

    All I am trying to do is import a csv into a new table.

    Once I have this working, I'll be import a 260 million row file and then smaller files each week thereafter.

    I have 7 fields out of about 60, where my source csv data looks similar to this:

    "<LoremIpsum LOR=""/All AllLorems/IpSum Lorems/Lorem Espilon/LOREM/1.23.4.5-5.43.2 (PICNIC)"" ID=""M-kdfkgj8459jdfskgf==""/>"

    I have tried using: double quote ("),and a pair of the double quotes (""), and nothing. I always get an:

    - Executing (Error)
    Messages
    Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Lorem_Ipsum" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
     (SQL Server Import and Export Wizard)
     
    Error 0xc020902a: Data Flow Task 1: The "Source - Test export.csv.Outputs[Flat File Source Output].Columns[Lorem_Ipsum]" failed because truncation occurred, and the truncation row disposition on "Source - Test export.csv.Outputs[Flat File Source Output].Columns[Lorem_Ipsum]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
     (SQL Server Import and Export Wizard)

    I am using MS SQL Server management Studio and SQl Server 2016.

    I am using the' flat file' data source for the source, and have tried both the:

    'Microsoft OLE DB provider' for SQL Server and the 'SQL Server native Client 11.0' as the destination.

    I did see a menu yesterday for 'Convert' and 'On Error' but today, I can't get the Management Studio to provide those options.

    I don't think i want to ignore the truncation error, but it seems odd that I don't have the same options today as i had yesterday. I've created new tables to see if it was a problem with using the same data file source and destination table, but that wasn't it.

    Asking the vendor to change their delimiter is not an options as it is COTS.

    Any help guidance, whatever that will help me import this data sanely will be quite appreciated.

    Thank-You.

    Friday, March 17, 2017 4:31 PM

Answers

  • You really should use an SSIS package for this. You can have the erroring rows directed to another table where they can be pooled so you can fix them at a later date. With these emedded double quotes you will find it difficult to import this csv.

    Another option would be to preparse your csv with a powershell or C# to fix errors like this.

    Friday, March 17, 2017 4:58 PM
  • Hi Bruce D.Meyer,

    After locating the flat file in the SSMS SQL Server Import and Export wizard , set text qualifier to "  and  change the error columns data type to Text Stream (DT_Text).

    Let me know if you still find any issues.

    Please Mark this reply as answer if it has solved your issue. And please use Vote As Helpful if my post was useful.


    • Edited by TWoW2017 Friday, March 17, 2017 6:03 PM
    • Marked as answer by Bruce D. Meyer Monday, March 20, 2017 12:38 PM
    Friday, March 17, 2017 6:02 PM

All replies

  • You really should use an SSIS package for this. You can have the erroring rows directed to another table where they can be pooled so you can fix them at a later date. With these emedded double quotes you will find it difficult to import this csv.

    Another option would be to preparse your csv with a powershell or C# to fix errors like this.

    Friday, March 17, 2017 4:58 PM
  • I had considered Powershell. I was hoping perhaps there was a tool that would handle this. I am unfamiliar with SSIS, I don't seem to have that installed on my machine. Hopefully it's part of the Management Studio. Thank for the quick response.
    Friday, March 17, 2017 5:04 PM
  • Check data in the column "Lorem_Ipsum" and see if there are some double quotes in it.

    A Fan of SSIS, SSRS and SSAS

    Friday, March 17, 2017 5:29 PM
    Answerer
  • Yes. That's the problem. It's like two scoops of Raisin Bran, except they are double quotes.

    There's 8 of them, in that field, I believe they are escaping the double quote with a double quote on the doubled, double ... oh never mind..

    :-)

    I think I'll go the Powershell route.

    Thank everyone!

    Friday, March 17, 2017 6:01 PM
  • Hi Bruce D.Meyer,

    After locating the flat file in the SSMS SQL Server Import and Export wizard , set text qualifier to "  and  change the error columns data type to Text Stream (DT_Text).

    Let me know if you still find any issues.

    Please Mark this reply as answer if it has solved your issue. And please use Vote As Helpful if my post was useful.


    • Edited by TWoW2017 Friday, March 17, 2017 6:03 PM
    • Marked as answer by Bruce D. Meyer Monday, March 20, 2017 12:38 PM
    Friday, March 17, 2017 6:02 PM
  • To test this and rule out 'other stuff' I made a 10 row version of my csv file.

    I chose the 'text stream' [DT_TEXT] field type, and that worked perfectly on the 10 rows.

    I then repeated this with a 3 GB csv from which the 10 row sample came from.

    This Worked! YAY!

    Here is a change I did. Whereas before I was diligently entering the field size (like chars, 23 chars etc, this time I only did this for the few fields that actually exceeded 50 chars in a field.I am switching (if that is an option) this response as the correct answer, as I have a successful import and no need to code a script to prepare my input csv.

    Thanks for sharing your knowledge.

    -= Bruce D. Meyer

    Monday, March 20, 2017 12:37 PM
  • Thanks it works for me
    Saturday, October 12, 2019 10:42 AM