Import data from one shop to another

[ozstar]

Hi,

 

I have exported the full database using sql and excel methods from a shop.

 

I have another shop of the same version which has sample data in it.

 

I would like to empty that data and then import the data from the previous shop.

 

This is what I ahve done thus far..

 

I have used the emptytables.sql mod to empty the data from the new db. The shop now shows no products or categories so I guess that worked.

 

Tried to import the new db via both sql and csv.

 

This is the error importing the sql

 

 Error

SQL query:

--
-- Dumping data for table `address_format`
--
INSERT INTO `address_format` ( `address_format_id` , `address_format` , `address_summary` )
VALUES ( 1, '$firstname $lastname$cr$streets$cr$city, $postcode$cr$statecomma$country', '$city / $country' ) , ( 2, '$firstname $lastname$cr$streets$cr$city, $state $postcode$cr$country', '$city, $state / $country' ) , ( 3, '$firstname $lastname$cr$streets$cr$city$cr$postcode - $statecomma$country', '$state / $country' ) , ( 4, '$firstname $lastname$cr$streets$cr$city ($postcode)$cr$country', '$postcode / $country' ) , ( 5, '$firstname $lastname$cr$streets$cr$postcode $city$cr$country', '$city / $country' ) ;

MySQL said: Documentation
#1062 - Duplicate entry '1' for key 1 

 

This is the message I get when importing the csv..

 

Import has been successfully finished, 2 queries executed.

The following structures have either been created or altered. Here you can:

   * View a structure`s contents by clicking on its name
   * Change any of its settings by clicking the corresponding "Options" link
   * Edit its structure by following the "Structure" link

   * justwine_2011 (Options)
         o TABLE 51 (Structure) (Options)

 

I have tried this a few times however it keeps adding an entry TABLE 50 or 51 and not importing into the correct fields.

 

What am I doing wrong?

 

Thanks

 

 

oz

[ozstar]

I have compared the addressbook table in both dbs via phpmyAdmin and they both look the same.. 29 entries and both start at ID 4.

 

Yet when I look in Admin in the new shop, It says 0 customers though in the old shop, it shows the 29 of them.

 

Most other tables are 0 except configuration, countries,zones etc... no product data etc.

 

Really need help here..

 

oz

[ozstar]

I have also just tried to import a 2003 Excel file into the new db with this message..

 

Error

SQL query:

INSERT INTO `justmine_2011`.`address_book` (`A`, `B`, `C`, `D`, `E`, `F`, `G`, `H`, `I`, `J`, `K`, `L`, `M`) VALUES (4, 4, 'm', NULL, 'Shaun', 'Chong', 'Street Address', NULL, 3000, 'Melbourne', NULL, 13, 184), (5, 5, 'm', NULL, 'Anthony', 'Izzillo', '8 Browne St', NULL, 2490, 'Lilybox', NULL, 13, 182), (3, 3, 'm', 'Starlite International Pty', 'David', 'Nor', 'PO Box 834', 'Nashville', 2678, 'Nashville', NULL, 13, 182), (6, 6, 'm', NULL, 'serge', 'sergeev', 'Rose blv. 34d', NULL, 2334, 'Newcomb', NULL, 13, 182), (7, 7, 'm', NULL, 'benton', 'duthie', '544 sweet st', 'Kenthill', 2076, 'sydney', NULL, 13, 182), (8, 8, 'm', NULL, 'john', 'hawkings', '126a Carl cres.', 'Kogs Bay', 2279, 'Sydney', NULL, 13, 182), (9, 9, 'm', NULL, 'Manfred', 'Honka', 'Raffineriestr. 64', NULL, 29323, 'Wietze', NULL, 81, 79), (10, 10, 'f', NULL, 'Suzanne', 'Thomas', 'Apt 679 Macquarie St', 'North Ryde', 2114, 'Sydney', NULL, 13, 182), (11, 11, 'm', NULL, 'Jon', 'Aufder-Heide', '22 Hawthorn Ave',[...]

MySQL said: Documentation
#1054 - Unknown column 'A' in 'field list'

[Xpajun]

David, let's start with the 1062 of your first post, the reason you are getting this is because although your table is empty the ID is still being registered as being used

 

Try this at the beginning of each table:

 

 

ALTER TABLE address_format AUTO_INCREMENT=0 

 

or as a group at the beginning of your query:

 

ALTER TABLE address_format AUTO_INCREMENT=0 
ALTER TABLE customers AUTO_INCREMENT=0 
ALTER TABLE customer_basket AUTO_INCREMENT=0 
ALTER TABLE orders AUTO_INCREMENT=0 
ALTER TABLE do_this_for_all_the_tables_etc AUTO_INCREMENT=0 

 

The reason for your 1054 (last post) is simply because you have asked your database to import into fields that do not exist: `justmine_2011`.`address_book` (`A`, `B`, `C`, `D`, `E`, `F`, `G`, `H`, `I`, `J`, `K`, `L`, `M`) - they should be `address_book` (`address_book_id`, `customers_id`, `entry_gender`, `entry_company`, `entry_firstname`, `entry_lastname`, `entry_street_address`, `entry_suburb`, `entry_postcode`, `entry_city`, `entry_state`, `entry_country_id`, `entry_zone_id`)

[ozstar]

Thank you Xpajun,

 

I will look at that and try and try again.

 

Must admit is has been confusing trying so many things, they all melt into one mess :-)

 

Many thanks,

 

 

David