Skip to Content
Former Member
May 20, 2009 at 10:33 AM

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



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