Loading

Quotes from CSV not parsing correctly

Fecha de publicación: Apr 1, 2025
Solución

SYMPTOM

When reading a CSV or a TXT file as CSV, if one of the fields has double quotes all the information following the first quote gets wrapped in under the same key. This group multiple records in one specific key.
This occurs even when specifying the field quote in the MIME Type properties.
  
<file:read doc:name="Read" doc:id="18fb49b1-028b-450b-9daa-bbed3992eb79" outputMimeType='application/csv; quote="\""; streaming=true; separator=|' config-ref="File_Config" path="TheFile.txt"/>
Example: 
id|name|lastname|address
1|Andrew|Doe|123 Street 1
2|"John|Doe|345 Street 2
3|Julia|Doe"|567 Street 1
This will get parsed as follows if expressed as JSON: 
[
  {
    "id": "1",
    "name": "Andrew",
    "lastname": "Doe",
    "address": "123 Street 1"
  },
  {
    "id": "2",
    "name": "John|Doe|345 Street 2\n3|Julia|Doe",
    "lastname": "567 Street 1"
  }
]
 

CAUSE

This is due to the CSV is malformed and the default value for the quote parameter being " (double quote). If you specify the double quote as quoting character like this \" it will have the same behaviour due to the same concept.
In summary, you are saying from this quote to the next this is a single record. You can prove this quite easily by specifying the letter A as quoting in the top example. 
[
  {
    "id": "1",
    "name": "ndrew|Doe|123 Street 1\n2|\"John|Doe|345 Street 2\n3|Julia|Doe\"|567 Street 1"
  }
]
 

SOLUTION

As there is multiple CSV with multiple formats you need to find a way to adapt the solution to your specific need. Here we present 3 possible solutions.

1.- Sending a correctly formatted CSV with the quoting in the correct position 
id|name|lastname|address
1|Andrew|Doe|123 Street 1
2|"John"|Doe|345 Street 2
3|Julia|"Doe"|567 Street 1
This will get parsed correctly as the quoting is opening and closing in the correct value.

2.- Specifying an empty quoting in the MIME Type properties.

If you are using a connector to read the file, you can specify the MIME Type properties. When doing so, the quote parameter will be autocompleted to ". So to achieve this solution, you need to modify the parameter from the XML View and leave it like this (File Connector example)
  
<file:read doc:name="Read" doc:id="18fb49b1-028b-450b-9daa-bbed3992eb79" outputMimeType='application/csv; quote=""; streaming=true; separator=|' config-ref="File_Config" path="TheFile.txt"/>

This will indicate that no quote character is specified. So every double quote will be escaped
  
[
  {
    "id": "1",
    "name": "Andrew",
    "lastname": "Doe",
    "address": "123 Street 1"
  },
  {
    "id": "2",
    "name": "\"John",
    "lastname": "Doe",
    "address": "345 Street 2"
  },
  {
    "id": "3",
    "name": "Julia",
    "lastname": "Doe\"",
    "address": "567 Street 1"
  }
]

3.- Using a DW transformation to correct the behaviour

After the connector, you can use a DW transformation to parse it correctly. In the connector do not specify output and then use the following transformation. 
%dw 2.0
output application/json
---
read(payload,"application/csv",{separator:"|",quote:""})

Disclaimer: This article only works as a guide for solving a common parsing issue
 
Número del artículo de conocimiento

001121560

 
Cargando
Salesforce Help | Article