• Announcements

    • xper

      MSFN Sponsorship and AdBlockers!   07/10/2016

      Dear members, MSFN is made available via subscriptions, donations and advertising revenue. The use of ad-blocking software hurts the site. Please disable ad-blocking software or set an exception for MSFN. Alternatively, become a site sponsor and ads will be disabled automatically and by subscribing you get other sponsor benefits.
Sign in to follow this  
Followers 0
Kelsenellenelvian

Graphical search of Excel database?

7 posts in this topic

Heres the goal:

I have an excel database of over 9,000 karaoke songs. We are setting up 3 PC kiosks with steady state.

What I want is a graphical search engine type program that will bring up the line(s) with that matching entry when a user enters the text.

Notes:

DRUNKS will be trying to use this! So I need it to be a simple and fool-proof as possible.

Does anyone have any suggestions? Price is a consideration also.

0

Share this post


Link to post
Share on other sites

Well, first of all, I would export that data in another format that's somewhat more usable. Getting data from excel programmatically kind of sucks, and it's rather error prone. A simple database (even a simple one table flat design -- sqlite would work fine), or even just a CSV or XML or plain old text file... Anything you can easily retrieve data from.

Then it's just a matter of having a trivial program where you can type text, and it does a substring search from that data, and then displays the relevant entries. Very simple system... Assuming that's all you want it to do (show song names).

The actual implementation would depend based on language/stack used... in C# I'd probably throw something together quickly that works like this (not exactly an enterprise-grade, robust system but it would do the job in a pinch):

1-loading the data into a plain old DataSet (sucking the data in using the Microsoft Jet OLE DB 4.0 provider, to read from the CSV file directly -- mind you it could read from Excel directly if you really wanted to...)

2-have a plain old DataGridView to show the data

3-add a DataView (having the DataSet as its source; you'd simply databind the DataGridView to this to view the data) -- this is what would handle the filtering ("searching") for you

4-have a textbox where the drunks can simply type what they're searching for, and have the TextChanged event handler set the filtering on the DataView (using its RowFilter property).

That would take less than a page of code. It's simple enough that price ought to be zero.

Again, that is assuming I'm understanding your needs perfectly (just search for songs and nothing else)

There might be pre-made apps for this kind of stuff. No idea...

0

Share this post


Link to post
Share on other sites

HEY THATS AWESOME!!!

How would I convert the excel to the preferred format? (Hopefully without having to re-enter all the entries)

Attached is a sample of the excel sheet. The goal is to get the number that matches the song OR title.

If you could help with this that would be the best...

0

Share this post


Link to post
Share on other sites

How would I convert the excel to the preferred format? (Hopefully without having to re-enter all the entries)

File > Save As > change "Save as type" to CSV (or whatever)

The goal is to get the number that matches the song OR title.

That's just as easy. I just mean, you don't actually want it to play stuff, or do other tasks besides searching (seemingly not)

If you could help with this that would be the best...

Just PM me a link to the Excel file (or CSV, doesn't matter one bit) and I'll take care of it tomorrow after work if that works for you (somewhat busy right now and it's almost midnight too, but I'll get around to it)

0

Share this post


Link to post
Share on other sites

DRUNKS will be trying to use this! So I need it to be a simple and fool-proof as possible.

... searching for "Ken Lee". :D

0

Share this post


Link to post
Share on other sites

I would like to publically that CoffeeFiend for his awesome help with this.

Not only did he make what I wanted he made it better than I imagined!

0

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.