Legacy CSV data > MySQL 5 Migration

[JackBurton07]

Hi,

I have a csv file with around 220000 records that I want to import into MySQL 5 but the data in the csv file is quite messy, as its from our old mainframe system and there wasnt much validation done to it etc.

I want to clean the data up. Should I use Microsoft Access to clean up the data and then load the cleaned up table into MySQL 5 Or should I clean it up in Access and then save each Access table as a separate csv file then use csv engine on MySQL to import? Haven`t done anything like this before so a bit confused

Is it difficult to do this?

 

[ingresman]

Access can be a good tool to see your data and do some cleaning, as can excel (but that has a limitation of 65536 rows), depends on what you need to do really.
I would suspect your biggest issue will be making sure that you have things like text fields delimited by quotes so any stray commas don't give you phantom columns.
Once you have it pretty I would think connecting directly to MySQL or unload/reload is equaly good. However as you have quite a lot of data you might be better off doing it in chunks to save reruns in case of failures.