Convert .CSV to .XLSX using command line

问题:

I'm looking for a way to batch-convert a series of .csv files to .xlsx using the command line.

I have tried a bunch of different VBScripts that I found but they all seem to be converting .xlsx to .csv and not the other way around.

Here is the closest one I could find but again it's .xlsx to .csv:

if WScript.Arguments.Count < 2 Then
    WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
    Wscript.Quit
End If
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
oBook.SaveAs WScript.Arguments.Item(1), 6
oBook.Close False
oExcel.Quit
WScript.Echo "Done"

Any ideas?

回答1:

only pre-requisite is that the ".csv" must be lower case in the filename:

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!"

回答2:

Disclaimer: I have written CSV2XLSX available as open-source at https://gitlab.com/DerLinkshaender/csv2xlsx

You may want to try an external tool like the one above. Why?

Advantages:

• may be used even where Windows Script Host is blocked.
• independent of operating system, so you may use a familiar tool with then same set of options on every OS (save brain memory :-) ).
• no installation of Excel or LibreOffice etc. needed, so you don't have to care about installing additional modules or languages.
• specifying several CSV parameters as command-line parameters makes the tool "SysAdmin-friendly", as they do not have to dig thru source code.
• you may even specify line or column ranges for the csv data.
• the xlsx file format is very complex, writing it without resorting to an installation of an office package is not for the faint of heart.
• better suited for batch processing as the tool can be integrated in the OS-specific loop logic.
• I tried to provide a useful set of command line flags with DevOps/SysAdmin in mind, many existing scripts lack good control via command-line options.

回答3:

For Windows, I recently responded to a similar question on SuperUser.com.

https://superuser.com/a/1011154/326177

I think Total CSV Converter is the least expensive option with the most features. It doesn't even require Excel to be installed and can can output CSV data to JSON, Access, DBF, XML or SQL.

http://www.coolutils.com/TotalCSVConverter

CSVConverter.exe <source> <destination> <options>

回答4:

Are you on Windows or Linux/Mac?

I might have a solution for you either way.

Here is the solution to your problems without the programs commented below:

https://social.msdn.microsoft.com/Forums/en-US/74df1378-7c0c-4c0f-b174-fa97a5c2969b/convert-csv-to-xlsx?forum=Vsexpressvb

EDIT So here is the basic solution:

Basically you apply a filter (which in the case would be the xlsx filter) from the directory where your file is located.

Nvm this I just saw you are on Windows ./directory --headless --convert-to xlsx:"Calc MS Excel 2007 XML" file.csv

in this case "Calc MS Excel 2007 XML" is the filter.

That works for single files, let me add batch in a sec.

回答5:

Here's a open-source tool for Windows machines I created using the NPOI libraries that does simple delimited file to XLS/XLSX conversions without Excel needing to be installed on the machine. The binary is in Bin/Debug if you don't want to build it yourself. All the necessary libraries are included in the executable so it can operate standalone.

https://github.com/nmolinos/csv2excel