Author Topic: Data Import / Export actions?  (Read 15225 times)

Offline Nimitz1061

  • Full Member
  • ***
  • Posts: 190
  • Karma: +21/-0
  • No matter where you go, there you are...
    • View Profile
Data Import / Export actions?
« on: June 26, 2012, 07:48:48 PM »
I see some references in the documentation for Data Import / Export that an XML ACTION tag is required to insert new products via this system, and get the impression that this would be required for CSV and other delimited files as well.  Can someone verify this, and advise as to any column heading requirements ??

David

Offline andrew.mikhailyk

  • Newbie
  • *
  • Posts: 25
  • Karma: +5/-0
    • View Profile
Re: Data Import / Export actions?
« Reply #1 on: June 27, 2012, 09:58:46 AM »
Hi, David.

Unfortunately "action" tag is only available for XML data import at the moment.
We've already added "action" for CSV import but it will be available in next build: v1.0.3.
AbanteCart v1.0.3 will be released in 1-2 days.

As for usage of "action" tag you'll just have to add column named "action" in the first line and set needed actions at the end of each row.
Action values are: "insert", "update", "update_or_insert" or "delete".

Offline Nimitz1061

  • Full Member
  • ***
  • Posts: 190
  • Karma: +21/-0
  • No matter where you go, there you are...
    • View Profile
Re: Data Import / Export actions?
« Reply #2 on: June 29, 2012, 12:43:22 PM »
Thank you Andrew.

So, to recap for clarity:

1.  delimited files do not insert at all in 1.0.2, but will in 1.0.3
2. A single Action column is used for each row
3. Available actions include "insert", "update", "update or insert" and "delete"


Further questions:

A. Are the actions case sensitive?
B. Will the logic recognize references to non-existing resources and insert them as well ?
C. What happens if an index for say, manufacturer or category references an entry which does not exist ??


At present, this tool seems extremely focused on dealing with data from Abantecart itself.  This would not be the most common use scenario, so I'm curious as to what plans are to address external sources which have no knowledge of the Abantecart data structures....

David


Offline Nimitz1061

  • Full Member
  • ***
  • Posts: 190
  • Karma: +21/-0
  • No matter where you go, there you are...
    • View Profile
Re: Data Import / Export actions?
« Reply #3 on: July 07, 2012, 06:59:16 AM »
I have accomplished my first import to the cart - manufacturer entries.

There was a problem.

Manually created entries in this system have two table entries.  One in the manufacturers table, one in the manufacturers_to_stores table.  The import creates no entries in this table even when the columns for that tables entries exist..

The resulting entries do not work in the cart, as they have no store assignments..

David
« Last Edit: July 07, 2012, 07:11:43 AM by Nimitz1061 »

Offline Nimitz1061

  • Full Member
  • ***
  • Posts: 190
  • Karma: +21/-0
  • No matter where you go, there you are...
    • View Profile
Re: Data Import / Export actions?
« Reply #4 on: July 08, 2012, 04:54:06 PM »
I'm now getting rather successful TEST imports on products.  Not sure just how indicative of eventual success this would be, given the lack of any error message about missing store relationship assignments in the manufacturer file.  But, seems promising so far. 

I'm a bit concerned about the number of fields required to handle one image, and the apparent defaulting of image titles and descriptions to be the same as the product name / description.  This MIGHT lead to some duplicate content issues down the road.

The presence of resource name and resource PATH fields raises the question of which should be available during import and what will be necessary to generate the other....

David
 

Offline Nimitz1061

  • Full Member
  • ***
  • Posts: 190
  • Karma: +21/-0
  • No matter where you go, there you are...
    • View Profile
Re: Data Import / Export actions?
« Reply #5 on: July 09, 2012, 08:01:20 AM »
While tests are successful, actual imports are not.

Each product line generates a MySQL error 1054:  unknown field 'name'. 

Apparently the importer fails to separate product elements into the appropriate inserts, and attempts to insert fields destined for the products_description table into the products table.

David

Offline abantecart

  • Administrator
  • Hero Member
  • *****
  • Posts: 3968
  • Karma: +243/-9
    • View Profile
    • Ideal Open Source Ecommerce Solution
Re: Data Import / Export actions?
« Reply #6 on: July 09, 2012, 08:56:33 AM »
Can you please share your import request steps? What do you select for import?
We need your help to build better free open source eCommerce platform for everyone.
See how you can help

Offline Nimitz1061

  • Full Member
  • ***
  • Posts: 190
  • Karma: +21/-0
  • No matter where you go, there you are...
    • View Profile
Re: Data Import / Export actions?
« Reply #7 on: July 09, 2012, 08:23:37 PM »
I posted details to the issue tracker.

Import system does not allow selecting what you are importing..

David

Offline andrew.mikhailyk

  • Newbie
  • *
  • Posts: 25
  • Karma: +5/-0
    • View Profile
Re: Data Import / Export actions?
« Reply #8 on: July 10, 2012, 08:16:43 AM »
Hi, David.

I've tried to replicate bug and all works for me.
The only difference between my import file and your is that your file doesn't enclose fields with double-quotes.
When I remove double-quotes from my file I have similar errors like you.

Can you tell us how do you generate file for import, how do you edit it, what software is used etc.

Thank you.

Offline Nimitz1061

  • Full Member
  • ***
  • Posts: 190
  • Karma: +21/-0
  • No matter where you go, there you are...
    • View Profile
Re: Data Import / Export actions?
« Reply #9 on: July 10, 2012, 12:22:08 PM »
The file used is a modified Easy Populate file generated from a CRE Loaded B2B cart.

It was processed using Excel, saved to text (Tab Delimited) using no changes from the default settings.

David

Offline Nimitz1061

  • Full Member
  • ***
  • Posts: 190
  • Karma: +21/-0
  • No matter where you go, there you are...
    • View Profile
Re: Data Import / Export actions?
« Reply #10 on: July 13, 2012, 09:50:59 AM »
Opened my file with Open Office SCalc, resaved.  This is encapsulated in double quotes.

Result:

Code: [Select]
Data is empty or corrupted.
File is not empty, (still contains 99K of data). 


The original tab delimited file still reports no errors when ran in test mode:

Code: [Select]
Test run completed. Total SQL queries: 360.  Show detailsand
Code: [Select]
Total loaded: 360. Updated: 0. Created: 360. Errors: 0
are the results.

Detail wise the queries reported as being generated for a single product are:

Code: [Select]
INSERT INTO `products` SET `product_id` = '', `model` = 'PNC28708', `weight` = '1', `weight_class_id` = '5', `date_available` = '4/1/2004 0:00', `date_added` = '4/1/2004 13:28', `quantity` = '99999', `price` = '32.95', `manufacturer_id` = '3', `tax_class_id` = '1', `status` = '1'

INSERT INTO `resource_map` SET `object_name` = 'products', `object_id` = 'new_id', `resource_id` = '', `default` = '', `sort_order` = '', `created` = '', `updated` = '', `name` = 'Toilet Tissue Aid', `description` = 'This toilet tissue aid is a simple solution for people who need an extended reach. The spring clamp on this toilet tissue holder easily opens to release tissue paper. This handy toileting aid measures 10" long. The vertical handle and horizontal clamp both measure 6" long. This bathroom aid is ideal for persons who are disabled, obese or are small people(dwarfism). Use with No Rinse Peri-Wash for added personal hygiene.', `meta_description` = 'Toilet tissue aides at Life Solutions Plus. Your source for toilet tissue holders, bathroom aides, toileting aides, toilet aides.', `meta_keywords` = 'toilet tissue aid, toilet tissue holder, bathroom aid, toileting aid, toilet aid, ass wipe, butt wipe'

INSERT INTO `resource_library` SET `resource_id` = 'new_id', `type_id` = '1', `created` = '', `updated` = ''

INSERT INTO `resource_descriptions` SET `resource_id` = 'new_id', `language_id` = '1', `name` = 'Toilet-Tissue-Aid-Paper-S.jpg', `title` = '', `description` = '', `resource_path` = 'Toilet-Tissue-Aid-Paper-S.jpg', `resource_code` = '', `created` = '', `updated` = ''


I have difficulty envisioning this as an encapsulation problem.

Note the resource map table query:

Code: [Select]
INSERT INTO `resource_map` SET `object_name` = 'products', `object_id` = 'new_id', `resource_id` = '', `default` = '', `sort_order` = '', `created` = '', `updated` = '', `name` = 'Toilet Tissue Aid', `description` = 'This toilet tissue aid is a simple solution for people who need an extended reach. The spring clamp on this toilet tissue holder easily opens to release tissue paper. This handy toileting aid measures 10" long. The vertical handle and horizontal clamp both measure 6" long. This bathroom aid is ideal for persons who are disabled, obese or are small people(dwarfism). Use with No Rinse Peri-Wash for added personal hygiene.', `meta_description` = 'Toilet tissue aides at Life Solutions Plus. Your source for toilet tissue holders, bathroom aides, toileting aides, toilet aides.', `meta_keywords` = 'toilet tissue aid, toilet tissue holder, bathroom aid, toileting aid, toilet aid, ass wipe, butt wipe'
This contains data that appears to be consistent with the use for which it is labeled in the file. 

Note also the fields reported, compared to the actual structure of the table involved:

Code: [Select]
--
-- Table structure for table `resource_map`
--

CREATE TABLE IF NOT EXISTS `resource_map` (
  `resource_id` int(11) DEFAULT NULL,
  `object_name` varchar(40) COLLATE utf8_bin NOT NULL,
  `object_id` int(11) NOT NULL,
  `default` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0-no, 1-Yes',
  `sort_order` int(3) NOT NULL DEFAULT '0',
  `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `resource_map_index` (`resource_id`,`object_name`,`object_id`),
  KEY `group_id` (`resource_id`,`object_name`,`object_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

This is clearly a mistake in the name of the table used in the previous query.

Where the actual resource map should be derived from I don't know.  But there are no name, description, meta_description or meta keywords fields in the resource_map table.

So, this should not work for the developer.

My working conclusion at present is that the code distributed and the code being tested by the developer are not the same.

David

Offline Nimitz1061

  • Full Member
  • ***
  • Posts: 190
  • Karma: +21/-0
  • No matter where you go, there you are...
    • View Profile
Re: Data Import / Export actions?
« Reply #11 on: July 14, 2012, 09:44:13 AM »
I have isolated the nature of the problem more specifically.

Double checked the encapsulation.  The only difference in the results was that live imports started to return a somewhat vague error message to the effect that the import file was either empty or corrupted.

I knew the file was not empty, so I went back to file contents.

While the test imports had not indicated any problems with content, the file I'm using DID include additional columns not related to Abantecart database tables and columns.  Removing SOME of these columns resulted in changes in the queries reported to be generated by the test routines, and a (somewhat) successful product import. 

The queries generated now look like:

Code: [Select]
INSERT INTO `products` SET `product_id` = '', `model` = 'NC28949-1', `weight` = '1', `weight_class_id` = '5', `date_available` = '4/1/2004 13:28', `date_added` = '4/1/2004 13:28', `quantity` = '99999', `price` = '0', `manufacturer_id` = '3', `tax_class_id` = '1', `status` = '0'

INSERT INTO `resource_map` SET `object_name` = 'products', `object_id` = 'new_id', `resource_id` = '', `default` = '', `sort_order` = '', `created` = '', `updated` = ''

INSERT INTO `resource_library` SET `resource_id` = 'new_id', `type_id` = '1', `created` = '', `updated` = ''

INSERT INTO `resource_descriptions` SET `resource_id` = 'new_id', `language_id` = '1', `name` = 'NC28949-1.jpg', `title` = '', `description` = '', `resource_path` = 'NC28949-1.jpg', `resource_code` = '', `created` = '', `updated` = ''

INSERT INTO `product_descriptions` SET `product_id` = 'new_id', `language_id` = '1', `name` = 'Guardian Economy Transfer Seat', `description` = 'The Guardian® Economy Transfer Bench is made of molded-plastic and has a nonslip textured surface and drainage hole.  The backrest can be attached on either side. Seat height on this shower transfer bench adjusts from 16 ½" to 21 ½". Thie tub transfer bench fit tubs from 11" to 17" deep. Optional legs with suction cups are availabe on this bath transfer chair to increase stability. Bath transfer bench supports up to 300 lbs.', `meta_description` = 'Economy transfer seats at Life Solutions Plus. Your source for shower transfer benches, bath transfer benches, shower chairs, bath seats.', `meta_keywords` = 'bath transfer bench, shower transfer bench, economy transfer seat, transfer seat, shower chair, bath seat, shower stool, Guardian economy transfer seat, bath tub transfer bench, tub transfer bench, bath chair handicap'

Note the new insert for products_description table, and the absence of attempts to insert data to missing fields in the resource_map table.

The total number of queries generated increased from 360 to 452.   The live import was successful to the extent that product data WAS imported.  Products were not assigned to the default store, or to categories.  This is not unexpected, given that neither was included in the file set. (Note:  I've yet to figure out how to assign the product to a store, though I do have an idea for how category assignment should work which I will test in the next few days.)

What was unexpected was that the resource column assignments which I thought would assign a product image file name to the product failed to do so.

So, some progress is being made.   Strangely, some foreign column headings (and content!) remained in the file.  Content included text and numbers.  The columns deleted contained only file names, including some with sub-directories  in the paths.  This may be a good clue.....


David

Offline andrew.mikhailyk

  • Newbie
  • *
  • Posts: 25
  • Karma: +5/-0
    • View Profile
Re: Data Import / Export actions?
« Reply #12 on: July 16, 2012, 08:36:36 AM »
Results of the test mode and actual import are not the same because in test mode script doesn't execute SQL queries.
But in actual import you see MySQL errors because table that you try to import doesn't have some columns that are present in your import file.

Offline Nimitz1061

  • Full Member
  • ***
  • Posts: 190
  • Karma: +21/-0
  • No matter where you go, there you are...
    • View Profile
Re: Data Import / Export actions?
« Reply #13 on: July 16, 2012, 12:08:07 PM »
Andrew,

I'd call that a reasonable restatement of my report.

Is there no way during the test run to check to see if sufficient data has been input to generate a complete object insertion ??

David




Offline abantecart

  • Administrator
  • Hero Member
  • *****
  • Posts: 3968
  • Karma: +243/-9
    • View Profile
    • Ideal Open Source Ecommerce Solution
Re: Data Import / Export actions?
« Reply #14 on: July 17, 2012, 08:35:26 AM »
Unfortunately, there is no way to do test run of SQL unless transaction is used with no commit. Currently by testing import system does whole operation with creating SQL for whole import and validates if there is any data issue that it can find without performing SQLs.

In the future we can add this feature to the test once we support InnoDB engine. I agree that this will be beneficial

Thanks.
We need your help to build better free open source eCommerce platform for everyone.
See how you can help