quoting strings in csv

onyourmark
onyourmark used Ask the Experts™
on
I have csv files. I am attaching a sample. Some of the strings are not quoted (most are quoted) but I need every string to be quoted.
I was thinking about how to do this. I thought that I could take any field (a field would seem to be something that was separated by commas) and if it contains any non-numeric data then put quotes around the entire contents.
However that definition of a field does not work since within the string there may be commas and so those commas would also (mistakenly) be considered the delineaters of a field.
So then I though I could first just remove all the commas (I don't need them for any meaning they might give to the string) but this does not work because that would eliminate all the field delineating commas as well.
Does anyone have a strategy for doing this?
The fields that need to be quoted are columns 2, 3, 4, 5, 10
Thanks.
Reviews.zip
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
I don't think there is a solution to this.

Where does the file come from? Can you influence the way it is created? If it is possible to create it with a different delimiter such as a tab or | which is unlikely to occur in the text, you would be able to use it.

Talk to the guy in charge of the application that creates the file and you may be able to solve your problem.
ozo
Most Valuable Expert 2014
Top Expert 2015

Commented:
does your file format include any special methods of including quotes inside a quoted string that we need to be aware of?
do empty fields also need to be quoted?
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Author

Commented:
Yes. Sorry. That is correct. Quotes inside of a string have to be escaped. And commas inside the string need to be as well. I guess I was going to try to handle that as the second step.
ozo
Most Valuable Expert 2014
Top Expert 2015

Commented:
How do they have to be escaped?
I do not see how the commas in quoted strings in your attached file are escaped.

Author

Commented:
The specification says that it is best to escape commas with slashes but it seems that it can work even when they are not escaped. This is to be input into WEKA (http://www.cs.waikato.ac.nz/ml/weka/) which is a data mining package. However, it seems that if the commas are not escaped it still works.
I have manually added quotes to the strings and not touched the commas and it seems to work in Weka, so perhaps I can leave the commas in the strings alone for now.
Terry WoodsIT Guru
Most Valuable Expert 2011

Commented:
Just in case you're using Excel to edit or open the .csv files, you might want to be aware that it changes them upon opening (and if you hit save, the changes are permanent)... I explored some aspects of this issue a while ago with a question of my own:

http://www.experts-exchange.com/Programming/System/Windows__Programming/Q_26427029.html

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial