VBScript to Convert Excel to CSV
Using Excel files as the source for Informatica mappings can be more trouble than it's worth. You have to setup a connection and name a range in the Excel document which can be difficult to maintain. I much prefer to read text or CSV files, they're simpler to work with and less error prone. I created a VB script for the purpose of converting Excel to CSV.
You can use the script to convert an Excel file into a CSV file or multiple CSV files, depending on how many worksheets the Excel file contains. The script can be ran from the command line, evoked from a PowerCenter Session's "Pre or Post-Session Command" property or from a Command task.
Here's an overview of the script:
- Accepts one parameter, the name of the Excel file
- Creates one CSV file for every worksheet in the Excel document
- The script will ignore worksheets without any data.
- The name of the worksheet is used to name the CSV document
- If you qualify the input file name with a folder path, that path will be where the CSV files are created
- If you do not qualify the input file name with a folder path, the path where the script is executed will be the default path for the input file and where the CSV files will be created.
syntax for calling script
Excel_To_CSV_All_Worksheets.vbs C:\folder name\File_Name.xlsx
Excel_To_CSV_All_Worksheets.vbs File_Name.xlsx
Modifying The Script
To make the script more flexible you could add a parameter(s) for the output folder. I included code in the script to ignore the header row (assumed as row 1) but have it commented out. You can use it if needed or add additional code to condition it with a parameter. That way you can control it.
See Also
This script is the companion to this CSV_To_Excel script located here.Windows 2008
If you cannot get vb scripts to run on your Windows 2008 server, please see this page.Download the script
The download is here.Thank you for your support
| or |  |
can you pls tell me where I'm Going Wrong ??
----------------------------------------------
rem XLS_To_CSV.vbs
rem =============================================================
rem convert all NON-empty worksheets in an Excel file to csv
rem CSV file names will default to Sheet names
rem output folder defaults to the folder where the script resides or
rem if path is specified with the input file, that path is used
rem
rem input parameter 1: Excel path\file in argument 1
rem (if path is not specified, the current path is defaulted)
rem
rem ============================================================
Dim strExcelFileName
Dim strCSVFileName
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 & "\\Natfs\Teams\Development\GSD\DataOperationsTeam\ODS\Master\GDPDailyBrief\DailyPlacementReport\SourceFile")
rem If the Input file is NOT qualified with a path, default the current path
LPosition = InStrRev(strExcelFileName, "4 - Schools & Placement Partners*.XLS")
if LPosition = 0 Then 'no folder path
strExcelFileName = strScriptPath & "\\Natfs\Teams\Development\GSD\DataOperationsTeam\ODS\Master\GDPDailyBrief\DailyPlacementReport\SourceFile" & strExcelFileName
strScriptPath = strScriptPath & "\\Natfs\Teams\Development\GSD\DataOperationsTeam\ODS\Master\GDPDailyBrief\DailyPlacementReport\SourceFile"
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
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
sheet.SaveAs strScriptPath & sheet.Name & ".csv", 6 'CSV
End If
Next
rem clean up
objWorkBook.Close
objXL.quit
Set objXL = Nothing
Set objWorkBook = Nothing
Set fso = Nothing
rem end script
-----------------------------------------------
If want to convert a file from \\Natfs\Teams\Development\GSD\DataOperationsTeam\ODS\Master\GDPDailyBrief\DailyPlacementReport\SourceFile
I recommend using an unaltered version of the script and executing it like so: Excel_To_CSV_All_Worksheets.vbs \\Natfs\Teams\Development\GSD\DataOperationsTeam\ODS\Master\GDPDailyBrief\DailyPlacementReport\SourceFile\File_Name.xlsx
i downloaded the script which u written and execute as directed in ur blog.but when i execute its showing following error.could u please clear it as i have a requirement of converting excel to csv on priority basis
" C:\Users\prasad\Desktop\New folder>Excel_To_CSV_All_Worksheets.vbs C:\Users\pra
sad\Desktop\New folder\Book1.xlsx
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.
C:\Users\prasad\Desktop\New folder\Excel_to_CSV_All_Worksheets.vbs(1, 1) Microso
ft VBScript compilation error: Invalid character"
Thanks,
If you are getting numerous blank rows, you may have to add some code (or another script) to delete the blank rows.
Please find the excel here https://drive.google.com/file/d/0Bwrrvs4ELBLfdUU1RVNiYXRPLVU/edit?usp=sharing
Thanks for any help...
Jeff
Can you please helpme with this very urgent..
I downloaded the script and put both the script and the source xls in the same folder and then ran the script via cmd window by writng
Excel_To_CSV_All_Worksheets.vbs C:\Users\kkzw377\Desktop\Script\GMT_524434 Run 1 Finalp_3c5a67.xls
I get error invalid charater code 800A0408 Line 16 char 46...but when I lok into the source code I cannot find any problem with the code as you see I am new to the VBScript code..
rem XLS_To_CSV.vbs
rem =============================================================
rem convert all NON-empty worksheets in an Excel file to csv
rem CSV file names will default to Sheet names
rem output folder defaults to the folder where the script resides or
rem if path is specified with the input file, that path is used
rem
rem input parameter 1: Excel path\file in argument 1
rem (if path is not specified, the current path is defaulted)
rem
rem ============================================================
Dim strExcelFileName
Dim strCSVFileName
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
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
sheet.SaveAs strScriptPath & sheet.Name & ".csv", 6 'CSV
End If
Next
rem clean up
objWorkBook.Close
objXL.quit
Set objXL = Nothing
Set objWorkBook = Nothing
Set fso = Nothing
rem end script
https://drive.google.com/file/d/0B-K0cuskxJZ7N3F2NEJIbFRyTEU/view?usp=sharing
I have the following issue when attempting to convert:
bash-3.2$ ./Excel_to_csv_All_Worksheets.vbs Houghton_04042016_COMPLETE_excel.xls
./Excel_to_csv_All_Worksheets.vbs: line 1: rem: command not found
./Excel_to_csv_All_Worksheets.vbs: line 2: rem: command not found
./Excel_to_csv_All_Worksheets.vbs: line 3: rem: command not found
./Excel_to_csv_All_Worksheets.vbs: line 4: rem: command not found
./Excel_to_csv_All_Worksheets.vbs: line 5: rem: command not found
./Excel_to_csv_All_Worksheets.vbs: line 6: rem: command not found
./Excel_to_csv_All_Worksheets.vbs: line 7: rem: command not found
./Excel_to_csv_All_Worksheets.vbs: line 8: rem: command not found
./Excel_to_csv_All_Worksheets.vbs: line 9: syntax error near unexpected token `('
./Excel_to_csv_All_Worksheets.vbs: line 9: `rem (if path is not specified, the current path is defaulted)'
bash-3.2$
Thanks for your help!
- Paul
1. I want to convert multiple .xls file to .csv.
2. Whenever I run the script it should check the folder. If the Folder contain some new .xls file then it should convert it to .csv file.
Can you please help me with this?
Thanks
Sneha
I want to convert the excel to a custom delimited text file ( ~#~) this is my delimiter. How can I change ?
Downloaded the script which u written and placed the vbs script and source .xlsx file in same folder and executed through IPC workflow manager command task.
Workflow running continuously while calling the vbs script and src file path as input parameter. It is not converting to csv file.
We are using window as operating system.
$PMSourceFileDir\Excel_To_CSV_All_Worksheets.vbs $PMSourceFileDir\test1.xlsx
Can you please help us!
Thanks in advance.