By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,592 Members | 2,158 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,592 IT Pros & Developers. It's quick & easy.

Loading a delimited file to a table - new bie question

P: n/a
I have background in SQL server and none in DB2 hence the question.
SQL Server has a bcp command - I am looking for an equivalent utility
in db2
I have a table called Employee(empid int ,fname varchar(20),lname
varchar(20), title varchar(20))

I have a file (c:\Employee.file) of this format (delimiter = | )
100|John|Doe|Mgr|
101|Sam|Joe|Engineer|
I want to know the exact command line (db2 load) to load this file to
Employee table factoring in the delimiter

Please help me out with an answer to this simple q

Thanks in advance
Suma

Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
"Suma" <su********@yahoo.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
I have background in SQL server and none in DB2 hence the question.
SQL Server has a bcp command - I am looking for an equivalent utility
in db2
I have a table called Employee(empid int ,fname varchar(20),lname
varchar(20), title varchar(20))

I have a file (c:\Employee.file) of this format (delimiter = | )
100|John|Doe|Mgr|
101|Sam|Joe|Engineer|
I want to know the exact command line (db2 load) to load this file to
Employee table factoring in the delimiter

Please help me out with an answer to this simple q

Thanks in advance
Suma

Check out the import command in the Command Reference manual. You can
specify the delimiter. You can also use the GUI interface in the Control
Center to do same.
Nov 12 '05 #2

P: n/a
Here is the IMPORT command which u can use

IMPORT FROM c:\Employee.file OF DEL MODIFIED BY NOCHARDEL COLDEL|
MESSAGES c:\LOG.LOG INSERT INTO Employee

However this is a logged operation. I mean if u are loading a large
chunk of data into the Employee table the log records after some time
may get full.

Use LOAD command which is a not-logged and fast as compared to IMPORT
command.

LOAD FROM c:\Employee.file OF DEL MODIFIED BY NOCHARDEL COLDEL|
MESSAGES c:\LOG.LOG INSERT INTO Employee

I hope this helps

Nov 12 '05 #3

P: n/a
Below is a sample of loading a comma-delimited file:
LOAD CLIENT
FROM 'c:\click_data.tsv' OF DEL
MODIFIED BY
delprioritychar coldel0x09
replace INTO dbprod.click_extract

Check out the utility guide and reference for a complete explanation or
http://publib.boulder.ibm.com/infoce...help/index.jsp with search
term "load". Good luck.

Pete H

Nov 12 '05 #4

P: n/a
Suma and Mark,

I don't know which version of db2 Suma is using, but using the wizard
is the easiest way to do this. (In Control Panel, right-click on the
table and follow the prompts.)

However, what is the symbol when the separator is <tab>? Is it "|" ?

SS

Nov 12 '05 #5

P: n/a
Ian
28******@gmail.com wrote:
Suma and Mark,

I don't know which version of db2 Suma is using, but using the wizard
is the easiest way to do this. (In Control Panel, right-click on the
table and follow the prompts.)

However, what is the symbol when the separator is <tab>? Is it "|" ?


0x09 is a tab (the hex representation of a tab in ASCII)

So,

load from <file> of del modified by coldel0x09 ...

Nov 12 '05 #6

P: n/a
Sometimes I have to load text files which are <tab> delimited. At this
moment I am converting them to comma delimited to LOAD.

Is "|" the same as <tab>?

SS

Nov 12 '05 #7

P: n/a

<28******@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Sometimes I have to load text files which are <tab> delimited. At this
moment I am converting them to comma delimited to LOAD.
You don't need to do the conversion from tabs to commas. Just use this as
part of your LOAD command

.... MODIFIED BY DEL USING COLDEL0x09 ...

This will work, provided that you're on an ASCII-based platform, where <tab>
= ASCII 0x09.
Is "|" the same as <tab>?


No. "|" is a vertical bar character, ASCII 0x7c.

--
Matt Emmerton
Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.