开发者

Comparing data to table in the database

I receive raw data in CSVs, and upload it to a table in a MySQL database (upon which my website functions). I want to compare a newer CSV to the data I uploaded from an older CSV, and I want to see the differences between the two (basically I want to diff the raw data with the table).

I have PHP, MySQL, and my desktop apps 开发者_如何学JAVA(e.g. Excel) at my disposal. What's the best way to go about this? Possible ways I can think of:

  • Inserting the newer data into a Table_Copy, then somehow diffing the two tables in mysql.
  • Somehow querying the database in comparison to the rawdata without having to upload it.
  • Downloading the data from the database into raw CSV format, and then comparing the two raw CSV's using a desktop program


Why don't you use the where clause to pull only the data that is new? For instance

select * from table where dateadded > '1-1-2011 18:18'

This depends on your table having a dateadded column and populating that with the date and time the data is added.


diff <(mysqldump test old_csv --skip-extended-insert) <(mysqldump test new_csv --skip-extended-insert) --side-by-side --suppress-common-lines --width=690 | more 


You can use the following approaches

1) Database Table comparison - create a copy of the table and then compare data.

You can use propriety tools to do it easily (Eg : EMS Data comparer).

You can also write some simple queries to achieve this (Eg : select id from table_copy not in (select id in table) )

2) Use a file comparer like winmerge Take the dump of both the tables with exact method, and them compare it.

I use both the approaches depending on my data size. For smaller data 2nd approach is good.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜