SSIS CSV failure to import

Question

Post reply

SSIS CSV failure to import

wood.kieran

SSChasing Mays

Points: 631
More actions
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.

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

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

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 1

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

wood.kieran SSChasing Mays Points: 631 More actions · Answer 2

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.

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 3

Kierian

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

Publish the exempel so we can look at it.

/Gosta

wood.kieran SSChasing Mays Points: 631 More actions · Answer 4

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.

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

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/

Garry M SSC Veteran Points: 266 More actions · Answer 6

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

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 7

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

wood.kieran SSChasing Mays Points: 631 More actions · Answer 8

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.

Garry M SSC Veteran Points: 266 More actions · Answer 9

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.

touchmeknot123 Right there with Babe Points: 735 More actions · Answer 10

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!

Dave23 Hall of Fame Points: 3903 More actions · Answer 11

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.

Scott-144766 SSCarpal Tunnel Points: 4333 More actions · Answer 12

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

--
Scott

aimtwo Grasshopper Points: 11 More actions · Answer 13

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: