When I use LOAD DATA LOCAL 'file.csv' IGNORE INTO..., is there any way to return a list of the rows that were inserted?

My table has about 28,000 rows, and when I run this once a month (via PHP cron), I'll expect 10-20 new rows to be inserted. These rows will need a small amount of manual editing, though, and it would be pretty helpful if I could email myself a list of the new rows instead of trying to remember to check on them manually.

On 11/20/2011 4:42 AM, Jason C wrote:
Look at the .csv file you're importing. It's just plain text. And I
would suggest editing the data before you load the file, not wait until
after.g

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On Sunday, November 20, 2011 8:55:40 AM UTC-5, Jerry Stuckle wrote:
Do you mean something like, load the table into a PHP array, load the CSV into an array, loop through manually to remove the ones that exist in both, then insert the remainder?

That can be done in PHP, sure, but I was hoping there was something more efficient. Like LAST_INSERT_ID(), but for a group of inserts.

On 11/20/2011 5:57 PM, Jason C wrote:
No, you can only have one LAST_INSERT_ID(), so you can't insert multiple
rows and expect to get multiple results back.

The question is - what kind of editing is required? Removing duplicate
rows is much different than editing incorrect data. The former would
just require editing the data before insertion.

As for loading everything in the table into an array - probably the
worst thing you can do. If you only have a few rows and are concerned
about duplicates, just add them one at a time and watch for duplicates.

Alternatively, load the data into a work table with the same layout and
check for duplicates using SQL.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 20.11.2011 23:57, Jason C wrote:
- select all IDs -> pre,
- load data,
- select all IDs -> post,
- array_diff(pre, post)

Jason C <jwcarlton (AT) gmail (DOT) com> wrote:

A list? No.

Like any other DML statement, LOAD DATA will return the number of
affected rows. And if your table has an auto-increment field, then
you will get the id of the first inserted row.

What is "PHP cron" ?

If that "editing" could be automated, then you can do it in the LOAD
DATA statement (by using variables and a SET clause).

Else it might be better to load into a separate table and move rows
to the real table only after manual inspection.

Alternatively add a field "inspected bool default false" to your table.
When a new row is added it is set to the default of needing inspection.
Later you can manually reset this flag.


XL

On Mon, 21 Nov 2011 10:27:43 +0100, Axel Schwenke wrote:
based on context, a php script launched by a scheduler process.

--
I don't see what C++ has to do with keeping people from shooting
themselves in the foot. C++ will happily load the gun, offer you a
drink to steady your nerves, and help you aim.
-- Peter da Silva