Data Cleansing and update

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
kdalts
Forum Newbie
Posts: 1
Joined: Sun May 25, 2008 4:08 am

Data Cleansing and update

Post 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
User avatar
Jaxolotl
Forum Contributor
Posts: 137
Joined: Mon Nov 13, 2006 4:19 am
Location: Argentina and Italy

Re: Data Cleansing and update

Post 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.
Post Reply