Difference between revisions of "DataPort Utility"

Revision as of 07:01, 24 October 2008

Installing DataPort

Download the latest version of DataPort. You can find the latest version at ftp://ftp.ablecommerce.com/evals/DataPort_1.0.0.19.zip. After downloading unzip/extract the files. There will be two files:

• The DataPort Installer (DataPort.msi)
• The ClientApi dll file (CommerceBuilder.DataClient.Api.dll)

Run the installer to get DataPort installed on your system. The ClientApi DLL file should be deployed to /Bin folder of your AC7 store website. You can replace the existing file if it exists.

Connecting DataPort to AC7 Store(s)

To connect the DataPort to your store Run the DataPort and a “Store Administrator Login” dialog screen will be displayed. Click the “New Store” button to create a new connection setting. Now fill in the information as follows:

• In the store name field enter any user friendly name for your store.
• In the ”Service URL” field type the ClientAPI service url running on your website. The service Url may be something like: http://www.mywebsite.com/ClientApi/ClientApiService.ashx
• Enter the user name and password for your store admin.

Click the “Connect” button to connect the DataPort to your store. A progress bar will be shown and after a successfully connection to store the login dialog will disappear. Now you can perform the various tasks related to data import and export.

If you feel any problems while connecting to store, please visit the forums for frequently asked questions and their answers. But before doing this first verify that your provided information in the login dialog is correct.

In case of multiple stores you can define settings for each store and this way you can switch stores easily.

Downloading (Exporting) Data With DataPort

You can download (export) data from your store in CSV and XML formats. You can even download your complete store data in xml format

Downloading in CSV format

You can download your store data for Categories, Products, Orders and Users in CSV format. You can filter out and customize the data you want to export. Here is how you can export your data in CSV format:

1. Select the link under “Customized Download” section for which you want to download your data. For Users, Products, and Orders the first screen is the customization options to filter out the data you want to export. Choose the options appropriate as either you want to download all data or selected data. The categories download have no such customization options available and each time all categories data will be downloaded (exported). Click “Next” after deciding which data you are downloading…
2. On the 2 nd screen for products, user and orders (first screen for categories download) you can select the export format. For Categories, Products and Users the options are CSV and XML. While for Orders export the options include “XML”, “CSV”, “UPS World Ship [only the orders shipped by UPS]”, “Quick Books 2002” and “Quick Books 2003 and up”. Choose the export format as you desire and select the output file and path where the exported data will be saved. After selection the export format and output file click next to next step.
3. If you have selected export format as CSV then the next step is to “Select or customize the export (download) template”. This step is a little bit tricky and complex and while using appropriate options you can end with a variety of results. You can select here the options regarding CSV export including i. what “Text Delimiter” and “Text Qualifier” will be used. Here you can map your data to your custom CSV headers and create Templates for your customization. For more details regarding the custom mapping and using templates see the next section “Custom Mapping and Templates”.

Custom Mapping and Templates

When exporting data to CSV format or importing from CSV format you can map your CSV data headers to respective AbleCommerce object fields. This feature is specially useful when importing/exporting data for third party applications or from some other sources. You can also export data and then edit and re-import back and thus can batch edit your data. I explain this by the following example:

Lets suppose we want to export the CSV data for categories in following format for some custom feed etc.

—————————-

‘Category ID’, ‘Category Name’,’Category Parent Id’,’Summary’,’Details’,’Search Keywords’,’Visible’

‘1’,’Computers and Electronics’,’0’,’’,’Products related to computers and electronics’,’Computers and Electronics’,’1’

‘2’,’Modems’,’Different Modems’,’0’,’Best modems at cheap prices’,’Modems’,’0’

—————————-

The first line contains the CSV header fields while the rest contains the data. As we can see that the comma (,) is used as text delimiter and single quote (‘) character is used as text qualifier. Now click the "Categories" link under the "Customized Download" section. This will open Customized Categories Download screen. Select export format as CSV and choose the output destination file. Click next to go to next step for "Select and customize the export (download) template". Now on this screen select the "Manual Mapping" option. This will make the template editable. Change the "Text Qualifier" from double quote (") to single quote (‘). Now we just want to download information for only 6 fields i.e. Name, ParentId, Summary, Description, HtmlHead, and VisibilityId. and also we need this information under different CSV headers. i.e The Name field header should be output as ‘Category Name’, ParentId header should be like ‘Category Parent Id’ and so on…

There are a number of columns/fields in the template for which the information is not required. For example the and StoreId, ThumbnailUrl and some other fields are not required to be exported. The template grid is used to map the AbleCommerce fields to the columns in your CSV export sheet. To omit that un-necessary information from output double click the "StoreId" field under the "Your Export Sheet" column. This will select the text, press delete to remove and clear the text against the AbleCommerce Field "StoreId". Same way clear text for all of the un-necessary fields like ThumbnailUrl, ThumbnailAltText, DisplayPage etc. Data will not be exported for the AbleCommerce fields which will be un-mapped.

Now the next issue is that we need the "CategoryId" field exported as "Category ID", "Name" field exported "Category Name" and so on. We can change the CSV headers in same way above. To change "CategoryId" to "Category ID" double click the cell in "Your Export Sheet" against the AbleCommerce field "CategoryId" in templaate grid. The cell text will be selected, now type "Category ID" and replace the old text "CategoryId". Same way change the Your Export Sheet columns texts from "Name" to "Category Name", From "ParentId" to "Category Parent Id", From "Description" to "Details" and so on. The CSV headers will be exported as given in the Template Mapping grid.

Another important point is that the CSV columns will be exported in the order shown in template grid, i.e. the columns which appears first in the template grid will be exported first in the output file. According to our scenario the "Category Name" should be exported in 2nd column i.e. prior to the "Category Parent Id", while in the template grid it is currently shown after the "Category Parent Id". We can use the UP and DOWN arrows to move the columns up and down. To do this select the row for Parent Id field in template grid and click the DOWN arrow. This will move the ParentId column down and the Name column is shifted above.

All the customization is done. You can safely click the "Next" button to next screen and download your data in the required format. But it is better to save your customization for CSV template so that it can be used in future. To do this check the checkbox for "Save your mapping as a new template" option. This will enable file selection and saving options. Select the output file for template and click "Save" button. This will save all your customized mapping to an xml file. Next time whenever you need the same mapping use the "Use Custom Template" option instead "Use Manual Mapping" and your saved template will be loaded for you.

The same process of CSV export customization or saved Template can be used for CSV data import.

Downloading in XML format

You can download complete AC7 store data in xml format or can download data for any of store objects in xml format. You can also customize and filter the data for Products, Orders, Users and Categories while exporting (downloading). To export complete store in XML format click "Store Download AC7" under "Store Data" section. Select the "Complete Store" option and choose output file. Click "Download" link to start download. To download data for some particular objects choose "Selected Data" option instead and only check the checkboxes for objects you want to export (download).

To download customized data for Products, Users, Orders or Categories click the respective link under "Customized Download" section. For Users, Products, and Orders the first screen is the customization options to filter out the data you want to export. Choose the options appropriate as either you want to download all data or selected data. The categories download have no such customization options available and each time all categories data will be downloaded (exported). Click “Next” after deciding which data you are downloading…

On the 2 nd screen for products, user and orders (first screen for categories download) you can select the export format. For Categories, Products and Users the options are CSV and XML. While for Orders export the options include “XML”, “CSV”, “UPS World Ship [only the orders shipped by UPS]”, “Quick Books 2002” and “Quick Books 2003 and up”. Choose the XML export format and select the output file and path where the exported data will be saved. After selection the export format and output file click next to next step. Review the changes you have made on the next screen and click the "Download" button to start download. Upon successful completion the data will be saved in the selected output file

Uploading (Importing) Data With DataPort

You can upload your data to your online store in a variety of ways. For example you can upload your AC5x store data to your new AC7 store. You can also import (upload) your data from one AC7 store to another AC7 store. The data to be uploaded should be in specified compatible CSV or XML format.

Uploading (Importing) AC5x data

It is assumed that you have already exported your data from your AC5x store and saved on your local hard drive. Click option "Store Upload AC5.x" in left navigation menu which can be located under "Store Data" section. This will open AC5.x Data Upload screen. Browse and locate your AC5.x data file. Choose appropriate "Preserve Id Option". Preserve Id Options are available for Products, Categories, WebPages and Affiliates only. There are 3 "Preserve Id Options" available:

1. Delete the existing object and save the imported AC5.x object with the original Id, you can select this option when you are setting up a new AC7 store based upon the existing AC5.x store data. So, any temporary objects or sample data in your existing store will be overwritten. If you have an AC5.x well established store and want to upgrade it to AC7 then this option is recommended for you. Preserving the Id’s for Products, Categories, Webpage and Affiliates will ensure the same URL’s for your product, category display pages and same affiliate URL’s as was in AC5.x store.
2. The 2nd option is to "Save the new AC5.x imported object with new Id". This option is recommended when you have already configured some data specially categories, products and affiliates in your AC7 store and you do not want to loose that data and you can compromise to loose the AC5x objects ranking and old URL’s. This option will however ensure that the existing AC7 data and the newly imported AC5x data will not overwrite each other.
3. Last option is "Do not import AC5.x object, just log a warning message" will not import the AC5.x objects (Products, Categories, WebPages and Affiliates) when an object with the same Id already exists in AC7 store. It will however log a detailed warning message that a conflict is there and you can tackle it manually.

While Importing AC5x data you can select to import complete store data to be imported (recommended) as well you can choose specific objects to be imported only.

Using DataPort it is now possible to upload very large data files to your new AC7 stores. The data is imported in small chunks and is sent to server in compressed format to increase the performance. As there were no integrity constraints in AC5x database, so, the data may be in inconsistent state. AC5.x import routine is implemented in such a way to import as much data as possible. But as there are strong integrity constraints enforced in AC7 database so, inconsistent data may fail to import. When some data will fail to import a detailed error message will be logged. You can use the final import log to figure out the reason why the data failed to import and also can tackle it manually.

For very large data file the import log can also be very large and it becomes difficult to traverse the log and find out the errors. To handle this complexity the warning messages and error messages are logged separately at the end of normal log. But still it is difficult to map the error or warning message to a very large data file and find out the exact reason and the correct solution.

Fortunately the latest version of DataPort has some very nice extra debug options available for such situations. When extra debug option enabled the DataPort will save data for each chunk and the respective import log in a log file at the specified location. This will make it very easy to track the errors and warnings and map those to the data which causing these errors and warnings because all this information is available in a small single file. The log files are saved with sequential numbering and friendly names. When the import completed a complete log file is shown in a save dialog. It is recommended that you save the log file for future. If the extra debug option is enabled then the complete log file is also saved with other smaller chunk log files.

Uploading (Importing) AC7 XML data

You can upload (import) your complete store data if it is in AC7 standard xml format. While uploading you can even select which objects to be imported or which should not be imported. To import (upload) your AC7 XML data click the link "Store Upload AC7" which can be located under "Store Data" section in left navigation menu. On the store upload screen select the appropriate import option. There are 3 available options:

1. IMPORT new objects, UPDATE existing objects

When selected this option the data for new objects will be imported as well the existing objects will be updated if any of those found in the uploaded xml. For example you are importing 5 products data and 2 of these products already exists in your AC7 store then the result will be "2 products updated, 3 products imported".

1. Only IMPORT new objects (SKIP UPDATING existing objects)

In the above case when importing 5 products and 2 of those already exists there at your AC7 store the result will be "3 products imported" and a warning log will be shown for the rest of 2 products that these already exists and are skipped.

1. Only UPDATE existing objects (SKIP IMPORTING new objects)

In the above case when importing 5 products and 2 of those already exists there at your AC7 store the result will be "2 products updated".

You can import complete store or you can choose which objects need to be imported. But it is recommended that you import complete store data in one go until you are an experienced user and are completely aware of the dependencies between the objects. Form dependency we mean that certain AC7 objects may be dependent on other AC7 objects like Orders data is dependent upon the Products and Users data while each Products and Users data is dependent upon certain other objects.

After making your selection for the import option and data selection click the upload button to start upload process. When the import process completes a complete import log will be shown. The import log shown for AC7 objects will be in hierarchal order according to dependency of objects on each other. The AC7 import also completes in multiple chunks and so, it is possible to upload very large XML files. It is always recommended that you save your import logs for future.

Uploading (Importing) AC7 CSV data

This feature is available to import Categories, Products, Users and Orders data to AC7 store in CSV format. Preparing and editing data in CSV is easy to do and understand. To import CSV data to your AC7 store you first need to prepare your data in CSV format compatible to DataPort. For more information about creating CSV data files for import see our section "Creating CSV files for data import". After preparing the CSV file click the respective link under "CSV Upload" section in left navigation menu. On the CSV upload screen browse and locate your CSV data file.

When you locate your CSV file in DataPort the data validation is performed and an error dialog may appear if the CSV data does not match the expected format. There might be 2 types for errors when validating the CSV data:

1. "Following columns in your provided CSV file does not match while using the current template" will list the CSV column names in CSV header that are not compatible against the currently selected CSV template. To fix this problem you can either use manual mapping or can choose an appropriate custom template file. NOTE: DATA FOR UNMAPPED CSV COLUMNS WILL NOT BE IMPORTED. When the CSV data validation is performed it is tried to automatically map the column names in CSV header to the AbleCommerce fields, but if the column names in CSV differ from the AbleCommerce fields or there are some extra columns in CSV header then this error message will be shown.

1. The 2nd type of error message that you can encounter while validation is performed is "Following required columns are missing in your provided CSV file". This occurs when some of the required column is missing in the CSV field headers. If any of the required columns is missing then it would not be possible to upload you data. If there is just a name difference in column names then you can use a previously saved custom template or can use manual mapping option to map the column name to the respective AbleCommerce field. For more information about the required column names for Categories, Products, Users and Orders CSV data see "Creating CSV files for data import" section.

You can use "Template Grid" to map your CSV header column names to respective AbleCommerce column names. The template grid can also be used to apply previously saved CSV template on your CSV data. You can also create and save CSV templates on this screen as well.

If your CSV data is using "Text Qualifier" and "Text Delimiter" different then the standard Double Quote and Comma (,) characters then you can choose "Manual Mapping" option to update these. CSV data validation will be re-performed when you will update these.

On the CSV upload screen the import options work similar to XML upload screens. For details about these see our section "Uploading AC7 XML data" section. When you are done with the import options and CSV template click "Next" button to proceed to next screen. If all of your required CSV fields are mapped properly then you will be able to proceed to next screen and can start upload by clicking the "Upload" button. A complete import log will be displayed after completion of import process.

Creating CSV files for Data Import

While preparing CSV files for data import to your AC7 store your CSV file should meet certain requirements. Although you can map the CSV header to AbleCommerce fields using the Template Grid but there are some required fields that must present in your CSV file in order to successfully import the data. Here is the list of columns for Categories, Products, Orders and Users:

Complete List of fields for Categories CSV Import:

CategoryId
StoreId
ParentId
Name
Summary
Description
ThumbnailUrl
ThumbnailAltText
DisplayPage
Theme
HtmlHead
VisibilityId

Required fields for Categories CSV import:

Name
ParentId

Complete list of fields for Products CSV Import:

ProductId
StoreId
Name
Price
CostOfGoods
MSRP
Weight
Length
Width
Height
Manufacturer
Sku
ModelNumber
DisplayPage
TaxCode
Shippable
WarehouseId
Warehouse
InventoryModeId
InStock
InStockWarningLevel
ThumbnailUrl
ThumbnailAltText
ImageUrl
ImageAltText
Summary
Description
ExtendedDescription
Vendor
CreatedDate
LastModifiedDate
ProductTemplate
IsFeatured
IsProhibited
AllowReviews
AllowBackorder
WrapGroup
ExcludeFromFeed
HtmlHead
DisablePurchase
MinQuantity
MaxQuantity
VisibilityId
Theme
IconUrl
IconAltText
IsGiftCertificate
Categories
UseVariablePrice
MinimumPrice
MaximumPrice
SearchKeywords
HidePrice

Required fields for Products CSV import:

Name

Complete list of fields for Orders CSV Import:

OrderId
OrderDate
StoreId
UserName
Affiliate
BillToFirstName
BillToLastName
BillToCompany
BillToAddress1
BillToAddress2
BillToCity
BillToProvince
BillToPostalCode
BillToCountryCode
BillToPhone
BillToFax
BillToEmail
ProductSubtotal
TotalCharges
TotalPayments
OrderStatus
RemoteIP
Referrer
GoogleOrderNumber
PaymentStatusId
ShipmentStatusId

Required fields for Orders CSV import:

OrderDate
UserName
BillToFirstName
BillToLastName
BillToAddress1
BillToCity
BillToProvince
BillToPostalCode
BillToCountryCode
BillToPhone
BillToEmail
TotalCharges
TotalPayments
OrderStatus

Complete list of fields for Users CSV Import:

UserId
StoreId
UserName
FirstName
LastName
Email
ReferringAffiliateId
Affiliate
AffiliateReferralDate
PrimaryAddressId
PrimaryWishlistId
PayPalId
PasswordQuestion
PasswordAnswer
IsApproved
IsAnonymous
IsLockedOut
CreateDate
LastActivityDate
LastLoginDate
LastPasswordChangedDate
LastLockoutDate
FailedPasswordAttemptCount
FailedPasswordAttemptWindowStart
FailedPasswordAnswerAttemptCount
FailedPasswordAnswerAttemptWindowStart
Comment
Nickname      
Company
Address1
Address2
City
Province
PostalCode
CountryCode
Phone
Fax      
AddressIsResidence
AddressIsValidated
PasswordPlainText
PasswordEncrypted

Required fields for Users CSV import:

UserName
Email
IsApproved
IsLockedOut

NEW IN DATA PORT BETA .31

14 new fields for user CSV export/import are added in DataPort Beta .31. There are 12 fields for user address export/import, while 2 fields(PasswordPlainText & PasswordEncrypted) for user password export/import. When exporting user data in CSV format user password will always be exported as encrypted text and "PasswordEncrypted" field will be used for the purpose. Now if a user want to import plain text password from sources other then AbleCommerce or you want to change user passwrd then "PasswordPlainText" field can be used for the purpose. Please note carefully that when importing user CSV data the preference is that when "PasswordPlainText" field will have value then the user existing password will be overwritten using the value of "PasswordPlainText" field. If this field will have no value then the "PasswordEncrypted" field value will be examined and the password will be updated if it is found different then existing password. A proper warning message will be logged when an encrypted password is overwritten including the old password and new password encrypted values.

The first line of your CSV file will contain the CSV header (a delimited list of column names) while the rest of line will contain data. You can use Comma (,), Pipe (|) or TAB as text delimiters, while Double Quote (") or Single Quote (‘) can be used as text qualifier in your CSV files. You can configure the field separator or text delimiter to use.

DataPort Advanced Configuration

Navigate to Options -> Settings menu in the menu bar, this will open the Advance Settings screen. There are 3 configuration options available:

1. Debug Mode: when enabled each request and response from server will be intercepted and the data sent or received from the server is shown to user. The process execution will be halted until you close the debug information dialog. That information can help a lot while debugging.
2. Cache Preload Data: When enabled the preload data for Products, Users, and Orders customization filter is cached at client end otherwise this data is loaded each time you switch from/to the customization filter.
3. Chunk Size: Chunk size refers to the amount of data sent / received to / from server when importing / exporting data. When importing large files to server or export large amount of data from server the data is sent or received from server in chunks. To understand the concept of chunk lets consider we have 10,000 products to upload to server. Uploading all these products to server will overburden the network traffic and server and there are more chances that the upload fails due to heavy network traffic. Also it will take a long long time to upload and import at server and our application will become un-responsive during the upload process. However if we send those 10,000 products in chunk with each chunk having about 100 products then it will reduce the network traffic and server load and application will be more responsive. Another aspect is that with very large data it is difficult to track if any error occurs in the middle while with smaller chunks it is possible to locate the exact reason of the error and find a remedy. Using the chunk size configuration option we can control the network and server load. The larger the chunk size will be the higher the network traffic and server load and vice versa. We can choose a little bigger chunk size if we have a fast high speed network connection and a ultra high responsive server. While smaller chunk size is recommended for slower network and server.

Changes to these configuration options work only for the current session and if you will re-start the DataPort the saved settings will lost and default values will be restored.