3 replies to this topic

[larrywcc]

Hi,
I have a data base that needs to be updated though the use of an excel file. Not sure if this is simple to do or not.

Only one field needs to be updated as I will use this for example.
Database name post

ID, first, last, company, status, description, count this is the data base that has over 1000 listings

As default when anyone new account or add listing it will set status unactive.

Now I have an excel file that has the ID, first, last, company, status

Now the company that I already approved are in the excel file that I have to go though each day to match new listings. Well now because of the amount of new listings I can not keep up on a daily bases.

Must be a simple way I can upload the excel spreed sheet to the server then run a cron to check the data base from unactive listings to the excel file and if the ID and company matches then will set the database to active?

I know how to pull data from a database but unsure how to read all listings and compare to the excel file. Thanks for any help

[rhossis]

Hi,

I think if you save your spreadsheet as a CSV file, it can be uploaded on server using the mysql LOAD DATA INFILE statement and can use queries to do the matching. Can you post a sample file? It is possible to use cron to automate as well.

[larrywcc]

HI,
I do not have a script as I am not sure how to start! I know how to read and print from mysql but not sure how to read the mysql and match to the excel file.

Think this is harder then I think or could be more simple then thinking about it. So just working on a place to start!
Thanks!

[rhossis]

Hi Larry,

Give this a try.
- Save your excel file as a ".csv" file. I will assume for example purpose that you have saved it as "list.csv", and your database is called "db1" and your table "table1".
- Try to run the following script in your mysql client or in the mysql gui tool your using e.g. phpmyadmin (ensure you replace db1 with your database name and table1 with your table name. Also include the correct path to your saved csv file)

LOAD DATA INFILE 'list.csv' IGNORE INTO TABLE db1.table1;

- If you are using windows, if your file is saved on C drive, use foward slashes when indicating the directory in which the file is saved e.g. LOAD DATA INFILE 'c:/list.csv' etc
- Using IGNORE keyword in the statement above should enable that only the new records are added onto the database.
I am not sure if you need any further processing e.g. setting the value of status?

Edited by rhossis, 13 February 2011 - 01:24 PM.