Deprecated feature: Loading existing customer information

This topic describes how to load existing customer information from a comma-separated value (CSV) file to the WebSphere Commerce database by using the loading utilities. A CSV file contains table data as a series of ASCII text lines. The lines are organized by column. Each column value is separated by a comma from the next column's value, and each row starts a new line. Many spreadsheet programs can export data as CSV files.

About this task

To load existing customer information:

Procedure

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.

Results

Creating 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

Transforming 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.
1. Open an XML or text editor.
2. For each CSV file, create a corresponding schema file, to instruct WebSphere Commerce how to interpret your 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 = "&quot;"
      HeaderIncluded = "true"
      HeaderLines = "1"
      ElementName = "user">
   </RecordDescription>
</TextSchema>
```
3. 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.
1. 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.
2. 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.

Generating a DTD for the WebSphere Commerce database

Generate a DTD file for those tables in the WebSphere Commerce database to which you 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

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 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.

Transforming 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 Generating 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.

Resolving 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.

Loading the data into the WebSphere Commerce database

Finally, load the XML data into the WebSphere Commerce database.

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