Hi,
I need help again as I do not have logic with coding.
Does someone know a script that will parse a .CSV formatted like this:
“ligne1”,“ligne11”,“ligne111”,“ligne1111”
“ligne2”,“ligne22”,“ligne222”,“ligne2222”
“ligne3”,“ligne33”,“ligne333”,“ligne3333”
and to output the following as a file:
<?xml version="1.0" encoding="UTF-8"?>Thanks and regards.
Henri
Try this. Just put in the inFile (the path to the csv file) and the outFile (path to the output file) variables.
set inFile to (path to desktop as text) & "a.csv"
set outFile to (path to desktop as text) & "a.xml"
set p1 to "<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<opml version=\"1.0\">
<head>
<title></title>
</head>
<body>"
set p2 to "</body>
</opml>"
-- compile the output text
set theLines to paragraphs of (read file inFile)
set csvText to p1
repeat with aLine in theLines
set theWords to words of aLine
set thisOne to " <outline text=\"" & item 1 of theWords & "\">
<outline text=\"" & item 2 of theWords & "\">
<outline text=\"" & item 3 of theWords & "\">
<outline text=\"" & item 4 of theWords & "\"/>
</outline>
</outline>
</outline>"
set csvText to csvText & return & thisOne
end repeat
set csvText to csvText & return & p2
-- write the file to outFile
try
set openFile to open for access file (outFile as text) with write permission
set eof of openFile to 0
write csvText to openFile starting at eof as text
close access openFile
on error
try
close access file (outFile as text)
end try
end try
Hi Hank,
Thank you so much, it works great.
I just maybe have made a mistake describing my csv file.
I give the following sample:
“ligne1”,“ligne11”,“ligne111”,“ligne1111”
but in fact can be a sentence into the content like this and also French accentuated characters:
“ligne1 éèü blabla blabla”,“ligne11 some text”,"ligne111 again some text ",“ligne1111 some text”
Then the output result takes only the first word instead of the full content of the field and French accents are not rendered.
I have also tried to do this:
set inFile to (choose file with prompt “Select a file to read:” as text)
set outFile to (choose file name with prompt “Save file as:” as text)
instead of this:
set inFile to (path to desktop as text) & “csv.txt”
set outFile to (path to desktop as text) & “a.xml”
But I get an error “impossible to render file (alias: MachintoshHD:etc…) as type file” when it read the following line:
set theLines to paragraphs of (read file inFile).
But I can clearly use the desktop solution.
Again thanks a lot.
Regards.
Henri
You’re welcome. It’s a simple fix to apply your updates. For example to handle the sentences as you explained we can use “text item delimiters” instead of “words” to separate the lines. And to handle the reading/writing of words with “French accentuated characters” we can read/write as utf-8 (I added handlers to do the reading and writing).
So try this…
set inFile to choose file with prompt "Select a file to read:"
set outFile to choose file name with prompt "Save file as:"
set p1 to "<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<opml version=\"1.0\">
<head>
<title></title>
</head>
<body>"
set p2 to "</body>
</opml>"
-- compile the output text
set theLines to paragraphs of readFrom_UTF8(inFile)
set csvText to p1
repeat with aLine in theLines
set {tids, text item delimiters} to {text item delimiters, ","}
set theWords to text items of aLine
set text item delimiters to tids
--set theWords to words of aLine
set thisOne to " <outline text=" & item 1 of theWords & ">
<outline text=" & item 2 of theWords & ">
<outline text=" & item 3 of theWords & ">
<outline text=" & item 4 of theWords & "/>
</outline>
</outline>
</outline>"
set csvText to csvText & return & thisOne
end repeat
set csvText to csvText & return & p2
-- write the file to outFile
writeTo_UTF8(outFile, csvText, false)
(************** SUBROUTINES ****************)
on readFrom_UTF8(targetFile)
try
set targetFile to targetFile as text
targetFile as alias -- if file doesn't exist then you get an error
set openFile to open for access file targetFile
set theText to read openFile as «class utf8»
close access openFile
return theText
on error
try
close access file targetFile
end try
return false
end try
end readFrom_UTF8
on writeTo_UTF8(targetFile, theText, appendText)
try
set targetFile to targetFile as text
set openFile to open for access file targetFile with write permission
if appendText is false then
set eof of openFile to 0
write «data rdatEFBBBF» to openFile starting at eof -- UTF-8 BOM
else
tell application "Finder" to set fileExists to exists file targetFile
if fileExists is false then
set eof of openFile to 0
write «data rdatEFBBBF» to openFile starting at eof -- UTF-8 BOM
end if
end if
write theText as «class utf8» to openFile starting at eof
close access openFile
return true
on error theError
try
close access file targetFile
end try
return theError
end try
end writeTo_UTF8
This would have worked if you had the parenthesis like this…
set inFile to (choose file with prompt "Select a file to read:") as text
set outFile to (choose file name with prompt "Save file as:") as text
Hi Hank,
Ouahh great, work perfectly.
Thanks for that and best regards.
Henri
Hi Hank,
Would it be possible while using this script to replace character & by &
Thanks and regards.
Henri
I added a subroutine findReplace() to handle this and perform the find/replace just before writing the new text. Good luck.
NOTE: you’ll have to fix the code because the & didn’t come through in the applescript, so fix that last term in the findReplace() line.
set inFile to choose file with prompt "Select a file to read:"
set outFile to choose file name with prompt "Save file as:"
set p1 to "<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<opml version=\"1.0\">
<head>
<title></title>
</head>
<body>"
set p2 to "</body>
</opml>"
-- compile the output text
set theLines to paragraphs of readFrom_UTF8(inFile)
set csvText to p1
repeat with aLine in theLines
set {tids, text item delimiters} to {text item delimiters, ","}
set theWords to text items of aLine
set text item delimiters to tids
--set theWords to words of aLine
set thisOne to " <outline text=" & item 1 of theWords & ">
<outline text=" & item 2 of theWords & ">
<outline text=" & item 3 of theWords & ">
<outline text=" & item 4 of theWords & "/>
</outline>
</outline>
</outline>"
set csvText to csvText & return & thisOne
end repeat
set csvText to csvText & return & p2
-- replace & with &
set csvText to findReplace(csvText, "&", "&")
-- write the file to outFile
writeTo_UTF8(outFile, csvText, false)
(************** SUBROUTINES ****************)
on findReplace(theString, search_string, replacement_string)
if theString contains search_string then
set AppleScript's text item delimiters to search_string
set text_item_list to text items of theString
set AppleScript's text item delimiters to replacement_string
set theString to text_item_list as text
set AppleScript's text item delimiters to ""
end if
return theString
end findReplace
on readFrom_UTF8(targetFile)
try
set targetFile to targetFile as text
targetFile as alias -- if file doesn't exist then you get an error
set openFile to open for access file targetFile
set theText to read openFile as «class utf8»
close access openFile
return theText
on error
try
close access file targetFile
end try
return false
end try
end readFrom_UTF8
on writeTo_UTF8(targetFile, theText, appendText)
try
set targetFile to targetFile as text
set openFile to open for access file targetFile with write permission
if appendText is false then
set eof of openFile to 0
write «data rdatEFBBBF» to openFile starting at eof -- UTF-8 BOM
else
tell application "Finder" to set fileExists to exists file targetFile
if fileExists is false then
set eof of openFile to 0
write «data rdatEFBBBF» to openFile starting at eof -- UTF-8 BOM
end if
end if
write theText as «class utf8» to openFile starting at eof
close access openFile
return true
on error theError
try
close access file targetFile
end try
return theError
end try
end writeTo_UTF8
Hi Hank,
Works perfectly. Thanks a lot.
Regards.
Henri