This is a quick reminder for myself on opening a CSV in MS Excel but without converting long numbers into exponential notations (which are incorrectly rounded anyway).

My Data:
Account ID,Name,Phone,Record ID
My Excel:
Account ID	Name	Phone	Record ID
1.4571E+18	Joel	4.41235E+11	1.4571E+18
1.4571E+18	Stanley	1709123456	1.4571E+18
1.4571E+18	David	3.31235E+11	1.4571E+18

Firstly, save the CSV file as text file (*.TXT).

This is an article to remind me how to search a column in an Excel file for values found in another column (in this example, on another worksheet in the same workbook).

So for demonstration purposes, I'm using a new Excel file with two worksheets called "Sheet1" and "Sheet2" respectively.

This article serves to explain how to split a spreadsheet consisting of multiple sheets into separate files per sheet.

The Excel file in question was about 36000 rows and had a file size of about 11Mb. In order for an import process to work, the import would only accept XLS files no greater than 1Mb. So our script has to split a single spreadsheet into multiple worksheets of 3000 rows each, and then output each sheet to a separate file that is formatted as XLS (Not *.xlsx).

In summary, we are going to move the Excel file to a folder of its own. We're going to run two VBScripts in two stages, firstly to split the specified rows into sheets, then each sheet into a file each. And we want all the files generated to be created in the same folder.

What I have:
A CSV file exported from Excel along with double-quotes

What I want:
To read the file (stored on the server) and convert to a JavaScript array of objects
var my_object_array = [
     { my_col1_val: 'item1a', my_col2_val: 'item2a' },
     { my_col1_val: 'item1b', my_col2_val: 'item2b' },
     { my_col1_val: 'item1c', my_col2_val: 'item2c,c' }
What I want again:
  • Read a CSV file already uploaded with JavaScript
  • Populate a JS array with each row
  • Account for strings containing double-quotes (and commas to ignore)
  • Sort the resulting object array


A quick article on how to get the value of 40°4′20″N 116°35′51″E into 40.079857, 116.603112.

Let's pretend all the names are in column A, in Column B I have the coordinates that I want to convert:
A                                       B
-------------------------------------   ----------------------
Beijing Capital International Airport   40°4′20″N 116°35′51″E
Beijing Shahezhen Air Base              40°8′57″N 116°19′17″E
Beijing Tongxian Air Base               39°48′40″N 116°42′30″E

Note that I tend to copy the coordinates off a website which has strange apostrophes and double-quotes. You can change this but remember to put two double-quotes if you are searching on it, eg:
FIND(""",B1)   // will NOT work!
FIND("""",B1)  // will work
FIND("″",B1)   // will work

A quick article on if you want to extract the link from some text you have copied off the web and into an MS Excel document.

  1. Open up a new workbook.
  2. Get into VBA (Press Alt+F11)
  3. Insert a new module (Insert > Module)
  4. Copy and Paste the Excel user defined function below
    Sub ExtractHL()
    Dim HL As Hyperlink
    For Each HL In ActiveSheet.Hyperlinks
    HL.Range.Offset(0, 1).Value = HL.Address
    End Sub
  5. Press F5 and click “Run”
  6. Get out of VBA (Press Alt+Q)


Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate
© 2021 Joel Lipman .com. All Rights Reserved.