Below is a script that will import a CSV without interpreting anything, I think. It will create a new Numbers document and you can copy/paste the results from there. All cells will be formatted as text. You may have to format some columns as automatic or another format afterward for certain formulas to work on the data. For example, numeric formulas (SUM being one) will ignore cells with text "numbers".
If I recall, this script is a mix of stuff posted here and elsewhere and some of my own. I am sure it could be improved. It has two limitations, the biggest being no cell can have a carriage return in it.
- Open the Script Editor app
- Create a new document
- Paste the script below into it
- Run it (hit the play button) and choose the file you want to import
If you like it,
- Save it to username/Library/Scripts.
- Open preferences for the Script Editor app and check the box to show the Script menu in menu bar
- You should see an icon that looks like a scroll on the main menu bar near wifi, bluetooth, etc. You can run the script from there.
-- Import CSV File into Numbers
-- This script reads a user-selected CSV file, creates a new Numbers document, and
-- imports the contents of the CSV file into the Numbers document.
-- The algorithm first converts the file to a "Unicode character 0001" delimited string as it interprets the file.
-- This means that "Unicode character 0001" is an unallowable character in the CSV file
-- Carriage return is an unallowed character in a CSV field, even if surrounded by quotes.
-- Latest addition is the ability to choose the CSV delimiter, though that code is commented out currently
set newTID to character id 1 --Unicode character 0001
set oldTID to AppleScript's text item delimiters
set csvFile to (choose file of type "CSV")
set csvText to read csvFile
-- If want user to select comma or semicolon as delimiter, swap the commenting of the next two lines
set csvDelimiter to ","
--set csvDelimiter to (choose from list {",", ";"} with title "Delimiter" with prompt "Choose a delimiter" default items ",") as text
-- Convert the CSV text to delimited string with the specified delimiter.
-- First turn it into an array of characters for easy mainipulation.
-- Pad the end with a null for the "quote test" portion of the algorithm.
-- Before converting back to a string, set the Applescript text delimiters to null so that no delimiters are put between characters
set theCharacters to characters of csvText
set end of theCharacters to ""
set i to 1
set openquote to false
repeat while i ≤ length of csvText
if item i of theCharacters = "\"" then
if openquote = true then
if item (i + 1) of theCharacters = "\"" then
set i to i + 1
else
set openquote to false
end if
else
set openquote to true
end if
set item i of theCharacters to ""
else if item i of theCharacters = csvDelimiter and openquote = false then
set item i of theCharacters to newTID
end if
set i to i + 1
end repeat
set AppleScript's text item delimiters to ""
set newText to theCharacters as string
-- Count paragraphs (rows in spreadsheet) and text items (columns in spreadsheet)
-- Use maximum number of text items to set number of columns, rather than add extra columns on the fly.
set AppleScript's text item delimiters to newTID
set rowCount to (count paragraphs in newText)
set maxColumns to 0
repeat with x from 1 to rowCount
set columnCount to count text items in paragraph x of newText
if columnCount > maxColumns then set maxColumns to columnCount
end repeat
--Create new Numbers document with correct sized table
--Set format of all cells to Text
tell application "Numbers"
activate
make new document at front
delay 1
tell document 1
tell sheet 1
delete every table
make new table at front with properties ¬
{row count:rowCount, column count:maxColumns, header column count:0, header row count:0}
set format of every cell of table 1 to text
end tell
end tell
end tell
--Loop through paragraphs and delimited items of newText and place them in the correponding
-- cells in the newly created table
repeat with nextRow from 1 to rowCount
set columnCount to (count text items in paragraph nextRow of newText)
repeat with nextColumn from 1 to columnCount
set nextValue to text item nextColumn of paragraph nextRow of newText
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
set nextCell to cell nextColumn of row nextRow
set value of nextCell to nextValue
end tell
end repeat
end repeat
-- Clean up. Set the delimiters back to what they were at the start
set AppleScript's text item delimiters to oldTID
