CODEWITHSUNDEEP
ssis
kumar
kumar

Reputation:

SSIS CSV File load to table

I have a problem loading the .CSV file as the connection manager editor settings are out of my knowledge.

When i load the .CSV file up to 18 rows i have no problem it is loading in to the table.

However, from the 19th column the data is not partioning correctly.

row delimeter is {CR}{LF} column delimeter is Comma {,}

How can i partition the data correctly?

any help?

Upvotes: 0

Views: 1294

Answers (5)

kumar
kumar

Reputation:

Nothing unusuale. when i paste in excel as one column and converting text to column has no problem. but i can see in the SSIS preview the field value where the problem has started has two square boxs and data of the next row. if any one want to see the file let me know i will e-mail you the file.

Upvotes: 0

Sam
Sam

Reputation: 7678

Put the file in a text editor such as notepad++ or textpad and change the view to show control characters. You will probably find your culprit there.

Upvotes: 0

MartW
MartW

Reputation: 12538

If it's not partitioning correctly, it might be something as trivial as one of your field values on row 19 containing a comma, thus throwing out the import by making that row seem to have more columns. If this is the case, I hope you can get a revised version of the CSV file - this time with a text qualifier set. If possible, use something like | rather than " as the qualifier so that it's less likely to appear in the field values.

Upvotes: 0

CodeRot
CodeRot

Reputation: 893

I've found the CSV Import to be a bit limited with regards to bad data. If you're having trouble with the 19th column, I would suggest figuring out why that column is failing. You can try and tell the import task's error conditions to Ignore Errors with data truncation, etc...but that may not fix the issue.

I have often switched complicated or error-prone CSV imports to simply use a SSIS Script Task, then just write my own code to parse out the CSV and handle bad data.

Upvotes: 0

John Saunders
John Saunders

Reputation: 161773

Here are some ideas I have with no details.

  1. What happens when you try to import the same .CSV file into Excel? Anything interesting around row 19?
  2. Does there appear to be anything different about row 19?
  3. If you delete row 19, what happens?

See, I bet you've thought of these things as well, and probably more, since you have the details. If you want anything more than superficial bad guesses, you'll have to provide a little detail.

Upvotes: 1

Related Questions