I first explored the idea of directly importing the data with the mysql INFILE command, but I was getting a large number of errors during the import (and MySQL is less than helpful when it comes to elucidating these errors). So, I elected to write a script that would assure that the data were properly formatted prior to insertion. However, I will explore the LOAD DATA function... I was not aware of it prior to your mention.
Thanks! | [reply] |
I elected to write a script that would assure that the data were properly formatted prior to insertion.
Well then, why not write a script that doesn't use DBI at all, but simply reads the original tab-delimited text file and writes a "fixed" version of it that mysql's import tool will accept? It should end up being the same amount of programming work or less, compared to doing the job with DBI -- you still have to understand what the problems are with the original data, and how to fix them (unless you end up taking short cuts in the DBI approach, either intentionally or accidentally, that result in the "large number of errors" being ignored rather than fixed, but you probably don't want that sort of short cut). And of course the script without DBI and inserts will run a lot faster than the DBI approach.
I think your reference to INFILE and my reference to LOAD DATA are actually referring to the same process. I know that when you run mysql directly, you can issue a LOAD DATA command as an sql statement, but I don't know for sure if this can be used via DBI -- it's worth a try.
| [reply] |