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. 🙂


92 Comments

Ryan · August 9, 2011 at 6:57 PM

Hi,

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

Thanks,
Ryan

gsol · September 30, 2011 at 4:42 PM

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!!!

cwest · September 30, 2011 at 4:58 PM

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.

    tommy · June 17, 2012 at 1:55 PM

    i am unable to convert my xls batch files please explain in laymans language how to do achive this i am a simple computer user

    sam · June 20, 2012 at 4:40 AM

    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.

    Dan · December 1, 2016 at 1:15 PM

    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

Scott · October 12, 2011 at 7:26 AM

Chris.

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

    tommy · June 17, 2012 at 1:52 PM

    i am unable to convert my xls file can you explain to me step by step. am a simple computer user

Bill · January 18, 2012 at 8:24 PM

Seriously helpful!

Thanks a bunch dude!

shrinivas · January 23, 2012 at 12:38 AM

Hi,
is there any method to convert real time excel data into csv?
Thanks

Jason · February 1, 2012 at 12:00 PM

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.

David · February 16, 2012 at 8:58 AM

Really excellent, thank you!

Andrew · February 20, 2012 at 5:43 AM

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!

    car;ps · February 26, 2012 at 10:25 PM

    Andrew, could you please share your modified file? thanks so much for the script !!!

    Kumar Sandeep Ramanath · September 4, 2012 at 7:10 AM

    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.!!!

Scott S · February 23, 2012 at 12:50 PM

Hey, awesome code. This was just what I needed. Works flawlessly!!! THANK YOU SO MUCH!

Dani · March 8, 2012 at 9:51 AM

Nice piece of work. I only have one issue, I need to change the separator to tilde. Is it possible?
Thanks

Tika · May 2, 2012 at 5:39 AM

Thanks for this script Chris. It saved me a lot a time !

For people like Dani who want to change to delimitor of the CSV, you have to change the parameter “xlCSV” in the script. The possible values are listed here for Excel 2007: http://msdn.microsoft.com/en-us/library/bb241279(v=office.12).aspx

Tarik · August 3, 2012 at 9:40 AM

Hi,

Can you please explain to how to call this script. Thanks

Amit Taneja · August 10, 2012 at 12:40 PM

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

Martin Banks · August 22, 2012 at 5:36 PM

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

Pamela Jacobs · September 15, 2012 at 2:13 AM

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

Ivanette · September 21, 2012 at 2:58 PM

Wonderful!!! I tweaked it a little to not has about removing and the removed the complete process.
thank you!!!

Tyler · September 22, 2012 at 9:18 PM

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

Archana · September 28, 2012 at 10:44 AM

Worked perfectly, but could you amend the script to only take the first sheet in case of multiple sheet files

Greg · October 4, 2012 at 2:21 PM

Genius!

Many Thanks man!!

Renee · October 6, 2012 at 8:26 PM

wow, thanks so much!

Peter Rodriguez · October 12, 2012 at 1:07 PM

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?

Hana · October 12, 2012 at 2:48 PM

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.

    Raj · September 20, 2015 at 10:06 PM

    Thanks. This works !!!

Bobby Garrett · October 18, 2012 at 8:16 AM

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.

Nicolai Lund Paustian · October 18, 2012 at 9:06 AM

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

    sunss · January 23, 2013 at 5:05 AM

    ws.SaveAs(csvPath, xlCSV, null, null, null, null, null, null, null, “true”);

Sutar · October 26, 2012 at 2:52 PM

Very helpful script, thanks a lot !!

BP · October 29, 2012 at 6:37 PM

Excellent script. Worked great, made my life a lot easier. Thanks for sharing this.

Renata · November 1, 2012 at 6:21 PM

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?

Kevin · November 5, 2012 at 9:25 PM

You Rock Chris!

Much Respect,
Kevin

Dave · November 8, 2012 at 12:41 PM

Nice script, helped a lot!! Would also like to skip the first line to avoid the headers.

Anyone have any ideas?

Dave

Dave · November 8, 2012 at 12:44 PM

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

Cindy P · December 6, 2012 at 12:47 PM

Wow! You just saved me a TON of time. Thanks!

Cameron · December 28, 2012 at 9:22 PM

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!

Omondi · January 3, 2013 at 12:08 PM

Thank you so very much, worked amazingly.

The commenting was also most helpful, Chris.

Colleen · February 6, 2013 at 7:28 PM

Amazing! Thank you!

Dani · February 11, 2013 at 5:00 AM

What if the file is an xlsb?

Jim · February 18, 2013 at 10:08 AM

Thank you! What a piece of coding genius. Amazing – thanks again for your kindness 🙂

jason haury · March 10, 2013 at 12:18 AM

amazing man. thanks a ton!

Marvin Glenn Lacuna · March 15, 2013 at 7:45 AM

This is perfect man, you saved my weekend.

Sandro · March 23, 2013 at 4:10 AM

Great man! Thank you very much!!

atul · March 24, 2013 at 5:47 AM

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.

John · March 25, 2013 at 7:07 AM

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.

John · March 25, 2013 at 10:37 AM

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.

Krunal Patel · May 16, 2013 at 5:37 AM

Thank you so much for the script!!

Jennifer · June 4, 2013 at 7:11 PM

OMG!! This is so much help for me! Very cool script. Thank you so much!! 🙂 🙂 🙂

leslie paul · June 4, 2013 at 9:09 PM

Hi Chris,
Many thanks for this. You are a legend. Saved me a huge amount of time and effort.

Max · June 6, 2013 at 12:10 PM

Great Post Chris!

I have a problem, how can I delete the first and last row of a sheet?
Thank you.

Max · July 23, 2013 at 3:55 PM

Thanks for the .js file – this thing works great. You are awesome!

Pete · August 12, 2013 at 10:15 AM

Very handy – many thanks!

yus · August 21, 2013 at 9:32 AM

Thank you so much!

Daniel · August 28, 2013 at 7:39 AM

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

    Chris West · August 28, 2013 at 8:29 AM

    There is a way but I suggest just using the app which does the same thing but gives you the ability to specify the delimiter.

      Daniel · August 28, 2013 at 8:46 AM

      Yes!!!
      Thank you so much Chris!
      I did test your App and it worked 🙂

eddie · December 13, 2013 at 12:27 AM

Hi chris,

What do I add in the jscript to set all cells format in the csv file to be text only?

Maurice · February 18, 2014 at 1:10 AM

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.

Surya · February 26, 2014 at 4:29 AM

Thank you very much buddy…. It saved a lot of effort…..

Nati · March 14, 2014 at 9:08 PM

Thank you so much! Much Appreciate your help!

Elle · June 2, 2014 at 6:32 AM

Thank you! This really helped me out after messing around with vba.

Pandurangan · June 13, 2014 at 9:24 AM

Thanks a mill Chris!.. that program of yours saved a lot of effort.

SRINIVASAN · June 22, 2014 at 1:11 AM

thank you so much Sir,

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

Vishal · July 17, 2014 at 1:53 AM

Hi Chris,

Thanks for sharing this … works like a charm!

Sagar K · July 18, 2014 at 6:03 AM

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

Happy Customer · July 29, 2014 at 9:14 PM

Awesome, not sure it could be any easier. Thanks Chris!

David Zhao · October 20, 2014 at 11:43 AM

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!

Amanda · December 1, 2014 at 4:25 PM

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.

Patrick · June 16, 2015 at 10:44 AM

Chris just used this to consolidate some info, thanks for taking the time to share!

Patrick

dospanes · August 20, 2015 at 5:54 PM

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!!!

TedB. · September 18, 2015 at 5:51 PM

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!

Raj · September 20, 2015 at 9:58 PM

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!

Raj · September 20, 2015 at 10:10 PM

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 !

Raj · September 20, 2015 at 10:12 PM

Is there an awesome script like this to combine multiple csv files into one ?

    dante · February 2, 2017 at 5:53 AM

    I’m manually using TotalCommander to Rename the resulting CSVs ([C] pattern) then Join-ing them.

bullit · April 14, 2016 at 10:46 PM

THANK YOU! THANK YOU! AND THANK YOU! THAT’S ALL I CAN SAY! BIG HELP!!! 🙂

il-luzhin · July 9, 2016 at 10:48 PM

This script saved my bacon. Thanks a ton.

David Martin · October 3, 2016 at 5:55 PM

Thanks so much, saved me lots of hours and dollars. You are a great person, Chris.

dante · February 2, 2017 at 5:51 AM

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).

Sammy · December 21, 2017 at 7:54 AM

Hi!
New to JavaScript so im not sure where to amend the code but i have saved the code within Notepad++ as a .js file, and saved this within the folder with the xls files. I run it and get the error ‘Alert’ is undefined.
Can you please enlighten me as to where i should be changing the code.

Thanks

Robert · March 3, 2018 at 8:25 PM

I Love This VBA for Multiple .xlsx Files Converted to .csv Files Except…

Hi…My name is Robert, and I am a schoolteacher who is taking a dip in the Excel VBA pool. I consider myself to be very knowledgeable of using spreadsheet formulas, but I am definitely an amateur when it comes to coding. I am trying to control my frustration of not being able to find the correct code or being capable of modifying code to satisfy my needs. My goal is to find a macro that will automate the process of converting multiple .xlsx files(exactly 20 files) to .csv files with the CSV files having the same exact names as their corresponding XLSX files. Furthermore, I want new CSV files to overwrite existing CSV files without having to deal with the following annoying message: “A file named _____ already exists in this location. Do you want to replace it?”. All of the XLSX files and CSV files are contained in the same folder. I provided you with the closest script I found on the Internet below this message. Although I modified it to allow for the selection of multiple files, the macro only returns one CSV file out of the 20 XLSX files that are located in the folder. I have a feeling the problem has something to do with the part of the script I typed in red. I tried changing .SelectedItems(1) to .SelectedItems(20), but the macro selects the 20th position of the file dialogue window rather than selecting all 20 Excel .xlsx files. Any assistance you can provide will be greatly appreciated.

Sub ConvertToCSV()

Dim myPath As String
Dim myString As Variant

‘Turn off Any Alerts
Application.DisplayAlerts = False

‘Open Workbook

With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show
myPath = .SelectedItems(1)
End With

‘Open Workbook
Workbooks.Open Filename:=myPath

‘Remove Excel Extension from String
myString = Split(myPath, “.”)
myPath = myString(0)

‘Save as CVS
ActiveWorkbook.SaveAs Filename:=myPath & ” .csv”, FileFormat:=xlCSV, CreateBackup:=False

‘Close Window
ActiveWindow.Close

‘Turn on Alerts
Application.DisplayAlerts = True

End Sub

madhuri · November 29, 2022 at 5:50 PM

Excellent quick program. came in very handy

How overcome ascii sort order with regard to file names when using batch scripts? (PLEASE HELP) - How-To Video · April 9, 2013 at 4:24 PM

[…] I have a batch script which calls a jscript file (link below) to save excel workbook sheets as csv files. gotochriswest.com/blog/2011/05… […]

How to overcome ASCII sort order with regard to file names when using batch scripts? | BlogoSfera · April 9, 2013 at 6:05 PM

[…] I have a batch script which calls a jscript file (link below) to save excel workbook sheets as csv files. http://gotochriswest.com/blog/2011/05/05/excel-batch-convert-xls-to-csv/ […]

Anonymous · January 6, 2014 at 3:59 PM

[…] […]

Leave a Reply

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