How do I import a semi-colon delimited file into Numbers?

Hi Dave,

I would open the CSV file in Pages and "Find and Replace" the semicolons with tabs. It may be easiest to do this if you change the extension to .txt before trying to open with Pages. After making the substitution, Copy the text in Pages and Paste into Numbers.

Jerry

I described several times the way to achieve this goal.

Numbers requires CSV with semi-colons used as separators when the decimal separator is comma.

So, to import such files, set temporarily the system's decimal separator to comma and import your file.

When you will reset the decimal separator to period, Numbers will have no proplel with the imported file.

I also posted scripts converting the semi-colons into TABs on the fly allowing us to paste the dats in a table.

Most of the time, it works but the result may be odd if some cells contain strings embedding semi-colons.

--{code}

--[SCRIPT csv2clipboard_as_tsv]

(*

Enregistrer le script en tant que Script : csv2clipboard_as_tsv.scpt

déplacer le fichier ainsi créé dans le dossier

<VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:

Il vous faudra peut-être créer le dossier Applications.

menu Scripts > csv2clipboard_as_tsv

choisir un fichier csv

Le script remplace les points-virgules (ou virgules) par des TABs

et stocke le résultat dans le presse-papiers

On peut également enregistrer en tant que progiciel (application sous 10.6.x)

Glisser-déposer l'icône d'un fichier csv sur celle du progiciel (application).

--=====

L'aide du Finder explique:

L'Utilitaire AppleScript permet d'activer le Menu des scripts :

Ouvrez l'Utilitaire AppleScript situé dans le dossier Applications/AppleScript.

Cochez la case "Afficher le menu des scripts dans la barre de menus".

--=====

Save the script as a Script: csv2clipboard_as_tsv.scpt

Move the newly created file into the folder:

<startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:

Maybe you would have to create the folder Applications by yourself.

menu Scripts > csv2clipboard_as_tsv

Choose a csv file

The script replace the commas (or semi-colons) by TABs characters.

and store the result in the clipboard

We may also save as an Application package (application under 10.6.x)

Drag & drop the icon of the text file on the application's one.

--=====

The Finder's Help explains:

To make the Script menu appear:

Open the AppleScript utility located in Applications/AppleScript.

Select the "Show Script Menu in menu bar" checkbox.

--=====

Yvan KOENIG (VALLAURIS, France)

2011/09/16

*)

--=====

property permitted : {"public.comma-separated-values-text", "public.csv"}

--=====

on run

(*

my main((path to desktop as text) & "csv-template.csv" as alias)

*)

if my parle_anglais() then

set myPrompt to "Choose a CSV file …"

else

set myPrompt to "Choisir un fichier CSV …"

end if -- parleAnglais

if 5 = (system attribute "sys2") then (*

it's Mac OS X 10.5.x with a bug with Choose File *)

set allowed to {}

else

set allowed to my permitted

end if -- 5 > (system…

my main(choose filewith promptmyPromptof typeallowed without invisibles) (* un alias *)

end run

--=====

on open sel

my main((item 1 of sel) as alias)

end open

--=====

on main(leFichier)

script un_script

property enListe : {}

property digits : {}

property une_liste : {}

local typeID, delim, i, j, k, maybe, maybe2, enTexte

tell application "System Events" to tell disk item (leFichier as text) to set typeID to type identifier

if typeID is not in {"public.comma-separated-values-text", "public.csv"} then

if my parle_anglais() then

error "“" & leFichier & "” isn’t a CSV file !"

else

error "« " & leFichier & " » n’est pas un fichier CSV !"

end if -- (do shell…

end if -- type…

if character 2 of (0.5 as text) is "," then

set {delim, deci, altDeci} to {";", ",", "."}

else

set {delim, deci, altDeci} to {",", ".", ","}

end if

set my digits to {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9", " ", altDeci}

set enTexte to readleFichier

if enTexte does not contain delim then

(*

The CSV doesn't match the local requirements, change the value separator

*)

if delim is ";" then

set delim to ","

else

set delim to ";"

end if

end if

set my enListe to paragraphs of enTexte

(*

drop ending empty rows

*)

repeat while last item of my enListe is ""

set my enListe to items 1 thru -2 of my enListe

end repeat

repeat with i from 1 to count of my enListe

set my une_liste to my decoupe(item i of my enListe, delim)

repeat with j from 1 to count of my une_liste

set maybe to item j of my une_liste

if (count of my decoupe(maybe, altDeci)) = 2 then (*

It may be a number *)

repeat with k from 1 to count of maybe

set maybe2 to ((character k of maybe) as text) is in my digits

if not maybe2 then exit repeat

end repeat -- k

if maybe2 then (*

It‘s really a number, replace altDeci by deci and remove space characters *)

set itemj of my une_liste to my supprime(my remplace(maybe, altDeci, deci), space)

end if -- maybe2…

end if -- (count…

end repeat -- j

set item i of my enListe to my recolle(my une_liste, tab)

end repeat -- i

set the clipboard to my recolle(my enListe, return)

set my enListe to {}

set my digits to {}

set my une_liste to {}

end script

run scriptun_script

end main

--=====

on decoupe(t, d)

local oTIDs, l

set oTIDs to AppleScript's text item delimiters

set AppleScript's text item delimiters to d

set l to text items of t

set AppleScript's text item delimiters to oTIDs

return l

end decoupe

--=====

on recolle(l, d)

local oTIDs, t

set oTIDs to AppleScript's text item delimiters

set AppleScript's text item delimiters to d

set t to l as text

set AppleScript's text item delimiters to oTIDs

return t

end recolle

--=====

(*

replaces every occurences of d1 by d2 in the text t

*)

on remplace(t, d1, d2)

local oTIDs, l

set oTIDs to AppleScript's text item delimiters

set AppleScript's text item delimiters to d1

set l to text items of t

set AppleScript's text item delimiters to d2

set t to l as text

set AppleScript's text item delimiters to oTIDs

return t

end remplace

--=====

(*

removes every occurences of d in text t

*)

on supprime(t, d)

local oTIDs, l

set oTIDs to AppleScript's text item delimiters

set AppleScript's text item delimiters to d

set l to text items of t

set AppleScript's text item delimiters to ""

set t to l as text

set AppleScript's text item delimiters to oTIDs

return t

end supprime

--=====

(*

Set the parameter delimiter which must be used in Numbers formulas

*)

on getLocalizedDelimiter()

if character 2 of (0.5 as text) is "." then

return ","

else

return ";"

end if

end getLocalizedDelimiter

--=====

on parle_anglais()

return (do shell script "defaults read 'Apple Global Domain' AppleLocale") does not start with "fr_"

end parle_anglais

--=====

--[/SCRIPT]

--{code}

Yvan KOENIG (VALLAURIS, France) lundi 3 octobre 2011 13:34:11

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

My iDisk is : <http://public.me.com/koenigyvan>

Please : Search for questions similar to your own before submitting them to the community

CdaveC wrote:

Thank Jerry,

I'm a little disappointed this isn't something numbers handles natively, but this should do the trick. Thanks!

Dave

I suppose that my answer sounded like a prescription, but I didn't mean it that way. I literally meant that I would do the conversion in Pages because that's my preferece ; it's simple, fast and I understand it completely. It may well be that there are more elegant solutions and solutions that work better in other situations. You have to determine what is best for you. If you do this every day, or many times per day, you would likely be better off with Yvan's System Preference solution or the script. You could even set up an alternate User Account in which to do that work.

I consider iWork to be the application, and Numbers to be a partition, so to me it's native. I know that with the parts of iWork now being sold separately this mindset is more difficult to defend.

Jerry

Thank you Koenig! You are a a lifesaver. I created an app with the script (by checking the create app box) in AppleScript Editor and put it in my apps folder. I called it NumbersSemicolonFix, so it sits next to numbers. I drag the semicolon-CSV that does not work in Numbers through your script and I can copy a functinoal version into numbers immediately. Amazing computer wizardry!