View previous topic :: View next topic
|
Author |
Message |
Daniel Prosser
New User
Joined: 05 Nov 2010 Posts: 57 Location: Amsterdam
|
|
|
|
Hi all,
I have a CSV VB file which I need to load into a DB2 table, my idea was to use DFSORT PARSE and BUILD to achieve this.
I have created some Sort steps to
a) PARSE the VB file & BUILD a FB file using %nn etc
b) Some Overlays to correct dates etc.
c) DB2 Load
This all works fine.
BUT I have a problem with VARCHAR fields/columns.
Some of our tables have many VARCHAR(256) columns, the DB2 load syspunch cards are expecting the VARCHAR to start at a certain position which is the column length.
Now I can assign the MAX to that position fine, but this is going to make the DB storage enormous as most of the VARCHAR(256) contain less than 10 characters.
I want to find a way to retrieve the LENGTH of the VARCHAR field during the PARSE and insert that length into the file to be LOADED to DB2.
(There may be a better way to load a CSV file into DB2 and I will post a Q on the DB2 forum)
e.g.
Input file
When the first and second fields are VARCHAR(10) I would expect the output to be like this
Code: |
??1234567890??1234567890
..ABC ..QQQ T |
Where the ?? on both VARCHAR fields is set to x'0003'
Does anyone have any bright ideas.
Daniel |
|
Back to top |
|
|
saiprasadh
Active User
Joined: 20 Sep 2006 Posts: 154 Location: US
|
|
|
|
Hi Daniel,
You can use below mentioned load card to load CSV file data to table.
Code: |
//SYSIN DD *
LOAD DATA
FORMAT DELIMITED COLDEL ',' CHARDEL '"' DECPT '.'
INTO TABLE TBQB0103
(FILENO CHAR,
DATE1 DATE EXTERNAL,
TIME1 TIME EXTERNAL,
TIMESTMP TIMESTAMP EXTERNAL)
/*
//SYSREC DD *
"001", 2000-02-16, 00.00.00, 2000-02-16-00.00.00.0000
"002", 2001-04-17, 06.30.00, 2001-04-17-06.30.00.2000
"003", 2002-06-18, 12.30.59, 2002-06-18-12.30.59.4000
"004", 1991-08-19, 18.59.30, 1991-08-19-18.59.30.8000
"005", 2000-12-20, 24.00.00, 2000-12-20-24.00.00.0000
/* |
Note : Moderator since this post is not created in Db2 forum i am providing the solution here.
Sai |
|
Back to top |
|
|
Daniel Prosser
New User
Joined: 05 Nov 2010 Posts: 57 Location: Amsterdam
|
|
Back to top |
|
|
Daniel Prosser
New User
Joined: 05 Nov 2010 Posts: 57 Location: Amsterdam
|
|
|
|
Thanks saiprasadh,
that looks interestering.
Dan. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Daniel,
I apologize,
your exact question has not been previously asked
what you want is exactly what asked for.
now, have you experimented with a few (2 or 3) rows to insure that the varchar(10) with a preceding length will properly load?
if so, then you have a map - the syspunch.
from that you can use parse to build the records. (as you have probably been able to do).
what i see is that you need help determining the 'non space count' of each field and have that inserted as binary in the preceding 2 char.
if so, that is the sort solution that you are looking for.
at this point, i imagine you can provide the starting and ending column of each field, and where the length has to be inserted/overlayed.
provide that map / definition of input file and i am sure someone can provide the control cards necessary. from that you can build and work on the rows of other tables.
thx, dbz |
|
Back to top |
|
|
Daniel Prosser
New User
Joined: 05 Nov 2010 Posts: 57 Location: Amsterdam
|
|
|
|
Thanks guys, the LOAD stmt works brilliantly.
Dan. |
|
Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010 Posts: 577 Location: USA
|
|
|
|
Daniel,
What utility do you use to load, db2 table?
Reason I am asking is because there is an option to load CSV file directly to the table. Something like FORMAT DELIMITED COLDEL ',' CHARDEL '"', in the load control card. You probably have to do some work to with PARSE, PAIR=QUOTE but it should be easier.
I haven't used this option in a while but I believe this takes care of setting correct length for the VARCHAR field.
Thanks, |
|
Back to top |
|
|
Daniel Prosser
New User
Joined: 05 Nov 2010 Posts: 57 Location: Amsterdam
|
|
|
|
Hey Sqlcode1 - that's exactly what I did.
I parsed the CSV file making some changes and created a new CSV which I loaded using the COLDEL you mentioned. saiprasadh mentioned this was possible.
So as far as I am concerned it's all working fine, oh for the record I am using DSNUPROC to load the tables.
thanks
Daniel |
|
Back to top |
|
|
|