none
Flat File Source Problem...

    Question

  • I have a weird thing happening.
    I have an .csv file. When I try to load it into a table, I can do it easily in DTS 2000. But when I am trying to do it in SSIS 2005 with exactly the same settings (like Text qualifier, row delimiter etc.), I am getting an error: "The last row in the sampled data is incomplete. The column or the row delimiter may be missing or the text is qualified incorrectly." I looked at the file and it looks complete to me.

    What could be the problem ?

    P.S. DTS 2000 is on 32-bit Windows, and SSIS 2005 is on 64-bit Windows 2003. Could that we a problem ?
    Wednesday, January 17, 2007 3:30 PM

All replies

  •  Victor_V wrote:
    I have a weird thing happening.
    I have an .csv file. When I try to load it into a table, I can do it easily in DTS 2000. But when I am trying to do it in SSIS 2005 with exactly the same settings (like Text qualifier, row delimiter etc.), I am getting an error: "The last row in the sampled data is incomplete. The column or the row delimiter may be missing or the text is qualified incorrectly." I looked at the file and it looks complete to me.

    What could be the problem ?

    P.S. DTS 2000 is on 32-bit Windows, and SSIS 2005 is on 64-bit Windows 2003. Could that we a problem ?


    I suppose it could be a problem, but DTS has historically been more forgiving when processing files.  Are you sure you don't have a carriage/line-feed as the last line in the file?  Sometimes this happens, and SSIS will think that is an incomplete row (It is, technically).
    Wednesday, January 17, 2007 3:50 PM
    Moderator
  • It is true that the parsing behavior in SSIS is very different from DTS 2000. DTS was very forgiving as Phil mentioned, and there could be an extra carriage/line-feed which is causing this. Also DTS used to process files even if not all columns were populated in a row. However, SSIS would bail out on such files.
    Wednesday, January 17, 2007 5:36 PM
    Moderator
  • How do I check for this extra carriage return character ? It's not really visible. What editor would I use ?

    Also, I usually get those "pipe" delimited files from Excel files, saving them as .CSV files.

    Thanks,

    Victor.

    Wednesday, January 17, 2007 6:25 PM
  •  Victor_V wrote:

    How do I check for this extra carriage return character ? It's not really visible. What editor would I use ?

    Also, I usually get those "pipe" delimited files from Excel files, saving them as .CSV files.

    Thanks,

    Victor.



    Go to the last line of the file.  Does the cursor sit at the end of a row of data, or on it's own line?  That's one way.
    Wednesday, January 17, 2007 7:13 PM
    Moderator
  • It sits on it's own line (at the beginning of a new line)..
    Wednesday, January 17, 2007 8:11 PM
  •  Victor_V wrote:
    It sits on it's own line (at the beginning of a new line)..


    Hit backspace on that line and then save the file.  Try running it again.  Or try a ragged-right format.
    Wednesday, January 17, 2007 8:13 PM
    Moderator
  • Actually, I just opened this file in UltraEdit in HEX view, and the last 2 characters in the file are "OD OA", which is the end of the line, just like at the end of every row...

    That's very strange...

    Wednesday, January 17, 2007 8:17 PM
  •  Victor_V wrote:

    Actually, I just opened this file in UltraEdit in HEX view, and the last 2 characters in the file are "OD OA", which is the end of the line, just like at the end of every row...

    That's very strange...



    That's not strange, that's the point.  SSIS sees that as a row, but there aren't any columns in it.
    Wednesday, January 17, 2007 8:19 PM
    Moderator
  • No, what strange is that those 2 characters ARE the last characters in the row (and each row), and there is nothing else there. That's how it looks like in UltraEdit HEX editor. But when I open the same file in Notepad and hit Ctrl+End to go to the end of the file, that's when the cursor goes to the new row, instead of stopping at the end of the last row.
    Wednesday, January 17, 2007 8:26 PM
  • I want to be sure on a few things...

    The format is: Delimited
    The text qualifier is: " (if needed)
    The Header row delimiter is: {CR}{LF}

    Under columns:

    The Row delimiter is: {CR}LF}
    The Column delimiter is: Comma {,}
    Wednesday, January 17, 2007 8:35 PM
    Moderator
  • The format is: Delimited
    The text qualifier is: <none>

    The Header row delimiter is: {CR}{LF}

    Under columns:

    The Row delimiter is: {CR}LF}
    The Column delimiter is: Comma {|}

    Wednesday, January 17, 2007 8:38 PM
  • Phil ? Anyone ? Any thoughts ?
    Thursday, January 18, 2007 12:59 PM
  •  Victor_V wrote:
    Phil ? Anyone ? Any thoughts ?


    Can you post a sample line from the csv file?
    Thursday, January 18, 2007 2:11 PM
    Moderator
  • Here are the last 5 rows in my .csv file:

    12|Community Network Services, Inc|MI|C|T1020-Low|Personal care services, per diem      |Blank||Per Diem||49|13461|13461|359677.92|26.72|26.72||0|13,461|13,461|0|615013.79|99.60%||0.00%|433.3|0.07%|2031.64|0.33%|617,479|0.00|45.69|0.00|0.03|0.15|45.87|24.96|20.63|33.51|22.81|29.7|same method from prior year
    12|Community Network Services, Inc|MI|C|T1020-Moderate|Personal care services, per diem      |TF||Per Diem||29|7391|7391|619809.26|83.86|83.86||0|7,391|7,391|0|490943.23|99.63%||0.00%|265.36|0.05%|1554.76|0.32%|492,763|0.00|66.42|0.00|0.04|0.21|66.67|68.16|53.58|68.53|54.88|67.43|rates reflect similar to prior years
    12|Community Network Services, Inc|MI|C|T2003|Non Emergency Transportation|||Trip||2|148|148||0.00|0.00||0|148|148|0|544.16|100.00%||0.00%||0.00%||0.00%|544|0.00|3.68|0.00|0.00|0.00|3.68|0|7.99|0|7.92|0|
    12|Community Network Services, Inc|MI||GF Pharmacy|GF Pharmacy||||||||||||||||||||||||753,848||||||||||||
    12|Community Network Services, Inc|MI||H0038|Drop-in Center||||||||||||||||||||||||447744.18||||||||||||

    Thanks, Phil.

    Thursday, January 18, 2007 2:17 PM
  • Take a look here to see if we match. It worked for me.

    http://ssistalk.blogspot.com/2007/01/this-post-is-in-reference-to-thread.html
    Thursday, January 18, 2007 3:59 PM
    Moderator
  • I had this problem also in SSIS (SQL 2005) when I tried to change my destination text file from comma delimited to tab delimited.  The error message was:

     

    "The last row in the sampled data is incomplete. The column or the row delimiter may be missing or the text is qualified incorrectly."

     

    I fixed this by deleting the existing text files on the pc (that were created last time I ran the package) and then changing the Connection Managers for the text files to tab delimited then.  No error message came up.

     

    Thursday, November 01, 2007 11:38 PM
  •  

    Also if you open the file in excel, excel will autocorrect and may add a line.  Then it will appear that you have a record with no columns.  When working with files in SSIS I have found that if you open them up and save them else where you will have problems.
    Monday, February 04, 2008 5:37 PM
  • Just make sure you are having correct TextQualifier value set in the Flat File Source connection.  typically is should be None
    Wednesday, April 18, 2012 8:06 AM
  • I struggled for ages with this, and ended up installing Notepad++ so I could see all the hidden characters.

    My problem turned out to be - the last row stopped half way along - then terminated correctly with carriage-return and line-feed.

    The fix (for my csv file) was to add a bunch of commas to the last-line (just before the carriage-return and line-feed), to build up the correct number of columns.

    Wednesday, January 02, 2013 2:12 PM