Convert XLSX to CSV correctly

Question

This is a problem very much like the one described here. However I need to do it horizontally, and my problems occur with the date. I'm on a Mac.

This is a picture of my .xlsx document. I have lots of entries like the ones in the first three rows, and I want to convert them into CSV as the last three ones. But my problem is this:

• 2012-08-16 (in A1) becomes 41137 (in A4)
• My session from 08:00 to 09:00 is 01:00 hour long (see H1 and I1 and J1) becomes a mess – ,0,333333333333333,0,375,
• My session from 09:00 to 10:00 has the same problem as the one above, only that the messy numbers are different.

My end goal is to export my .xlsx time sheet into toggl

P.S. Minor problems that may lead to the real ones:

• A1 2012-08-16 becomes 16-aug-12
• J1 01:00:00 becomes 01:00 as well as 08:00:00 becomes 08:00 and 09:00 becomes 08:00:00 and so on.

Solution

The easiest solution is to simply "Save As..." and select CSV as the file type.

I'm guessing you're trying to do this in some automated fashion. If the following assumptions are true:

• you're on a Windows platform
• Excel is installed

the easiest way to convert "XLSX" to "CSV" is with a bit of VB Script:

Set objArgs = WScript.Arguments
InputName = objArgs(0)
OutputName = objArgs(1)
Set objExcel = CreateObject("Excel.application")
objExcel.application.visible=false
objExcel.application.displayalerts=false
set objExcelBook = objExcel.Workbooks.Open(InputName)
objExcelBook.SaveAs OutputName, 23
objExcel.Application.Quit
objExcel.Quit

Invoke this as:

wscript script.vbs C:\...\file.xlsx C:\...\file.csv

Update: Take a look at this posting which performs the conversion with a Perl script.

Update 2 Apparently the VBA code is finicky with respect to paths. Unqualified paths are resolved relative to your documents directory. So for reproducible results, use a full path to the input and output files.

Comments

[jacob]

I'm on a mac. Yes, I do want to do it in an automated fashion.

[cusman]

I tried this out of curiosity and I get a 'file.xlsx' not found. Check the spelling of the file name, and verify that the file location is correct.. I ran the command from Command Prompt from within directory where all files are located.

[Devon_C_Miller]

Try using a full path to file.xlsx.