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  PayPal


 

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.

26 Response to "VBScript to Convert Excel to CSV"

  1. Anonymous 8/20/12, 2:16 AM
    nice work
  2. Unknown 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. Unknown 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. Unknown 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. Unknown 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.
  14. Anonymous 3/2/15, 1:10 PM
    Hi Jeff, Is there a way to modify the script to convert the excel file into a text file (tab or pipe delimited file) instead of a csv file
  15. Unknown 8/17/15, 4:27 PM
    This script works like a treat. Great work, Jeff.
  16. Fanny 12/11/15, 8:53 AM
    Hi 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
  17. J. Kinzer 3/24/16, 8:51 AM
    Fanny, please download the file again. My apologies there were bad characters in the script's code causing it to fail.

    https://drive.google.com/file/d/0B-K0cuskxJZ7N3F2NEJIbFRyTEU/view?usp=sharing
  18. Unknown 5/5/16, 11:45 PM
    Hello,
    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
  19. J. Kinzer 6/22/16, 8:18 AM
    Paul, are you running this on a Windows server or Unix/Linux? This script was developed for Windows. I cannot guarantee it will run on other platforms.
  20. Anonymous 8/8/16, 7:36 AM
    Hi Jeff,
    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
  21. GowthamPrabhu 6/10/17, 9:24 AM
    Hi ,

    I want to convert the excel to a custom delimited text file ( ~#~) this is my delimiter. How can I change ?
  22. GowthamPrabhu 6/10/17, 9:25 AM
    This comment has been removed by a blog administrator.
  23. J. Kinzer 6/13/17, 9:49 PM
    GowthamPrabhu, you may have to change the list separator setting on the server. See Control Panel –> Region and Language, and then click the Additional settings > List separator
  24. Anonymous 7/19/18, 12:39 PM
    Hi jeffkinzer,

    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.

  25. Anonymous 9/17/19, 9:04 AM
    Hello, how can i run this in task scheduler? Now it just keeps running and i can see in the job list that it opens excel.exe but never closes?

Post a Comment