Reading a csv file, creating new MySQL table?

Databases
1

I receive Excel files that I need to compare against a MySQL database and where there is a match on specific fields pull out data. What I do with the data will depend on how the original data is handled. Not dealing with a lot of rows in the Excel file, maybe 10,000 max. Not sure the best way to do this.

Plan A - convert the data to csv with header row. Read the file and create a new table in MySQL. Do a search or join of somekind, get the necessary data, and add it into new columns on the newly created table.

Plan B - convert the data to csv with header row. Read the file into a variable of somekind, dictionary maybe? Loop through the records in the variable, get the data, add it into the variable, and save it out to a new text file.

Plan C - convert the data to csv with header row. Do all the work in text files. Open the original, work through the records, get the data, and create a new text file with the new data adding new rows as necessary.

Hmmm…I think plan C is looking rather simple but I’ll take suggestions.

Thanks.

2

For converting I use Python (sqlite3 and panda) for a big file (at the end there are 171.014 rows with 21 columns)
It takes less than a minute.

unzip file (20MB) - > replacing with sed (because it is bad json) → json to csv → csv to sqlite

3

Very cool solution. I didn’t think of using an SQLite db for this step. For me though, I don’t expect every Excel file to come to me in the same size and formatting. There will have to be some human intervention on the receiving end I believe.

Thanks.

4

that’s the benefit of excel : the user can put the datas whereever he wants … and precisely where you don"t expect them.