Hello All!

I'm new to the forums, and don't know how to classify myself when it comes to linux.

I've used linux mainly in the business environment for postfix,dansguardian, creating virtualhost websites, and other basic file transfer stuff. I'm basically self taught on a need to know basis...If I need to do something, I just try to look it up and play until I find an answer.

Here is my current situation, which is over my head. Hopefully I explain it properly and with enough detail.

My company has an old system that is no longer used but has hundreds of thousands of entries. I'm not terribly familiar with it, but it is based on AIX, and due to licensing and ability to access the device are only able to export data in report form (raw text, csv, xls). I want to use this data and make it into some sort of database (open to whatever).

The data is static and will never change again. What we want to do is create a simple webpage that is linked to the database file. From that webpage we want to use search boxes for entry (fname, lname, etc) and when they click search it accesses the database file and returns results.

Can anybody point me in the direction of some guides on how I might accomplish this? I've been looking around and I really only see people alluding to how to do it, and not found any actual steps

I appreciate any and all assistance in this.

What you are describing is a multi-step process. This is just off the top of my head, the order isn't perfect.

1. Export data as .csv.
2. Make some hardware decisions. If it's going to be 100's of thousands of rows or more, then lots of RAM and plenty of CPU are required.
3. examine the data structure in the csv. Single-quotes, double-quotes, unconventional field format will have to be sorted out.
4. Create a database with a table that has columns to accept the csv file. You are going to want some indexes on some of the columns to speed up the searches.
5. Use perl to insert the data one row at a time.

6. Install a web server of your choosing.
7. Find a CMS/groupware/php-perl application that might provide most of what you are looking for as a user interface. I think there's the equivalent of a browser-based query browser already. But, will it work for you?

8. Deploy.
9. Enjoy!

Thanks for the reply!

Since I had a response I hope you don't mind me asking questions based off of your answers. I should have also identified my weaknesses for databases/scripting, but didn't necessarily think it would be a huge task.

It appears that you have at least some experience in doing this, so...

What would your recommendation be for turning it into a database file? Is there a tool that is better/smoother to use than others? I have little experience in it, but more than happy to do the work if I have instructions.

Are there simple perl scripts for me to just nab and use?

Our webserver is OpenSuse, using apache2 so hopefully that will work fine

Is there a particular site/sites that have UI's that are well regarded?

Thanks again,

James

The task can get pretty big depending on the data and what you want to do with it.

Perl works for me. It's not fun or easy. If you are desperate, I freelance.


Apache works! Lots of CMS's out there and probably a few straight php/perl apps too, but it depends on your needs.

Don't forget you need a database too. I know mysql is popular, but postgresql is out there and very enterprise-ish.

Thanks again for the reply.

I'll take your pieces of information and see what I can do with it.

Again, I appreciate the feedback.

~James

Moved: This thread is more suitable in the Programming forum and has been moved accordingly to help your thread/question get the exposure it deserves.

So just open it in Excel. No reason for website or a database.

If you really want to go the database/webpage route, then the steps are:

Design a normalized database schema to hold the data. (Important step of nontrivial difficulty)

Set up a MySQL server

Install PhpMyAdmin and point it at the MySQL server (the way you've described your requirements, this is the only "web page" you need).

Create the database schema (tables, constraints, relationships, etc)

Write a script (use Python) to parse a CSV file and transform it into MYSQL INSERT statements consistent with your database schema. Export the CSV file, run the script against it, and then insert it into the database. This is the "data migration" step.

You're done.

I like dugan's advice a lot. Especially the part about designing a proper database. I have gone down the road you're on, as a database newbie. I learned enough SQL to be dangerous, and created a database that was not at all well designed. The result worked, and I was able to get the immediate basic functionality that I wanted. Eventually I was overcome by my own success, and needed the database and web interface to do more and more. By virtue of my own ignorance, I had painted myself into an inextricable corner. The code to use the database was becoming very unwieldy, and adding more functionality to it was almost impossible. It turned into my worst nightmare. I was only rescued by a benevolent database expert who donated quite some time into re-designing the database in a way that became maintainable. Some applications had to be re-written by me, and that turned out to be a net gain, but not without some pain.

The idea of using PHPMyAdmin (or PgAdmin for postgresql) seems like smart advice. It will allow you to understand how you need to use the data, and eventually you can write special purpose applications that generate reports and new data and are easy to use, especially for non-experts. I don't know about using Excel for a database containing hundreds of thousands of records. I especially don't know how good that would be for a multi-user system. While I do not know your particular business use-case, I would expect that your static database will turn into something that is much less static and more organic as users find ways to access it. It is probably worth anticipating this up front.
--- rod.