How to import a csv file into a table in Numbers using AppleScript

Craft79 wrote:

I have an exists Numbers sheet, and inside I already have tables. every table have the same name of *.csv file, and I would like to copy each file in its table.

😉

You can try the script below.

1. Copy-paste into Script Editor (in Applications > Utilities)
2. Make sure Script Editor.app is checked at System Preferences > Security & Privacy > Privacy > Accessibility.
3. Gather your CSV files together into one folder, and make sure their names (minus the extension) match exactly one table in your document. For example if your file is named 'mydata1.csv' then make sure you have one table in your document named 'mydata1'.
4. With your Numbers document open, click the 'Run' button and choose the folder at the prompt.

As written it will paste the data at A2 of each table, but you can change that in the first line of the script.

Be sure to test on a backup of your document. As written the script overwrites old data.

SG

property tgtCell : "A2" # paste block at A2 of each table (change to A1, etc as needed)

tell application "Finder"

set fldr to choose folder with prompt "Choose folder with CSV files to import to Numbers"

set ff to fldr'sfiles as alias list

end tell

# process each file

repeat with f in ff

tell application "Finder" to set theExt to f's name extension

if theExt is in {"csv", "txt"} then

# save the name less the .csv or .txt extension

tell application "Finder" to set fileName to (f's name as text)'s text 1 thru -5

# read file

set csvTxt to (readfas «class utf8») -- may need to remove: as «class utf8»

# convert to TSV (for pasting into Numbers)

set tsvTxt to csvToTSV(csvTxt)

set tFound to false# flag in case matching table not found

pasteToNumbers(fileName, tsvTxt, tFound)

end if

end repeat

to pasteToNumbers(fileName, tsvTxt, tFound)

set the clipboard totsvTxt

tell application "Numbers"

tell front document

repeat with s in sheets

repeat with t in s's tables

set tblName to t's name as text

if fileName is tblName then

set tFound to true

deletet'srows 3 thru -1 # remove rows with old data

deletet'scolumns 2 thru -1 # remove columns to right

activate

tell t to set selection range to range tgtCell

delay 0.3

tell application "System Events" to keystroke ¬

"v" using {option down, shift down, command down}

end if

end repeat

end repeat

if tFound is false then display dialog "Table '" & fileName & "' not found" buttons "Ok"

end tell

end tell

end pasteToNumbers

to csvToTSV(csvTxt) # convert to TSV for pasting

set text item delimiters to quote

set ti to csvTxt's text items

repeat with i from 1 to count ti by 2

set nonQItem to (a reference to ti's item i)

-- odd-numbered items are the ones not surrounded by quotes

if nonQItem'slength is 0 then -- if adjacent "" add back a quote

set nonQItem to quote

else -- otherwise replace commas with tabs

set nonQItem'scontents to my replace(nonQItem, ",", tab)

end if

end repeat

return ti as text

end csvToTSV

to replace(tt, x, y) # generic replace

set text item delimiters to x

set tmp to tt's text items

set text item delimiters to y

return tmp as string

end replace

Hi,

You can try this CSV to Tabs on Clipboard Automator Service (Dropbox download).

To install double-click the downloaded .workflow package and give permission at System Preferences > Privacy & Security. Also make sure Automator.app is checked at System Preferences > Privacy & Security > Privacy > Accessibility.

To use in Finder (not Numbers) navigate to the csv file, right-click and choose CSV to Tabs on Clipboard from the Services contextual menu. Answer the prompt about the delimiter, go to Numbers, click in the top-left cell where you want the data, and type command-v to paste.

That's it! A few clicks and you're done. No need to know about scripting.

To view the AppleScript contained in the service you can open it in Automator and inspect the code in the Run AppleScript action in the right pane. Because of the nature of the problem, this particular script is necessarily quite sophisticated, with crucial bits based on the work of Nigel Garvey at macscripter.net. (Macscripter.net is a good source for learning about AppleScript, as is macosxautomation.com).

SG

Hi Craft79

You say a 'specific table'. Do you mean that the table already exists in a Numbers sheet, or do you want to create a new Numbers document or sheet for each .csv file?

Either is possible, but without further detail of what you are trying to achieve it's difficult to suggest precise answers.

I believe that the books by Sal Saghoian and by Hanaan Rosenthal are good general introductions to AppleScript, although both are quite expensive and neither, as far as I'm aware, has any information about scripting Numbers.

There is a section on scripting Numbers at macosautomation.com but there isn't great deal of information there about the sort of thing you're trying to do.

The forums at macscripter.net are quite active and there's a huge amount of knowledge on hand.

Within Apple Communities itself, there's a Mac OS Technologies section which is probably the best place to ask general questions about AppleScript.

Hope this helps.

H

Craft79 wrote:

Hi SGII,

thank you for your answer, but I have more .csv file to copy in more table and I am finding a way for automatize it with applescript if it is possible.

What is the "it" that you are talking about when you say you want to "automatize it"? Specifics can lead to a solution.

SG

Craft79 wrote:

How I can to remove all empty columns?

Not sure. Could it be that your csv data has extra , ?

SG