Import relational database from filemaker

[joe_ma]

Hello

I am about to import csv files that have been exportet from a filemaker database, so as the customer can maintain the data through the backend.

Now I have found this topic in the forum and I think, this might help me importing these tables. Unfortunately I am not that skilled with php to be able to tweek Ryan's module to meet my needs. So here's what I have got as tables from the export:

addresses:

addresses of institutions with information about what they do (about 650 data sets)

offers:

different offers like e.g. "translations", "language courses", "soliciting" etc.

target audience:

e.g. coummunities, professional, private persons …

catogories:

e.g. "old age", "work", "school" …

And I have four tables that establish the relations between the tables.

No I think it is fairly easy to import the tables with the basic information. I'd think the structure in pw would look like this:

- addresses

    - address 1

    - address 2

    - address 3

    - …

- categories

    - category 1

    - category 2

    - category 3

    - …

- offers

    - kind of offer 1

    - kind of offer 2

    - kind of offer 3

    - …

- target audience

    - communities

    - professionals

    - private persons

So far so good. In the templates for the addresses I can use page reference fields to select the categories, offers and audience (i.e. the relations as stored in the other tables).

But how can I populate these fields while importing the tables, so as I don't need to do that manually after importing the tables?

Thanks for help

[LostKobrakai]

You would first import all the single pages, without anything dependent on other pages. Each page should include an unique identifier from the old db (id, linenumber, …). Now in a second run you can save all the references, as all pages are now existing and you find them via the "old" unique identifier.

Edit: Ryan's module is solely equipped to do the first step. The second one is probably something you'd need to code yourself.

[joe_ma]

The second one is probably something you'd need to code yourself.

Yes, I thought so much. But that is exactly my problem.

Any clues on how to proceed?

So there'll be an "old_address_id" on the address pages and an "old_cat_id" on the category pages. Plus there is the table that lists the relations between the two.

How do I translate that to pw/php?

[LostKobrakai]

So you've not only a single csv, but multiple? I'd really suggest taking a look in the code of Ryan's module. It's essentially just reading the file line by line and if you code the thing on your own you can decide what you do with the data you're getting: Create a new page, setup a page field, … All of those things can be done with the processwire api like whereever else. 

To start out I'd just create a template for the import and copy paste code over to the template. If it's a one time import there's no need to work with modules and lots of functions. 

[joe_ma]

So you've not only a single csv, but multiple?

Exactly.

But I am still stuck with the relational table and how to get it populate the page field.

So I have the following situation:

Imported files:

addresses.csv –> imported without any problems

categories.csv –> imported without any problems

Template for addresses:

Added PageReferenceField for categories; added field for address id from old table.

cat_adr.csv:

relational table with two columns

- old address id

- old category id –> replaced these with new pw id

Now, how do I proceed to populate the PageReferenceField in address pages with the right pages from categories?

Sorry, I am really absolutely helpless and appreciate any hints.

[LostKobrakai]

I'm just using this importer by myself, but it would need you to use the command line.

<?php

include("index.php");

// First argument (batchnumber) is required
if(empty($argv[1]) || $argv[1] == null) die("Please provide a batch number!" . PHP_EOL);

// Convert batchnumber to int and set batchsize
$start = (int)$argv[1] - 1;
$batchSize = 1000;

// Read file
$csv = file("newsletter.csv", FILE_IGNORE_NEW_LINES);
$length = count($csv);

// Loop over each line
for($i = 0; $i < $batchSize; $i++){
	$key = $start * $batchSize + $i;
        if($key == 0) continue; // If first line are the column headlines
	if($key == $length){
		die("End of file!" . PHP_EOL);
	}
	$data = str_getcsv($csv[$key], ";", '"'); // Change csv delimiter and quotes if needed
	

        // $data is an array of your csv data. e.g. array(1203, 1230) for your cat_adr.csv
        $addr = wire('pages')->get("oldAddId=". (int) $data[1]);
        $cat = wire('pages')->get("id=". (int) $data[2]);

        $addr->of(false);
        $addr->category = $cat;

	try {
		$addr->save();
	} catch (Exception $e) {
		echo "$key: $e" . PHP_EOL;
	}

        // Prevent memory overflow
	wire('pages')->uncacheAll();
}

Pack this in a file import.php and put it in processwire's root and the csv as well. Call it from the command line by using "php import.php 1". You can remove the batch part if you don't need it. You can do whatever you need to between the str_getcsv line and the uncache line. If you don't have access to the command line you could still use the script by adding a file inputfield, where you upload the file to the server and read it from there, but you'd need to keep track of things like timeouts in the browser environment.

[joe_ma]

Thanks a lot, LostKobrakai!

[joe_ma]

Hmm. Not quite there yet …

I modified your code like this:

<?php

include("index.php");

// Read file
$csv = file("angebote.csv", FILE_IGNORE_NEW_LINES); //
$length = count($csv);

// Loop over each line
for($i = 0; $i < 1000; $i++){
	$key = + $i;
        if($key == 0) continue; // If first line are the column headlines
	if($key == $length){
		die("End of file!" . PHP_EOL);
	}
	$data = str_getcsv($csv[$key], "\t", '"'); // Change csv delimiter and quotes if needed
	

        // $data is an array of your csv data. e.g. array(1203, 1230) for your cat_adr.csv
        $addr = wire('pages')->get("adr_id=".  $data[1]);
        $cat = wire('pages')->get("title=".  $data[2]);

        $addr->of(false);
        $addr->adr_angebote = $cat;

	try {
		$addr->save();
	} catch (Exception $e) {
		echo "$key: $e" . PHP_EOL;
	}

        // Prevent memory overflow
	wire('pages')->uncacheAll();
}

I received the "End of file!" message. Apart from that, nothing happened.

I suspect it has to do with $length. With

print $length;

I get "1".

So it seems like there is only 1 line, which is not true.

Or does it mean 1 file? So it doesn't read the file?

"adr_id" is the name of the old address id, which is a five to six digit number with leading zeroes. So it's not an integer, is it? So i skipped the "(int)" from the code.

[LostKobrakai]

So it seems like there is only 1 line, which is not true.

Or does it mean 1 file? So it doesn't read the file?

For such cases please use to the documentation. 

"adr_id" is the name of the old address id, which is a five to six digit number with leading zeroes. So it's not an integer, is it? So i skipped the "(int)" from the code.

If you're comparing that with the value of an textfield, than that's ok. Otherwise the leading 0's should also be gone in your existing pages.

If you don't need batching you can leave all the calculation to the for loop. 

// Loop over each line
for($i = 0; $i < count($csv); $i++){
        if($i == 0) continue; // If first line are the column headlines
	$data = str_getcsv($csv[$i], "\t", '"');

        […]
}

And I'm not sure why your file is not read correctly. I'd look into the used charset (UTF-8 at best) and if the user running the php command has rights to read the file.

[joe_ma]

And I'm not sure why your file is not read correctly. I'd look into the used charset (UTF-8 at best) and if the user running the php command has rights to read the file.

Charset is UTF-8.

I am working on a local xampp installation. I call the script via URL: localhost/website/import.php (this workes for other scritps like e.g. setting languages to active). And the csv file is in the root and readable.

Still doesn't work …

The file doesn't seem to be read.

	print $data[1];

after the str_getcsv command prints nothing.

[LostKobrakai]

Maybe try this before the script:

if (!file_exists("angebote.csv")) {
   die("Can't find the file!")
}

If that does trigger you may need to use the full path to the file: wire('config')->paths->root . "angebote.csv"

[joe_ma]

That doesn't trigger.

[LostKobrakai]

What does $csv[$i] print out in the the loop? It's really strange you're not getting any errors. If it doesn't print out a thing what does var_dump(file_get_contents("angebote.csv")) return?

[joe_ma]

Sorry, been out of office …

What does $csv[$i] print out in the the loop?

It prints out the whole file. The var_dump command dito, with «string(17236)» at the beginning.

Edit:

Like this:

for($i = 0; $i < count($csv); $i++){
		print $csv[$i];

        if($i == 0) continue; // If first line are the column headlines
	$data = str_getcsv($csv[$i], "\t", '"'); // Change csv delimiter and quotes if needed

it prints out the whole file.

Like this:

for($i = 0; $i < count($csv); $i++){
        if($i == 0) continue; // If first line are the column headlines
	$data = str_getcsv($csv[$i], "\t", '"'); // Change csv delimiter and quotes if needed

	print $csv[$i];

It doesn't.

[LostKobrakai]

It seems php doesn't recognize the line-endings of the file. You can either manually explode the lines by using this before the loop:

$csv = explode($lineending, $csv);

Or you make sure the line-endings are in a format that php does understand. Either way you need to take a look at the line-endings which are present in the file.

[joe_ma]

With notepad++ I saw, that the line endings were "\r". I changed these to "\r\n". Now it reads the lines correctly, print $csv[$i] in the loop shows 1 line.

But I now get this error message:

Error: Call to a member function numChildren() on null (line 976 of F:\xampp-neu\htdocs\pw-test\wire\core\Pages.php)

[LostKobrakai]

The error suggests that somehow the page you're saving doesn't have a name nor does it have a parent, so please add this, so you can be sure you got the correct pages. 

// $data is an array of your csv data. e.g. array(1203, 1230) for your cat_adr.csv
$addr = wire('pages')->get("adr_id=".  $data[1]);
$cat = wire('pages')->get("title=".  $data[2]);

if(!$addr->id || !$cat->id){
  echo "$key: Could find either \$addr or \$cat" . PHP_EOL;
  continue;
}

[joe_ma]

OK, I finally got it.

Thank you very much for your good advice and endless patience.