Importing excel avoiding some columns

PHPMaker General Discussion (PHPMaker)
1

Hi all:

Using v2019
I have a table with some fields:
id(AI),item,qty,serial

I have an excel file with this headers
id(null),item,description,qty,serial

I want to import the rows, check if item exist in a master table, and if it no exists, then add item to the master table with the description.

I’ve been following several posts, and I know I have to use Page_Importing and Row_Import to fit the headers to my table fields, and unset the excel fields I don’t want to import.
I’ve tried some solutions described in this two posts:
https://discourse.hkvstore.com/t/change-header-for-the-import-file/1789/4
https://www.hkvforums.com/viewtopic.php?f=4&t=45584&p=144329&hilit=import+excel+field#p144329


If I don’t make anything in the Page_Importing event, I get messages of unknown fields.
So in the Page_Importing I make:

function Page_Importing($reader, &$options) {
$options[“headers”] = [“id”,“item”,“qty”,“serial”];
$options[“offset”] = 1;
return TRUE;
}

and try to unset the fields in:

function Row_Import(&$row, $cnt) {
unset($row[2]);
return TRUE;
}

In this case I get:

Warning: array_combine(): Both parameters should have an equal number of elements in …lineas_list.php on line 2052 ($row = array_combine($headers, $values):wink:
Warning: Invalid argument supplied for foreach() in…lineas_list.php on line 2158 (// Check field values foreach ($row as $name => $value) {…)


I’ve tried too:
function Row_Import(&$row, $cnt) {
$ori = array_slice($row, 0); // Clone
$row = ; // Clear
$row[“id”] = $ori[0];
$row[“item”] = $ori[1];
$row[“qty”] = $ori[3];
$row[“serial”] = $ori[4];
//I’ve tried too with indexex, row[“0”], row[“1”], etc…
return TRUE;
}

I get:
array_combine(): Both parameters should have an equal number of elements in …lineas_list.php on line 2052 ($row = array_combine($headers, $values):wink:
Warning: array_slice() expects parameter 1 to be array, boolean given in…lineas_list.php on line 2716 ($ori = array_slice($row, 0); // Clone)

So, in both cases it seems I can’t get a right row array, that match with the headers.

I’ve tryed var_dump, but, as said in the above posts, I can’t get the values, not sure if it’s due to the modal form.

Thanks in advance.

2

Make sure that the number of columns matched with your headers first. You start with 5 columns but only four headers.

3

Hi arbei:

Thanks for you answer. Not sure if I understand.
Do you mean I start with five colums in the excel file and four fields in my database table?
If it’s what you mean, it’s what I want to do.
I want to import the five colums excel into the four fields database, transforming the row array of the excel file in four fileds, avoiding the “description” one.

If I test with a four colums excel, I can import properly.

Thanks again

4

josejad wrote:

I have an excel file with this headers
id(null),item,description,qty,serial => 5 fields
$options[“headers”] => [“id”,“item”,“qty”,“serial”]; // 4 fields only

Hence the warning: array_combine(): Both parameters should have an equal number of elements

You may try:

$options[“headers”] => [“id”,“item”,“description”,“qty”,“serial”]; // 5 fields

Then in Row_Import() you may remove the unwanted field, e.g.

unset($row[“description”]);

5

I see, I thought I had to adjust the headers too to what I wanted.

If I try:
options[“headers”] => [“id”,“item”,“description”,“qty”,“serial”]; // 5 fields
(I think this is the same as if I don’t use options[“headers”], because are the same headers the file has)

I get:
“Invalid field name: description”

It seems I get this error before Row_Import is fired, so I get the error from Page_Importing, that’s why I use options[“headers”] without “description” field, but, then, it seems is not unset in Row_Import, and that’s why headers and values don’t match.

6

arbei wrote:

Then in Row_Import() you may remove the unwanted field, e.g.

unset($row[“description”]);

https://www.php.net/manual/en/function.unset.php

You may debugg by checking the the $row first, e.g.

var_dump($row);
die();

7

var_dump and die don’t show nothing.

I’ve tried unset, but I think Row_Import() is not being fired, because I get: Both parameters should have an equal number of elements.
I’ve tried to play with headers, but no luck.

As far I can understand:

  • Page_Importing is fired. If I don’t change the headers, I get “unknow name field”. If I change the headers, I get: Both parameters should have an equal number of elements.
    So, it seems that Row_Importing gets an already combined row with the headers and data.
    Unset seems not to work…

Re-reading again the help file “Import Data”, I think is not posible having a table with 3 fields, and import a file with 5 fields and avoid import two of them in Row_Importing.

8

Make sure your Page_Importing server event returns true. From the default event code:

// Page Importing event
function Page_Importing($reader, &$options)
{
//var_dump($reader); // Import data reader
//var_dump($options); // Show all options for importing
//return false; // Return false to skip import //***
return true; //***
}

9

Hi again:

Yes, it returns TRUE. :frowning:

Following the code from the public function import($token)

First error:
Warning: array_combine(): Both parameters should have an equal number of elements

foreach ($records as $values) {
$importSuccess = FALSE;
try {
$row = array_combine($headers, $values); ///error
$cnt++;
…

$row is assigned before the unset in row_import fuction.

Second error:
Warning: Invalid argument supplied for foreach()

// Check field values
foreach ($row as $name => $value) { //error
…

due to array_combine returns FALSE if the arrays don’t have the same number of elements.

10

So, I have to left the headers as it, and just unset the fields I want to avoid in Row_Import event, but the unset seems not to work because I get the "“Invalid field name: description” message.

I’m trying to debug $row value before and after the unset, but var_dump is not working…

Keep on trying