Q: I have a database that is present in an Excel file. It has about 5000 entries. I want to be able to access the database online and searchable, and have a PHP page that allows you to add / edit entries in the database. How difficult would this be using PHP / MySQL?
Database Normalization EBook(R.
Re: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!
Easy learning MySQL Database less than 24 hours
Re: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.
WhereCode – Area Code Exchange City Database
Re:Thanks guys. I have a PHP/MySQL book I'm going to hit up. Hopefully this will be something I can tackle!
Driver Detective – Industry’s Largest Driver Database
Re:Originally posted by: guy
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)
e-Tattoo – Online Tattoo Gallery Database
Re: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
The Magic 100 Database
Re:Originally posted by: guy
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. 
Asken Research – Access Specialists.
Re: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.
Goverment & Police Auction Database.
Re:If you have access to EM, then it's an easy import from CSV.
Search Quarry – Dig Up Anyone’s Public Record Instantly
Re: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
Helene International Marriage Agency
Re: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 (http://www.gold-software.com/download9217.html)
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.
0 Comments.