Load existing customer information

Administer > Transforming, loading, and extracting data > Overview of the mass load utilities > Load data using the mass load utility > Loading existing user data

Load existing customer information

Overview

This topic describes how to load existing customer information from a CSV file to the WebSphere Commerce database using the loading utilities.

Load existing customer information

Create CSV files in the correct format.
Transform the CSV data to generic XML data.
Generate a DTD for the WebSphere Commerce database.
Transform the generic XML data to WebSphere Commerce XML data.
Resolve the identifiers in the XML document.
Load the data into the WebSphere Commerce database.

Create CSV files
The section will describe how to create CSV files for the following tables:

Table name Description
MEMBER create a new member entry
USERS creates the user
MBRREL creates the member relationships, to define the hierarchy to get to the new user node

Optionally, you may also want to load the following data:

USERPROF user profile
BUSPROF business profile
ADDRESS addresses
MBRROLE roles
MBRATTRVAL custom member attributes

Transform the CSV data to generic XML data

Transform the CSV data received from the Line of Business Manager into a single XML data file.
To convert CSV files to XML files you use the Text transform command.
Create schema files.
The text schema files tell the Text Transformer how to parse a particular CSV source file. In particular, each text schema file indicates the values for field, record, and text delimiter as well as whether header lines are included in the source file.
Open an XML or text editor.
For each CSV file, create a corresponding schema file, to instruct WebSphere Commerce how to interpret the CSV file. Use the following XML example as a guide. Replace the ElementName attribute value with a name appropriate for the record in the CSV file.
<?xml version="1.0" encoding="UTF-8" ?>

<TextSchema DataType = "CSV Format">   

    <RecordDescription
          FieldSeparator = ","
          RecordSeparator = "&#10;&#13;"
          StringDelimiter = """
          HeaderIncluded = "true"
          HeaderLines = "1"
          ElementName = "user">   
    </RecordDescription>

</TextSchema>
Save the schema file as your_element_name_schema.xml.
Create the manifest.txt file:
The manifest file, manifest.txt also referred to as a "command" or "parameter" file, instructs the text transformation tool. The manifest file specifies:

which files to parse as input (ElementName.csv)

which text schema files to use for each source file (ElementName_schema.xml)

which files to use for output (ElementName_data.xml)

how to write to the output file (create or append)

The files referenced in the manifest.txt are then used as input to the text transformation.
In a text editor create the manifest information, using the following example:
a.csv,a_schema.xml,a_data.xml,Create
b.csv,b_schema.xml,b_data.xml,Append
c.csv,c_schema.xml,c_data.xml,Append
Where a, b, c are ElementNames from step 1b.
Save the file as manifest.txt.
Run the text transform command:

txttransform.sh manifest.txt

txttransform.cmd manifest.txt

The output file is specified as c_data.xml. Matching DTD and schema files, c_data.dtd and c_data.xsd, are also created.
Generate a DTD for the WebSphere Commerce database

Generate a DTD file for those tables in the WebSphere Commerce database to which we will be importing data. The DTD Generate command accepts the database name, database user name, database user password, and file containing a list of table names as input. The command writes out a DTD file.
Create a file containing a list of table names. Each table name should be on a separate line, specify table names in lowercase letters, for example:
address
busprof
mbrattrval
mbrrel
mbrrole
member
user
userprof
Save the file as tablenames.txt.

Run the DTD Generate command:

dtdgen -dbname db_name -dbuser db_user -dbpwd db_password -infile tablenames.txt -outfile wcsample.dtd

where:

dbname Target database name as displayed in the relational database directory (WRKRDBDIRE).
dbuser Name of the user connecting to the database. This is usually the same as the instance user name.
dbpwd Password for the user connecting to the database.
infile Name of an input file containing a database table name on each line.
outfile Name of the output DTD file. The command creates the wcsample.dtd file.
Transform the generic XML data to WebSphere Commerce XML data

Transform the XML data created in Transforming the CSV data to generic XML data into an XML document that conforms to the DTD created in Generate a DTD for the WebSphere Commerce database.
To transform generic XML data to WebSphere Commerce XML data you use the XML Transform command. The XML Transform command takes an XML source file and an XSL file as input. The XML Transform command writes out a new XML document that conforms to the DTD defined by wcsample.dtd.
The XSL file is essential in this process. It accomplishes two important tasks:

It defines the mapping of data from the source DTD into the target DTD.

It defines the special identifiers needed for the ID resolution.

Run the XML transform command,

xmltransform -infile c_data.xml -transform file.xsl -outfile wcdata.xml -param "name=value"

where:

infile Name of the file to be transformed
outfile Name of the output DTD file.
transform Name of the transform XSL rule file
outfile Name for the output XML file in which the transformed data will be stored
param Name-value pair parameter to be passed to the XSL rule file; for example, "storeId=-2001". This parameter is optional. This parameter can be specified multiple times to pass multiple name-value pairs.

Resolve identifiers in the XML document

Next, resolve the identifiers in the XML document, wcdata.xml. created in...

Transforming the generic XML data to WebSphere Commerce XML data

To resolve entities you use the ID Resolve command. The ID Resolve command accepts the database name, database user name, database user password, source XML document, and method of resolution (mixed) as input. The command writes out a new XML document as output. This file has all of the primary and foreign keys values in place within the XML document.

wcdataid.xml

Run the ID Resolve command:

idresgen -dbname db_name -dbuser db_user -dbpwd db_password -infile wcdata.xml -outfile wcdataid.xml -method mixed

where:

dbname Name of the target database. This is the database name as displayed in the relational database directory (WRKRDBDIRE).
dbuser Name of the user connecting to the database. This is usually the same as the instance user name.
dbpwd Password for the user connecting to the database.
infile Name of the input XML document containing table records.
outfile Name of the output XML file to be produced; this file can be used as input to the massload utility.
method Method to be used in processing the input file. The default method is load. The load method treats the input file as though the records do not exist in the database. The update method assumes that there are already identifiers for the input objects. Use the mixed method when some records do not exist in database and some do.

Load the data into the WebSphere Commerce database

To load the XML data into the WebSphere Commerce database.

massload -dbname db_name -dbuser db_user -dbpwd db_password -infile wcdataid.xml -method sqlimport

Related reference

Example: Loading data into the MEMBER table (idresgen utility)

+
Search Tips | Advanced Search

Table name	Description
MEMBER	create a new member entry
USERS	creates the user
MBRREL	creates the member relationships, to define the hierarchy to get to the new user node

USERPROF	user profile
BUSPROF	business profile
ADDRESS	addresses
MBRROLE	roles
MBRATTRVAL	custom member attributes

dbname	Target database name as displayed in the relational database directory (WRKRDBDIRE).
dbuser	Name of the user connecting to the database. This is usually the same as the instance user name.
dbpwd	Password for the user connecting to the database.
infile	Name of an input file containing a database table name on each line.
outfile	Name of the output DTD file. The command creates the wcsample.dtd file.

infile	Name of the file to be transformed
outfile	Name of the output DTD file.
transform	Name of the transform XSL rule file
outfile	Name for the output XML file in which the transformed data will be stored
param	Name-value pair parameter to be passed to the XSL rule file; for example, "storeId=-2001". This parameter is optional. This parameter can be specified multiple times to pass multiple name-value pairs.

dbname	Name of the target database.	This is the database name as displayed in the relational database directory (WRKRDBDIRE).
dbuser	Name of the user connecting to the database.	This is usually the same as the instance user name.
dbpwd	Password for the user connecting to the database.
infile	Name of the input XML document containing table records.
outfile	Name of the output XML file to be produced; this file can be used as input to the massload utility.
method	Method to be used in processing the input file. The default method is load. The load method treats the input file as though the records do not exist in the database. The update method assumes that there are already identifiers for the input objects. Use the mixed method when some records do not exist in database and some do.