Thread: Willing to Pay for Help On Importing/Updating Database

The MySQL bulk upload into the database from a csv file is called LOAD DATA, and it happens one table at a time. It is impossible to upload a single csv file that updates several different tables. This is a limitation of the database itself, not of phpMyAdmin or anything like that. That is why all the references you have seen talk about using software that can handle splitting up the csv file into separate tables' data. The PHP coding references you found are for writing your own (small) software to do this.

As Dagon said in his/her comment, the correct approach to this problem is to not export and import multiple csv files -- a process just asking for you to make a mistake and end up corrupting your data (trust me, I once erased the first 20 products out of a products database this way) -- but rather to write a custom process by which you update your data on the site itself. This involves writing web pages with forms on them that, when you click "Save" (or whatever), saves the changes you want to make to the database. This is very basic website stuff.

Because you know nothing, you might try contracting someone with the necessary experience to build this for you. An experienced person should be able to do what you need in a week or two, unless your needs are really complex (it doesn't sound like they are if you think you can handle them in Excel). If your needs are super simple, it might take only a few hours. If you do it yourself, you could be looking at several weeks to several months of learning curve (depending on your personal abilities) before you get anything that works. You'll have to weigh the costs yourself.

If you choose to learn how to do it, you'll need to look specifically into using mysqli or PDO in PHP to handle the database. Do NOT use the mysql extension, even though there are tons of examples of it on the internet. Those examples are old. The mysql extension functions are buggy, non-secure, and will not be supported by PHP in the future. You will need to also research how to make MySQL queries.

EDIT:

WordPress is exactly the kind of software I was talking about in my first paragraph. It's PHP software, actually. It only allows 300-500 at a time because otherwise one can run into issues with server memory and time constraints that I won't go into, except to say that I have personally encountered them. There's a reason for enforcing that number.

What you do here, if you are insistent on using giant csv files, is write a script that will "chunk" a large csv file into smaller pieces and feed those pieces to the WordPress code one at a time. I do this with my own csv uploader program. I insert each chunk as part of a back-end process using the exec function and use ajax to inform me of the progress. My program actually does not use the csv bulk upload but instead runs validation and uses transactional queries to handle what happens when specific rows don't meet format requirements.

Your example of voter registration has to do with "inserting," or adding more entries to the table. That is definitely a good use of csv files, as long as you make sure they a) can't accidentally overwrite unrelated previous rows of the table, b) can't accidentally add duplicates, and c) never ever have formatting issues that will cause only the first 637 rows to import and throw out the next 23,375 rows.

Good luck with that last one. Excel likes to mess things up for you. For example, we have a product with the manufacturer part number "10-51" (and other similar ones). Excel is bound and determined to turn that part number into the date "October 1st, 1951" every time I open a csv that has that part number. To get around it, I have to set a blank worksheet to format text only and then import the file as text only -- what a hassle! You can't turn off this "feature."

But I digress. Your original question had to do with "updating," or changing what's in the table already. For updating, it is better to build an interface that allows you to select what you want to change and make that change. To continue your fictitious example, let's say that some gerrymandering occurred and you had to change a lot of voters' districts. It would be easier and less prone to error to do this as part of a PHP script than to do it by hand in Excel. You tell the database to change the district of certain people if they meet certain rules, such as having a particular zip code or having street name "Washington" plus a street address number less than 600. If you know what to tell the database, the whole update will be done in two seconds. But if you did this by hand in Excel, you might miss a row, or accidentally delete a row, or accidentally change someone one row down instead of the one you intended, and it would take you far longer than two seconds.

It is quite possible to write code that will let you make whatever bulk updates you need to make, plus allow you to make individual updates as necessary. This is the kind of "need" I was talking about before. Whatever changes you are making in Excel can be made with less room for error in a custom script.