Import entries from CSV file

[electricarts]

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

[szabesz]

Hi,

Probably the simplest way to do this is to implement a simple import script, such as this one:

Yeah, I know it's XML, but CSV should be simple too, eg: http://stackoverflow.com/questions/9139202/how-to-parse-a-csv-file-using-php

Hope this helps.

[FrancisChung]

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");

[electricarts]

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. 

[FrancisChung]

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.

[adrian]

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?

[electricarts]

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

 

[adrian]

 

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?

[electricarts]

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

[adrian]

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!

[electricarts]

One title field "Import". 

[adrian]

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?

[electricarts]

Yes, it's the required Title field. 

[adrian]

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.

[electricarts]

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?

[adrian]

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.

[electricarts]

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 September 18, 2016 by adrian
Removed login details

[adrian]

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.

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:

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?

[electricarts]

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!

 

[LostKobrakai]

6 minutes ago, electricarts said:

The encoding should be Excels fault.

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

[electricarts]

1 minute ago, LostKobrakai said:

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

Quality Management, the MS way. 

[adrian]

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?

[cstevensjr]

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

[adrian]

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.

[bernhard]

As this was the first google search result for PW get CSV I just wanted to mention that we now have $files->getCSV()

https://processwire.com/api/ref/wire-file-tools/get-c-s-v/