Load Data into the Database Tables
Occasionally you run into a situation where you have to load some plain csv data into a table in your database. This table is either your own custom table or application specific table. The easiest way to do this is to use the database load commands that are native to the database you are using. The alternative is to use the websphere commerce data load process. In my opinion, if it is a custom table and there and no dependencies on other tables through foreign key constraints, it is best to employ the load commands that are native to the database. You might argue that the reason we should use commerce commands is because if the backend database changes, these load commands have to be revisited. But when was the last time that something like this happened? In fact if the backend database changes, say from db2 to oracle or vice-versa, you have a lot more headaches to deal with than just the load commands!
One example, I recently used, is for loading address validation data into the commerce database. This data goes into just one table and this is a stand alone table. Since my backend database is DB2, I searched for a simple commnd that can do the job. To my delight, I found DB2 Load that is very easy to use. This command has some very archaic and complex usage approach. All I needed was to load the data and I did not care if the data got cleaned up before loading the new data. Here is how I used this command:
db2 LOAD FROM path_to_data_file OF DEL MODIFIED BY USEDEFAULTS METHOD P (1,2,3…n) MESSAGES “path_to_message_file” REPLACE INTO USER.TABLENAME (column names) COPY NO INDEXING MODE AUTOSELECT
The above command loads a csv file into the database. The text in italics in the above command must be replaced with appropriate values. If you have, say, 5 columns in the table than replace 1,2,3..n with 1,2,3,4,5 and then specify the names of the five columns in the column names section. The USEDEFAULTS works well with a comma separated value file. The message_file contains some very good information if the load fails. If you are using tab separated values file, you are in for a surprise. The USEDEFAULTS must be replaced with COLDEL0×09. COLDEL0×09 has two parts to it - COLDEL and 0×09, where 0×09 indicates that the columns are separated by a tab(ascii hex value of 0×09). Similarly when columns are separeted by a ‘|’, you should use 0×7c. Check out this useful ascii table for additional characters.
If you are using Oracle database, you may consider using sql loader.