Page 1 of 1

Data Cleansing and update

Posted: Sun May 25, 2008 4:16 am
by kdalts
Hi guys... first of all, I'm no expert with PHP so please be patient with me :?

Here is my challenge...
I need to import arounf 1000 records (about 40 fields) of data each month into a MySQL database from an Excel spreadsheet. The data is rubbish in terms of formt (dates are a mess, som text, some values due to user entry). I can clean the data easily in EXCEL uing forumlae's etc to replace the rubbisg data but then I need to get the data into MySQL under the following conditions ..

The process must be automated - ie: 1 click on a button in EXCEL or from a web page.

So, can anyone map out the best way to do this (remembering that I can automate the data cleansing in EXCEL uing a macro). Do I get the EXCEL macro to perform data cleansing, then export the clean data or can I run some really clever PhP code from my browser to export the rubbish data from MySQL to EXCEL, clean it up and then suck it back in?

Look forward to any replies

Re: Data Cleansing and update

Posted: Sun May 25, 2008 6:26 pm
by Jaxolotl
A good challange for you would be take notes of this:
First format the columns of your exel file as a pseudo database (columns like record properties/fields, rows like unique records)
then export them into csv (comma separeted files)
then feed a function with the file and process each line (put them into an array or something) to make an INSERT query using string replacement functions.
then run your queries.

You'll learn a lot with that.

Or you may use a manual replacement tool on your preferred code editing tool an create the queries in a semi-automatic way.