Jump to content

Welcome to MSFN Forum
Register now to gain access to all of our features. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more. This message will be removed once you have signed in.
Login to Account Create an Account


Photo

Graphical search of Excel database?

- - - - -

  • Please log in to reply
6 replies to this topic

#1
Kelsenellenelvian

Kelsenellenelvian

    WPI Guru

  • Developer
  • 8,729 posts
  • OS:Windows 7 x64
  • Country: Country Flag
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.


How to remove advertisement from MSFN

#2
CoffeeFiend

CoffeeFiend

    Coffee Aficionado

  • Super Moderator
  • 5,399 posts
  • OS:Windows 7 x64
  • Country: Country Flag
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...
Coffee: \ˈkȯ-fē, ˈkä-\. noun. Heaven in a cup. Life's only treasure. The meaning of life. Kaffee ist wunderbar. C8H10N4O2 FTW.

#3
Kelsenellenelvian

Kelsenellenelvian

    WPI Guru

  • Developer
  • 8,729 posts
  • OS:Windows 7 x64
  • Country: Country Flag
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...

#4
CoffeeFiend

CoffeeFiend

    Coffee Aficionado

  • Super Moderator
  • 5,399 posts
  • OS:Windows 7 x64
  • Country: Country Flag

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)
Coffee: \ˈkȯ-fē, ˈkä-\. noun. Heaven in a cup. Life's only treasure. The meaning of life. Kaffee ist wunderbar. C8H10N4O2 FTW.

#5
Kelsenellenelvian

Kelsenellenelvian

    WPI Guru

  • Developer
  • 8,729 posts
  • OS:Windows 7 x64
  • Country: Country Flag
HOLY CHIT!!!

Its on its way :)

#6
Ponch

Ponch

    MSFN Junkie

  • Patrons
  • 3,223 posts
  • OS:none specified
  • Country: Country Flag

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

#7
Kelsenellenelvian

Kelsenellenelvian

    WPI Guru

  • Developer
  • 8,729 posts
  • OS:Windows 7 x64
  • Country: Country Flag
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 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users



How to remove advertisement from MSFN