Post reply

SSIS CSV failure to import

  • March 11, 2009 at 5:49 am

    #131607

    Hi,

    I have a CSV file which has been generated from an Excel Spreadsheet. I have various problems importing this CSV file via a Flat File Source Data Flow Component. The first problem is that when I preview the data in the Flat File Connection Manager Editor the last column shows two squares in the data value. I assume this means that the Data Flow Component has not been able to interpret the row correctly.

    I have set the following options; -

    Format = Delimited,

    Text qualifier = "

    Header row delimiter = {CR}{LF}

    Header rows to skip: 0

    Column names in the first row.

    Kieran.

    Any help would be greatly appreciated.

  • March 12, 2009 at 5:49 am

    #958216

    Kieran

    "I have a CSV file which has been generated from an Excel Spreadsheet."

    Why not go around the problem and save the Exel Spreadsheet as a TAB

    separated text file and use SSIS or even better (will always work) bulk insert.

    Gosta

  • March 12, 2009 at 9:37 am

    #958441

    I've just exported the spreadsheet as a tab delimited file which contained a single record.

    I then changed the File Connection Data flow / Connection Manager to specify the tab delimiter.

    When I previewed the file within the File Connection Data flow preview the exactly the same happened, e.g. there where 2 squares in the last column.

    I also noticed that when I populated all of the columns in the file whether it be comma separated or tab separated. The extract appeared in the File Connection Data flow preview OK.

    Any further suggestions would be greatly appreciated.

    Thanks,

    Kieran.

  • March 12, 2009 at 12:18 pm

    #958589

    Kierian

    Thsi is strange. Du you have an example of the file?

    Publish the exempel so we can look at it.

    /Gosta

  • March 13, 2009 at 5:22 am

    #958999

    OK, Thanks again for your response. I need to annonimize the column names and data with everything else I have on this may take me a week.

    Thanks again for your input.

    Kieran.

  • March 13, 2009 at 7:48 am

    #959107

    wood.kieran (3/11/2009)

    Hi,

    I have a CSV file which has been generated from an Excel Spreadsheet. I have various problems importing this CSV file via a Flat File Source Data Flow Component. The first problem is that when I preview the data in the Flat File Connection Manager Editor the last column shows two squares in the data value. I assume this means that the Data Flow Component has not been able to interpret the row correctly.

    I have set the following options; -

    Format = Delimited,

    Text qualifier = "

    Header row delimiter = {CR}{LF}

    Header rows to skip: 0

    Column names in the first row.

    Kieran.

    Any help would be greatly appreciated.

    The squares I think means the column contains non-printable characters. When you import do you get an error ? You may try to filter these column values.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • March 13, 2009 at 8:11 am

    #959122

    If you open up your csv in a hex editor, you'll be able to see what the characters are, then you perhaps have a couple of options.

    1)Change your column to a column type that supports the chars

    2)Take the columns into a component that removes them; perhaps a derived column and an expression or a script component.

    Garry

  • March 13, 2009 at 8:21 am

    #959135

    You say that the file is exported from Excel. Do you see any strange charaters in a

    cell in Excel? Clear the content and try again. If strange characters what is the reason for that? Avoid the problem in the source if possible.

    /Gosta

  • March 13, 2009 at 8:32 am

    #959147

    I've opened the csv file in notepad and not noticed anything strange characters there. To be honest I don't think there is a problem with rouge characters in the source file. I think it's the way SSIS handles null values in very wide csv files generated by Excel.

    To prove this I populated all the fields with values from the Excel file, generated the csv file and the sqaures disappeared when I previewed in csv. I repeated this process and removed the value from the last column and the sqaures re-appeared.

    Just to clarify further can you recommend a hex editor. I havn't used one since my software engineering days.

  • March 13, 2009 at 8:37 am

    #959152

    I use Hex Editor Neo, I only use it rarley for instances such as this where I want to "see" hidden characters. Not sure if it is any good or not but is fine for this type of thing.

  • July 21, 2009 at 4:20 pm

    #1027991

    Hi,

    Can you help me out.

    I have the same issue, I see square characters in the preview of the file.

    How did you resolve the problem ?

    Thanks in advance!

  • July 22, 2009 at 7:19 am

    #1028292

    For flat file viewing, especially files of over 4Gb in size, I use and recommend the "V" viewer written by Charles Prineas. It is not an editor, but it will view ASCII and EBCDIC text files in a number of different modes, including Hex. It's shareware. CSVed by Sam Franke is also a useful tool for manipulating and viewing CSV files in particular.

  • July 24, 2009 at 5:02 am

    #1029556

    They are probably carriage return/line feed characters. Have you got the header row delimiter set to {CR}{LF} in the connection manager?

    --
    Scott

  • January 22, 2011 at 2:48 pm

    #1276926

    My example failure data

    "aaa",1.00,"AAA"{LF}

    "bbb",1.00,"BBB"{LF}

    "ccc",1.00,"CCC"{LF}

    1. Use HEX Edit (I using UltraEdit) find the line feed {LF} (HEX code is 0A)

    2. Replace HEX 0A to 0D 0A

    Completed.:w00t:

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply