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



 

IMPORTANT,  YOU are responsible for backing up your systems before trying or implementing any suggestions from this blog. I do not guarantee 100% accuracy of any code examples. I do not presume to know your system environment(s) or Security requirements; all code examples from this blog should be thoroughly tested before any attempted use on a production system.

14 Response to "VBScript to Convert Excel to CSV"

  1. Anonymous 8/20/12, 2:16 AM
    nice work
  2. Balaji Karunakaran 4/3/13, 1:05 PM
    Hi Expert

    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
    -----------------------------------------------
  3. J. Kinzer 4/5/13, 12:59 PM
    Balaji, let me know what specific problem or error you are having.

    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

  4. siva 2/13/14, 12:38 PM
    Hi jeffkinzer,

    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"
  5. J. Kinzer 2/14/14, 12:08 PM
    Siva, please send me the scripts and files you are trying to convert. You can try emailing them or put them on a public site like drop box so I can download them my email jeffkinzer at gmail
  6. Rakib Hasan 3/4/14, 6:29 AM
    Script is inserting empty rows at the bottom after converting to CSV from xlsx.
  7. J. Kinzer 3/4/14, 12:25 PM
    Rakib Hasan, does it do this for every xlsx file? You may have some extra rows in the xls file. Try positioning your cursor on the first empty row of your excel file and selecting it and the next 10 or 20 and deleting them
  8. Rakib Hasan 3/5/14, 12:01 AM
    Yes J. Kinzer, for every xlsx file it's inserting one extra blank row. I tried to remove next 10 or 20 from the blank row position but still no luck. If you want then i can send you the excel file.

    Thanks,
  9. J. Kinzer 3/5/14, 8:28 AM
    Rakib Hasan, send me your xlsx file. The script will produce one extra row. Opening an XLSX file in Excel and "Saving As" CSV will produce the same result. The script works the same as Excel.

    If you are getting numerous blank rows, you may have to add some code (or another script) to delete the blank rows.
  10. Rakib Hasan 3/5/14, 10:46 PM
    If i manually saved the file to CSV with save as type: CSV(Comma delimated)(*.csv) from the excel window then it works fine. No extra rows added but i used above vbscripts then it adds one extra row at bottom.

    Please find the excel here https://drive.google.com/file/d/0Bwrrvs4ELBLfdUU1RVNiYXRPLVU/edit?usp=sharing
  11. Anonymous 4/1/14, 12:50 PM
    Is there a way to do the export to CSV so that the cell formats are not kept? For example, I have a spreadsheet that has some number fields. In the spreadsheet, the cell formats are set to Number, 0 decimal places, and it uses the 1000 separator. In the CSV file, the number will have commas in it if it is over 999. I don't want the commas in the number. It messes up the process that uses the CSV file.

    Thanks for any help...
  12. J. Kinzer 4/3/14, 8:22 AM
    @ Anonymous Post on 4-1-2014. Would it work if the output csv columns were all in double quotes? Once option my be to customize the script to convert your number columns to text columns without formatting.

    Jeff
  13. Anonymous 5/9/14, 12:36 AM
    Thank for sharing Jeff. This script is really helpful.

Post a Comment