Skip to main content

I have a bot reading a CSV that sometimes will have a single double quote (“) symbol. Once it attempts to read the field with this symbol, it will fail and produce the error “Unterminated quoted field at end of CSV line”. Typical code will always have two double quotes (“ “) at the beginning and end of a value, so I see why it is trying to find the other quote. What is the best way to develop around this, to where the bot would remove the single double quote and proceed as if there were no symbol?

Hi ​@AnalystCallen

Can you please check the lines /fields that end with “ which may not have “ at start or may not have have “ at end but at start. and add the missing quotation mark at the appropriate place.

Hi ​@Anjali.K1! Yes, if I manually add or remove the double quote character, it is processed appropriately. However, the data I am using unfortunately has end user-entered information, and I do not have a way of controlling the intake. I’m hoping to find ideas in this thread to develop a way to ingest the information from the CSV, then either ignore the specific character, replace it with a space, or some other idea. 

Thank you! 

Hi ​@AnalystCallen If you are just reading that particular field then what you can do open the file with excel command and by using replace command replace ‘’ with some keyword and then close the excel after saving it. Then you can use your csv the way you want to and then at last revert it back. Please let me know if this solution works for you..

Hi ​@AnalystCallen, 

Have you tried the following: 
1.  Rename the CSV to a txt file. using 

  1. Use a get text string function to get all text.
  2. Use a replace string regex operations:
    ​​​​​(?<!")"(?!")

     

  3. Use log to re-write the CSV to a new filename (e.g. processed_ Name].csv)
    highly advised to use UTF8 encoding.
  4. Read the new CSV processed.csv

Let me know how it turns out 😀

Reply


Terms & ConditionsCookie settings