E9.2 Special Characters in CSV file

Loveen Mehta

Member
Hi Team,

I have to export data in CSV format for Item Master records from F4101.
But the Description fields have lots of special characters like , # & " " etc. which causes the columns to be misaligned and prints data into subsequent columns.

I have tried enclosing the value in double quotes, but it creates issues for records which have double quotes in them.

Ex: COGNEX LENS 2/3 FIXED 12.5MM MANUAL, # LFC-12.5F breaks into COGNEX LENS 2/3 FIXED 12.5MM MANUAL and # LFC-12.5F" when enclosing value within double quotes.

PVC flexible conduit pipe 1", 30mtr roll breaks into PVC flexible conduit pipe 1 and 30mtr roll"

Is there a way wherein all the special characters can just be treated as normal text when writing records to csv file using B34A1010 BSFN.


Thanks in advance!


Regards,
Loveen
 
If you are rolling your own CSV output you will need to escape the quote. I believe it is "".

Code:
Steel Pipe 1" diameter

would be

Code:
"Steel Pipe 1"" diameter"

Or something like that. Look at https://www.ietf.org/rfc/rfc4180.txt and double check.
 
Hi Brian,

Thanks for your response.

I had tried the "variable" but then there are records which contain both commas (,) and double quotes (" ") and that is where the records split incorrectly.

I have changed the delimiter to ^ and then tried exporting the data, but facing challenges for some records.

There are around 21000 records being exported from the report and currently I am facing issue with 17 records.

Appreciate any help in trying to root out this issue.

I have attached the sample csv file for reference.

Thank you!


Regards,
Loveen Mehta
 

Attachments

  • 08-06-24_130637.zip
    2.2 KB · Views: 4
Changing the delimiter (people often use a pipe) and/or text qualifier is only going to reduce the number of errors you encounter since the chances of encountering those characters in your data is lessened. A properly formed CSV file can contain both delimiters (usually a comma) and text qualifiers (usually a double quote) in the data w/o any issues. If you are having errors your CSV is not well formed, i.e. it is not conforming to the CSV standard.

Your text field can contain both the comma and the text qualifier, you just have to properly escape any text qualifiers that are in the data. If you read the RFC link I sent, it states:

Code:
7.  If double-quotes are used to enclose fields, then a double-quote
       appearing inside a field must be escaped by preceding it with
       another double quote.  For example:

       "aaa","b""bb","ccc"

Your exact example:

Data
Code:
PVC flexible conduit pipe 1", 30mtr roll

CSV
Code:
"PVC flexible conduit pipe 1"", 30mtr roll"

--same example w/ more example fields--

"text data","PVC flexible conduit pipe 1"", 30mtr roll","more text data",

Field1:  text data
Field2:  PVC flexible conduit pipe 1", 30mtr roll
Field3:  more text data
 
One way to handle it would be to create a NER that accepts a text (description) field, loops through the field character by character and replaces any special characters with blanks. You might also want it to replace LF and CR as well.
 
Back
Top