How to convert .xls file into .csv without MS excel?

It should be possible in either App. Just open the xls file in either app, and use their "Save As'' or Export Feature to save it as a CSV.

Are you having an issue with this in either application?

Hi Craft79

That is not a true Excel spreadsheet. It's an HTML file, possibly exported from a spreadsheet, with the file extension set to ".xls"

My desktop version of Excel will open it and display the contents as a table, after protesting that the format does not match the extension. But Excel is a much more powerful program than Numbers. Numbers just shows you the raw contents.

Change the extension of the original file to ".html"

Click Use .html

Then double-click it. It will open in your default web browser:

If you open it in a text editor it looks like this:

It's HTML.

So what you actually want to do is to convert an .html file to .csv.

Let me see...

Hi Craft79

It's not possible to do this with Automator alone, as there are no Automator Actions for Numbers.

However, it is possible with AppleScript and Numbers. It's equally possible to run an AppleScript from within an Automator workflow if you want to. But below is a pure AppleScript solution.

This AppleScript first creates an 'output' folder in your Documents folder called 'Converted Excel files', then asks you to select the files you want to convert. Only .xls and .xlsx files can be selected.

The script then opens each file in Numbers, exports a .csv file with the same name to the 'output' folder, then closes the original Excel file. Slight warning: any files of the same name will be overwritten in the 'output' folder if you run the script twice.

To run the script, launch Script Editor, copy and paste the script below into a new window, then click the Hammer button to compile the script (it should be colour coded for syntax). If you get no errors, click the Run button (forward arrow). I would suggest you only select a couple of Excel files to start with, to make sure it has the desired effect.

I have not tested it with Excel documents containing charts, or complex functions, pivot tables etc.

--begin script

--check for an output folder and create it if necessary:

set docs_folder to path todocuments folder

set conversion_folder to (docs_folder as string) & "Converted Excel files"

tell application "Finder" to if not (exists folder conversion_folder) then ¬

make new folder at docs_folder with properties {name:"Converted Excel files"}

--ask user to choose Excel files (.xlsx or xls):

set file_list to (choose file of type {"org.openxmlformats.spreadsheetml.sheet", ¬

"com.microsoft.Excel.xls"} with multiple selections allowed)

--process each file in turn by opening in Numbers, exporting as csv to the ouput folder, then closing the original

repeat with each_file in the file_list

tell application "Numbers"

activate

openeach_file

repeat while not (existsdocument 1) -- wait for Excel file to open in Numbers

end repeat

set file_name to name of document 1

export document 1 to file (conversion_folder & ":" & file_name & ".csv") as CSV

closedocument 1 without saving

end tell

end repeat

--end script

Hope it helps!

H

EDIT - added Numbers activate command. Numbers may throw errors without it.

Hi Craft79,

(Your script is replacing one comma with another and will have no effect).

The problem is that ".csv" means Comma Separated Values. Any comma in the original data in a .csv file is seen by Numbers (and a lot of other programs) as a field separator. In your original html file this is fine, but as I said right at the start, as far as I'm aware there is no way of importing commas within the individual chunks of data.

In a .csv file, Numbers sees

1, 2, 3

as three pieces of data and

1,10471, 2,7361836, 3,12356861235

As six pieces of data.

The quickest solution is to import the data into Numbers as above and then use its search and replace function to swap the full stops with commas. In a quick test (working the other way as I use decimal points), this will let you perform calculations on the replaced text.

The other way, which would need going back to the start with the script, would be to save your html as a Tab Delimited .txt file. This should allow the commas to come through unscathed.

Hi Craft 79,

Try this:

--begin script

--confirm existence of folder called 'dati trading csv' in downloads folder.

-- create it if it doesn't exist:

set downloads_folder to path todownloads folder

set conversion_folder to (downloads_folder as string) & "dati trading csv"

tell application "Finder" to if not (exists folder conversion_folder) then ¬

makenewfolderatdownloads_folderwith properties {name:"dati trading csv"}

--choose the original files

set file_list to (choose file of type {"org.openxmlformats.spreadsheetml.sheet", ¬

"com.microsoft.Excel.xls"} with multiple selections allowed)

repeat with each_file in file_list

--parse name of original file without extension:

tell application "System Events" to set file_name to name of each_file

set old_tids to AppleScript'stext item delimiters

set AppleScript'stext item delimiters to "."

set base_name to text item 1 of file_name

set AppleScript'stext item delimiters to old_tids

--process text for each file

set old_text to readeach_file

set nu_text to ""

set parcount to countparagraphs in old_text

repeat with x from 1 to parcount

set next_par to paragraphx of old_text--build new text string ignoring html headers etc

if next_par begins with "<th>" then set nu_text to nu_text & next_par & return

if next_par contains "<td>" then set nu_text to nu_text & next_par & return

end repeat

--remove final return from string:

set nu_text to text 1 thru -2 of nu_text

--search and replace...

set nu_text to replace_chars(nu_text, "</td><td>", "\",\"") -- replace html code with comma separator surrounded by double quotes

set nu_text to replace_chars(nu_text, "<td>", "\"") --remove html code at beginning of line and replace with double quote

set nu_text to replace_chars(nu_text, "</td>", "\"") --remove html code at end of line

set nu_text to replace_chars(nu_text, "</th><th>", "\",\"") --ditto

set nu_text to replace_chars(nu_text, "<th>", "\"") --ditto

set nu_text to replace_chars(nu_text, "</th>", "\"") --ditto

--set up path and name of new .csv file:

set nu_file to conversion_folder & ":" & base_name & ".csv"

--create file if it doesn't exist, empty it if it does, write text to it and close it

try

set text_write to (open for accessfilenu_file with write permission)

set eoftext_writeto 0

writenu_texttotext_write

close accesstext_write

on error

close accessnu_file

end try

end repeat

-- search and replace handler from osxautomation.com (and many other sources). Used with thanks:

on replace_chars(this_text, search_string, replacement_string)

set AppleScript'stext item delimiters to the search_string

set the item_list to every text item of this_text

set AppleScript'stext item delimiters to the replacement_string

set this_text to the item_list as string

set AppleScript'stext item delimiters to ""

return this_text

end replace_chars

-- end script

With thanks to SGill for pointing me in the right direction.

Well it is working to the extent that it's creating a new .csv file 😉

But it looks as though the original file has been saved as xml. The files I have tested it on are plain Excel files.

Are you able to upload the original file to a file transfer website (wetransfer.com for example) so I can take a look at it?

What do you see if you manually open the original Excel file with Numbers?

HD wrote:

as far as I'm aware there is no way of importing commas within the individual chunks of data.

Hi HD,

I may be misunderstanding here, but the csv standard (which is a pretty loose standard but still has some conventions) calls for chunks of data containing imbedded commas to be surrounded with double quotes. Most parsers recognize that. So you should be able to have your script surround the chunks with "".

SG

Hi Craft79,

"path to documents folder" in my original script is an AppleScript command that returns the path to the current user's Documents folder. You don't need to hard-code anything except the name of the folder that it contains.

"path to downloads folder" is a similar command, so if you want a different path, then try this:

set docs_folder to path todownloads folder

set conversion_folder to (docs_folder as string) & "dati trading csv"

tell application "Finder" to if not (exists folder conversion_folder) then ¬

makenewfolderatdocs_folderwith properties {name:"dati trading csv"}

... followed by the rest of the script.

Hope this helps, let me know what happens!

H

OK,

I believe this script will work with the file you uploaded. I can't guarantee that it will work with any others.

It doesn't use Numbers, it uses AppleScript's own text processing routines and a time-honoured handler for string replacement.

There are two issues.

First, the original data uses commas as decimal separators, as is the convention in continental Europe. Writing a comma-separated file that contains non-separating commas was beyond me, so I have converted the decimal separators into points. You may find that when you open the CSV file they are silently converted back to commas, but I can't test that.

Second, it will will not work if you use it with a "genuine" Excel spreadsheet. (Although my original script will.)

Here's the script:

--begin script

--confirm existence of folder called 'dati trading csv' in downloads folder.

-- create it if it doesn't exist:

set downloads_folder to path todownloads folder

set conversion_folder to (downloads_folder as string) & "dati trading csv"

tell application "Finder" to if not (exists folder conversion_folder) then ¬

makenewfolderatdownloads_folderwith properties {name:"dati trading csv"}

--choose the original files

set file_list to (choose file of type {"org.openxmlformats.spreadsheetml.sheet", ¬

"com.microsoft.Excel.xls"} with multiple selections allowed)

repeat with each_file in file_list

--parse name of original file without extension:

tell application "System Events" to set file_name to name of each_file

set old_tids to AppleScript'stext item delimiters

set AppleScript'stext item delimiters to "."

set base_name to text item 1 of file_name

set AppleScript'stext item delimiters to old_tids

--process text for each file

set old_text to readeach_file

set nu_text to ""

set parcount to countparagraphs in old_text

repeat with x from 1 to parcount

set next_par to paragraphx of old_text--build new text string ignoring html headers etc - just including the table rows

if next_par begins with "<th>" then set nu_text to nu_text & next_par & return

if next_par contains "<td>" then set nu_text to nu_text & next_par & return

end repeat

--remove final return from string:

set nu_text to text 1 thru -2 of nu_text

--search and replace...

set nu_text to replace_chars(nu_text, ",", ".") -- replace decimal comma with decimal point

set nu_text to replace_chars(nu_text, "</td><td>", ",") -- replace html code with comma separator

set nu_text to replace_chars(nu_text, "<td>", "") --remove html code at beginning of line

set nu_text to replace_chars(nu_text, "</td>", "") --remove html code at end of line

set nu_text to replace_chars(nu_text, "</th><th>", ",") --ditto

set nu_text to replace_chars(nu_text, "<th>", "") --ditto

set nu_text to replace_chars(nu_text, "</th>", "") --ditto

--set up path and name of new .csv file:

set nu_file to conversion_folder & ":" & base_name & ".csv"

--create file if it doesn't exist, empty it if it does, write converted text to it and close it

try

set text_write to (open for accessfilenu_file with write permission)

set eoftext_writeto 0

writenu_texttotext_write

close accesstext_write

on error

close accesstext_write

end try

end repeat

-- search and replace handler from osxautomation.com (and many other sources). Used with thanks:

on replace_chars(this_text, search_string, replacement_string)

set AppleScript'stext item delimiters to the search_string

set the item_list to every text item of this_text

set AppleScript'stext item delimiters to the replacement_string

set this_text to the item_list as string

set AppleScript'stext item delimiters to ""

return this_text

end replace_chars

-- end script

I hope this works for you.

H

Hi SGill, yes indeed and I thought I had tried that, but without success.

But what I had failed to do, and I've only just realised it, was to change the file extension on my hand-rolled, manually created test file from '.txt' to '.csv'.

Converting the original to lines like this from :

LABEL,"1,2","3","4,4","5","6,6","7"

LABEL,"1,2","3","4,4","5","6,6","7"

LABEL,"1,2","3","4,4","5","6,6","7"

appears to do the trick.

Working all those double quotes into the script will be quite an exercise, but I'll give it a try.

Cheers,

H

You should try it and see what happens. No reason to believe, at this point, that it is not possible,

If you really want to use Automator (you haven't explained why) then you might try posting in the Mac OS X Technologies section. Be sure to give enough details about what you are trying to do.

SG