Comma in the column data splitting the column when table data export to excel file in SSIS
Hi folks, I am trying to export data from a table view to excel file using SSIS. My table view has address fields which may contains comma. but, the output is splitting the address when there is comma occurs. Eg: when my view has the following data column Address1 has the value "SHOP 108, 1/F., BLOCK B, DISCO" it gives output: Address1 shows like this "SHOP 108 and the next part goes to next column that is 1/F. and BLOCK B goes next DISCO" goes to next. Can anyone to suggest a solution so as to ignore commas inside the data. regards,
March 21st, 2011 7:24am

What are the various components being used in data flow task?Nitesh Rai- Please mark the post as answered if it answers your question
Free Windows Admin Tool Kit Click here and download it now
March 21st, 2011 7:29am

in dataflow task, i have oledbdatasource for database connection, derived column for some column manipulation, oledb command for calling a stored procedure to update database once the excel sheet is generated, and flatfile destination. flatfile destination is connected to a flatfile connection manager. In flatfile connection manager, rowdelimiter has set to {CR}{LR}, column delimiter has set to Comman{,}. In prview, its coming perfect, but the output csv file shows wrongly..
March 21st, 2011 7:33am

So you are pulling dat from view to flat file destination and data in falt file is not as expected. What is the derived column for? Are you creating some new columns and taking those new columns to the falt file destinaiton?Nitesh Rai- Please mark the post as answered if it answers your question
Free Windows Admin Tool Kit Click here and download it now
March 21st, 2011 7:39am

Obviously.. the derived column is for creating a data column. I have already tried without the derived column. So, I dont think the issue is not related to the derived column. The previews given be flatfile manager and flatfile destinations are correct. but, when i open the excel sheet its giving wrongly.
March 21st, 2011 7:43am

In flatfile connection manager, rowdelimiter has set to {CR}{LR}, column delimiter has set to Comman{,}. When you know that the incoming row has coma (,) as part of the column value then why are you taking coma as column delimiter? Since you want to use a csv file then I understand that you are left with no choice but either you can replace the coma in the column values with some other character or change your destination to text file or an excel file.Nitesh Rai- Please mark the post as answered if it answers your question
Free Windows Admin Tool Kit Click here and download it now
March 21st, 2011 7:45am

view data is DAddr1 DAddr2 DAddr3 DInst1 DInst2 CDate HIM SHOP 108, 1/F., BLOCK B, DISCO DISCOVERY BAY, HONG KONG 46:17.0 result data in excel sheet is ''DAddr1'' ''DAddr2'' ''DAddr3'' ''DInst1'' ''DInst2'' ''CDate'' ''HIM'' ''SHOP 108 1/F. BLOCK B DISCO'' ''DISCOVERY BAY HONG KONG'' '''' '''' ''2005-10-21 12:46:17''
March 21st, 2011 7:47am

Thank you Nitesh. If i change the destination file to excel how it will work?
Free Windows Admin Tool Kit Click here and download it now
March 21st, 2011 7:55am

Thank you Nitesh. If i change the destination file to excel how it will work? In excel file you need to add all the columns (in a sheet and use that sheet in the excel destination ) and do the appropriate mapping in the excel destination.Nitesh Rai- Please mark the post as answered if it answers your question
March 21st, 2011 9:29am

Thank you Nitesh, that works very fine, but, Excel file must be there in the destination. Is there any work around, so as to remove the dependency.
Free Windows Admin Tool Kit Click here and download it now
March 21st, 2011 10:34pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics