Today I am starting work on a quick flashcard app that will use Google Docs to read data from a spreadsheet. I thought I had written and published a function which parses an Excel like cell address and converts it into the column number and the row number but I wasn’t able to find it. Thusly I wrote the following `parseAddress` function:

```/**
* Takes in a cell address and returns an array containing the corresponding
* column number followed by the row number.
* @return {!Array.<number>}  An array of two numbers:  the column number and
*     the row number.
*/
var colNum = 0;
colNum += (parseInt(letter, 36) - 9) * Math.pow(26, --i);
return '';
});
}
```

This function takes the cell address as a string (eg. `AD32`) and converts it to an array (eg. `[30, 32]`). The first value in the array will be the column number and the second will be the row number.

## Excel – Converting From UNIX Time

Today I was working on a one-time HIVE report which due to the amount of data that is in each table, took a significant amount of time to run. After running though, I had the data that I wanted except for the fact that I neglected to convert the UNIX timestamp into a text version of the date. Instead of re-running the query again, I decided to use a formula in order to convert the UNIX time into a meaningful string.

1. First I used `=DATEVALUE("01/01/1970")` in order to get the number representation of the first day in UNIX time (January 1, 1970).
2. Next I determined that UNIX time must be divided by the amount of seconds in a day (86400) in order to be in accordance with the way Excel converts dates to numbers.
3. Finally I ended up with the following formula in order to convert the UNIX time stored in cell A2 into a text representation: `=TEXT(A2/86400+25569,"YYYY-MM-DD HH:MM:SS")`

It is important that there is no built-in way to determine the timezone offset of the current users computer with a function. On the other hand, if you already know the timezone offset, you could include this in your calculations. For instance, if the data was captured in a timezone of -0400, you could use the following formula: `=TEXT([UNIX_TIME_CELL]/86400+25569-4/24,"YYYY-MM-DD HH:MM:SS")`.

## Batch Excel To CSV Converter Application

I have been truly fascinated by the amount of visitors that I get because of the simple XLS/XLSX to CSV converter script that I made a while back (original post). For that reason I have created an HTML application (HTA) which offers the same functionality and then some:

## How this Differs from the Script

• There is an easy to use interface.
• You can customize how the resulting files are named.
• You can filter which sheets will be exported as CSVs using simple text matches and/or regular expressions.
• You will see the results of the conversion process in one place after the process is finished.

## Using It

• Unzip the .HTA file.
• Double-click on the .HTA file.
• Click the Browse for Folder button and browse for the folder that contains the XLS and/or XLSX files whose worksheets should be exported as CSVs.
• Modify the CSV Naming Schema as you like (hints are given in the application).
• If you would like to only export worksheets with specific names, enter those names in the Sheet Filters textbox (only one per line). You may also use JavaScript-style regular expressions (one on each line) to match the sheets that you want to convert.
• If you would like to remove the linefeed characters from all cells, check the appropriate checkbox.
• Click the Start Conversion button and wait for the results to appear.

## Version History

• 1.0 – The first version.
• 1.0.1
• Fixed the error thrown when specifying a normal string as a “Sheet Filter”.