Jump to content

Check out our Community Blogs

Register and join over 40,000 other developers!

Recent Status Updates

View All Updates

- - - - -

Excel Data To Html Select Option


  • Please log in to reply
3 replies to this topic

#1 TylerB


    CC Lurker

  • Just Joined
  • Pip
  • 2 posts
  • Programming Language:PHP, JavaScript, Bash, Others
  • Learning:Java, PHP, JavaScript, Perl, Bash

Posted 09 July 2012 - 10:35 AM

Hi, I am needing to import over 2000 items from an excel spreadsheet into an HTML <select> <option> list. I really do NOT want to do this by hand considering the amount of data contained within the spreadsheet. Is there an easier way to do this? Doing this one by one will take hours and honestly I don't have that kind of time.

Any helps, tips or suggestions would be greatly appreciated!
  • 0

#2 BlackRabbit


    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts
  • Location:Argentina
  • Programming Language:C, C++, C#, PHP, JavaScript, Transact-SQL, Bash, Others
  • Learning:Java, Others

Posted 09 July 2012 - 11:22 AM

First i think that an html selector with 2000 items is kinda un-selectable, lol
still, if you want to do it i suggest you do the following :

get a spreadsheet with only 2 columns, the 2 you care about, like item-name, item-value
if the value you want to be returned when selected in the html is just the name, then copy item-name to another column, have it

so, now you have a 2 columns spreadsheet, now lets take a look on how and html <select> looks like :

<option value="20">January 20th</option>

so, you have to transform your two columns to that "option" thingie

well, in the excel you use @concatenate( '<option value=", A1,'">',B1,'</option>' )

et voila !, you have in ... lets say CELL C1 the html written :D
you copy that formula to the end of the sheet
when it is all calculated you select that html syntax columns range and copy to a new column, but mind to do "values" copy, so formula is not copied but only the values, ergo the final html text sentence,
then erase the previous 3 columns,
add the <select> at the top, the </select> at bottom
and save the spreadsheet as a txt file, then copy-paste that into your html code and you are on ;)

i hope this speed up your job :D
  • 0

#3 TylerB


    CC Lurker

  • Just Joined
  • Pip
  • 2 posts
  • Programming Language:PHP, JavaScript, Bash, Others
  • Learning:Java, PHP, JavaScript, Perl, Bash

Posted 09 July 2012 - 02:43 PM

Oh thank you so much. Lol you're right, 2000 items in an option list is a little unacceptable. I am using a Jquery script to only display certain options at under certain circumstances. The list is of over 2000 colleges in the United States and I am going to break them down by state. When a user selects a state it will display only those colleges within that state.

I am yet to try your method, but I will post back after with results.

Thank you again.
  • 0

#4 Orjan


    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 09 July 2012 - 11:21 PM

then you should create a list for each state, so you're down to somewhat agreeable average of 40 per list which is fine. There are many ways of performing this. I's use a database and make my backend create the lists on the fly (eventually with an ajax call). in that case, Id let excel create my sql insert queries instead.

then you would use something like
@concatenate( 'insert into colleges (state, name) VALUES (', A1,', ',B1,');' )

based on a table with id, state name as fields, where id is auto incrementing
  • 0

I'm a System developer at XLENT Consultant Group mainly working with SugarCRM.
Please DO NOT send mail or PM to me with programming questions, post them in the appropriate forum instead, where I and others can answer you.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download