MySQL Conversion from Excel - Loaded Question

Kevin_Isley

Ok, here is this mess. I'm redesigining a database for a local company. The previous database creator used Access 97, many years ago. He has so much duplicated data it isn't funny!

So anyway, the users of this database have to enter a

Seller Name
Buyer Name
Property Address

Previously, they had been entering the Seller Name in the Seller Name table and the EMFile Table, and the same for the other two. So, when I recreated a MySQL table, I designed it so that the Seller Name is entered Once in the Seller Name Table and what links the EMFile Table to the Seller Name Table is that the EMFile table stores the Seller Name ID.

My delima now is that somehow I am going to need to take the old access database which I've converted each table into an excel sheet, but I'll need to take the Seller Name entry, and somehow compare it to the Seller Name Table's Seller Name ID field so I can make this puppy work correctly.

Does anyone have any ideas on this??

God Bless,

Kevin

AstroTeg

Its not overly clear what exactly you're doing, but it sounds like you just need to look up the Seller ID based on the Seller's Name. And then its just a matter of sprinkling that ID where ever its needed. This correct? Where are you stuck at?

Kevin_Isley

Okay, basically, I need to take the excel sheet that I have and insert it in as DATA into my MySQL database. But instead of the EMFILE table storing a name, I need it the field in the excel sheet that is currently a text name to be converted into the correct number that matches the Seller Table's ID number.

Does that make sense?

AstroTeg

Ok, yeah. You'll need to write an import script that will read in each row from the Excel sheet and then work its magic on the data. So it reads in a row. Then performs a look up on the Seller's Name and grabs the ID, and then inserts it into the table of your choice, using the ID you just got. Add some fun to it by checking if the seller's name exists and if it doesn't, insert the seller's name and grab its new ID and return it back. I've had to do this a few times here and there.

Kevin_Isley

Okay cool.

Now I'm fairly new at using PHP and MySQL. In fact less than a year of experience. So, could you give me an idea of how to go about writting such a script ??

Kevin_Isley

Can anyone help with this?????

leatherback

Hi,

Make it easy on you:

In Excel there is the vertical lookup function.

you have one list of id cells and corresponding usernames (THat'd be your users table). A next sheet has all the names. You can then in an empty colum use the vlookup function to search for the username in that sheet, and sp[it out the ID in the other sheet. Then it is a matter of copying / pasting and inserting into the database as-is!

The lookup function assumes the most-left colum of the dataarray specified to look in is the value to look for, soyou may have to swap id and name.

I'd easily give you a sample-file, but... Not allowed to post it here.

leatherback

See Screenshot: attatched file.

leatherback

See your other thread on this topic.