.txt File Format Issue with Data

dataservices job design
1

Hi Experts,

I have come across an issue as I have not worked a ton on File Formats, Suggestions / recommendations are appreciated.

Scenario:

We have a Task, where we need to export all the list of Materials and its descriptions into a .txt file, where DS can Consume and Send it to all Apps.
Note: we have Material Numbers with all the characters (@#!~*") (You name it from the library). We are using substring to export with a logic and below is the description.

Format Setup:

We are exporting a file with simple select and load with Pipe ‘|’ as delimiter.

Issues:

-We have data with PIPES (|)
-We have data with Double quotes (")

Example:
Raw Material - ABC"|123
Material in File - “ABC”“|123”
Material when file opened in excel - ABC"|123
Material BODS File Format shows after importing - ABC|123

File format setup in BODS is
-Delimited
-| is the delimiter
-TEXT is set to " so it can identify delimiters within data.

We are missing double quotes within the data :hb: :hb: even after all this.

We can try any other character but Material has it anyways so its always an issue.

Thanks,
KP

Randy_KP (BOB member since 2012-10-23)

2

Several solutions.

  1. Use XML instead of CSV.
  2. User Fixed Width instead of delimited.
  3. Treat the file twice, once as a one field record that replaces all double quote pairs with something impossible to find in a material number, like so:

Raw Material - ABC"|123
Material in File - “ABC”"|123"
Material in File After first pass- “ABCZzZzZzZzZz|123”

Then when you read it in the second time with the regular file format you replace substring of ZzZzZzZzZz with a double quote.

  • Ernie

eepjr24 :us: (BOB member since 2005-09-16)