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

  1. Create CSV files in the correct format.
  2. Transform the CSV data to generic XML data.
  3. Generate a DTD for the WebSphere Commerce database.
  4. Transform the generic XML data to WebSphere Commerce XML data.
  5. Resolve the identifiers in the XML document.
  6. 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.
  1. 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.
  2. 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.
  3. Run the text transform command:
    • LinuxAIXFor IBM i OS operating system txttransform.sh manifest.txt
    • Windows 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.
  1. 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
    
  2. Save the file as tablenames.txt.
  3. Run the DTD Generate command:
    dtdgen -dbname db_name -dbuser db_user -dbpwd db_password -infile tablenames.txt -outfile wcsample.dtd
    

    where:

    dbname
    LinuxAIXWindows Name of the target database.
    For IBM i OS operating system This is the database name as displayed in the relational database directory (WRKRDBDIRE).
    dbuser
    LinuxAIXWindowsName of the user connecting to the database.
    For IBM i OS operating system 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:

  1. It defines the mapping of data from the source DTD into the target DTD.
  2. 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
LinuxAIXWindowsName of the target database.
For IBM i OS operating system This is the database name as displayed in the relational database directory (WRKRDBDIRE).
dbuser
LinuxAIXWindowsName of the user connecting to the database.
For IBM i OS operating system 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