1

I need a programmatic way to repeatedly convert a downloaded CSV into an XLS, essentially keeping the same name of the file and just replacing the XLS each time. I work on a Mac 10.12, and am well-versed in the command line, but haven't found an easy way to do this without having to download a third-party library. I found an answer that gave me a VBscript that I can use on PowerShell, but I need some help with it.

I've downloaded PowerShell and I'm able to use it in Terminal. I have this script from another StackOverflow question:

Dim file, WB

With CreateObject("Excel.Application")
    On Error Resume Next
    For Each file In WScript.Arguments
        Set WB = .Workbooks.Open(file)
        WB.SaveAs Replace(WB.FullName, ".csv", ".xlsx"), 51
        WB.Close False
    Next    
    .Quit
End With

WScript.Echo "Done!"

What would be the command to execute this script (in PowerShell) with an argument?

I've tried a variation of execution commands but always get CommandNotFoundException, probably because I'm doing it super wrong. My ideal outcome here is that Desktop/data.csv can become Desktop/data.xls repeatedly, whether it's with this script or something infinitely easier for a Mac user.

Thank you!

*edited for specificity/simplification

impostorsyndrome
  • 103
  • 1
  • 10
  • 1
    You know that Excel is easily able to open CSV files, don't you? If you still want to create Excel files you could try the great module from Doug Finke [ImportExcel](https://www.powershellgallery.com/packages/ImportExcel/5.4.2). – Olaf May 09 '19 at 22:57
  • 2
    CreateObject() is COM automation and that's a Windows-only technology; that kind of automation of Excel is not going to work on a Mac. I doubt VBScript is going to work on a Mac either. (Microsoft Office scripting in VBA is not VBScript). Automating Excel might be possible with AppleScript, but Olaf's suggestion of ImportExcel is probably a neat route forward. – TessellatingHeckler May 09 '19 at 23:12

1 Answers1

4

I do not think that there is a native solution on Mac to do this. The simplest solution I known is to use Gnumeric, an open-source spreadsheet which has a terminal interface.

To install Gnumeric using Homebrew:

brew install gnumeric

Then you can use in the Terminal:

for f in *.csv; do ssconvert $f ${f%.csv}.xls; done

If it is a requirement to use Powershell, this should also be working:

gci *.csv | %{ssconvert $_ ($_.BaseName + ".xls")}

You can also use the ImportExcel Module, as suggested, but to my knowledge it will only converts in .xlsx and not in .xls as asked:

Install-Module -Name ImportExcel -RequiredVersion 5.4.2 
gci *.csv | %{Import-Csv $_ | Export-Excel ($_.basename + ".xlsx")}

Alternatively you can just change the extension from .csv to .xls, Excel will show an error message when the file is opened but will nonetheless do to the conversion by itself.

BeMayer
  • 380
  • 1
  • 9
  • Thanks for your response! If I can do this in the Terminal, that'd be ideal. I've downloaded gnumeric (says it's version 1.12.44), but when I try any command with ssconvert, i.e. `ssconvert data.csv data.xls` I get the same error: `TypeError: string indices must be integers, not unicode`. The line before the error line is `File "/Users/user/.virtualenvs/junk/lib/python2.7/site-packages/spreadsheetconverter/config.py", line 27, in __init__ fields = self.rules['fields']` so it seems to be going into something called `spreadsheetconverter`? This happens for any `ssconvert` command. – impostorsyndrome May 10 '19 at 15:15
  • 1
    Just a heads up -- I had `gnumeric` installed in a virtualenv which didn't seem to work well. When I deleted the virtualenv and ran the `ssconvert` command, it worked. Thank you for your help! – impostorsyndrome May 10 '19 at 17:53
  • I am glad it helped :) – BeMayer May 10 '19 at 18:21