windows 在命令行上将 XLS 转换为 CSV

Convert XLS to CSV on command line

提问by Joel

How could I convert an XLS file to a CSV file on the windows command line.

如何在 Windows 命令行上将 XLS 文件转换为 CSV 文件。

The machine has Microsoft Office 2000 installed. I'm open to installing OpenOffice if it's not possible using Microsoft Office.

机器安装了 Microsoft Office 2000。如果无法使用 Microsoft Office，我愿意安装 OpenOffice。

回答by ScottF

Open Notepad, create a file called XlsToCsv.vbs and paste this in:

打开记事本，创建一个名为 XlsToCsv.vbs 的文件并将其粘贴到：

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"

Then from a command line, go to the folder you saved the .vbs file in and run:

然后从命令行，转到保存 .vbs 文件的文件夹并运行：

XlsToCsv.vbs [sourcexlsFile].xls [destinationcsvfile].csv

This requires Excel to be installed on the machine you are on though.

不过，这需要在您所在的计算机上安装 Excel。

回答by plang

A slightly modified version of ScottF answer, which does not require absolute file paths:

ScottF 答案的稍微修改版本，它不需要绝对文件路径：

if WScript.Arguments.Count < 2 Then
    WScript.Echo "Please specify the source and the destination files. Usage: ExcelToCsv <xls/xlsx source file> <csv destination file>"
    Wscript.Quit
End If

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit

I have renamed the script ExcelToCsv, since this script is not limited to xls at all. xlsx Works just fine, as we could expect.

我已将脚本重命名为 ExcelToCsv，因为该脚本根本不限于 xls。xlsx 工作得很好，正如我们所料。

Tested with Office 2010.

使用 Office 2010 进行测试。

回答by plang

A small expansion on ScottF's groovy VB script: this batch file will loop through the .xlsx files in a directory and dump them into *.csv files:

ScottF 的 groovy VB 脚本的一个小扩展：这个批处理文件将遍历目录中的 .xlsx 文件并将它们转储到 *.csv 文件中：

FOR /f "delims=" %%i IN ('DIR *.xlsx /b') DO ExcelToCSV.vbs "%%i" "%%i.csv"

Note: You may change extension .xlsx to .xls andname of script ExcelToCSV to XlsToCsv

注意：您可以将扩展名 .xlsx 更改为 .xls 并将脚本 ExcelToCSV 的名称更改为 XlsToCsv

回答by YOU

How about with PowerShell?

使用 PowerShell 怎么样？

Code should be looks like this, not tested though

代码应该是这样的，虽然没有经过测试

$xlCSV = 6
$Excel = New-Object -Com Excel.Application 
$Excel.visible = $False 
$Excel.displayalerts=$False 
$WorkBook = $Excel.Workbooks.Open("YOUDOC.XLS") 
$Workbook.SaveAs("YOURDOC.csv",$xlCSV) 
$Excel.quit()

Here is a post explaining how to use it

这是一篇解释如何使用它的帖子

How Can I Use Windows PowerShell to Automate Microsoft Excel?

如何使用 Windows PowerShell 自动化 Microsoft Excel？

回答by Christian Lemer

I had a need to extract several cvs from different worksheets, so here is a modified version of plang code that allows you to specify the worksheet name.

我需要从不同的工作表中提取几个 cvs，所以这里有一个修改版的 plang 代码，它允许您指定工作表名称。

if WScript.Arguments.Count < 3 Then
    WScript.Echo "Please specify the sheet, the source, the destination files. Usage: ExcelToCsv <sheetName> <xls/xlsx source file> <csv destination file>"
    Wscript.Quit
End If

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(1))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(2))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.Sheets(WScript.Arguments.Item(0)).Select
oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit

回答by Chris Rudd

Here is a version that will handle multiple files drag and dropped from windows. Based on the above works by

这是一个可以处理从 Windows 拖放的多个文件的版本。基于以上作品

Christian Lemer
plang
ScottF

Open Notepad, create a file called XlsToCsv.vbs and paste this in:

打开记事本，创建一个名为 XlsToCsv.vbs 的文件并将其粘贴到：

'* Usage: Drop .xl* files on me to export each sheet as CSV

'* Global Settings and Variables
Dim gSkip
Set args = Wscript.Arguments

For Each sFilename In args
    iErr = ExportExcelFileToCSV(sFilename)
    ' 0 for normal success
    ' 404 for file not found
    ' 10 for file skipped (or user abort if script returns 10)
Next

WScript.Quit(0)

Function ExportExcelFileToCSV(sFilename)
    '* Settings
    Dim oExcel, oFSO, oExcelFile
    Set oExcel = CreateObject("Excel.Application")
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    iCSV_Format = 6

    '* Set Up
    sExtension = oFSO.GetExtensionName(sFilename)
    if sExtension = "" then
        ExportExcelFileToCSV = 404
        Exit Function
    end if
    sTest = Mid(sExtension,1,2) '* first 2 letters of the extension, vb's missing a Like operator
    if not (sTest =  "xl") then
        if (PromptForSkip(sFilename,oExcel)) then
            ExportExcelFileToCSV = 10
            Exit Function
        end if
    End If
    sAbsoluteSource = oFSO.GetAbsolutePathName(sFilename)
    sAbsoluteDestination = Replace(sAbsoluteSource,sExtension,"{sheet}.csv")

    '* Do Work
    Set oExcelFile = oExcel.Workbooks.Open(sAbsoluteSource)
    For Each oSheet in oExcelFile.Sheets
        sThisDestination = Replace(sAbsoluteDestination,"{sheet}",oSheet.Name)
        oExcelFile.Sheets(oSheet.Name).Select
        oExcelFile.SaveAs sThisDestination, iCSV_Format
    Next

    '* Take Down
    oExcelFile.Close False
    oExcel.Quit

    ExportExcelFileToCSV = 0
    Exit Function
End Function

Function PromptForSkip(sFilename,oExcel)
    if not (VarType(gSkip) = vbEmpty) then
        PromptForSkip = gSkip
        Exit Function
    end if

    Dim oFSO
    Set oFSO = CreateObject("Scripting.FileSystemObject")

    sPrompt = vbCRLF & _
        "A filename was received that doesn't appear to be an Excel Document." & vbCRLF & _
        "Do you want to skip this and all other unrecognized files?  (Will only prompt this once)" & vbCRLF & _
        "" & vbCRLF & _
        "Yes    - Will skip all further files that don't have a .xl* extension" & vbCRLF & _
        "No     - Will pass the file to excel regardless of extension" & vbCRLF & _
        "Cancel - Abort any further conversions and exit this script" & vbCRLF & _
        "" & vbCRLF & _
        "The unrecognized file was:" & vbCRLF & _
        sFilename & vbCRLF & _
        "" & vbCRLF & _
        "The path returned by the system was:" & vbCRLF & _
        oFSO.GetAbsolutePathName(sFilename) & vbCRLF

    sTitle = "Unrecognized File Type Encountered"

    sResponse =  MsgBox (sPrompt,vbYesNoCancel,sTitle)
    Select Case sResponse
    Case vbYes
        gSkip = True
    Case vbNo
        gSkip = False
    Case vbCancel
        oExcel.Quit
        WScript.Quit(10)    '*  10 Is the error code I use to indicate there was a user abort (1 because wasn't successful, + 0 because the user chose to exit)
    End Select

    PromptForSkip = gSkip
    Exit Function
End Function

回答by Jay Riggs

Why not write your own?

为什么不自己写？

I see from your profile you have at least some C#/.NET experience. I'd create a Windows console application and use a free Excel reader to read in your Excel file(s). I've used Excel Data Readeravailable from CodePlex without any problem (one nice thing: this reader doesn't require Excel to be installed). You can call your console application from the command line.

我从你的个人资料中看到你至少有一些 C#/.NET 经验。我会创建一个 Windows 控制台应用程序并使用免费的 Excel 阅读器来读取您的 Excel 文件。我已经使用了 CodePlex 提供的Excel 数据阅读器，没有任何问题（一件好事：这个阅读器不需要安装 Excel）。您可以从命令行调用控制台应用程序。

If you find yourself stuck post here and I'm sure you'll get help.

如果你发现自己被困在这里，我相信你会得到帮助。

回答by agershun

You can do it with Alacon - command-line utility for Alasqldatabase. It works with Node.js, so you need to install Node.jsand then Alasqlpackage.

您可以使用 Alacon - Alasql数据库的命令行实用程序来完成。它适用于 Node.js，因此您需要安装Node.js，然后安装Alasql包。

To convert Excel file to CVS (ot TSV) you can enter:

要将 Excel 文件转换为 CVS (ot TSV)，您可以输入：

> node alacon "SELECT * INTO CSV('mydata.csv', {headers:true}) FROM XLS('mydata.xls', {headers:true})"

By default Alasql converts data from "Sheet1", but you can change it with parameters:

默认情况下，Alasql 转换来自“Sheet1”的数据，但您可以使用参数更改它：

{headers:false, sheetid: 'Sheet2', range: 'A1:C100'}

Alacon supports other type of conversions (CSV, TSV, TXT, XLSX, XLS) and SQL language constructions (see User Manualfor examples).

Alacon 支持其他类型的转换（CSV、TSV、TXT、XLSX、XLS）和 SQL 语言结构（示例参见用户手册）。

回答by Tim Robinson

There's an Excel OLEDB data provider built into Windows; you can use this to 'query' the Excel sheet via ADO.NET and write the results to a CSV file. There's a small amount of coding required, but you shouldn't need to install anything on the machine.

Windows 中内置了一个 Excel OLEDB 数据提供程序；您可以使用它通过 ADO.NET“查询”Excel 工作表并将结果写入 CSV 文件。需要少量编码，但您不需要在机器上安装任何东西。

回答by Charles Crous

Building on what Jon of All Trades has provided, the following (~n) removed the pesky double extension issue: FOR /f "delims=" %%i IN ('DIR *.xlsx /b') DO ExcelToCSV.vbs "%%i" "%%~ni.csv"

基于 Jon of All Trades 提供的内容，以下 (~n) 删除了讨厌的双重扩展问题： FOR /f "delims=" %%i IN ('DIR *.xlsx /b') DO ExcelToCSV.vbs "%%i" "%%~ni.csv"