Author Topic: Double Quotes in a string from a CSV file  (Read 2194 times)

0 Members and 1 Guest are viewing this topic.

Kevinpo

  • Mosquito
  • Posts: 3
Double Quotes in a string from a CSV file
« on: February 17, 2020, 10:41:41 AM »
Hello,
I am reading a cell I have parsed from a CSV file and if it contains a few quotes it adds extra quotes to the line of text.
The CSV file reads: 1-3/4"X9-1/2"X20' S PINE 2.0LVL BEAM
The read-line function gives me: "\"1-3/4\"\"X9-1/2\"\"X20' S PINE 2.0LVL BEAM\""
Once the text is placed in my attribute field it displays: "1-3/4""X9-1/2""X20' S PINE 2.0LVL BEAM"
I have already found a way to strip the quotes from the begging and the end but in the middle it's giving me double quotes.

I guess for starters I need to find out why it reads the line of text from the CSV file like that.
I will need to use this attribute to search and compare back with the CSV file later so stripping them out might not show as a match but I don't want them displayed. Why does read-line give me two quotes instead of one? Is there another way to get a literal read from the CSV file?

Thank you

ribarm

  • Gator
  • Posts: 3225
  • Marko Ribar, architect
Re: Double Quotes in a string from a CSV file
« Reply #1 on: February 17, 2020, 11:49:03 AM »
I've just tested...
Here is what I get in 'r' variable :

Code: [Select]
Command: (setq f (open (getfiled "" "" "csv" 16) "r"))
#<file "D:\\                TESTING ROUTINES\\xxx.csv">
Command: (setq r (read-line f))
"1-3/4\"X9-1/2\"X20' S PINE 2.0LVL BEAM"
Command: (close f)
nil
Marko Ribar, d.i.a. (graduated engineer of architecture)

:)

M.R. on Youtube

Dlanor

  • Bull Frog
  • Posts: 263
Re: Double Quotes in a string from a CSV file
« Reply #2 on: February 17, 2020, 01:00:46 PM »
Code - Auto/Visual Lisp: [Select]
  1. (while (vl-string-search "\"\"" str) (setq str (vl-string-subst "\" " "\"\"" str)))

This should replace the two double quotes with a single double quote and a space. I am assuming that the first double quote of the pair is in fact an inches symbol

Lee Mac

  • Seagull
  • Posts: 12905
  • London, England
Re: Double Quotes in a string from a CSV file
« Reply #3 on: February 17, 2020, 02:00:35 PM »
Do you receive the doubled-up double-quotes using my Read CSV function?

Kevinpo

  • Mosquito
  • Posts: 3
Re: Double Quotes in a string from a CSV file
« Reply #4 on: February 17, 2020, 02:15:34 PM »
Thank you all for the quick response!!

For some reason I still get the same result. 
Thank you Dlanor for the workaround of removing the extra quotes. Works great.

Lee, your ReadCSV function worked great and returned the correct result.

I will explore these options for correcting the issue.

I'm not very familiar with coding directly to/from an Excel file. Is there a way to search the xlsx file to find a matching cell and then return the next cell on the same line? That would let me avoid having to export to CSV first.

Thank you!!!

Lee Mac

  • Seagull
  • Posts: 12905
  • London, England
Re: Double Quotes in a string from a CSV file
« Reply #5 on: February 17, 2020, 05:45:45 PM »
Lee, your ReadCSV function worked great and returned the correct result.

Excellent to hear - the additional double quotes typically arise when the CSV cell content contains either a delimiter character or a double quote.

Is there a way to search the xlsx file to find a matching cell and then return the next cell on the same line? That would let me avoid having to export to CSV first.

Yes  :-)

BIGAL

  • Swamp Rat
  • Posts: 1396
  • 40 + years of using Autocad
Re: Double Quotes in a string from a CSV file
« Reply #6 on: February 18, 2020, 04:00:02 AM »
kevinpo take all your books and stack them on top of each other that gives you an idea of how many times solutions have been posted about Excel <-> Autocad. It needs it own section, there was another request yesterday different forum to "find" a cell containing certain text.

The dirty answer was to search all cells within active region. Autocad can drive excel so I googled and answer is in here.

https://forums.autodesk.com/t5/visual-lisp-autolisp-and-general/find-method-in-excel/td-p/1798529

A man who never made a mistake never made anything

Dlanor

  • Bull Frog
  • Posts: 263
Re: Double Quotes in a string from a CSV file
« Reply #7 on: February 18, 2020, 11:03:52 AM »
Thank you Dlanor for the workaround of removing the extra quotes. Works great.

No problem.  :-)

JohnK

  • Administrator
  • Seagull
  • Posts: 10604
Re: Double Quotes in a string from a CSV file
« Reply #8 on: February 18, 2020, 12:13:12 PM »
Comparing Excel to CSV is comparing apples to oranges. CSV is for keeping "records" and Excel is used for making calculations-i.e. applying a compounding interest rate over 25 years-; meaning, you shouldn't be keeping your static records/data in an excel files. If you have "pipe/beam/thing size data", "weather data for 1000 years", etc. all that information should be stored in a CSV not excel (retrieving static type data from a proprietary format is far too inefficient).

Step up your game.
https://www.theswamp.org/index.php?topic=8476.msg108378#msg108378
TheSwamp.org (serving the CAD community since 2003)
Member location map - Add yourself

Donate to TheSwamp.org