Tag Archives: Excel

JavaScript Snippet – Parse A Spreadsheet Address

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.
 * @param {string} strCellAddr  The address of the cell (eg. AD32).
 * @return {!Array.<number>}  An array of two numbers:  the column number and
 *     the row number.
 */
function parseAddress(strCellAddr) {
  var i = strCellAddr.search(/\d/);
  var colNum = 0;
  strCellAddr = +strCellAddr.replace(/\D/g, function(letter) {
    colNum += (parseInt(letter, 36) - 9) * Math.pow(26, --i);
    return '';
  });
  return [colNum, strCellAddr];
}

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:
XLS To CSV Converter
DOWNLOAD THIS APP

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

  • Download it from here.
  • 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”.
    • Fixed the copyright symbol in the “About” message box.
  • 1.1.0
    • Added the ability to specify the delimiter.

Plans for Improvement

As of right now this application is very simple, but I do hope to beef it up a little as time goes on. Let me know if you have any comments, questions, suggestions, or issues with this new application. Most of all, have fun!!! 8-)