CSV file with text qualifiers around each field causing error on Import

[Former Member]

Hi

I have a csv file which I am trying to import - a one line extract is shown below. It is delimited by semi colon and each field has a text qualifier around it.

XXX Drinks Ltd;"BR01";"1";"001.2008";"2008";"Distribution";"-186";"-186";"-186"

When importing i get the following issue

1) BPC doesn't seem to handle the text qualifier for the fields. For example the "BR01" field above requires me to put a conversion as follows ""BR01"" i.e. I have to double the quotes because BPC adds them

2) Even after the required conversion, BPC does not like the double quotes around the amounts, even though when validating the transform I get no error message, when running the import package I get the following message

Record Count: 1

Accept Count: 1

Reject Count: 0

Skip Count

The number of failing rows exceeds the maximum specified. (Microsoft Data Transformation Services (DTS) Data Pump (8004202c): TransformCopy 'DTSTransformation__9' conversion error: General conversion failure on column pair 1 (source column 'SIGNEDDATA' (DBTYPE_STR), destination column 'SIGNEDDATA' (DBTYPE_NUMERIC)).)

Does this my source file can't have double quotes as a text qualifier?

thanks in advance

Scott Farrington

[Former Member · Accepted solution]

James, thanks for your reply

does that mean that BPC can't deal with the double quotes? I understand about removing them and using a comma for a delimiter, but this is the file format I have been given.

What I really need to know is, given this format, using a transformation and/or mapping function, can I import the data the way it is?

And I still need an answer to my second point, about the error message received on running the import package,

thanks

Scott

[james_lim · Accepted solution]

Scott,

Unfortunately, BPC can't handle it

So you can not use that data file with " (Double Quatation)

if you want to use it in the package, you need to add a custom script task and replace " to null.

I think that is the only way to do.

I hope it will help you.

James Lim

[Former Member]

Thanks to James for answering my question,

cheers

Scott Farrington

[james_lim]

Scott,

Please remove all " (Double quotation) and just use semi colon as separator.

BPC doesn't distinguish text and amount.

so all dimension members will import as text and signed data will import as number into the Fact table

I think that is enough to import data.

I hope it will help you.

James