Intercept import process and manipulate data

The only one way to achive what you need is using events.

Thanks for replying.
Maybe I was not clear. I wrote that "I know how to use events", so obviously I am looking at this direction <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=80437&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
My question was: Can I use events to intercept the import process and then use data from the source file to write to two tables? Import only maps data to one table (i.e. master) but I need to read other source columns so that I insert data to another table also (i.e. details).
I hope I am describing a little better now.
Regards
Jim

The way you are looking for not possible within current phprunner feature rather using events. I tried with no success. What you can do, import all headers for Master & Detail into a separate single Temp table then use trigger on the Temp Table at db level to segregate new imported data into Master & Detail tables. You cannot map both Master & detail table columns from your single excel file. Phprunner map only the excel sheet related to the specific table not connected table.

Use BeforeInsert event to insert record to either table or two both tables at once:

http://xlinesoft.com/phprunner/docs/before_insert_record.htm

Hello Abul,
Now that's a very interesting approach. I will try and get back to this.
Thanks !
Jim

Ok, but then how do I import data for both tables from the same source file? Abul has a point about mapping.
Thanks,
jim

This is actually a question that you have to ask yourself - how do I import two tables data from one source file? How did you plan it to work?

But...
I already asked myself and he could not answer and this is the reason why another fellow user (Abul) tried to help us

I am planning on following Abul's idea unless there is a better way.
Regards,
jim

I don't think you understand the question. I'm not talking about programming. I'm talking about the structure of import file, how do you fit two tables data into a single Excel sheet - this is the question you need to ask yourself before anything else.
The answer on how to perform that import will be different depending on import file structure.

Thanks for getting back on this. I describe the import file structure in my first post. This import file structure is given by a customer as they are gathering all their data using excel spreadsheets. One line of that import file should go into one master record and few details records.
Regards,
Jim

Which is the excel file Master or Child? Can you show the fields in the file you are importing and the relationship to those tables. Two excel files will not work on one import but beforeinsert will work if you have a relationship between the two tables , depending on your structure .
I use quite a bit before insert to update other tables . here is an example
// *import notes - must use xlsx - xls or csv date for excel cells must be formatted as date ** upload template_c must have write permissions set on IIS
// BEFORE INSERT EVENT

//seems your raw data is the master so I will use as example
$strSQLExists = "select invoice_id from childtable Where invoice_id=" . $values["invoice_id"] . " ";

$rsExists = db_query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);
if($data){
$sql = "INSERT INTO childtable (product_id , customer_id , producta) values (". $values["product_id"] ." , " . $values["customer_id"] . " , ". $values["producta"] ." ) Where invoice_id = " . $values["invoice_id"] ." AND customer_id = ". $values["customer_id"] ." ";

CustomQuery($sql);
// can use OR instead of AND depending on what you need
} else ....
[color="#FF0000"]// You can reverse this to only insert if INVOICE dosen't exist. Change query to NOT EXISTS IF Invoice is always new . ( if using mysql )
// if you don't have customer_id in raw file then take out of example

// iF use test with where values first to avoid data corrupt/loss.

// You can change to update child records or insert data depending on you product_id

Thanks for taking the time to reply.
To answer your question, my excel file has information for both master and child.
The problem is not the insert itself but the excel_column-to-sql_column mapping during import. I can map the master columns allright (customer and invoice columns go to relevant master table fields) but I don't know what to do with the excel-->children row columns (invoice, productA, productB, productC) because the information in the excel is row/column rotated (excel column 'productA' must go to a new child row with product="productA", another row with**product="productB" etc.**).
I understand your query but it's not what I need: You wrote [quote]

[color=#1C2837]


[font="arial, verdana, tahoma, sans-serif"][color="#1c2837"]
Jim

Ok, I figured out a good-enough-for-now solution:

1. I added a temporary column to my master table
2. I added a concatenated column to my excel (e.g. productA:10&productB:50&productC:5)
3. During import I matched 1 with 2
4. Using the BeforeInsert event of the import page, I split the concatenated field and created the child rows (I had to call "CustomQuery("SET FOREIGN_KEY_CHECKS=0;");" to let the child records in for the session)
5. set the temporary column to null

Hope this helps someone
Thanks for all feedback friends
Regards,
Jim