[SOLVED] Exporting a Country And importing it

[Aficionado]

Hello.

I need to do some consolidation and merge countries to one multi-country site.

So i have UK on a separate domain, with country/cities custom by me since Osclass geo-files for UK is not of any good.

So the question is how do i Export the whole country and how do i import it to an (already existing and with 20+ countries) osclass site.

Any help is appreciated.

Thanks

[Aficionado]

I have done some digging and i have stuck.

I exported the SQL from my UK Osclass, removed what was not needed and kept the Country/Regions/Cities data.

All OK, they imported 100% right into my dev system.

BUT when i tried to import them to my live multi-country Osclass, i got an error:

#1062 - Duplicate entry '782126' for key 'PRIMARY'

I guess other errors also exist similar to this and the sql stops to the first error.

Apparently some other country is occupying the keys ? If so, what can i do ?

Thanks

[teseo]

Hi,

#1062 - Duplicate entry '782126' for key 'PRIMARY'

Is that from t_region table? Run this query on PhpMyAdmin:

Code: [Select]

SELECT * FROM oc_t_region WHERE pk_i_id = 782126
(Change the prefix if you're using other than the standard "oc_")

You'll see the record of the already existent region from another country.

Now, is this a simple addition to the 3 location tables or your goal is to import ads as well?

Regards

[Aficionado]

The

Code: [Select]

SELECT * FROM oc_t_region WHERE pk_i_id = 782126
results:

Code: [Select]

SELECT *
FROM oc_t_region
WHERE pk_i_id =782126
LIMIT 0 , 30
So i guess the answer is yes, from t_region. And shows a record from an other country. But i guess that is not the only conflict, just the first.

And yes, i wanna just import the Countries - Regions - Cities and NO ads. Nothing. Just to save me hours of typing.


Here is the sql in case you need some more info. Or anybody else.

[teseo]

***CORRECTED***

Well, first you need to change all your regions and cities Id's to make sure those Id's are not already in use by other countries.

The easiest way is to use Id's with a very high number (unlikely to be already used):

For instance:

(740441,'gb','Bedfordshire','bedfordshire',1),
=>
(1740441,'gb','Bedfordshire','bedfordshire',1),

To achieve that you can use this online tool:

http://textmechanic.com/text-tools/basic-text-tools/find-and-replace-text/

1.- Extract oc_t_region and oc_t_city data to two separate files.

Note: You have here a "region" with an ID way too low that might be an issue:

(311,'gb','London - Central','london-central',1),

So the first step is to assign it a free ID in the range of the rest:

(740440,'gb','London - Central','london-central',1),

And then, using a text editor, replace all '311' in oc_t_city with '740440' (single quotes included)


2.- Process oc_t_region:

    2a. Tick "Enable regular expression" and "Multiline matching".
    2b. Paste your oc_t_region data on the 3rd textarea.
    2c. "Find this" textarea => ^\((\d+),
    2d. "Replace with" textarea => (1$1,
    2e. Click on "Find and replace text" button and replace your original code with the results

2.- Process oc_t_city:

    3a. Tick "Enable regular expression" and "Multiline matching".
    3b. Paste your oc_t_city data on the 3rd textarea.
    3c. "Find this" textarea => ^\((\d+),(\d+),
    3d. "Replace with" textarea => ($1,1$2,
    3e. Click on "Find and replace text" button and replace your original code with the results

Import your modified .sql over the original database with all the other countries and everything should be all right.

Regards

[Aficionado]

Regions were imported well.

Cities not at all because of this error:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`osclassdb`.`oc_t_city`, CONSTRAINT `oc_t_city_ibfk_2` FOREIGN KEY (`fk_c_country_code`) REFERENCES `oc_t_country` (`pk_c_code`))

#1452 is:

Code: [Select]

(36135,1742010,'Birmingham','birmingham','gb',1),
and i see no formating error in the above.

[teseo]

Change your modified .sql like this:

1.- At the top:

/* OSCLASS MYSQL Autobackup (September 10, 2016 10:04 am) */
/* Table structure for table `oc_t_locale` */
/* dumping data for table `oc_t_country` */

SET FOREIGN_KEY_CHECKS = 0;

2. At the bottom:

SET FOREIGN_KEY_CHECKS = 1;

Restore the three tables from old database and import modified .sql

[Aficionado]

Set the check to off to which sql ? The Cities ?

I'm doing all this within Phpmyadmin.

[Aficionado]

Restore the three tables from old database and import modified .sql

Restore what 3 tables ? I restores my full sql backup before i started all this. What must i do ?

[teseo]

I restores my full sql backup before i started all this.

That's fine.

Code: [Select]

Set the check to off to which sql ? The Cities ?
I was assuming there's only a .sql for the three tables, just add that to any .sql you want to import.

[Aficionado]

Well, i started from #1 and all went well without the need to turn off foreign keys.

I think when i changed London from 311 to 740440, i didn't replaced also in the rest of the file. So that corrupted somehow the whole procedure.

Seems to work just fine now, i will keep an eye open.

I don't know how to thank you Teseo (once again).

Thanks a million times.

[teseo]

I think when i changed London from 311 to 740440, i didn't replaced also in the rest of the file. So that corrupted somehow the whole procedure.

Something I was no taking into account, you also need to modify cities pertaining to London:

Note: You have here a "region" with an ID way too low that might be an issue:

(311,'gb','London - Central','london-central',1),

So the first step is to assign it a free ID in the range of the rest:

(740440,'gb','London - Central','london-central',1),

And then, using a text editor, replace all '311' in oc_t_city with '740440' (single quotes included)

You're welcome. Please add [SOLVED] to the title of this thread.

Regards

[Aficionado]

I had to do that, because the UK domains now ask for a valid UK address.

So ...