How to import table of data into ProcessWire

[Tony Carnell]

Hi,

I'll confess to having only just discovered ProcessWire, and am currently getting my head around the fundamental differences between it and other CMSs/CMFs I've used in the past. I'm very impressed with its potential though.

I have a question however that I hope you guys can help me with.

I'm involved in a project to implement a searchable database of dams from across the world (in fact something akin to the Skyscraper demo except that the dams will be searchable on many more criteria).

We already have a database table containing all 600+ dams and I'm wondering how to import those into ProcessWire in a way that it can handle.

Is it possible simply to add that table to the ProcessWire database and tell it how to interact with it, or will that complicate things further down the line?

I don't fancy having to manually add all the dams to ProcessWire, since quite apart from the time and hassle it would also introduce human error.

All advice is gratefully received

Tony.

[Fokke]

Hi Tony!

Create a template for dam and attach all needed fields to the template. Then export database table to CSV file and use this module to create dam pages from table rows.

http://modules.processwire.com/modules/import-pages-csv/

[LostKobrakai]

You don't even necessarily need the csv step. You could also create a php file which connects to the existing database and then imports them to pw via the api. 

[Craig]

Hi Tony! Welcome to the forum

I think one of the fundamental things to consider with projects like these, is to actually look and analyse the existing data - the structure, the format, the quality, and any relationships there may be. It's almost going through the normalisation process but perhaps not as strict.

It would be a good idea to take every column in the existing database to decide how that will be represented in the ProcessWire site. Probably, some of these will be simple translations - Varchars will become Text fields, and Ints will still be Integer fields. But what will be more important is the data in these fields - does it vary with just about every single dam? Is it a value representing Yes/No? Are there only a few values for that column that all dams can potentially use? If so, do the values all use the same spelling of words, or are some mixed case or have weird formatting or spacing issues? When you have a set list of values to choose from, this is where you can decide how to store the data in ProcessWire - Page fields, PageTables, Options field, etc.

If your source data quality is good, your job will be easier. If it's not, there will be some work involved in preparing it so that it is clean to work with.

You may already have a good idea of how your data looks or is structured, but I usually like to do some form of planning on paper or using a spreadsheet to map out the source data to the ProcessWire templates and fields. Doing the important work now will save you hassle later on down the line and will really help you to make the search feature more functional.

As an example:

In ProcessWire, I imagine there would be a template created called 'dam'. You might add the following fields to this template (just for starters):

• title
• description
• location (FieldtypeMapMarker)
• height (integer/float)
• length (integer/float)
• capacity/volume (integer/float)
• country (Page field/Options field?)
• built (date)
• opened (date)

For each dam, a Page would exist somewhere in the page tree, using the 'dam' template.

As you've already seen the Skyscraper profile, you can probably look at that for more ideas on how to structure things though

[bernhard]

welcome tony,

we had a very nice example of filtering a database in the showcase section some days ago: https://processwire.com/talk/topic/3671-ohmspeakercom/

http://ohmspeaker.com/speaker-filter/

good luck with your project!

[pwired]

You don't even necessarily need the csv step. You could also create a php file which connects to the existing database and then imports them to pw via the api.

It would be very interesting to see some php example of this.

[LostKobrakai]

$myDB = new Database('localhost', 'username', 'password', 'db_name', 3306);

$result = $myDB->query("SELECT * FROM my_table");

while($row = $result->fetch_assoc()) {

$p = new Page();

$p->template = "dam";

$p->parent = "something";

$p->title = $row['title'];

$p->save();

}

[pwired]

@LostKobrakai, thanks for this example. I like that kind of approach. Never thought this could be done

with so little lines of code. Nice to see this pdo/php directly mixed with processwire api.

Edit:

There is more to find about this in the forum like here:

https://processwire.com/talk/topic/2242-interact-with-external-database-mysql/#entry20969

But are $myDB and new Database reserved words in Processwire, and if so where can I find them declared or explained, or can you chose just any other word ? I can't find anything about Database in the api cheatsheet

or with google.

[Christophe]

I guess $myDB could be anything else.

And that Database() is like Page(), something related to ProcessWire.

But I could be wrong .

[LostKobrakai]

Totally right Christophe. Database is a core class like Page and $myDB is a variable like any other.

[Tony Carnell]

Wow, thanks for all your comments and suggestions guys.

The ProcessWire community sure seems a friendly and helpful lot!

I'll read through all the pointers you've given me and take it from there.

You've certainly all given me plenty of food for thought.

Thanks again, it's much appreciated.

Tony.

[LostKobrakai]

@pwired.

You can find out about these things, when looking into the wire/core (and wire/modules/) folders. These classes are just things the core itself uses and are not providing an "api" like $page or $pages. About the length of the snippet: Even raw php wouldn't need that much more lines to read from an mysql table.

[horst]

There is one thing that needs a bit attention when importing data. We need to check if a record was also imported by a previous run of the script, otherwise you may end up with numerous duplicates.

while($row = $result->fetch_assoc()) {
  $title = wire("sanitizer")->text($row['title'], array("maxLength"=>128));  // fetch and sanitize the title
  $p = wire('pages')->get("title={$title}");                                 // check if it already exists
  if(0 < $p->id) continue;                                                   // move on to the next record if it exists
  $p = new Page();
  $p->template = "dam";
  $p->parent = "something";
  $p->title = $title;                                                        // use the sanitized title
  $p->save();
}

[Adam Kiss]

Or by some other field/way, if title might not be unique.

For instance, when adding children to previously empty page for the first time, you simply check for $parent->children->count

[Craig]

When I'm importing data, I usually add a ProcessWire field called legacy_id. Any import script will set this value to the actual ID from the item in the old dataset. It helps the script easily and accurately find existing imported items, and you can keep this around for a little while after so you can trace it back if you need to.

[bernhard]

hi craig,

i'm not sure if i understand you. can you please go a little bit more into detail, thanks

[diogo]

Craig is saying that he adds a field for each created page that contains the original id in the old database. If something goes wrong, or you need to adjust something in the import, or you need some more info from the old database that you didn't import originally, or you simply need to check if the items were imported correctly, you can check that field against the ID in the old database.

$p->legacy_id = $row['id'];

[bernhard]

thank you diogo

[kixe]

To select/ provide any data of any table in your database which does not belong to processwire use module FieldtypeSelectExtOption. No need to import or transform data in a processwire format.

[neosin]

On 9/2/2015 at 3:31 PM, horst said:

There is one thing that needs a bit attention when importing data. We need to check if a record was also imported by a previous run of the script, otherwise you may end up with numerous duplicates.

while($row = $result->fetch_assoc()) {
  $title = wire("sanitizer")->text($row['title'], array("maxLength"=>128));  // fetch and sanitize the title
  $p = wire('pages')->get("title={$title}");                                 // check if it already exists
  if(0 < $p->id) continue;                                                   // move on to the next record if it exists
  $p = new Page();
  $p->template = "dam";
  $p->parent = "something";
  $p->title = $title;                                                        // use the sanitized title
  $p->save();
}

how would I do this for multilingual data?

if in the external DB in each row we have some fields for both languages

example:

$row['title_en'] $row['title_fr']

$row['summary_en'] $row['summary_fr']

$row['other_en'] $row['other_fr']

 

 

[bernhard]

https://www.google.at/search?q=site:processwire.com+save+page+multilanguage+api

[neosin]

17 minutes ago, bernhard said:

https://www.google.at/search?q=site:processwire.com+save+page+multilanguage+api

@bernhard   thanks, this should work I guess

while($row = $result->fetch_assoc()) {
  $title_en = wire("sanitizer")->text($row['title_en'], array("maxLength"=>128));
  $title_fr = wire("sanitizer")->text($row['title_fr'], array("maxLength"=>128));
  $p = wire('pages')->get("title={$title_en}");                                 
  if(0 < $p->id) continue;                                                   
  $p = new Page();
  $p->title->setLanguageValue($languages->get('en'), $title_en);
  $p->title->setLanguageValue($languages->get('fr'), $title_fr);                                                       
  $p->save();
}

 

[JeevanisM]

On 9/2/2015 at 2:34 AM, Craig A Rodway said:

Hi Tony! Welcome to the forum

I think one of the fundamental things to consider with projects like these, is to actually look and analyse the existing data - the structure, the format, the quality, and any relationships there may be. It's almost going through the normalisation process but perhaps not as strict.

It would be a good idea to take every column in the existing database to decide how that will be represented in the ProcessWire site. Probably, some of these will be simple translations - Varchars will become Text fields, and Ints will still be Integer fields. But what will be more important is the data in these fields - does it vary with just about every single dam? Is it a value representing Yes/No? Are there only a few values for that column that all dams can potentially use? If so, do the values all use the same spelling of words, or are some mixed case or have weird formatting or spacing issues? When you have a set list of values to choose from, this is where you can decide how to store the data in ProcessWire - Page fields, PageTables, Options field, etc.

If your source data quality is good, your job will be easier. If it's not, there will be some work involved in preparing it so that it is clean to work with.

You may already have a good idea of how your data looks or is structured, but I usually like to do some form of planning on paper or using a spreadsheet to map out the source data to the ProcessWire templates and fields. Doing the important work now will save you hassle later on down the line and will really help you to make the search feature more functional.

As an example:

In ProcessWire, I imagine there would be a template created called 'dam'. You might add the following fields to this template (just for starters):

• title
• description
• location (FieldtypeMapMarker)
• height (integer/float)
• length (integer/float)
• capacity/volume (integer/float)
• country (Page field/Options field?)
• built (date)
• opened (date)

For each dam, a Page would exist somewhere in the page tree, using the 'dam' template.

As you've already seen the Skyscraper profile, you can probably look at that for more ideas on how to structure things though

thanks for the input