TheSwamp

Code Red => AutoLISP (Vanilla / Visual) => Topic started by: Kevinpo on February 17, 2020, 10:41:41 AM

Title: Double Quotes in a string from a CSV file
Post by: Kevinpo 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
Title: Re: Double Quotes in a string from a CSV file
Post by: ribarm 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
Title: Re: Double Quotes in a string from a CSV file
Post by: Dlanor 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
Title: Re: Double Quotes in a string from a CSV file
Post by: Lee Mac on February 17, 2020, 02:00:35 PM
Do you receive the doubled-up double-quotes using my Read CSV (http://lee-mac.com/readcsv.html) function?
Title: Re: Double Quotes in a string from a CSV file
Post by: Kevinpo 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!!!
Title: Re: Double Quotes in a string from a CSV file
Post by: Lee Mac 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  :-)
Title: Re: Double Quotes in a string from a CSV file
Post by: BIGAL 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

Title: Re: Double Quotes in a string from a CSV file
Post by: Dlanor on February 18, 2020, 11:03:52 AM
Thank you Dlanor for the workaround of removing the extra quotes. Works great.

No problem.  :-)
Title: Re: Double Quotes in a string from a CSV file
Post by: JohnK 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