Jump to content
electricarts

Import entries from CSV file

Recommended Posts

Hallo,

i'm searching for a way to import entries from a css file. I have a csv (or txt) file with some comma-separated data and want to import just 1 entry into a list (text or textarea field) in the PW (3.0.33) backend. 

I tried the "Import Pages from csv files" module. I'm not sure if this is the right solution. The file can't be imported, because after Step 2 and starting the import, the "Unable to import page because it has no required 'title' field or it is blank" error message appears. I'm doing something wrong or is using this module the wrong way of importing csv entries?

Mario

Share this post


Link to post
Share on other sites

Perhaps this is more what you're looking for ?

http://faisalman.github.io/simple-excel-php/

I think I ran into some problems with the importCSV module (most likely user error on my part) so had to look for alternatives like SimpleExcel.

Here's some code I've used on my project.  It should be fairly straight forward and simple.

 


    public function ExecuteContent($file) {


        //TODO : Automate or Refactor this
        $_EOF = false;
        $_rowStart = 2;

        $_colTitle  = 1; //A
        $_colMeta   = 10;//J    Meta Title = SEO Title
        $_colDesc   = 11;//K
        $_colURL    = 12;//L
        $_colURLnew = 13;//M
        $_colCanonical = 14;//N
        $_colAlt    = 15;//O
        $_colKeywords   = 16;//P

        $excel = new SimpleExcel('TSV');
        $excel->parser->loadFile(__DIR__."/{$file}");

        //$url =  $excel->parser->getCell($_rowStart, $_colURL );
        $row= $_rowStart;
        do
        {
            try {
                $title = $excel->parser->getCell($row, $_colTitle);
                $meta = $excel->parser->getCell($row, $_colMeta);
                $desc = $excel->parser->getCell($row, $_colDesc);
                $url = $excel->parser->getCell($row, $_colURL);
                $urlnew = $excel->parser->getCell($row, $_colURLnew);
                $canonical = $excel->parser->getCell($row, $_colCanonical);
                $alt = $excel->parser->getCell($row, $_colAlt);
                $keywords = $excel->parser->getCell($row, $_colKeywords);

                $internal = str_replace("http://sprachspielspass.de", '', $url);

                $page = wire(pages)->get("path=$internal");

                //TODO: Error handling and logging
                if (!IsNullPage($page)) {
                    $page->setOutputFormatting(false);
                    set_time_limit(60);

                    $page->title = $title;
                    $page->seo_title = $meta;
                    $page->seo_description = $desc;
                    $page->url = $urlnew;
                    $page->seo_canonical = $canonical;
                    $page->image_alt = $alt;
                    $page->seo_keywords = $keywords;


                    try {
                        $page->Save();
                        WriteLog("{$internal} saved");
                    } catch (\Exception $e) {
                        WriteLog($e->getMessage() . " " . "{$page->title}" . " has errors");
                    }
                } else {
                    WriteLog("{$internal} NOT FOUND!");
                }
                //Manually deallocate $page as PHP's garbage collection is truly garbage.
                unset($page);

                /**
                 * unset($title);
                 * unset($meta);
                 * unset($desc);
                 * unset($urlnew);
                 * unset($canonical);
                 * unset($alt);
                 * unset($keywords);
                 * unset($internal);
                 **/
                $row++;
            }
            catch (\Exception $e)
            {
                $_EOF= true;
            }

        }  while (!IsNullOrEmptyString($url) && (!$_EOF) );

        /*      $excel->convertTo('JSON');
                $excel->writer->addRow(array('add', 'another', 'row'));
                $excel->writer->saveFile('example');*/
        //unset($url);
        unset($excel);
    }

Don't forget to reference the SimpleExcel.php file using either require_once or include_once.

e.g. 

require_once(__DIR__."/../vendor/SimpleExcel/SimpleExcel.php");
  • Like 1

Share this post


Link to post
Share on other sites

Many thanks @szabesz and @FrancisChung! I'll try your suggestions.

But the problem with both solutions will be, that for the user (editor of the site) it has to be super-easy to load these csv-files on a regular basis. Every few weeks there are a few changes in the file and it should be like "select the file and click upload" to apply the changes. 

Share this post


Link to post
Share on other sites

If the file itself doesn't change (i.e. you're not adding new fields to Page or Template), then it shouldn't be an issue.

One of my partners uses this on a regular basis, so I can't see why your user won't be able to either.

If the fields are being changed or removed, you'll need a more generic solution.

 

You could perhaps code in such a way that you match the field name of the Excel file to the field name of the Template/Page you're trying to update.  

i.e. column 1 title corresponds to a field called title on the page and so on.

You would loop through the columns and row(s) and update fields and page(s) accordingly.

Share this post


Link to post
Share on other sites
5 hours ago, electricarts said:

Many thanks @szabesz and @FrancisChung! I'll try your suggestions.

But the problem with both solutions will be, that for the user (editor of the site) it has to be super-easy to load these csv-files on a regular basis. Every few weeks there are a few changes in the file and it should be like "select the file and click upload" to apply the changes. 

Have you looked at BatchChildEditor: 

 

You can move the interface to anywhere you want - either part of the Children tab, the Content tab, or even its own tab. You can predefine field pairings for the CSV columns to PW fields, etc. In Update mode it will update existing PW pages. You can also choose from Add and Replace modes depending on your needs.

Maybe it might be helpful?

  • Like 2

Share this post


Link to post
Share on other sites

Thanks @adrian for your suggestion. But i'm unable to make it work. The csv file has 6 columns (screenshot). So i add 6 fields to my template. When i choose "add" at the Batch Child Editor tab, choose the csv file and hit "save", the error:

"The number of columns/fields in your CSV do not match the number of fields in the template. Nothing can be safely imported. If you need to exclude certain fields, make sure the CSV has blank values for the excluded fields."

appears. 

I don't know what i'm doing wrong. I also tried the import with 4 fields (because of the 2 blank columns). But with no success. 

 

Mario

 

Screen Shot 2016-09-15 at 17.41.07.png

Share this post


Link to post
Share on other sites

 

On 9/15/2016 at 8:43 AM, electricarts said:

Thanks @adrian for your suggestion. But i'm unable to make it work. The csv file has 6 columns (screenshot). So i add 6 fields to my template. When i choose "add" at the Batch Child Editor tab, choose the csv file and hit "save", the error:

"The number of columns/fields in your CSV do not match the number of fields in the template. Nothing can be safely imported. If you need to exclude certain fields, make sure the CSV has blank values for the excluded fields."

appears. 

I don't know what i'm doing wrong. I also tried the import with 4 fields (because of the 2 blank columns). But with no success. 

Hi Mario,

Can you provide a list of the template's fields, including the type of each field, along with the csv you are trying to import so I can test at my end?

  • Like 1

Share this post


Link to post
Share on other sites
2 minutes ago, electricarts said:

Hi adrian,

for my test i use 6 fields of "text" type (from textfield_1 to textfield_6). Textfields should display numbers too, right? The csv file is attached.  

article-list.csv

What about the Title field?

Yes - text fields can display numbers just fine!

Share this post


Link to post
Share on other sites
2 minutes ago, electricarts said:

One title field "Import". 

Sorry not sure I quite follow. It is the required/global PW Title field I am referring to. Have you removed that from this template? Is "Import" an additional field in the template (in addition to the 6 fields of "text" type (from textfield_1 to textfield_6) ?

Can you maybe paste in a screenshot of the list of fields from the template view page?

Share this post


Link to post
Share on other sites

So the problem is that there are seven fields in the template and only six in the CSV. You need to supply the value for the title field in the CSV file as well.

Share this post


Link to post
Share on other sites

Ah, ok. I thought the title field is separated from the fields in which the entries are imported to.  So i've to add a blank column in the csv file or a column with "Import" in every row?

Share this post


Link to post
Share on other sites
1 hour ago, electricarts said:

Ah, ok. I thought the title field is separated from the fields in which the entries are imported to.  So i've to add a blank column in the csv file or a column with "Import" in every row?

It really depends on what you are doing. If you are adding new pages then the title field is required. Sorry, I assumed that was what you were doing.

If you are updating existing pages, then you need to use the update option in BCE and the field pairings setting to match csv columns to template fields. If you do that, then you don't need the title field. Did you read about and understand the field pairings setup?

PS If you're having trouble I'd be willing to take a look at the settings on your site if you're willing to give me access.

Share this post


Link to post
Share on other sites

Ahh, thanks for pointing me in the right direction. ;) I was able to import and update the entries from a csv file! 

But i'm wondering where the field pairings setup is. Of course i read the ReadMe, but from the beginning a was searching for theses settings. I see "Data entry / CSV import settings", but no pairings setup. Maybe i'm blind. :(

I uploaded a test site to the server:

 

 

 

Edited by adrian
Removed login details

Share this post


Link to post
Share on other sites
2 hours ago, electricarts said:

Ahh, thanks for pointing me in the right direction. ;) I was able to import and update the entries from a csv file! 

But i'm wondering where the field pairings setup is. Of course i read the ReadMe, but from the beginning a was searching for theses settings. I see "Data entry / CSV import settings", but no pairings setup. Maybe i'm blind. :(

I uploaded a test site to the server:

Thanks for the access.

The field pairings option is mentioned in the first post of the BCE support thread. What is probably not clear is that because different pages / templates on your site may require different field pairings, this setting is only available from the Settings tab of pages that are set to be separately configurable from the main module settings.

I have done this for you and everything is now working as expected in Update mode even without a title set in the CSV file.

Screen Shot 2016-09-18 at 9.22.23 AM.png

Now for some caveats.

If you try to import a CSV file that doesn't have a title column (even in update mode) that also has more rows than there are already existing child pages, then it will throw an error because each page in PW needs a name/title. But so long as you already have a child page for each tire size, you should be fine. Otherwise, make sure the CSV has the tire size as the title for each row and that will work fine.

The other thing I noticed is that the CSV file you sent me was encoded UTF16 LE with BOM and it was failing to be read. I changed it to UTF8 and it was fine. Perhaps this is something that BCE could handle better, but for now, you'll want to be aware of that.

I also played around with the labels to make this more specific to your client's needs, but of course you can edit further:

Screen Shot 2016-09-18 at 9.28.32 AM.png

Please let me know how you go.

 

PS Is there any reason you've gone with the generic "Tire" title/name for each page, rather than maybe using the size code or something else unique?

  • Like 2

Share this post


Link to post
Share on other sites

Hi adrian,

many thanks for your help! 

Now i understand the field pairings setup. Of course, that only makes sense to display the settings in the page settings.

The encoding should be Excels fault. >:( I receive many excel files from clients which have wrong encodings (mostly for characters like ä, ö, ü and so on). I've to open this files in Apple Numbers and re-save to fix this. 

If i understand it right, the CSV file should have a additional column for the title (with the tire size)? This should fix the "Tire" title for every subpage too. 

Once again many thanks!

 

Share this post


Link to post
Share on other sites
6 minutes ago, electricarts said:

The encoding should be Excels fault.

Even Excel for Mac and Excel on Windows do use different encodings. 

Share this post


Link to post
Share on other sites
1 minute ago, LostKobrakai said:

Even Excel for Mac and Excel on Windows do use different encodings. 

Quality Management, the MS way. :)

  • Like 1

Share this post


Link to post
Share on other sites
4 hours ago, electricarts said:

The encoding should be Excels fault. >:( I receive many excel files from clients which have wrong encodings (mostly for characters like ä, ö, ü and so on). I've to open this files in Apple Numbers and re-save to fix this.

I am not sure the best thing that BCE can do here - it seems that in PHP it's not really possible to convert encoding without knowing what it is and it's impossible to determine the encoding completely reliably. This seems like the most likely candidate for a solution: http://stackoverflow.com/a/7980354/1524576 but there are still no guarantees, but maybe it's worth trying?

I know Excel can be a pain and I was reading a little more here: https://help.surveygizmo.com/help/encode-an-excel-file-to-utf-8-or-utf-16 - it mentions that:

Quote

Unfortunately there is not an option for UTF-8 encoding a CSV in Excel for Mac

But when I save an excel file to CSV on my Mac, it is automatically UTF-8, so I don't know what is going on. Maybe that article is outdated as it refers to 1999 to 2010 and I am running Excel 2011?

Share this post


Link to post
Share on other sites
5 minutes ago, adrian said:

But when I save an excel file to CSV on my Mac, it is automatically UTF-8, so I don't know what is going on. Maybe that article is outdated as it refers to 1999 to 2010 and I am running Excel 2011?

A possible answer to your question above

http://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_mac/does-excel-2016-for-mac-support-utf-8-csv-imports/4576cfb2-4e23-442d-a487-2f9adaa78f72?auth=1

  • Like 1

Share this post


Link to post
Share on other sites
7 hours ago, electricarts said:

If i understand it right, the CSV file should have a additional column for the title (with the tire size)? This should fix the "Tire" title for every subpage too. 

Well it depends - if you have the tire child/subpages already setup and they won't change, then using field pairings to ignore the title, you can exclude it from the CSV file. It really depends on whether this is a one time import or whether the client will be using it to update the site and how their csv will be structured. Anyway it sounds like you have a handle on how the module works - now it's up to you to decide the best approach.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Vineet Sawant
      Hi,
      I'm trying to import some heavy data into Processwire, but I'm not sure what would be the best way to do it.
      Usually I use CSV to Pages plugin, but this time the data is too heavy(~40k rows with 10+ columns of excel sheet), thus this plugin can't help.
      I also tried Tasker plugin but I can't seem to go through the setup itself, it requires some template setup but I'm totally clueless about how to do it, so that plugin is not of any use either.
      I wanted to know from you guys how you do it and in future what would be the best way to migrate thousands of rows of data in to PW.
       
      Thanks.
       
       
    • By hellerdruck
      Hi all
      I need to export all the texts from a website to a translation company (as json or csv or txt...). How can this be done? Of course manually, but this website is huge and it would take me years...
      Also, as a second step, importing the translation ...
      Any ideas anyone? Tutorials? Plugins?
      Thanks for your help.
    • By Rodd
      Hi everyone!
      I have a website in a production environment and I want to duplicate it in a local environment. I exported the content of the website (with the 'Site Profile Exporter' module) but I cannot use it actually. I've got an issue with the database. I imported this one in MAMP then.

      I also exported the pages (with the 'ProcessPagesExportImport' module), but I cannot import it to my local website because the fields don't exist. So I created this fields, but I have this error :
      How can I use the elements that already exist and are presents in my database? How can I duplicate correctly the templates, fields and pages?
      Thanks by advance
      PS: Sorry if my english is bad
       
    • By hellerdruck
      Hi all
      I need help with something. Situation: We have let's say 2'000 Files (Excel) that should be displayed (list with links) on a page. We'd need to filter these files by given Keywords or a tree structure or both. Now, I'm looking for a solution whereas our customer can synchronise the files from his local computer with the folder on the webserver. They will update and upload files on a daily basis. Therefore, it would need to synchronise rather than load the files manually in pages or repeaters. Maybe indexing would be an idea, too.
      Are there any modules for Processwire that would help achieving this? Could anyone point me in the right direction?
      Thanks in advance.
×
×
  • Create New...