Data Cleansing and update
Posted: Sun May 25, 2008 4:16 am
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
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