1

I need to let users import huge csv files varied between 200k - 1.5m lines. For this I use LOAD DATA INFILE which is the fastest way to import those files. Thing is however that I can't check the user input, I have solved this by using a trigger on a table. My table structure is as following: (narrowed down to make it more readable):

tbl_debtor: debtor_id (AI), debtor

tbl_debtor_tmp: debtor

tbl_debtor_err: error

Now the (before insert) trigger is on tbl_debtor_tmp where users can literally import anything. In that trigger I check their user input, if okay I'll do a insert on tbl_debtor. If the user input is not okay I write to tbl_debtor_err. After the import is done I'll do a DELETE FROM tbl_debtor_tmp

My question here is, is there a more efficient way? I have about 20 of those table structures which I think is alot.

Improve this question
9
  • 1
    Why don't you import it into a staging table (no trigger), then validate it, then move it to the real table?
    – Neil McGuigan
    Commented Apr 8, 2015 at 6:40
  • @NeilMcGuigan I haven't heard of it before. Do you have some material to read?
    – user3363940
    Commented Apr 8, 2015 at 6:47
  • by staging table i mean a short-lived table that you create, mimicking your real table. What do you mean "I can't check the user input"?
    – Neil McGuigan
    Commented Apr 8, 2015 at 6:50
  • I mean usually, with >10k lines in a CSV file, I can check the CSV file through php code (without losing performance). How can I check the user input? Some validations that needs to be done are: is it a specific type (int, decimal etc.) , date format, comma or dot, max/min length of characters, not negative etc. If those validation aren't met the user needs to see that their input isn't valid.
    – user3363940
    Commented Apr 8, 2015 at 6:59
  • 2
    What Neil was inferring is to break process into 2 stages, load the raw csv in to a table and after that use SQL to validate or clean the data on it's way into the final destination. Of course you could use PHP to iterate through the lines in the CSV, clean the data as it processes and then batch the rows in larger inserts so you can ensure a better performance.
    – eroomydna
    Commented Apr 8, 2015 at 8:32

1 Answer 1

Reset to default
1
  1. LOAD DATA ... INTO staging ...; It has VARCHARs in place of INTs, etc. It also has some unique id (perhaps auto_increment) for step 3.

  2. Copy "bad" rows to debtor_err in one pass:

    INSERT INTO debtor_err SELECT ... WHERE LENGTH(..) > .. OR NOT EXISTS( SELECT * ... ) -- FK check OR ... > 12

  3. Copy "good" rows to debtor table -- Use JOIN to see which ones are good

    INSERT INTO debtor SELECT ... -- where nec, convert from VARCHAR FROM staging s LEFT JOIN debtor_err e ON s.id = e.id -- suitable UNIQUE key WHERE e.id IS NULL -- to get the non-error rows

  4. DELETE FROM staging; -- Reset for next time. (Using DELETE instead of TRUNCATE may avoid id not working 'right' the second time around.)

Suggestion: Use MyISAM for staging; InnoDB for permanent tables.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.