I had the task of generating a CSV file from SSIS. With all the source columns being in nvarchar datatype, I decided to go for unicode csv file. Everything worked fine, except that all the columns in CSV file, when opened in EXCEL 2007 came under a single column. I tried many options like setting Text qualifier as "(double quotes) or '(single quote) and also dropped and recreated flat file destination. But, no change was observed.
When I myself created a csv file in unicode format in Notepad, it was opened fine by Excel 2007 without issues. Finally, after lot of googling, I found out this is due to 0xFF character inserted in the beginning of file in the forum
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109794
I had no other choice, but to use Data Conversion transform to convert all the nvarchar, ntext stream into char,text stream before inserting into ANSI flat file destination. Now, it worked without issues. Excel 2007 and SSIS being from Microsoft, and known fact that csv files are usually opened by Excel 2007 and I hoped that unicode csv file from SSIS 2005 would open fine in Excel 2007 and this problem was rather surprise to me.
Hope this problem is resolved in SQL 2008 as simplistic csv files are popular just for the reason that they are easily opened by Excel.
/Venkat
When I myself created a csv file in unicode format in Notepad, it was opened fine by Excel 2007 without issues. Finally, after lot of googling, I found out this is due to 0xFF character inserted in the beginning of file in the forum
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109794
I had no other choice, but to use Data Conversion transform to convert all the nvarchar, ntext stream into char,text stream before inserting into ANSI flat file destination. Now, it worked without issues. Excel 2007 and SSIS being from Microsoft, and known fact that csv files are usually opened by Excel 2007 and I hoped that unicode csv file from SSIS 2005 would open fine in Excel 2007 and this problem was rather surprise to me.
Hope this problem is resolved in SQL 2008 as simplistic csv files are popular just for the reason that they are easily opened by Excel.
/Venkat
No comments:
Post a Comment