Hi All,
I’m attempting to parse a downloaded .csv file. When the file is opened in either Numbers or Excel it displays correctly within the rows and columns.
However, as a text file it displays quite differently. The columns are still separated with commas but if there are commas or line returns in a cell then my script see’s them as commas even though they are encased within quotations.
For example…These 3 rows of data with 10 columns…
Date,Workout,Result,Prescribed,Pukie,Work performed,Work time,Formatted Result,Notes,Description
2015-10-09,Karen,558000,true,false,59203,558000,9 mins 18 secs,“”,“150 Wall Balls, 20 lbs”
2015-10-20,Clean & Jerk 1-1-1-1-1-1-1-1-1-1,670.0,true,false,7757,“670 kg | 50 kg, 50 kg, 60 kg, 60 kg, 70 kg, 70 kg, 75 kg, 75 kg, 80 kg, and 80 kg”,“”,“Sets
1 Clean & Jerk | 50 kg
1 Clean & Jerk | 50 kg
1 Clean & Jerk | 60 kg
1 Clean & Jerk | 60 kg
1 Clean & Jerk | 70 kg
1 Clean & Jerk | 70 kg
1 Clean & Jerk | 75 kg
1 Clean & Jerk | 75 kg
1 Clean & Jerk | 80 kg
1 Clean & Jerk | 80 kg”
The first line parses correctly, the second line see’s the comma in the quoted “Description” column of “150 Wall Balls, 20 lbs” as a text item delimiter.
Also, the line returns in the third row description confuse my script.
Is there an easy way to make my script treat any text within quotes as a complete text item and to disregard the included commas and line returns?
set theSettingsScriptPath to path to desktop folder from user domain as string
set theSettingsScriptPath to theSettingsScriptPath & "Library:ScriptingAdditions:dataTest.csv"
set csvData to read file theSettingsScriptPath
set csvEntries to paragraphs of csvData
repeat with i from 1 to count csvEntries
set {theDate, theWorkout, theResult, theRxd, thePukie, theWorkPerformed, theWorkTime, theFormattedResult, theNotes, theDescription} to parseCsvEntry(csvEntries's item i)
end repeat
to parseCsvEntry(csvEntry)
set AppleScript's text item delimiters to ","
set {theDate, theWorkout, theResult, theRxd, thePukie, theWorkPerformed, theWorkTime, theFormattedResult, theNotes, theDescription} to csvEntry's text items
set AppleScript's text item delimiters to {""}
return {theDate, theWorkout, theResult, theRxd, thePukie, theWorkPerformed, theWorkTime, theFormattedResult, theNotes, theDescription}
end parseCsvEntry