This have been done so many times but here is my approach to create CSV, TSV from input from columnHeader list, value list and format:(tab, “,”)
The idea was to set the result to the clipboard and paste in Numbers. This way a script
with lot of values could be much faster. In other words instead of using a repeat loop to fill cells with values.
set columnHeaders to {"First Name", "Last Name", "Age"}
set theValues to {{"John", "Doe", 42}, {"Jane", "Smith", 65}, {"Billy", "Idol", 25}}
set csvString to its createSeparatedValue(columnHeaders, theValues, ",")
log csvString
set tsvString to its createSeparatedValue(columnHeaders, theValues, tab)
log tsvString
-- In Europe we use semicolon
set csvEString to its its createSeparatedValue(columnHeaders, theValues, ";")
log csvEString
on createSeparatedValue(columnHeaders, theValues, format)
set ASTID to AppleScript's text item delimiters
set AppleScript's text item delimiters to format
set separatedVString to text items of columnHeaders as string
set separatedVString to separatedVString & return
repeat with i from 1 to count theValues
set anItem to text item i of theValues as string
set separatedVString to separatedVString & anItem & return
end repeat
set AppleScript's text item delimiters to ASTID
set separatedVString to text 1 thru -2 of separatedVString as string
return separatedVString
end createSeparatedValue
To make the last touch in the Automation to paste the seperated values into Numbers.
set columnHeaders to {"FirstName", "LastName", "Age"}
set theValues to {{"John", "Doe", 42}, {"Jane", "Smith", 65}, {"Billy", "Idol", 25}}
set csvString to its createSeparatedValue(columnHeaders, theValues, ",")
set tsvString to its createSeparatedValue(columnHeaders, theValues, tab)
set csvEString to its createSeparatedValue(columnHeaders, theValues, ";")
set the clipboard to csvEString
-- select cell A:1
its selectCellFromRange("A1")
-- paste the sepated values to Numbers
pasteValueWithNumbers()
on pasteValueWithNumbers()
tell application "System Events" to tell application process "Numbers"
set frontmost to true
key code 9 using {command down}
end tell
end pasteValueWithNumbers
on selectCellFromRange(rangeValue)
tell application "Numbers"
tell table 1 of sheet 1 of front document
set value of cell rangeValue to missing value
end tell
end tell
end selectCellFromRange
on createSeparatedValue(columnHeaders, theValues, format)
set ASTID to AppleScript's text item delimiters
set AppleScript's text item delimiters to format
set separatedVString to text items of columnHeaders as string
set separatedVString to separatedVString & return
repeat with i from 1 to count theValues
set theValue to text item i of theValues as string
set separatedVString to separatedVString & theValue & return
end repeat
set AppleScript's text item delimiters to ASTID
set separatedVString to text 1 thru -2 of separatedVString as string
return separatedVString
end createSeparatedValue
Here we also include: alignment
set columnHeaders to {"First Name", "Last Name", "Age"}
set theValues to {{"John", "Doe", 42}, {"Jane", "Smith", 65}, {"Billy", "Idol", 25}}
-- set csvString to its createSeparatedValue(columnHeaders, theValues, ",")
-- set tsvString to its createSeparatedValue(columnHeaders, theValues, tab)
set csvEString to its createSeparatedValue(columnHeaders, theValues, ";")
set the clipboard to csvEString
-- select cell B:3
its selectCellFromRange("B3")
-- paste the sepated values to Numbers
its pasteValueWithNumbers()
its setAlignmentInRange("B3:D5", "left")
on setAlignmentInRange(rangeString, alignmentString)
tell application "Numbers"
tell table 1 of sheet 1 of front document
if alignmentString is "left" then
set alignment of range rangeString to left
else if alignmentString is "right" then
set alignment of range rangeString to right
else if alignmentString is "center" then
set alignment of range rangeString to center
end if
end tell
end tell
end setAlignmentInRange
on pasteValueWithNumbers()
tell application "System Events" to tell application process "Numbers"
set frontmost to true
key code 9 using {command down}
end tell
end pasteValueWithNumbers
on selectCellFromRange(rangeString)
tell application "Numbers"
tell table 1 of sheet 1 of front document
set value of cell rangeString to missing value
end tell
end tell
end selectCellFromRange
on createSeparatedValue(columnHeaders, theValues, format)
set ASTID to AppleScript's text item delimiters
set AppleScript's text item delimiters to format
set separatedVString to text items of columnHeaders as string
set separatedVString to separatedVString & return
repeat with i from 1 to count theValues
set theValue to text item i of theValues as string
set separatedVString to separatedVString & theValue & return
end repeat
set AppleScript's text item delimiters to ASTID
set separatedVString to text 1 thru -2 of separatedVString as string
return separatedVString
end createSeparatedValue
And as JavaScript
const separator = ',';
const quoteRE = new RegExp(`\\s+|${separator}`);
const csvString = [["John Jack", "Doe", 42], ["Jane", "Smith", `65"`], ["Billy", "Idol", 25]].map(inner => {
return inner.map(i => {
/* Replace double quotes with two double quotes in strings */
if (typeof i === "string" && i.includes('"')) {
i = i.replaceAll('"','""')
}
if (quoteRE.test(i)) {
return `"${i}"`
} else {
return i;
}
})}).join('\n')
console.log(csvString);
I omitted the headline and the parts setting the selection range and the clipboard and pasting from the clipboard, as this is trivial. The interesting part is building the CSV string. It uses the constant separator which is a comma here (but can be set to ; or \t for semicolon or tab). Then the textRE is built so that it matches space(s) or the separator. The next few lines loop over the outer array’s elements with map, building a new array from each element, again using map.
The simple approach of Alexas using separator alone will cause trouble for edge cases, namely a string containing the separator itself or a space. Therefore, these strings are surrounded by quotes in the if quoteRE.test() part.
Just before that, double quotes in strings are masked by replacing them with two quotes.
The test string has been modified to contain two of these edge cases. The script outputs
"John Jack",Doe,42
Jane,Smith,65""
Billy,Idol,25
when run.