locked
Problem importing .CSV file using SSIS RRS feed

  • Question

  • Hi All,

    I am having problems loading a comma delimited, .CSV file into SSIS. The flat file connection Manager thinks there are 13 rather than 14 columns as the final column is empty in nearly all cases I think. There is also no ‘Header row’ in the CSV file.

    The error message the flat file connection shows is "the last row in the sampled data is incomplete. The column or the row delimiter may be missing or the text is qualified incorrectly."

    If I place a zero in the last row of the file I do not receive an error message, however this is not an option in the source system generating the .CSV file.

    The final column has been set with a Column Delimiter of ‘{CR}{LF}’ rather than Comma.

    An example couple of row of data as follows;

    field1data;field2data;field3data;field4data;;;;field5data;;;;;;;
    field1data;field2data;field3data;field4data;;;;field5data;;;;;;;

    I would be really greatful if anyone could assist me in loading this file into SSIS.

    Regards,
    Chris

    Sunday, November 8, 2009 6:06 PM

Answers

  • Hi All,

    Many thanks for your suggestions.

    I resolved the issue by changing the extended properities on the Excel Connection. As default excel checks the first 8 rows of data to guess data type.

    See below link.


    http://msdn.microsoft.com/en-us/library/ms141683.aspx

    Regards,
    Chris
    Tuesday, November 10, 2009 5:27 PM

All replies

  • You have used semi-colon (;) as column delimiter.
    What is row delimiter? ; OR CR/LF
    How many valid columns are there in the file?

    Nitesh Rai- Please mark the post as answered if it answers your question
    Sunday, November 8, 2009 6:28 PM
  • You are missing a "Carriage Return" at the end of your last row in the CSV file. Just go to the end of the last row in your data file and press "Enter". This will insert a "CR/LF" character, which is the Row Identifier in your connection, and you'll see the data parsed correctly. I just tested this and found it working fine.


    Cheers!!
    M.
    Sunday, November 8, 2009 6:48 PM
  • Hi All,

    Many thanks for your suggestions.

    I resolved the issue by changing the extended properities on the Excel Connection. As default excel checks the first 8 rows of data to guess data type.

    See below link.


    http://msdn.microsoft.com/en-us/library/ms141683.aspx

    Regards,
    Chris
    Tuesday, November 10, 2009 5:27 PM