How do Import a csv file that contains strings

If your file is named with the .csv name extension, Numbers will import it flawlessly.

At least, it's the way it behave on my machines.

Yvan KOENIG (VALLAURIS, France) mercredi 25 janvier 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

My Box account is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

Values embedding commas must be enclosed by double quotes as what you wrote in your first message.

If your server put single quotes, it's the wrongdoer.

PS : the single quotes are used for an other task.

When There is one as the 1st char of a val, it tell the app that it must treat the rest as a string (not as a number, a date …)

This is why you loose it in cells. It's also why in the cells containing only numeric characters the content is aligned on the left which flags strings.

Yvan KOENIG (VALLAURIS, France) mercredi 25 janvier 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

My Box account is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

BEfore improting into numbers do a search and replace. Replace ,'$ with ;'$

Then remove all the commas. Replace "," with ""

You should be now able to import the file using ; as separator or if you prefer you can do again the change of ; with ,

In this way you could even remove the $ and put it back later in numbers with the right formatting.

Hello Walter

You are describing the Numbers behavior when the decimal character is the comma (as it is in France or in Italy).

In this cas values must be separated by semi-colon.

But this doesn't apply when the decimal character is period as it is in the OP's machine.

Yvan KOENIG (VALLAURIS, France) mercredi 25 janvier 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

My Box account is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

I apologize but I don't know Perl language but if you want to be able to read your files you must find a way to match the rule.

Maybe it would be easier to create text files in which values are separated by TAB characters.

Numbers will be perfectly at ease with them (if they are using the .txt name extension) and in this case, no need for single or double quotes.

I wrote quite often that from my point of view, CSV is the worst format ever invented.

Yvan KOENIG (VALLAURIS, France) mercredi 25 janvier 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

My Box account is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

I Yvan,

I understand your explanation about numbers.

My suggetion was about how to remove the extra commas from a text file (.CSV), that is the commas not to be used to separate the values. May be I forgot to explain to do this with a text editor. After you have got this, you can import the file into numbers and have each field in its cell of numbers. Later you can do any numbers formatting as required.

I believe it will work.

Sorry Bradley. My english does not let me to get the full meaning of your last message.

You try to find a direct way to import without the 2-3 operations on the CVS that I was suggesting ?

In this case I have no other suggestion.

I don't know how

I apologize but you wrote :

WALTER-MILANO-ITALY wrote:

You should be now able to import the file using ; as separator or if you prefer you can do again the change of ; with ,

It's not a matter of preferences. The OP can't import a file whose values are separated by semi-colons.

He must replace them by commas.

With your scheme :

Original datas

'Total Dollars','$1,123,123,123','some other string','etc'

after your step 1

'Total Dollars';'$1,123,123,123','some other string','etc'

after your step 2

'Total Dollars';'$1,123,123,123''some other string''etc'

and the datas are completly unusable

A possible scheme would be :

Original datas

'Total Dollars','$1,123,123,123','some other string','etc'

'Totals Dollars','$12,123,123,123’,’blahblah’,’etc'

after possible step 1 (replace ‘,’ by ",")

'Total Dollars";"$1,123,123,123";"some other string";"etc'

'Totals Dollars";"$12,123,123,123";"blahblah";"etc'

after possible step 2 (replace single ‘ by ")

"Total Dollars";"$1,123,123,123";"some other string";"etc"

"Totals Dollars";"$12,123,123,123";"blahblah";"etc"

after possible step 3 (replace ";" by ",")

"Total Dollars","$1,123,123,123","some other string","etc"

"Totals Dollars","$12,123,123,123","blahblah","etc"

This one may be imported by Numbers on thez OP’s machine.

I don't know if these changes may be done with Perl but I know that they are easy to do with AppleScript.

But honestly, if Perl is able to build a TAB separated values file, it would be more efficient.

Yvan KOENIG (VALLAURIS, France) mercredi 25 janvier 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

My Box account is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

Can you not modify your perl to utilize escape characters to output the double quotes? ( \" instead of ')

http://www.perlmonks.org/?node_id=401006

maybe something like this? (I know it isnt right exactly, but the first single quote would be replaced by a double quote on output I think).

print REPORT3 "\"".$county."\",,,,\n";

Jason