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
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 AlsoThis script is the companion to this CSV_To_Excel script located here.
Windows 2008If you cannot get vb scripts to run on your Windows 2008 server, please see this page.
Download the scriptThe download is here.
Thank you for your support