May 28, 2011 at 10:44 am
I am importing a csv file into sql server 2008 using SSIS. the file is comma delimited and has 2 currency fields. in the currency fields, whenever the currency has a number with comma, it is wrapped in quotations. how can I fix that when importing into sql server. I will be creating a new table not importing into existing table. here is a sample data:
IDProjectNumberAmount StartDate EndDate ActualDurationLIAmount
1610510 "6,838.65"30-Dec-0904-Jan-103.0 "6,838.65"
1710510 785 12-Aug-0810-Oct-080.23785
when the amount does not have comma, there is no quotes.
It seems also that the flat file source task is not reading some of the commas which causes it to merge 2 columns together. how can I fix that.
thanks
Al
May 28, 2011 at 10:55 am
this is a clarification to my msg above, in the currency fields where there is a comma, the column gets split. for example:
"6,838.65"
will be split to "6 and 838.65"
this caused it to bump the subsequent columns and jumbles the data. it should be 6,838.65 as a currency field in one column. any idea how I can fix this?
Al
May 29, 2011 at 11:55 pm
Regarding the quotations: this is to indicate that the text contains a delimiter. In this case, a comma.
You can convert the data to currency inside the dataflow, using a derived column, a data conversion component or a script task.
Regarding the split problem: that shouldn't occur, as the quotations indicate that the data shouldn't be split. So that is odd behaviour.
The simplest solution would be to use a real delimiter, such as the pipe |, or even better, a combination of characters that aren't used much, e.g. |&
That way you will be sure that there aren't "accidental" delimiters in your data.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply