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 this 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
Tip Jar
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