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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By maba
      Hello,
      I need to import regularly - every 15 or 30 days - a big .xslx file into my PW installation.
      This file now has 14 columns, 5.000 rows and grows every month.
      I'll need to group, order and work with these data to:
      analyse User monthly costs analyse User costs per Asset ... User (real AD account) has to match with a PW user - I can't join to the domain - but as you can see I have some services users (start with sca_*) or no user at all. Those rows have to be assigned to a specific user, e.g. account100.
      And:
      I would like to be able to have a kind of diff function to compare User assets between this and last month (and so on) other request is to have a notification when something change for a User between actual and latest import First request: which is the best solution to store those data in your opinion? Page, Table, Repeater Matrix, ...?
      Those are very repetitive data and I think a page reference is better than to import all the data every time but I have to understand how to manage those "dynamic" groups of software (AccType Det), hardware (Asset), ... For example Price will be imported and not stored with the description because it could be change in the future and I'll not have any control on it.
      Thanks!
      User,OE,productNmr,AccType1,AccType Det,Count,Price (€),Sum,ASNA,CC,AccType Info,Asset,AccGroup,,,,,,,,,,,,,
    • By dragan
      Is it by design that a site/ready.php is not included when creating a new site profile? Is it possible to include it with a hook? Or are there any security thoughts? (I don't want to redistribute it in public, it's just so I have my own boilerplate)
    • By karian
      I don't know why multiple instances (repeater_repeat_columns1, repeater_repeat_columns2, ...) of my repeater field are displayed inside Template field (see image).
      Is there a way to clean/reset it ?
       

    • By psy
      I'm combining two PW sites into one, Site A into Site B.
      At each step, I did it bit by bit as the 'all at once' approach failed.
       
      First, I exported all the fields from Site A and imported into Site B. Any field types not supported by import/export, eg FieldtypeOptions I manually recreated. All good.
      Next I exported all the templates from Site A and imported them into Site B and copied across their associated template files. All good.
      Finally I exported the pages I needed from Site A into Site B - again, bit by bit to ensure it all went smoothly.
      From the admin side, it all looked and worked perfectly.
      Front end was a totally different story. All existing pages in Site B worked as expected. NONE of the pages imported from Site A displayed. They all ended in a redirect loop with no errors in the PW logs or Tracy Debugger.
      After some trial-and-error, I finally got it working with:
      - create a new template in Site B admin with no associated template file and just a title field
      - import the fields from the imported Site A template into the newly created template (both on Site B)
      - copy the Site A php template file into a new file that matched the new PW Site B template name and save in Site B site/templates
      I can deal with the above workaround. Just curious to know if I did something wrong or if the template import/export feature is problematic?
       
      ### Solution:
      While the export/import was a slow process, turned out the front end redirecting issue was unrelated. For reasons unknown, all templates marked as HTTPS only were the ones redirecting, ie all templates from Site A. Finally solved it by changing the $config->https to true in site/config.php
      Now the pages display correctly as https whether the template forces the issue or not.
       
    • By rareyush
      i am receiving and error whenever I try to run my processwire on localhost,

       
       
      sql code
       
      -- -- Table structure for table `field_fieldset_meta_end` -- CREATE TABLE `field_fieldset_meta_end` ( `pages_id` int(10) UNSIGNED NOT NULL, `data` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `field_fieldset_meta_END` -- CREATE TABLE `field_fieldset_meta_END` ( `pages_id` int(10) UNSIGNED NOT NULL, `data` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; anyone ?
      whenever I make a new database and upload it there, database get imported without errors.
×
×
  • Create New...