How to deal with comma values in CSV file into one column while exporting to Sqlserver table using ssis?

Question

Hi All,

This is very urgent requirement.

We have CSV file which contains the data as LIC Finance Limited,Inc.

Here we facing the issue like Inc is moving to next column and that column value goes to next column and so on to all the other columns.

So,Can any one guide me how to fix this issue ASAP.

Earlier response is appreciable.

Regards,

Sudha

---

sudha

Answer 1

Hi Sudha,

What text qualifiers are you using in this file. Can you paste a sample row here?

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 2

Hi,

Sample data is as follows:

Original requirement

OrderID    ordername     comapnyname                 Email

1001       abc            LIC FinanceLTD,INC          [e-mail]

Below is the one which we are getting while loading from CSV to sql server table:Here my problem is in CSV file the data is as below,So could you guide us how to solve this issue.

OrderID ordername ComapnyName        Email       ShipFirstName

1001     abc             LIC FinanceLTD       INC     [e-mail]

---

sudha

Answer 3

Sudha,

If there is text qualifier then you will not see any issue in loading. If there is no text qualifier then it will be little difficult to load the file.

c1,c2,c3
"ABC","ABC,D","ABC"
"ABC","ABC,D","ABC"
"ABC","ABC,D","ABC"
"ABC","ABC,D","ABC"

I am able to load this without any issue

Regards,Eshwar.

---

--Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.

Answer 4

Can you open that file using notepad then you will see the text qualifier?

Regards,Eshwar.

---

--Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.

Answer 5

What is the column delimiter you are using OR else dexcribe in detail every step you use to configure the Flat File Source connection manager. 

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 6

It seems that this file uses a tab as a column delimiter

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 7

Hi Everyone,

I have opened csv file in notepad,it is using comma as text qualifier and in the file it is using tab as column delimiter.

So,now my issue is how to rectify that .

Regards,

Sudha

---

sudha

Answer 8

can you paste how the data looks when it is opened using notepad? It can't be both separators.

Regards,Eshwar.

---

--Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.

Answer 9

Sudha,

Open your Flat file connection manager, set text qualifier as <none>. Navigate to columns section and ensure that column delimiter is set as "Tab". Now click on Refresh button.

Also read this: http://www.bimonkey.com/2009/05/the-flat-file-connection-manager-and-source/  

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 10

Hi Eshwar,

please find the below data that looks in notepad.

OrderID,ClientCode,OrderDivision,UserID,ContactID,OrderDate,PurchaseOrderNumber,ShipCompany,ShipSalutation,ShipFirstName,ShipLastName,ShipEmail,ShipAddress1,ShipAddress2,ShipAddress3,ShipCity,ShipState,ShipPostalCode,ShipCountry,ShipPhoneNumber,ShippingMethod,carrier,ShippingDate,Manifest,DateTimeEntered,orderType
13882171,COLE,IBD,[e-mail],0033000000HIxjq,2012-12-05 00:00:00,,NFP Securities, Inc.,,William,Rabbitt,[e-mail],29 South Main St Town Center,,,West Hartford,CT,06107,,,Ground - Commercial,UPS,,,2012-12-05 16:00:00,Marketing Order

---

sudha

Answer 11

I don't think this is possible to load as there is no delimiter!

Regards,Eshwar.

---

--Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.

Answer 12

Hi,

Could you elaborate your answer?

Regards,

Sudha

---

sudha

Answer 13

Hi Eshwar,

please find the below data that looks in notepad.

OrderID,ClientCode,OrderDivision,UserID,ContactID,OrderDate,PurchaseOrderNumber,ShipCompany,ShipSalutation,ShipFirstName,ShipLastName,ShipEmail,ShipAddress1,ShipAddress2,ShipAddress3,ShipCity,ShipState,ShipPostalCode,ShipCountry,ShipPhoneNumber,ShippingMethod,carrier,ShippingDate,Manifest,DateTimeEntered,orderType
13882171,COLE,IBD,[e-mail],0033000000HIxjq,2012-12-05 00:00:00,,NFP Securities, Inc.,,William,Rabbitt,[e-mail],29 South Main St Town Center,,,West Hartford,CT,06107,,,Ground - Commercial,UPS,,,2012-12-05 16:00:00,Marketing Order

---

sudha

You don't have qualifiers around each value which makes it impossible to detect whether the comma is a delimiter or part of the value... the only solutions is to get a better csv file.

But after that is fixed also see:
http://connect.microsoft.com/SQLServer/feedback/details/282396/ssis-flat-file-parser-does-not-read-column-delimiters-embedded-in-text-data
http://connect.microsoft.com/SQLServer/feedback/details/312164/flat-file-parser-cannot-import-files-with-embedded-text-qualifiers

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 14

Hi ALL,

Thanks all for your replies,But I didnt get the better answer for my problem.

Can any pone help on this?

Regards,

---

sudha

Answer 15

Hi ALL,

Thanks all for your replies,But I didnt get the better answer for my problem.

Can any pone help on this?

Regards,

---

sudha

There is no solutions for your problem, other then to get a better file... one with qualifiers or even better one with an other delimiter like |

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter