Skip to Content
author's profile photo Former Member
Former Member

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

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on May 20, 2009 at 03:42 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • Posted on May 20, 2009 at 03:32 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 26, 2009 at 09:11 AM

    Thanks to James for answering my question,

    cheers

    Scott Farrington

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.