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

Convert Anything To A Number

There are many times in JavaScript when you need to scrub the input and give output of a specific type. Today, I will cover how you can convert anything into a number. The function to do so is as follows:

// Converts any object into a number.
function parseNumber(obj) {
  return parseFloat(obj)||+obj||0;
}

Now the question is, what happens under different circumstances. Look at (or even try out) the following to find out:

// Convert any empty string into a number:
var a = "";
var b = parseNumber(a);  // 0
alert(JSON.stringify(a) + "\n" + b);

// Convert a string version of a number into a number:
var a = "9563.49";
var b = parseNumber(a);  // 9563.49
alert(JSON.stringify(a) + "\n" + b);

// Convert a string starting with space and then a number with extra decimal points into a number:
var a = "    9563.49.2";
var b = parseNumber(a);  // 9563.49
alert(JSON.stringify(a) + "\n" + b);

// Convert a sentence into a number:
var a = "I am 23.";
var b = parseNumber(a);  // 0
alert(JSON.stringify(a) + "\n" + b);

// Convert an array of numbers into a number:
var a = [129,84,7];
var b = parseNumber(a);  // 129
alert(JSON.stringify(a) + "\n" + b);

// Convert a date into a number:
var a = new Date();
var b = parseNumber(a);  // milliseconds since January 1, 1970
alert(JSON.stringify(a) + "\n" + b);

// Convert an object literal into a number:
var a = {};
var b = parseNumber(a);  // 0
alert(JSON.stringify(a) + "\n" + b);

The most interesting of the above test are probably the parsing of dates and arrays. For dates, the number will be the amount of milliseconds since January 1, 1970. For arrays, the number will be the numeric value of the number in the first position of the array.

Math In the Address Bar

Did you know that you can do math problems in the address bar of your browser? Regardless of what browser your using, you can use its address bar to find the answer to an arithmetic problem. For instance, let’s say that you are using Internet Explorer and all of a sudden someone asks you what 54 × 32 is. To figure it out, you type the following into the address bar and then hit the ENTER key:

javascript:alert(54 * 32)

If you tried it out, you know the answer is 1728. What about the square root of 1728? To figure it out, you could use the following:

javascript:alert(Math.sqrt(1728))

Now that you know that Math.sqrt(…) exists, you may be wondering, what other math related functions and constants like this exist. Here is a list of them derived from DevGuru.com:

Constants

Math.E
Euler’s constant and the base of natural logarithms (~2.7183).

Math.LN10
The natural log of 10.

Math.LN2
The natural log of 2.

Math.LOG10E
The base 10 log of E.

Math.LOG2E
The base 2 log of E.

Math.PI
PI – The circumference of a circle divided by its the diameter.

Math.SQRT1_2
One divided by the square root of 2.

Math.SQRT2
The square root of 2.

Functions

Math.abs(X)
The absolute value of the number X.

Math.acos(X)
The arccosine of X (which must be greater than or equal to -1 and less than or equal to 1) as a value between 0 and PI.

Math.asin(X)
The arcsine of X (which must be greater than or equal to -1 and less than or equal to 1) as a value between -PI / 2 and PI / 2.

Math.atan(X)
The arctangent of X as a value between -PI / 2 and PI / 2.

Math.atan2(X, Y)
The arctangent of X / Y as a value between -PI / 2 and PI / 2.

Math.ceil(X)
If X is an integer this evaluates to X, otherwise it evaluates to the next integer up.

Math.cos(X)
The cosine of X as a value between -1 and 1.

Math.exp(X)
The value of EX where E is Euler’s constant.

Math.floor(X)
If X is an integer, this evaluates to X, otherwise it evaluates to the next integer down.

Math.log(X)
The natural log (base E) of X (which must be greater than 0).

Math.max(X, Y, …)
The maximum number of X, Y, and any other parameters that you specify.

Math.min(X, Y, …)
The minimum number of X, Y, and any other parameters that you specify.

Math.pow(X, Y)
The value of XY.

Math.random()
A random number that is greater than or equal to 0 and less than 1.

Math.round(X)
The rounded value of X. If the fractional portion of the number is less than 0.5, the number is rounded down, otherwise it is rounded up.

Math.sin(X)
The sine of X as a value between -1 and 1.

Math.sqrt(X)
The square root X (which must be greater than or equal to 0).

Math.tan(X)
The tangent of X.

If you haven’t already guessed it, alert(...) actually displays whatever you specify in a new dialog box. Believe it or not, you can also assign values to variables and then use those variables later on. Of course, though, you have to do all of this on one line. Here is an example of what I mean by assigning values to variables and then using them later:

javascript:age = 2011 - 1987; alert(age + Math.sqrt(age))

If you tried the above code out in your address bar, you now know what my age plus the square root of my age is.

All I have to say now is, “welcome to the world if JavaScript!!!”