使用VBScript将xls / xlsx文件（所有工作表）转换为csv（以分号分隔） [英] Convert xls / xlsx files (all sheets) to csv using VBScript (delimited by semicolons)

问题描述

我需要将包含其中所有工作表的excel文件（* .xls，* .xlsx）转换为每张表拆分的CSV文件。
输出的CSV文件应该由SheetName命名，并以分号分隔，而不是逗号。
我这样做，通过绑定几个VBS脚本到一个，但我还有一个bug。
我的代码下面的XLS文件中的所有工作表的转换分号分号，以sheet命名 - 但一个小错误是，如果有多个工作表，下一个工作表的内容被第一个工作表覆盖。
有什么错误吗？在cmd中运行它：
xls_to_csv.vbs ExcelFile.xls OutPutdir

I need to convert an excel file (*.xls , *.xlsx) including ALL worksheets inside into a CSV file split per sheet. The output CSV file(s) should be named by SheetName(s) and delimited by semi-colons, instead of commas. I have done this, by bonding several VBS scripts into one, but I still have there a bug. My code below does convertion for all sheets in XLS file delimited by semicolons,named by sheets - BUT a little bug is that if there is more than 1 worksheet, the contents of next sheets are overwritten by a first sheet. Whats wrong there please? Im running it in cmd with: xls_to_csv.vbs ExcelFile.xls OutPutdir

'------------------- SET SEMI-COLON DELIMITER VIA WIN REGISTERS ---------------
strDelimiter = ";"

strSystemDelimiter = ""           ' This will be used to store the current sytem value
Const HKEY_CURRENT_USER = &H80000001

' Get the current List Separator (Regional Settings) from the registry
strKeyPath = "Control Panel\International"
strValueName = "sList"
strComputer = "."
Set objRegistry = GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv")
objRegistry.GetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strSystemDelimiter

' Set it temporarily to our custom delimiter
objRegistry.SetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strDelimiter


'----------------------------- CONVERT XLS TO CSV ------------------------------
Dim strExcelFileName
Dim strCSVFileName
Dim objFSO
Set objFSO = CreateObject("scripting.filesystemobject")  
strPath = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(1))

strExcelFileName = WScript.Arguments.Item(0)  'file name to parses

rem get path where script is running
Set fso = CreateObject ("Scripting.FileSystemObject")  'use this to find current path
strScript = Wscript.ScriptFullName
strScriptPath = fso.GetAbsolutePathName(strScript & "\..")

rem If the Input file is NOT qualified with a path, default the current path
LPosition = InStrRev(strExcelFileName, "\")
if LPosition = 0 Then 'no folder path
strExcelFileName = strScriptPath & "\" & strExcelFileName
strScriptPath = strScriptPath & "\"
else                 'there is a folder path, use it for the output folder path also
strScriptPath = Mid(strExcelFileName, 1, LPosition)
End If
rem msgbox LPosition & " - " & strExcelFileName & " - " & strScriptPath  ' use this for debugging

Dim objXL
Dim objWorkBook, local
Set objXL = CreateObject("Excel.Application")
Set objWorkBook = objXL.Workbooks.Open(strExcelFileName)

objXL.DisplayAlerts = False
rem loop over worksheets
  For Each sheet In objWorkBook.Sheets 
      'only saveAS sheets that are NOT empty
if objXL.Application.WorksheetFunction.CountA(sheet.Cells) <> 0 Then
rem             sheet.Rows(1).delete  ' this will remove Row 1 or the header Row
local = true  
call objWorkBook.SaveAs(strPath & "\" & sheet.Name & ".csv", 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, local)
    End If
  Next

rem clean up 
objWorkBook.Close
objXL.quit
Set objXL = Nothing
Set objWorkBook = Nothing
Set fso = Nothing

'------------------------- RETURN REGISTRY CHANGES BACK --------------------
' Reset the system setting to its original value
objRegistry.SetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strSystemDelimiter

rem end script    

推荐答案

我的建议是使用不同的语言。为了改变分隔符，编辑注册表项至少是hacky。​​

My suggestion would be to use different language for this. Editing registry entries for the sake of changing delimiter is "hacky" to say at least.

它可以通过简单的Python脚本和使用xlrd和csv包来完成。这将使它可以在多个平台上使用。您可以使用Py2exe轻松地将Python脚本转换为.exe。

It can possibly be done by simple Python script and using xlrd and csv packages. This would make it usable on multiple platforms. You can easily convert your Python script to .exe using Py2exe.

这篇关于使用VBScript将xls / xlsx文件（所有工作表）转换为csv（以分号分隔）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！