Load data in custom table using dataload

When there is a requirement to load data in custom created table using dataload then OOB dataload utility provides a Mediator called “TableObjectMediator” which can be used for such purpose, this technique does not require any coding and can be quickly setup by defining configuration files to map data csv file with custom table.

Below given steps also demonstrates loading of system generated(automatically, no need to include it in CSV) and custom unique key from a CSV file, any new records will be inserted and existing records(based on primary and unique) keys will be updated.

Step 1:

Create a custom table, in this example my primary key is system generated and we will use IDResolver for this purpose, all other data will be inserted from the input CSV file.

—– Create custom_table_derby_db.sql with following content —–

connect '..\db\mall;create=true'; 

CREATE TABLE X_CUSTOM_TABLE (
 MY_SYSTEM_ID BIGINT NOT NULL,
 MY_CUSTOM_ID BIGINT NOT NULL,
 column1 VARCHAR(254),
 column2 VARCHAR(254),
 column3 VARCHAR(254)
);

ALTER TABLE X_CUSTOM_TABLE
       ADD PRIMARY KEY (MY_SYSTEM_ID);
       
CREATE UNIQUE INDEX I0000_X_CUSTOM_TABLE ON X_CUSTOM_TABLE 
(
       MY_CUSTOM_ID ASC
);

INSERT INTO KEYS (KEYS_ID, TABLENAME, COLUMNNAME, COUNTER, PREFETCHSIZE, LOWERBOUND, UPPERBOUND, OPTCOUNTER)
VALUES ((SELECT MAX(KEYS_ID) + 1 FROM KEYS), 'X_CUSTOM_TABLE', 'MY_SYSTEM_ID', 0000, 1, 1000, 999999, 1);

commit;

Step 2:

Run the SQL for Derby as follows.

C:\IBM\WCDE_ENT70\bin>ij.bat "..\samples\dataload\custom_table_derby_db.sql"

Step 3:

Define wc-dataload.xml file with following content.

<?xml version="1.0" encoding="UTF-8"?>
<_config:DataLoadConfiguration
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.ibm.com/xmlns/prod/commerce/foundation/config ../../../../xml/config/xsd/wc-dataload.xsd"
    xmlns:_config="http://www.ibm.com/xmlns/prod/commerce/foundation/config">
  <_config:DataLoadEnvironment configFile="./wc-dataload-env.xml" />
  
  <_config:LoadOrder commitCount="100" batchSize="1" dataLoadMode="Replace">
         <!--_config:property name="firstTwoLinesAreHeader" value="true" /-->
         <_config:LoadItem name="X_CUSTOM_TABLE" businessObjectConfigFile="wc-loader-custom.xml" >
              <_config:DataSourceLocation location="MyCustomData.csv" />
         </_config:LoadItem>
  </_config:LoadOrder> 

</_config:DataLoadConfiguration>

NOTE: For initial load we should replace dataLoadMode=”Insert”.

Step 4:

Define wc-loader-custom.xml with following content.

<?xml version="1.0" encoding="UTF-8"?>
<_config:DataloadBusinessObjectConfiguration xmlns:_config="http://www.ibm.com/xmlns/prod/commerce/foundation/config" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.ibm.com/xmlns/prod/commerce/foundation/config ../../../../xml/config/xsd/wc-dataload-businessobject.xsd">
   <_config:DataLoader className="com.ibm.commerce.foundation.dataload.BusinessObjectLoader">
      <_config:DataReader className="com.ibm.commerce.foundation.dataload.datareader.CSVReader" firstLineIsHeader="true" useHeaderAsColumnName="true">
         <_config:Data>
            <_config:column number="1" name="MY_CUSTOM_ID" />
            <_config:column number="2" name="column1" />
            <_config:column number="3" name="column2" />
            <_config:column number="4" name="column3" />
         </_config:Data>
      </_config:DataReader>
      <_config:BusinessObjectBuilder className="com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder">
         <_config:Table name="X_CUSTOM_TABLE">
            <!-- System generated primary key-->
            <_config:Column name="MY_SYSTEM_ID" value="MY_SYSTEM_ID" valueFrom="IDResolve">
               <_config:IDResolve tableName="X_CUSTOM_TABLE" generateNewKey="true" />
            </_config:Column>
            <!-- Unique key fed from the CSV file-->
            <_config:Column name="MY_CUSTOM_ID" value="MY_CUSTOM_ID" />
            <_config:Column name="column1" value="column1" />
            <_config:Column name="column2" value="column2" />
            <_config:Column name="column3" value="column3" />
         </_config:Table>
         <_config:BusinessObjectMediator className="com.ibm.commerce.foundation.dataload.businessobjectmediator.TableObjectMediator" />
      </_config:BusinessObjectBuilder>
   </_config:DataLoader>
</_config:DataloadBusinessObjectConfiguration>

Step 5:

Define an input CSV file with following content, first rows of the CSV indicates field names.

MY_CUSTOM_ID,column1,column2,column3
1234,mydata_old,mydata_old
1234,mydata_new, mydata_new
2345,mydata_old,mydata_old

Step 6:

Run the dataload now.

c:\IBM\WCDE_ENT70\bin> dataload.bat ..\samples\dataload\wc-dataload.xml -D.level=FINER

You should see a similar output, as given below in the logs. It has successfully processed three records from input csv file. The first one was inserted and second record was updated as the record already exists in the table and the third one also get inserted as the ID is different not the same as previous one.

================================================================================
==
WebSphere Commerce Data Load
================================================================================
==

Load started at: Fri Oct 05 20:50:46 CDT 2012
Initialization completed in 4.204 seconds.

Processing X_CUSTOM_TABLE...

--------------------------------------------------------------------------------
--
Load summary for load item: X_CUSTOM_TABLE.
--------------------------------------------------------------------------------
--
Business Object Configuration: wc-loader-custom.xml
Data loader mode: Replace.
Batch size: 1.
Commit count: 100.
Error Tolerance Level: 1.
Error Count: 0.
Amount of data processed: 3.
Amount of business objects processed: 2.
Amount of business objects committed: 2.
Data loader initialization time: 0 seconds.
Data loader execution began: Fri Oct 05 20:50:50 CDT 2012
Data loader execution ended: Fri Oct 05 20:50:51 CDT 2012
Data loader completed in 0.85 seconds.
Total flush time: 0 seconds.
Total commit time: 0.001 seconds.
CSV file location: ..\samples\dataload\MyCustomData.csv.
Affected tables (1):
Table name: X_CUSTOM_TABLE, Affected number of rows: 3.
--------------------------------------------------------------------------------
--


Program exiting with exit code: 0.
Load completed successfully with no errors.

Load ended at: Fri Oct 05 20:50:51 CDT 2012
Load completed in 5.07 seconds.


Review the log generated at following location to debug in case of issues
C:\IBM\WCDE_ENT70\logs\wc-dataload.log

Note: If you face any issue by executing the above steps please let me know in comment. Thanks.