| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-27-11, 14:34
|
|
Registered User
|
|
Join Date: Dec 2011
Posts: 2
|
|
|
load problem with delimiters
|
|
Hi everybody,
I'm trying to load data from the following data-file. All data is enclosed by " including the numeric fields. If I use the load client command, all data is imported except for the decimal and integer data which appearantly cannot be converted (they stay null). If I use data studio, the data file is imported correctly. However I want to use the load client command. What am I missing here. I'd appreciate the advice. Cheers
source.csv
"Balance","A0000","Balance code","20100101","750","Balance code","1.35","1.65"
"Balance","A0001","Balance code","20100101","600","Balance code","1.02","1.25"
"Balance","A0002","Balance code","20100101","1205","Balance code","1.1","2.35"
"Letter","C0000","Balance code","20100101","1803","Main mailcode","1.12","1.34"
load client from "source.csv" of del modified by
timestampformat="yyyy-mm-dd hh:mm:ss"
dateformat="yyyymmdd"
decpt.
chardel""
coldel,
dumpfile="/tmp/SQLDIR.LK0/source.dmp"
method P (1, 2, 3, 4, 5, 6, 7, 8) messages "sourcebron.log"
replace into IMPORT.SOURCE(
FIELD_A,
FIELD_B,
FIELD_C,
FIELD_D,
FIELD_E,
FIELD_F,
FIELD_G,
FIELD_H)
copy no indexing mode autoselect;
table import.source (
FIELD_A varchar(255),
FIELD_B varchar(255),
FIELD_C varchar(255),
FIELD_D date,
FIELD_E bigint,
FIELD_F varchar(200),
FIELD_G decimal(10,2),
FIELD_H decimal(10,2)
)
|
|
12-28-11, 08:22
|
|
Registered User
|
|
Join Date: Dec 2011
Posts: 10
|
|
Are you trying to load the file from a remote server?
The LOAD CLIENT specifies that the .csv file is being located remotely.
If you are loading from remote client then code page conversion is not performed. If the code pages of data & server differs then the data code page should be specified using modified by codepage=<your_server_code_page>.
|
|
12-28-11, 10:19
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
|
|
DB2 treats whatever is enclosed in string delimiters as character data, which it refuses to LOAD (or IMPORT) into decimal fields. I guess you have two options: either manipulate the input file and remove the unnecessary string delimiters, or do a LOAD into a staging table where the columns in question are defined as character, then do another LOAD from a cursor that has an explicit cast from character to decimal.
|
|
12-29-11, 04:31
|
|
Registered User
|
|
Join Date: Dec 2011
Posts: 2
|
|
Thanks for the help.
Unfortunately, I don't have any influence over the file format that is delivered. I assumed that DB2 would convert automatically, like Oracle, SQLServer and Sybase.
I'll use a staging table for now, but I'm not giving up. Like I wrote before, Data studio can import this data without a problem.
|
|
12-29-11, 14:57
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
Originally Posted by n_i
... or do a LOAD into a staging table where the columns in question are defined as character, then do another LOAD from a cursor that has an explicit cast from character to decimal.
|
This may be an example to insert into target table from staging table.
Example 1:
Test Data
Code:
------------------------------ Commands Entered ------------------------------
SELECT * FROM import.staging;
------------------------------------------------------------------------------
COLUMN_ALL
----------------------------------------------------------------------------------------------------
"Balance","A0000","Balance code","20100101","750","Balance code","1.35","1.65"
"Balance","A0001","Balance code","20100101","600","Balance code","1.02","1.25"
"Balance","A0002","Balance code","20100101","1205","Balance code","1.1","2.35"
"Letter","C0000","Balance code","20100101","1803","Main mailcode","1.12","1.34"
4 record(s) selected.
INSERT statement
Code:
------------------------------ Commands Entered ------------------------------
INSERT INTO import.source
SELECT MAX( CASE cn WHEN 1 THEN extracted END )
, MAX( CASE cn WHEN 2 THEN extracted END )
, MAX( CASE cn WHEN 3 THEN extracted END )
, INSERT( INSERT(
MAX( CASE cn WHEN 4 THEN extracted END )
, 5 , 0 , '-' ) , 8 , 0 , '-' )
, MAX( CASE cn WHEN 5 THEN extracted END )
, MAX( CASE cn WHEN 6 THEN extracted END )
, MAX( CASE cn WHEN 7 THEN extracted END )
, MAX( CASE cn WHEN 8 THEN extracted END )
FROM import.staging
CROSS JOIN LATERAL
(SELECT cn , del_cn
, LAG( del_cn , 1 , 2 )
OVER( ORDER BY del_cn ) AS del_lag
FROM (VALUES 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 ) AS f(cn)
CROSS JOIN LATERAL
(VALUES COALESCE(
NULLIF( INSTR(column_all , '","' , 1 , cn) , 0 )
, LENGTH(column_all)
)
) AS f(del_cn)
)
CROSS JOIN LATERAL
(VALUES SUBSTR(column_all , del_lag + 3 , del_cn - del_lag - 3)
) AS f(extracted)
GROUP BY
column_all
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
|
Last edited by tonkuma; 12-29-11 at 15:03.
Reason: Add test data.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
load problem with delimiters
