在命令行上将 XLS 转换为 CSV

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

这台机器已经安装了 MicrosoftOffice2000。如果不能使用 MicrosoftOffice，我愿意安装 OpenOffice。

292585 次浏览
小开

用 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()

这里有一篇文章解释如何使用它

如何使用 Windows PowerShell 自动化 MicrosoftExcel？

小开

为什么不自己写呢？

从你的资料来看，你至少有一些 C #/。NET 经验。我会创建一个 Windows 控制台应用，并使用一个免费的 Excel 阅读器来读取你的 Excel 文件。我使用了可从 CodePlex 获得的 Excel 数据读取器，没有任何问题(一件好事: 这个阅读器不需要安装 Excel)。您可以从命令行调用控制台应用。

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

小开

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

小开
最佳答案

打开记事本，创建一个名为 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"

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

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

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

小开

斯科特 · F 的回答是我在互联网上找到的最好的。为了满足我的需要，我确实加入了他的代码。我补充道:

关于 Error Resume Next <-在顶部解释批处理中缺少的 xls 文件。 OBook.申请。栏(“ A: J”)。NumberFormat = “@” <-在 SaveAs 行之前，确保我的数据被保存为文本格式，以防止 Excel 删除数据中的前导零和数字字符串中的逗号，例如(1,200到1200)。列的范围应调整，以满足您的需要(A: J)。

我还删除了回声“完成”，使其非互动性。

然后，我将该脚本添加到 cmd 批处理文件中，以便通过任务每小时处理自动化数据。

小开

我尝试了 ScottF VB 解决方案，并得到了它的工作。然而，我想转换多选项卡(工作簿) Excel 文件为一个单一的。Csv 文件。

这没有工作，只有一个标签(当我打开它通过 Excel 突出显示)得到复制。

是否有人知道一个脚本可以将一个多选项卡的 Excel 文件转换成一个. csv 文件？

小开

一个稍微修改过的 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

我已经将脚本重命名为 ExcelToCSv，因为这个脚本根本不限于 xls。Xlsx 工作正常，如我们所料。

使用 Office2010进行测试。

小开

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

小开

我需要从不同的工作表中提取几个简历，所以这里有一个修改过的 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
小开

基于 Jon of All Trade 提供的信息，下面(~ n)解决了讨厌的双重延期问题: FOR /f "delims=" %%i IN ('DIR *.xlsx /b') DO ExcelToCSV.vbs "%%i" "%%~ni.csv"

小开

您可以使用用于 阿拉斯加数据库的 Alacon 命令行实用程序来完成这项工作。它与 Node.js 一起工作，因此需要先安装 Node.js，然后再安装 阿拉斯加包。

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

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

默认情况下，Alasql 从“ Sheet1”转换数据，但是您可以使用参数更改它:

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

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

小开

下面的版本将处理多个文件从窗口拖放。 基于以上作品

Christian Lemer
plang
ScottF

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

'* 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
小开

所有这些答案都帮助我构建了下面的脚本，它将通过在脚本上删除一个或多个文件(或通过命令行) ，自动将 XLS * 文件转换为 CSV 反之亦然。抱歉我的格式很糟糕。

' https://stackoverflow.com/questions/1858195/convert-xls-to-csv-on-command-line
' https://gist.github.com/tonyerskine/77250575b166bec997f33a679a0dfbe4


' https://stackoverflow.com/a/36804963/1037948
'* Global Settings and Variables
Set args = Wscript.Arguments


For Each sFilename In args
iErr = ConvertExcelFormat(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 ConvertExcelFormat(srcFile)


if IsEmpty(srcFile) OR srcFile = "" Then
WScript.Echo "Error! Please specify at least one source path. Usage: " & WScript.ScriptName & " SourcePath.xls*|csv"
ConvertExcelFormat = -1
Exit Function
'Wscript.Quit
End If


Set objFSO = CreateObject("Scripting.FileSystemObject")


srcExt = objFSO.GetExtensionName(srcFile)


' the 6 is the constant for 'CSV' format, 51 is for 'xlsx'
' https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlfileformat-enumeration-excel
' https://www.rondebruin.nl/mac/mac020.htm
Dim outputFormat, srcDest


If LCase(Mid(srcExt, 1, 2)) = "xl" Then
outputFormat = 6
srcDest = "csv"
Else
outputFormat = 51
srcDest = "xlsx"
End If


'srcFile = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
srcFile = objFSO.GetAbsolutePathName(srcFile)
destFile = Replace(srcFile, srcExt, srcDest)


Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(srcFile)
' preserve formatting? https://stackoverflow.com/a/8658845/1037948
'oBook.Application.Columns("A:J").NumberFormat = "@"
oBook.SaveAs destFile, outputFormat
oBook.Close False
oExcel.Quit
WScript.Echo "Conversion complete of '" & srcFile & "' to '" & objFSO.GetFileName(destFile) & "'"


End Function
小开

* UTF-8适用于 MicrosoftOffice2016或更高版本！

试试这个代码:

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 = 62


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
小开

在桌面上创建一个名为“ xls2csv.vbs”的 TXT 文件并粘贴代码:

Dim vExcel
Dim vCSV
Set vExcel = CreateObject("Excel.Application")
Set vCSV = vExcel.Workbooks.Open(Wscript.Arguments.Item(0))
vCSV.SaveAs WScript.Arguments.Item(0) & ".csv", 6
vCSV.Close False
vExcel.Quit

将一个 XLS 文件拖动到它(如“ test.XLS”)。它将创建一个转换后的名为“ test.xls.CSV”的 CSV 文件。然后，将其重命名为“ test.csv”。成交。

小开

For anyone wondering how to get your LOCAL delimiter to show up in the csv files instead of the comma/tab this is how you do it. This was soo challenging to find and I am amazed no one ran into it before 2022?

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)


local = true
oBook.SaveAs dest_file, csv_format, 0, 0, 0, 0, 0, 0, 0, 0, 0, local


oBook.Close False
oExcel.Quit

提交答案