Excel – Batch Convert XLS To CSV

Update!!!

A newer HTML application (HTA) has just been created which accomplishes the same thing but in a more customizable manner. Click here to read the post.

Original Post

A few days ago, someone asked me to help them convert a large number of XLS files to CSV. Since, while working for Bristol-Myers Squibb, I became very familiar with writing macros and JScripts to work with Excel, I decided to write a JScript to accomplish the task. Basically, all you have to do is put the script inside of the same folder as all of the Excel files and double-click it to run it. The script uses Excel to open all of the files and convert each worksheet of each XLS file to a CSV. The naming convention for the CSV files is as follows:
{file_name}-{worksheet_name}.csv

Here is the script:
Excel – XLS To CSV Converter

If you examine the script, you will notice that it uses jPaq for user interaction.  All of the code, except for the jPaq because it is the minifieid version, is pretty well documented.  You may do whatever you want to the script. All I ask is that, if you re-use the code, you give credit where it is due. :)

86 thoughts on “Excel – Batch Convert XLS To CSV”

  1. Hi,

    I’ve unfortunately lost my original association of .js files to an application. What application should I ‘open with’ this script?

    Thanks,
    Ryan

  2. dude thank you so much, i cant believe this file is only 4kb, free, and it does everything those other files do that cost 40-99 dollars. thanks again!!!

  3. gsol :

    dude thank you so much, i cant believe this file is only 4kb, free, and it does everything those other files do that cost 40-99 dollars. thanks again!!!

    I’m glad I could help. I hope there aren’t too many people getting scammed by that 40-99 dollar script that you are talking about. To tell you the truth, this script is incredibly simple. I would not charge someone for it.

    1. i would like to edit the script so that i can the output file with file name ony. i.e if the original xls file name was abc the it should remain like so and no rename csv file as abc-abc. please help.

    2. Looks to be a great tool, I’m not a Java person so this is an easy question for you but hard for me.
      I saw one post that asked how would you selected the first tab only regardless of the tab’s name.
      I didn’t see a response.

      The one sample on the app it self I tried different version of it but wasn’t able to figure it out.

      I would appreciate it very much if you could show me this, w/o me having to learn Java Script.

      Thanks,
      Dan

  4. You are the man! Perfect script… I was working with several xls files and needed to easily extract the data from protected worksheets. This did it.. .perfectly. Huge time saver – much appreciated.

  5. Absolutely perfect. Just what was needed. Modified slightly to suppress the dialogue box and set a default value for the removal of line feed chars, and to only process the first sheet in a workbook (That’s all that was needed from multiple sheet XLS files). Thanks!

    1. Big Thanks to Chris for writing this beautiful js.

      Please find the below modification to avoid 2 alert messages:
      Change 1 : To remove alerts showing “Would you like to remove linefeed characters from all cells?” and “Remove Linefeed Characters” :
      Old :var removeLf = alert.Yes == alert(msg, title, alert.YesNo + alert.Question);
      New : var removeLf = alert.No;

      Change 2 :Remove the alert showing “The results are as follows:nFiles converted: ” and “Conversion Process Complete”
      Old : alert(msg, title, alert.Information);
      New : Comment the above line.

      Hope it helps.!!!

  6. I had 204 files converted today in a jiffy. All thanks to you Chris. Brilliantly Simple utility that solves important problems for people.

  7. For those who asked how to run this; copy it into a notepad file and save it as a .js.

    Then it runs on double click

  8. Very cool! Worked perfectly!! Any suggestions on how I can batch in a column and add the filename? Basically I have 70ish xls (soon to be csv YAY! =) ). The file name is a simple number (5.xls, 63.xls, etc.) I just want to add in a column at the end and put in “Category” as the header and the simple number of the file filled in each row. After I get all my files together I will be combining them and then adding to a single database table. This is basically my look up for a product listing. Any assistance would be greatly appreciated! =) Thanks!! Pam

  9. Thanks for the script!

    I am getting an error. Can anyone point me in the right direction? After about half of the 48 files are converted correctly I get an error saying “The following error caused this script to fail: Unable to get the Open property of the workbooks class”

    Really appreciate any help…

    Tyler

  10. Hi Cris! Thank you very much. The script works great! I have a question. How can I modify it so that the Excel headers (column names) are not copied to the CSV?

  11. The .js has to be associated with Microsoft Windows Based Script Host in order to run properly. Right click on the script > properties > In general tab, click Change > Browse > then go to C:WINDOWSsystem32wscript.exe and click open > OK > OK again. Once this is done, double click on the script again to run the script.

  12. Hi Chris,
    Could you modify this script to run with no user intervention and explain how I could call it in a batch file? I need to automate a FTP process of CSV files that start as XLS files. This script work perfertly except I have to answer the removeLF question and the completion box. Thanks in advance for any help!!! I’m not a programmer.

  13. This is a great script. How do you add the function: “Local:=True” to the save as? My problem is that I get “comma” seperated files while I really would like “semicolon” seperated files.

    Any help?

    Best regards,
    Nicolai

  14. Wow, this is great. My 2,300 files are in .txt unfortunaly. Do you have something similar to convert .txt files into .xls or .csv files?

  15. Also, I’ve got the script so it doesn’t prompt you for anything or create a box on your screen. If anyone wants that, let me know.

    Dave

  16. This came in very handy for me today. Well written, concise, and easily modifiable – a major bonus for someone who is not particularly fluent in Excel scripting!

    Thanks a bunch, Chris!

  17. Chris.

    Thank you very much. Had to convert 134 Excel files to CSV files and this saved quite some time. Does the job perfectly.

    Once again thanks.

  18. This is a fantastic script!
    Thank you so much for sharing it Chris.

    I have a limited understanding of jscript so I was hoping someone might be able to help me with what I am trying to achieve.

    Would it be possible to change the naming convention for the CSV files to {sheet_number}-{file_name}-{worksheet_name}.csv

    This would make it easier for a batch script to gather all the sheet 1 files to a sheet 1 folder, sheet 2 files to sheet 2 folder etc.

    These files within each folder could then be merged with “copy *.csv mergedfile.csv”

    I would be delighted to share the final all encompassing batch script with everyone.

  19. Actually I solved the problem I mentioned above using the variable “number” to increment with each loop:

    var number = 0;
    for (var e2 = new Enumerator(wb.Sheets); !e2.atEnd(); e2.moveNext())

    {
    var ws = e2.item();
    if (removeLf) {
    ws.UsedRange.Replace(“n”, “”);
    }
    var csvPath = aFile.Path.replace(re, function ($0, $1) {
    number = number + 1;
    return “Sheet ” + number + “-” + $1 + “-” + ws.Name + “.csv”;
    });
    ws.SaveAs(csvPath, xlCSV);
    csvCount++; // Increment the number of CSV’s.
    }

    I think this is correct as the output is:

    Sheet 1 – Workbook 1 – sheet 1
    Sheet 1 – Workbook 2 – sheet 2
    Sheet 1 – Workbook 3 – sheet 3
    Sheet 1 – Workbook 4 – sheet 4
    Sheet 2 – Workbook 1 – sheet 1
    Sheet 2 – Workbook 2 – sheet 2
    Sheet 2 – Workbook 3 – sheet 3
    Sheet 2 – Workbook 4 – sheet 4
    Sheet 3 – Workbook 1 – sheet 1
    Sheet 3 – Workbook 2 – sheet 2
    Sheet 3 – Workbook 3 – sheet 3
    Sheet 3 – Workbook 4 – sheet 4
    Sheet 4 – Workbook 1 – sheet 1
    Sheet 4 – Workbook 2 – sheet 2
    Sheet 4 – Workbook 3 – sheet 3
    Sheet 4 – Workbook 4 – sheet 4

    The final part of the file name would in reality be the sheet name.

  20. Hi Chris,
    Thanks for the Script. It works well, but I’ve one question!
    Is it possible to change the script that I get semicolon separated files instead of comma separeted files?
    If yes, what do I have to change?

    Thanks in advance!
    Daniel

  21. Pingback: Anonymous
  22. Dude, you practically solved the problem I faced for hours in just one click. Thank you, I hope one day I can do the same for someone else.

  23. thank you so much Sir,

    please guide me on this task – converting CSV to XLX /XLSX FILE
    how to arrive at it.

  24. thank you so much chris
    It worked for me.
    I was about to write macro for same problem, but came across your blog and didn’t stop myself for using your script

    cheers

    Sagar
    India

  25. Thanks for the app, saved me lots of time!
    but the date field csv output is 2 digit year format, would you please change to “mm/dd/yyyy” format?

    I have the DOB fields 09/23/1956 =>09/2/2056.
    Thanks!

  26. THIS IS AMAZING!!! Just saved me at least 20 minutes… Oh, who am I kidding probably an hour if you include me getting frustrated with how long Excel takes to launch files. Beautiful, and thank you.

  27. you are the shiiiiat! I had to convert 120 xls files with 5 cheets each to csv and it worked like a charm! thanks man!!!

  28. Thanks! Just converted 50 files!
    For those of you wondering how to create the .js file to run inside the folder:
    I copied the code Chris provided into notepad++ (https://notepad-plus-plus.org/)
    Then “save as” –> choose java script (.js)
    and a little .js file will appear wherever you saved it.
    Put that file inside your folder of .xls and click it and presto!

    Thanks Chris!

  29. I am using a windows machine. I cannot execute this JS file. Actually, I don’t know javascript. So, can you please add dummy level steps to get this running ? Thanks!

  30. The code works, but it should create a separate folder to save the csv output. A windows commandline based solution. Open cmd > cd to the folder where your excel files and csv output is > mkdir csv > move *.csv csv. Done !

  31. Thanks a lot for the script.

    Characters outside ASCII were converted to “?” so I’ve had to set xlCSV to 42 (IxlUnicodeText), so the script now generates TAB-separated CSVs but with a better text encoding (UTF-16).

Leave a Reply

Your email address will not be published. Required fields are marked *


5 − three =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="">