+ Reply to Thread
Results 1 to 6 of 6

Data from XLS into Javascript array?

  1. #1
    Registered User
    Join Date
    08-14-2008
    Location
    USA
    Posts
    4

    Question Data from XLS into Javascript array?

    I have an XLS of about 500 zipcodes in one column:

    12345
    67890
    45378
    etc.

    that I wish to turn into a javascript array:

    var myarray = [12345, 67890, 45378 ];
    etc

    I initially thought I could cut the column of data then paste-special-with-transpose into another XLS, but the second XLS would not allow me to paste it in (maybe too many columns? I have 500+ zipcodes.)

    Does anybody have any advice on the route I can take?

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    dont know java script but will the aray have all 500 in it?
    i.e
    var myarray = [12345, 67890, 45378 ..... 500th zip];
    or only 3 at a time as in your example? the easiest way to get a list seperated by , say 1,2,3,4,5,6,7,8
    from a column of numbers
    is to copy then paste column into word(edit paste special unformatted text)
    select all then
    then use find/replace
    find ^p
    replace ,
    Last edited by martindwilson; 10-15-2008 at 08:10 PM.

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Considering the character limits in Excel, I think it would be easier to copy the data from Excel to Word, replace paragraphs with commas, and then do a little clean up.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Forum Contributor
    Join Date
    10-16-2008
    Location
    Iowa
    Posts
    121
    1. Copy|Paste Transpose to new workbook to make each value a column header. Save the workbook as type .csv. Open the resulting csv file in notepad adn copy the comma separted values into your array.

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    i think youll find he tried that but ran out of columns only goes up to col IV pre 2007

  6. #6
    Registered User
    Join Date
    08-14-2008
    Location
    USA
    Posts
    4
    Hi folks -

    Thanks martindwilson, shg and mhuston for your replies.

    What I ended up doing was using a formula that created a new column of values, with a comma appended to them (needed to separate values in the javascript array.)

    Then in excel, I was able to select the whole column (yes, thousands of zipcodes) for pasting into my javascript.

    One problem, I found was that javascript interpreted my zipcodes beginning with 0 as octal, so I went back to my excel formula and modified it to surround the new column values with single quotes, so that javascript would then interpret them as string values.

    In the new column, the formula I used was something like

    ="'"&A1&"',"

    Cheers to all!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. DDE Macros and data formatting
    By JMann in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-28-2008, 05:33 AM
  2. Macro or Formula to extract data
    By incognito in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-24-2007, 11:28 PM
  3. Make data more compact (with array?)
    By Alecs2613 in forum Excel General
    Replies: 0
    Last Post: 05-14-2007, 04:43 PM
  4. Replies: 3
    Last Post: 05-12-2007, 08:12 AM
  5. Storing Column Data in an Array
    By Dohko in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-12-2007, 02:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1