How difficult to make a SQL database from an excel file with 5000 entries?

[Red]

I need to access a database that exists within an Excel file. It has about 5000 entries. I want to be able to access the database online and make it searchable and also have a PHP page that enables you to add/edit entries in the database. How difficult would this be with PHP/MySQL?

 

[dighn]

You can export the Excel file to CSV format and then write a very simple script to import the data into MySQL. You might have to disable the timeout because the operation might take a while. Or you could try something like this

Accessing/modifying the database with PHP should be very simple. Searching should be too, depending on how complex you want it to be of course.

 

[Carl Uman]

Of course how hard this will be is directly tied to how well you know PHP/MySQL. Based on your questions all the tasks will take you a good deal of time but after you do it once the rest will be far easier

 

[HeroOfPellinor]

If you have access to EM, then it's an easy import from CSV.

 

[Carl Uman]

I'm guessing "EM" is enterprise manager? If so then I don't think it help since he is using MySQL. Otherwise I would agree. A DTS package would make short of the import.

 

[HeroOfPellinor]

Originally posted by: Carl Uman
I'm guessing "EM" is enterprise manager? If so then I don't think it help since he is using MySQL. Otherwise I would agree. A DTS package would make short of the import.

Ah, just skimmed through the actual post....he says "SQL database" in the title. My bad.

Then my advice would be to skip PHP/MySQL alltogether and get a SQL/ColdFusion hosting plan. Or get CF Express and use MSDE for a free home-based solution.

 

[Carl Uman]

I did the same thing but MySQL jumped out and bit me

I already do CF/MSSQL but looking to do PostgreSQL/PHP on linux at home LOL

 

[BurnItDwn]

Originally posted by: Carl Uman
I did the same thing but MySQL jumped out and bit me

I already do CF/MSSQL but looking to do PostgreSQL/PHP on linux at home LOL

It shouldn't be too tough.

I run postgres on my server at home.

I import my data a bit differently then the CSV method (because I am dealing with directory names inside a directory in the format of NAMEA - NAMEB - NAMEC and also because I'm used to doing it this way)

I just do a ls -1 to an out file and use tr -d ' ' to wipe out spaces.
Then I just import my data using a delimiter of the dash

COPY tablename FROM '/path/to/file' using DELIMITERS '-' with NULL as '';

Of course to do it this way, the actual construction of the table must be complete first ...

Mine is something like this (at least my big simple table)
CREATE TABLE "tablename" ("variable1" character varying(20), "variable2" character varying(20), "variable3" character varying(6));

REVOKE ALL on "tablename" from PUBLIC;
GRANT SELECT on "tablename" to "apache";



to the OP ... I don't know much about the differences between mysql and postgresql ... but given the popularity of mysql, I'd think it should not be any more complicated then the above postgresql steps .... (the create table part should be exactly the same, just don't know if it can do a copy from a file, or what not)

 

[Red]

Thanks guys. I have a PHP/MySQL book I'm going to hit up. Hopefully this will be something I can tackle!

 

[pcthuglife]

you could always just use phpmyadmin to import the csv. you'd only need php to display the db contents and to edit the data in the db.

 

[Carl Uman]

Good info BurnItDwn

 

[WildHorse]

The main issue is in sseparating the Excel data into tables that are in proper normal form.

Getting that done is where almost all the work is.

Then copy only the portion of Excel data which will go into each individual table of your database, and save as text files (csv format).

Last, create your database, and go into it (Use dbName_xyz. I understand you'll be using MySQL.

While inside the db, you can load each text file directly into a table like this (you don't need any other special applications to do this):

LOAD DATA LOCAL INFILE 'C:/Datafile/table1.txt' INTO TABLE table1;

(notice the single quote marks around the path to your text file)

Simple!