Preserving record ID's when importing via XML

[TallyGuy]

I'm attempting to utilizing the Dataport (v7.0.34) Utility to import records into my store while preserving the ID's that are included in the XML file. I understand the XML structure and have performed a small test to import a new vendor. The vendor is created successfully. However, the VendorId that is provided for the Vendor is not being preserved when the record is created. Instead, the VendorId is incremented to the next available number as it would be if the vendor were to be created through the (web-based) admin interface. Could anyone please provide me with some insight as to why the ID isn't being preserved?

Thanks in advance

[mazhar]

What is vendorid in XML file which you want to preserve, it should be greater then 0.

[TallyGuy]

My VendorId value is greater than zero (0). I've included the relevant section of the xml below:

<Vendors>
<Vendor>
<VendorId>999</VendorId>
<StoreId>1</StoreId>
<Name>_Import05_Vendor</Name>
<Email />
<VendorGroups />
</Vendor>
</Vendors>

Thanks

[Naveed]

When importing new objects to AC7 via xml the Id's will not be preserved, and always be assigned a next available Id. Let me explain how it will work:

When importing vendors the import routine will try to find out an existing vendor with same name. If an existing vendor found, it will update that otherwise will create a new vendor and a new available Id is automatically assigned.

Can you please explain why you want to preserve vendor Id's?

If it is to use while importing products, then you can simply use the vendor name in "Vendor" xml node in products xml/csv data. This will work the same and try to find out an existing vendor with same name and will automatically attach that existing vendor to product.

[TallyGuy]

It isn't that I want to preserve vendor id's specifically as much as dicate the id process from the client side in general as it pertains to vendors/manufacturers, categories and products (and their related objects, such as variants, etc.). We wish to generate an XML document based on data extracted from an Access data source and upload it via DataPort. Records will also need to be updated/modified on a regular basis (inventory updates, new sizes/colors, etc.). We didn't want to have to retrieve information for an XML document and 'backfill' the id's in our data source for future provision (in a later XML document). Is the auto generate approach that you provided for vendors the same for all records (i.e. the object name is used as the unique identifier)?

Your assistance is appreciated.

[Naveed]

Is the auto generate approach that you provided for vendors the same for all records (i.e. the object name is used as the unique identifier)?

No, object name is not always considered as unique identifier. It is only for some specific objects like vendors, manufacturers, tax codes etc.

It is to to facilitate the import process that we provide two way association. Like:

<Product>
<VendorId>2</VendorId>
<Vendor>vendor name</Vendor>
</Product>

You can specify either Id or name and the product will associated to the respective vendor in either way.

[Naveed]

Another important thing to note:

When you trying to import new records always use Id value as zero like:

<ProductId>0</ProductId>

This is the standard way of importing new objects via xml( also for csv). Otherwise if any existing record with same Id is found will be overwritten.

Like if you trying to import a new product but you specify the Id value as 5.
<ProductId>5</ProductId>
and a product with Id 5 already exists then that product will be overwritten. The import routine will in such case think that you are trying to update the existing object.

[TallyGuy]

With that being the case, what will occur when the an update is sent that modifies something like the variant information for an existing product? If the Product.Name is referenced (making the link possible although the ProductId will be 0), but the ProductVariantId's are provided as zero (0), will DataPort properly update the existing variant or create a new (duplicate) variant? I ask this because, unlike options and choices which have a name element, variants do not.

Thanks

[Naveed]

Variants are always nested inside respective product, you can not import variants without the respective product. Here is the xml structure:

<Product>
<ProductOptions>
</ProductOptions>
</Product>

So, when you will export or import products the respective variants will always be imported to correct products.

I suggest to manually create some sample data at AC7 store and then export the data as xml using DataPort. This way you can analyze the exported data for these kind of details.

[TallyGuy]

Variants are always nested inside respective product, you can not import variants without the respective product. Here is the xml structure:

<Product>
<ProductOptions>
</ProductOptions>
</Product>

So, when you will export or import products the respective variants will always be imported to correct products.

I suggest to manually create some sample data at AC7 store and then export the data as xml using DataPort. This way you can analyze the exported data for these kind of details.

Naveed,
My apologies if I wasn't clear, but I didn't expect to provide the options/variants without the product information. I already have an understanding of the XML structure. My questions concern the updating of existing information (such as stock levels for variants). If I were only ever adding records, I honestly wouldn't care. If we could have controlled (assign) the record ID's (understanding that we were responsible for their integrity), we would know without question that those same records (products, options, variants, etc.) could be updated utilizing the record id's that we maintained. As it is, unless we subsequently export and process an xml document from the store after importing an xml document that added the new records, we would not know what the assigned records id's were. In an attempt to prevent that additional step, I wanted to verify that providing updated variant information for an existing product would not result in a duplicate record.

I can certainly perform this test myself (as I analyzed the xml structure myself), but was hoping that the 'details' for this aspect of DataPort would be documented/known, saving me the time.

Thanks again.

[bemara579]

I have been riddled with the exact situation in this thread. I understand the XML structure and am grateful that XML makes it possible to import just about ANY object/entity.

What I cannot understand is the logic for ignoring the ID's and depending solely on the name. There are many products that have generic names like "Gold Men's Watch" or "Microsoft Wireless Keyboard". There is never a primary key on the name in databases.

Tallyguy, I faced the same issues with uploading options and it WILL duplicate the product options even if the name of the product option is the same. This is a huge problem. I even tried the latest DataPort that was created 3/10 for v7.0.2. Here is a sample that I used (just upload it twice, changing the product id to the relative one in the second upload):

Code: Select all

<?xml version="1.0" encoding="utf-8"?>
<AbleCommerceExport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ExportVersion>7.0</ExportVersion>
  <SourceVersion>7.0</SourceVersion>
  <SourcePlatform>ASPX</SourcePlatform>
  <SourceBuildNumber>1.0</SourceBuildNumber>
  <ExportPlatform>ASPX</ExportPlatform>
  <ExportBuildNumber>1.0</ExportBuildNumber>
  <Store>
    <StoreId>1</StoreId>
    <Products>
      <Product>
        <ProductId>10</ProductId>
        <Name>Mobile Bench Table</Name>
        <Price>879.99</Price>
        <CostOfGoods>0</CostOfGoods>
        <MSRP>1361.90</MSRP>
        <Weight>265.00</Weight>
        <Length>34.00</Length>
        <Width>56.00</Width>
        <Height>69.00</Height>
        <Sku>MBT10</Sku>
        <ThumbnailUrl>~/Assets/ProductImages/MBT_t.jpg</ThumbnailUrl>
        <ThumbnailAltText>Mobile Bench Table</ThumbnailAltText>
        <ImageUrl>~/Assets/ProductImages/MBT.jpg</ImageUrl>
        <ImageAltText>Mobile Bench Table</ImageAltText>
        <Summary>Many tables summary...</Summary>
        <Description>Some description..</Description>
        <IconUrl>~/Assets/ProductImages/MBT_i.jpg</IconUrl>
        <IconAltText>Mobile Bench Table</IconAltText>
        <ExtendedDescription>Save even more on quantities of 6 or more.</ExtendedDescription>
        <Assets />
        <CustomFields />
        <Images />
        <ProductOptions>
          <ProductOption>
            <ProductId>10</ProductId>
            <OptionId>0</OptionId>
            <Option>
              <OptionId>0</OptionId>
              <Name>Edge Band Color</Name>
              <ShowThumbnails>true</ShowThumbnails>
              <ThumbnailColumns>5</ThumbnailColumns>
              <Choices>
                <Choice>
                  <OptionChoiceId>0</OptionChoiceId>
                  <OptionId>0</OptionId>
                  <Name>Black Dyna-Rock</Name>
                  <ThumbnailUrl>~/Assets/ProductImages/Options/OPT647-15-15.jpg</ThumbnailUrl>
                  <ImageUrl>~/Assets/ProductImages/Options/OPT647jpg</ImageUrl>
                  <PriceModifier>98.00</PriceModifier>
                </Choice>
                <Choice>
                  <OptionChoiceId>0</OptionChoiceId>
                  <OptionId>0</OptionId>
                  <Name>Black</Name>
                  <ThumbnailUrl>~/Assets/ProductImages/Options/OPT648-15-15.jpg</ThumbnailUrl>
                  <ImageUrl>~/Assets/ProductImages/Options/OPT648.jpg</ImageUrl>
                  <PriceModifier>0</PriceModifier>
                </Choice>
                <Choice>
                  <OptionChoiceId>0</OptionChoiceId>
                  <OptionId>0</OptionId>
                  <Name>Brown</Name>
                  <ThumbnailUrl>~/Assets/ProductImages/Options/OPT649-15-15.jpg</ThumbnailUrl>
                  <ImageUrl>~/Assets/ProductImages/Options/OPT649.jpg</ImageUrl>
                  <PriceModifier>0</PriceModifier>
                </Choice>
                <Choice>
                  <OptionChoiceId>0</OptionChoiceId>
                  <OptionId>0</OptionId>
                  <Name>Grey</Name>
                  <ThumbnailUrl>~/Assets/ProductImages/Options/OPT650-15-15.jpg</ThumbnailUrl>
                  <ImageUrl>~/Assets/ProductImages/Options/OPT650.jpg</ImageUrl>
                  <PriceModifier>0</PriceModifier>
                </Choice>
                <Choice>
                  <OptionChoiceId>0</OptionChoiceId>
                  <OptionId>0</OptionId>
                  <Name>Putty</Name>
                  <ThumbnailUrl>~/Assets/ProductImages/Options/OPT651-15-15.jpg</ThumbnailUrl>
                  <ImageUrl>~/Assets/ProductImages/Options/OPT651.jpg</ImageUrl>
                  <PriceModifier>0</PriceModifier>
                </Choice>
              </Choices>
            </Option>
          </ProductOption>
          <ProductOption>
            <ProductId>10</ProductId>
            <OptionId>0</OptionId>
            <Option>
              <OptionId>0</OptionId>
              <Name>Plywood Core Top</Name>
              <ShowThumbnails>true</ShowThumbnails>
              <ThumbnailColumns>1</ThumbnailColumns>
              <Choices>
                <Choice>
                  <OptionChoiceId>0</OptionChoiceId>
                  <OptionId>0</OptionId>
                  <Name>Yes</Name>
                  <ThumbnailUrl>~/Assets/ProductImages/Options/OPT689-23-23.jpg</ThumbnailUrl>
                  <ImageUrl>~/Assets/ProductImages/Options/OPT689.jpg</ImageUrl>
                  <PriceModifier>48.00</PriceModifier>
                </Choice>
                <Choice>
                  <OptionChoiceId>0</OptionChoiceId>
                  <OptionId>0</OptionId>
                  <Name>No</Name>
                  <ThumbnailUrl />
                  <ImageUrl />
                  <PriceModifier>0</PriceModifier>
                </Choice>
              </Choices>
            </Option>
          </ProductOption>
        </ProductOptions>
        <TemplateFields />
        <Variants />
        <Categories>
          <Category>
            <CategoryId>1</CategoryId>
            <Path>Test</Path>
            <OrderBy>-1</OrderBy>
          </Category>
        </Categories>
      </Product>
    </Products>
  </Store>
</AbleCommerceExport>

Regarding defining ID's for new products, if the problem is increasing the next available ID properly, I think this can be solved in one of two ways:
1.) Allow us to define the ID's for new products, but the DataPort should only allow ID's that are less than or equal to next available ID in AbleCommerce. This way we are forced to assign ID's that are sequential order. If you try to create an ID that is 100 numbers ahead of the next available ID, it should fail. This way there is never a gap in the ID's.
2.) Let us use the sku property as an alternate key. So we can upload AND associate by sku. This I think is better and will solve more migration snags than the ones in this thread.

Thanks for any help.

[Naveed]

What I cannot understand is the logic for ignoring the ID's and depending solely on the name. There are many products that have generic names like "Gold Men's Watch" or "Microsoft Wireless Keyboard". There is never a primary key on the name in databases.

In a previous post I already described that we are not depending upon the names. However only for some certain objects like Vendors, Manufacturers and TaxCodes, "Name" field is considered as an identifier. This is just to facilitate the products import and this way you can easily associate products to Vendors, Manufacturers and TaxCodes etc.

However for Products and other major objects respective ID's are the key fields and identifiers.

Tallyguy, I faced the same issues with uploading options and it WILL duplicate the product options even if the name of the product option is the same. This is a huge problem. I even tried the latest DataPort that was created 3/10 for v7.0.2. Here is a sample that I used (just upload it twice, changing the product id to the relative one in the second upload):

As I stated above, "Name" field for some specific objects, for all others it is the respective ID field. If you will specify the ID field value as zero, the import routine will always think that you are trying to import a new object and will create a new one.

[bemara579]

Naveed, thanks for the reply, but you misunderstood me.

What I meant was that you are solely depending on the name when creating objects. But if this is coming from a completely different database/system and the objects already have ID's for it, then uploading (to create) will ignore the ID's.

The sad part is the ID's from a "legacy" system use that same identifier to associate it with every other object in the system - which is why we would love for DataPort to preserve the ID's that we import for new objects.

If the primary key in the legacy system was the name, then it wouldn't be an issue.

[ZLA]

I don't know if this thread is too old or not but I'm using the CSV import and the same problem occurs! [This is in the latest Dataport: 7.0.2.1] I imported the following records twice:

7,1,0,Shop By Family,,,,Shop By Family,,,,0,Do not delete
8,1,0,Shop By Price,,,,Shop By Price,,,,0,Do not delete
9,1,0,Shop By Other,,,,Shop By Other,,,,0,Do not delete

I selected the first option for IMPORT new objects UPDATE existing objects.

I ended up with 6 categories! I'll report this as a bug in a separate post.