Import data of an excel table in seperate fields

[doolak]

Hi all,

I need to display an Excel table in a page - of course it would generally be possible just to copy and paste this (seems to work fine in the actual IE) but as the behaviour how it will be imported in the Textfield (TinyMCE) will always be dependend on the behaviour of the browser etc. I would prefer to have the single datas of the table imported in seperate fields.

I used "Import csv to Pages" several times for similar purposes - but in this case it would be necessary to import the data not in seperate pages but in seperate fields on one page. And, another problem is, that the excel tables are sorted like this:

Does anybody have an idea how one could import such data into a page?

[diogo]

https://support.google.com/drive/answer/55244

http://office.microsoft.com/en-001/web-apps-help/share-it-embed-an-excel-workbook-on-your-blog-HA102029502.aspx

https://zohowikihelp.wiki.zoho.com/Embed-Zoho-Sheet.html

[doolak]

Thanks for your answer, diogo!

This would be a possibility to have the tables displayed properly - but it will be a lot of data sets which has to be imtegrated, maybe 50.000 to 100.000 products, so this will too much work to include them.

And I would prefer to import the data into fields, not just embed the table into the website. So it would be possible then to use that data later for other purposes.

One idea which I have now in my mind is the following:

1. convert the Excel table to CSV and import with "Import pages from CSV files" each row of the table as page, which will be a subpage of the corresponding product page then

2. run a script which verifies if the product page has such subpages, and then adds the data of the subpages as fields to the product page

This maybe could work, one just would have to run the script after importing the CSV - but that would be just ok I guess.

What do you think about this possibility? Or do you have a better idea?

[diogo]

In that case that's the way to go. The "Import pages from CSV" module works great, and is very flexible. You just have to create the fields and templates that you need and choose to which fields the columns go.

[dragan]

You could try https://code.google.com/p/php-excel-reader/

Or convert the XLS to CSV, and use default PHP CSV functions to import / parse the data.

http://php.net/manual/en/function.str-getcsv.php

Depending on the number of imports and the number of different templates / fields, you could write a few lines of PW API code. It's quite easy to copy and paste Excel fields and convert to PHP arrays. Simple example:

$f = array("height","width","thickness","weight","price"); // field names
$v = array(38,75,3,220,10); // field values
$id = 123; // page id
$myPage = $pages->get($id);
$myPage->of(false);
foreach($f as $k=>$v) {
    $fld = $f[$k];
    $myPage->$fld = $v;
}
$myPage->save(); 

It's also quite easy to update a whole bunch of pages with one script (e.g. define a parent and/or template in the selector).

[dragan]

Just a little word of caution: It only works "out of the box" with the PW default language. If you have a multi-language site, all alternative languages have to be updated seperately.

I've been importing and updating product pages for a 4-language site the last couple weeks (you know... clients' deadlines are always very flexible...).

It's also possible to write PHP code directly in your Excel sheets, copy and paste it into a text-editor, and run the script just once. Just make sure the field-name arrays and field-value arrays always match (hidden rows in Excel are the devil - they get exported to .csv as well).

With 50 or 100k products / pages, it's a good idea to add set_time_limit(), or split the data in chunks...

http://php.net/manual/en/function.set-time-limit.php

[doolak]

In that case that's the way to go. The "Import pages from CSV" module works great, and is very flexible. You just have to create the fields and templates that you need and choose to which fields the columns go.

Yes, I guess this should work fine - just it will not be possible to have all "parameters" as seperate field, because the already existing tables are using the first column of the table as "Label" so with "Import pages from CSV" I will have to import the data as follows:

- product page

   - subpage with title "Name", second field empty and third field the name of the product

   - subpage with title "height", second field "cm" and third field "38"

   - etc.

The tables are always structured like that, so I could have predefined fields like "weight", "height" etc. - so one would have just to choose if it's column 1, column 2, column 3 etc. during the import with "Import pages from CSV".

You could try https://code.google.com/p/php-excel-reader/

Will this just "read" and display the Excel data or add all the data to the database?

Or convert the XLS to CSV, and use default PHP CSV functions to import / parse the data.

http://php.net/manual/en/function.str-getcsv.php

This sounds as it could do exactly what I need, but I have to dig into that stuff first and it will be maybe not easy to code this...

Depending on the number of imports and the number of different templates / fields, you could write a few lines of PW API code. It's quite easy to copy and paste Excel fields and convert to PHP arrays. Simple example:

$f = array("height","width","thickness","weight","price"); // field names
$v = array(38,75,3,220,10); // field values
$id = 123; // page id
$myPage = $pages->get($id);
$myPage->of(false);
foreach($f as $k=>$v) {
    $fld = $f[$k];
    $myPage->$fld = $v;
}
$myPage->save(); 

It's also quite easy to update a whole bunch of pages with one script (e.g. define a parent and/or template in the selector).

There are a lot of different "fields" - what do you mean with "copy and paste"? Where should it been pasted?

[diogo]

what do you mean with "copy and paste"? Where should it been pasted?

dagan means, pasting the column names int the array "height","width","thickness","weight","price"

With 50 or 100k products / pages, it's a good idea to add set_time_limit(), or split the data in chunks...

This is a good point. The good thing is that you don't need to use find() and keep giant arrays in memory, but you should bootstrap processwire in a php file, and call it from the terminal. This will avoid to overcharge the server and allow you to echo the steps and any errors that might ocurr and monitor what's happening in real time. Also use uncacheAll() in every loop, so the PW pages don't occupy memory.

Not related with the importing itself, but maybe you will need to interact with all those pages at some point. Don't use find() for this as it will quickly drain all the ram. I posted on another thread a solution that helped me a lot in a similar situation: http://processwire.com/talk/topic/2038-distinct-selector-in-processwire/?p=43720

[doolak]

For the moment the costumer just would like to copy paste the table into the editor - this works great but I suggested him that it would be better to find a possibility to import the tables in a different way. But then I would have to find a possibility to make this so easy like it's now with drag and drop... ;-)

I guess It will be necessary to write a module for this or maybe rewrite the  "Import CSV to pages"... 

So I will leave it like it is for the moment and maybe come back later to this point - if the costumer is happy with the table as it is - why not? ;-)

Thanks a lot for your help!!!