VBScript to convert CSV to XLS

Posted by J. Kinzer
Labels: , ,

Informatica can easily output target data in comma separated value format (CSV) but sometimes you require output formatted in native Excel.  Here's a reusable Visual Basic Script that will convert a CSV file into a nicely formatted native Excel file.

The script can be ran individually or as this tip will demonstrate, can be evoked from a PowerCenter Session's "Post-Session Success Command" property.

Here's an overview of what the script can do:

  • Adjust width of the columns (auto fit)
  • Sets the Header row (row 1) to Bold Font
  • Freezes the Header row
  • Adds Data Filters to each column of Header Row
  • Sets the Header Row Background color to gray
  • Saves AS native Excel format into a new file name

Note, the techniques in this article where developed using PoweCenter 8.6 running on a Windows server.  It is assumed the reader has a basic to moderate knowledge of PowerCenter Session properties.

How To Output A CSV Target
In order to evoke the script from a Session, it's best if your target's output is a comma separated flat file.  If you're not familiar with how to configure a Session to output CSV, here's an overview. (If you already know how to do this then skip to the next section.)

While you are editing a Session, click the "Mapping Tab" then click on the Target to view its properties.  If the Target is type "Relational" (db table) change it to a type of "File Writer". See example here.

Next, click the "Set File Properties" link. On the resulting dialog change the file properties to "Delimited" and click the "Advanced" button to make sure the Column Delimiter type is a comma, you can also set the Quote option as needed. See example here.  Close the dialog.

Change the Target's “Header Options” property to “Output Field Names” and change the Output filename so it has a “CSV” extension as shown in this image.

Now when you execute the workflow for this session, a flat file will be created in the default target folder.

How To Convert CSV To Native Excel
Download the CSV_To_Excel.vbs file to a folder on your Informatica server.  The syntax for running the command is below, paste it into the "Post-Session Success Command" property of a Session with a CSV target. Here's an example of evoking the script from the Session.

D:\<<folder name>>\CSV_To_Excel.vbs 
$PMTargetFileDir\<<target file name>>.csv 
$PMTargetFileDir\<<new target file name>>.xlsx

Notice the required input parameters:
  1. the folder path and name of the CSV file
  2. the folder path and name of the new Excel file
These parameters make the script reusable.

Modifying The Script
By default the script creates Excel files formatted for version 2007-2010, if you require older (or newer) version Excel files, change the line of code "objWorksheet1.SaveAs tgtxlsfile, 51" by replacing the "51" with a number that represents the Excel version you require, for example using "56" will give you an Excel 97-2003 compatible file. More about this topic can be found here.

To make the script more flexible you could add an additional input parameter for the file type and pass in the number representing the Excel version.

The script could be further parameterized to control the formatting, for example, a flag could be passed in to conditionally freeze the header row or not.

Caveats
When running this script in a batch environment (as from an Informatica Session) be aware that instances of the Excel object can stay resident in memory.  I have not noticed this happening on my company's current landscape (Windows em64-bit).  If you do notice this behavior in your system you may need to run an additional script to find and kill the rouge instance.

Windows 2008
If you cannot get vb scripts to run on your Windows 2008 server, please see this page.

Conclusion
I hope you find this tip (and script file) useful. If you have questions or suggestions post them as a comment below.


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.

| Share

30 Response to "VBScript to convert CSV to XLS"

  1. Anonymous 7/13/10, 1:17 PM
    Hey Jeff, thanks for this!!
    I am probably going to use it in an SSIS program to convert to xls.
    -Kim Cantrell
  2. Ethan 8/10/11, 10:13 AM
    Great script!
    I needed to speed up the script so I commented out Autofit, Bolding, FreezePanes & Autofilter.
  3. Tam Nguyen 4/24/12, 2:33 PM
    For some reason, this doesn't output the Excel on a WinXP 32bit machine.
  4. J. Kinzer 4/25/12, 8:16 PM
    Tam, it should work. Send me a screen capture of any errors you are getting and the non-excel output to jeffkinzer at gmail dot com and I will try to help you
  5. tworzenie stron 7/20/12, 6:17 AM
    How change separators to ;
  6. J. Kinzer 7/22/12, 4:28 PM
    @ Tworzenie Stron, lookup this keyword TextFileSemicolonDelimiter = True, you would have to modify the script
  7. Unknown 11/7/12, 3:05 PM
    Hi jeff
    I have a column in csv file which has leading zero's. but when I use this vb script it doesnt show the leading zero's in the excel. what would be the change in the script
  8. J. Kinzer 11/7/12, 6:42 PM
    lijju, if you are using Informatica to create the csv file, you can concatenate a non-breaking space to the column like this: CHR(160) || RTRIM(LTRIM(colun_name)), add this to the port just before the target.

    or you can add something "like" this to the vbs code, changing the column letter to you column and changing the number format to your format, i.e. if the column is 10 characters in length, use 10 zeros.

    Columns("E:E").Select
    Selection.NumberFormat = "0000000"
  9. Unknown 11/7/12, 8:15 PM
    Thanks Jeff. Yes I am using Informatica to generate the files and your first solution worked perfectly. You made my day
  10. Anonymous 1/17/13, 7:18 PM
    Jeff, you're a lifesaver, only your center isn't hollow.. This script allowed me to finally import my CSV and export it as an XLSV (once I changed .SaveAS from using "51" (Office 2007/2010) to "56" (Office 97-2003).

    Thank you!!!!!!
  11. Unknown 3/5/13, 10:50 PM
    Hey Jeff..
    I am taking the output of my informatica mapping to a unix server. I tried to keep the vbs file given by you in the unix server, but it does not convert to excel. Is there a limitation that we cannot use it in Unix? If so, then where should I place the VB Script, to get my output in Excel format?

    Another question is does it support .xls or .xlsx ?

    Thanks in advance for your help.

    Regards
    Ankit
  12. Unknown 3/6/13, 8:56 AM
    Jeff,
    To be more clear, My informatica server is on unix. Am getting the output in Unix server. When tried to keep the VB Script in unix server it is not working as expected. Can you please provide a solution to convert CSV to EXCEL in Unix.

    Awaiting for your response at the earliest.
  13. Anonymous 2/19/14, 5:10 AM
    tworzenie stron 7/20/12, 6:17 AM
    How change separators to ;

    J. Kinzer 7/22/12, 4:28 PM
    @ Tworzenie Stron, lookup this keyword TextFileSemicolonDelimiter = True, you would have to modify the script

    ==========================
    Could you show an example, how to modify the script?
    Thanks
  14. Anonymous 4/23/14, 5:13 PM
    how save csv files to excel to use macro
  15. Unknown 7/16/14, 4:03 PM
    I need to call this script from my application (Qt). In command line if I go to the directory and then run the script it works but if I call it from any other place without the actual path it does not run.
    My application and the script are in same directory.
  16. J. Kinzer 7/21/14, 7:04 AM
    Prasenjit Gupta, not sure what's happening there. Can you set an Environment Variable with the path to the application and script?

    Do you get a specific error when running the script from the app or is it that the script is just not found?
  17. Anonymous 2/16/15, 11:24 AM
    Jeff,
    Thank you for your efforts.
    The spreadsheet is generated when I execute the script in command prompt but not in Informatica. The session log doesn't throw any errors. Also, I tried to invoke the script by .bat file but esults were the same. Could you suggest anything?
  18. Stavros 11/9/15, 2:11 PM
    there is an issue that this solution does not cater for.
    Leading zeros in csv column value
    So if i have a column that has 00000 in the csv in the xlsx file it will be copied as 0.
    This is not correct
  19. J. Kinzer 11/20/15, 3:31 PM
    Stavros, you are correct, that is by design. That would be a change you would have to make.
  20. Anonymous 10/17/16, 2:33 PM
    Hi Jeff,

    given script is working from command prompt ,but not working through post session command.

    post session command is as below:

    C:\Users\AgarwaA\Downloads\CSV_To_Excel.vbs $PMTargetFileDir\Mea.csv $PMTargetFileDir\Mea.xlsx .

    Please suggest.
  21. J. Kinzer 10/18/16, 12:50 PM
    To Anonymous poster on 10-17-2016.

    You might try putting the script in $PMRootDir

    Can you export the workflow and send the xml to me?
    Thanks
    Jeff


  22. Anonymous 3/28/17, 5:48 PM
    Hello Jeff,
    The script is working from command prompt ,but it is not working through command task.

    command task is as below:

    $PMRootDir\Scripts\test\CSV_To_Excel.vbs $$PMTargetFileDir$$OutputFile $$PMTargetFileDir$$NewFileName

    Could you please suggest anything?
    Thanks in advance.
  23. J. Kinzer 3/28/17, 7:38 PM
    Hello, I assume you are using Windows as your Informatica server, so please read this article about running VB scripts on Windows server. http://jeffkinzer.blogspot.com/p/vb-scripts-on-windows-server-2008.html

    Also, do you have \ in your parameters? Please email me your parameter file and the xml export for the workflow


    jeffkinzer@gmail.com
  24. Unknown 7/21/17, 7:01 AM
    Thanks Jeff for your excellent script.
    I have modified some small additional lines as per my requirements (like changing color of some cells as per some specific values.)
    Now I need to created Excel header in something like pre-formatted Excel where Legends also will be at bottom.
    Is it possible to create that type of excel while converting CSV ???
    if not then is that possible to write these CSV data in some pre-formatted Excel?
    If you want to get more input like my csv data and formatted excel details, I can mail you in details.
    Thanks in Advance
    Bithun
  25. Unknown 10/13/17, 5:13 AM
    Not able to modify the script which can comparable for semicolon separator
  26. Roopesh 11/13/17, 4:35 AM
    Do I need to have MS Excel on the machine where I am running this script from?
  27. amu 6/29/18, 8:55 AM
    Hi,

    Great script...
    I run thru 180 files but one file always gets stuck. It gives me an error "Excel has detected that file.csv is a SYLK file but cannot load it. either it has errors of not in the right format....etc." but once I hit OK it works. I will like to automate this process and it is annoying to click yes. Is there a work around that I can try?
    Thank you...
  28. Unknown 9/14/18, 9:31 AM
    Jeff,
    Is it possible to automate this script? my problem is, that any automation gets done by a service user which is not able to open Excel (only remote desktop users can do that). So if manually click on my file.vbs than it converts multiple csv or xls to a single one. But if I trigger this vbs it does not work.
    Any suggestions are vere much appretiated.
    Thank you
  29. morris 3/11/21, 5:52 PM
    I have read a few of the articles on your website now, and I really like your style of blogging. I added it to my favorites blog site list and will be checking back soon. Please check out my site as well and let me know what you think. http://psiprograms.com
  30. Unknown 6/25/21, 1:40 AM
    Анонимный
    19.02.14, 5:10
    tworzenie stron 20.07.12, 6:17
    Как изменить разделители на;

    J. Kinzer 22.07.12, 16:28
    @ Tworzenie Stron, найдите это ключевое слово TextFileSemicolonDelimiter = True, вам придется изменить сценарий

    =================== =======
    Не могли бы вы показать пример, как модифицировать скрипт?
    Спасибо

    =================== =======
    Доброго времени суток!!!
    та же проблема, никак не могу настроить сценарий!, csv файл нужно прочитать с разделителем ";" помогите с примером настройки ?

    за раннее спасибо!

Post a Comment

Subscribe to: Post Comments (Atom)