Export to comma-delimited file - with a difference!

[PeterG5]

Hi All,

I'm trying to export a DB/2 table in a comma-delimited format, suitable for later loading into WebFocus.
When I issue the command:
EXPORT TO C:\IBI\PGFTEMP\EXP_STAT.TXT OF DEL
SELECT SO_STATUS_NEXT_ID,
SO_STATUS_NEXT_DESC
FROM E6DW_DATA.SOT_LU_STATUS_NEXT
WHERE SO_STATUS_NEXT_ID <= '999' WITH UR;
It works fine and I get this output:
"502","Sales Quote with PDC"
"545","Picking Confirmation"
So far, so good.....
I don't really want the double-quote marks around everything but I can live with them - BUT - what I really want is:
"502","Sales Quote with PDC",$
"545","Picking Confirmation",$
So, I need to insert the string ,$ at the end of the line, exactly as shown, with NO quotes, either single or double, around the ,$
Is there any way to do this easily?
Many Thanks for any help, regards, Peter

 

[PruSQLer]

You could do this:

EXPORT TO C:\IBI\PGFTEMP\EXP_STAT.TXT OF DEL
SELECT SO_STATUS_NEXT_ID,
SO_STATUS_NEXT_DESC,
'$'
FROM E6DW_DATA.SOT_LU_STATUS_NEXT
WHERE SO_STATUS_NEXT_ID <= '999' WITH UR;

And then in the text file just do a REPLACE of "$" with $.

 

[PeterG5]

Hi PruSQLer,

Thanks for the helpful tip. Now I'm getting:
"501","Sales Quote Product Design","$"
"531","Awaiting Date Confirmation","$"
and I can, of course, manually edit the file.....
The problem is that this is part of an un-attended operation running overnight and I really need a way of not having the quotes at all - or, probably a long shot, maybe
finding some way of automating the change from "$" to $

Thanks Again,

Peter.

 

[sathyarams]

If you are on unix systems, you can use

sed 's/"//g' <filename>

to remove the double-quotes ... This will remove all double quotes from the file ...

BTW,the other option could be to use a select statement,

SELECT SO_STATUS_NEXT_ID||','||SO_STATUS_NEXT_DESC||','||'$'
FROM E6DW_DATA.SOT_LU_STATUS_NEXT
WHERE SO_STATUS_NEXT_ID <= '999' WITH UR

and redirect the output to the file you want ...

If you get spaces between the filed and the ',' you may use RTRIM ,

For eg :
RTRIM(SO_STATUS_NEXT_ID)||','|| ... etc

HTH


Cheers
Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

 

[gregsimpson]

Peter,

you don't need to have the double quotes. I posted this is in the Forum in the past.

John,

try something like the following

db2 export to c:\temp\employeeoutput of DEL messages
C:\temp\errors.txt select FNAME, SNAME from EMPLOYEE


I believe this will give you files of fixed length. I think it uses the length of the fields in the DDL to establish the format for the output files.

Therefore each record on the file should always look the same and you'll be able to reference it accordingly.

Greg


I'm sure you can take that and select your $ on the end. The full post is at

http://www.tek-tips.com/viewthread.cfm?SQID=408731&SPID=178&newpid=178&page=3

Cheers
Greg

 

[PeterG5]

Thanks Sathyaram/Greg,

Sorry to be a pain, but.....
When I try the first suggestion (SELECT within EXPORT), I get:
"501,Sales Quote Product Design,$"
"620,Sales Journal/Update,$"
- almost there!
Then I tried SELECT (not EXPORT).....
From the command prompt, it looked fine on the screen, i.e.
501,Sales Quote Product Design,$
620,Sales Journal/Update,$

-perfect!.... now to get it into a nice, clean file.....
I called the SQL again and tried to direct the output with:
DB2 - TVF EXPORT1.SQL >C:\IBI\PGFTEMP\TEST.TXT
It ran OK but what I got in the TEST.TXT file was one huge long line with the connect info, the SQL command itself, the actual output and the disconnect info......

So near and yet fo far.....

 

[gregsimpson]

Peter,

can you post the 2 scripts please.

Cheers
Greg

 

[PeterG5]

Hi Greg,

Command 1:
DB2 - TVF EXPORT1.SQL >C:\IBI\PGFTEMP\TEST.TXT
EXPORT1.SQL script:
SELECT SO_STATUS_NEXT_ID || ',' || SO_STATUS_NEXT_DESC || ',' || '$'
FROM E6DW_DATA.SOT_LU_STATUS_NEXT
WHERE SO_STATUS_NEXT_ID <= '999' WITH UR;
- this gives the big long line in TEST.TXT, as follows:

connect to e6dwprod user e6dw_data using Database Connection Information Database server = DB2/NT 8.1.5 SQL authorization ID = E6DW_DAT... Local database alias = E6DWPROD SELECT SO_STATUS_NEXT_ID || ',' || SO_STATUS_NEXT_DESC || ',' || '$' FROM E6DW_DATA.SOT_LU_STATUS_NEXT WHERE SO_STATUS_NEXT_ID <= '999' WITH UR 1 ------------------------------------ 501,Sales Quote Product Design,$ 540,Print Picklists,$ 560,Shipment Confirmation,$ 581,Specialised Invoicing,$ 620,Sales Journal/Update,$ 550,Print Delivery Note & Bx Label,$ 904,Backorder in Ship. Conf.,$ 922,Added in Work Order Generation,$ 984,Canceled in Ship. Conf.,$ 502,Sales Quote with PDC,$ 585,Print Interbranch Invoice,$ 500,Sales Quote Created,$ 902,Backorder in Commitments,$ 914,Added in Ship. Conf.,$ 982,Canceled in Commitments,$ 503,Sales Quote for Sales Dept,$ 600,Invoice Journal,$ 900,Backorder in S/O Entry,$ 912,Added in Commitments,$ 980,Canceled in Order Entry,$ - , Not Defined,$ 508,Sales Quote Development,$ 510,Sales Quote Pricing Dept.,$ 511,Sales Quote for Approval,$ 513,Quote Approved.,$ 518,Printed Sales Quotes,$ 531,Awaiting Date Confirmation,$ 532,Undated Order Status,$ 536,Awaiting Work Order Completion,$ 545,Picking Confirmation,$ 549,Assign Waybill Number,$ 580,Print Invoices,$ 595,Consignment Receipting,$ 999,Complete - Ready to Purge,$ 520,Enter Sales Order,$ 35 record(s) selected. connect reset DB20000I The SQL command completed successfully.

Command 2:
DB2 - TVF EXPORT2.SQL
EXPORT2.SQL script:
EXPORT TO C:\IBI\PGFTEMP\EXP_STAT.TXT OF DEL
SELECT SO_STATUS_NEXT_ID || ',' || SO_STATUS_NEXT_DESC || ',' || '$'
FROM E6DW_DATA.SOT_LU_STATUS_NEXT
WHERE SO_STATUS_NEXT_ID <= '999' WITH UR;
- this gives, in the EXP_STAT.TXT file,
"501,Sales Quote Product Design,$"
"536,Awaiting Work Order Completion,$"
etc., etc.
Thanks, Peter.

 

[gregsimpson]

Peter,

could you not use a file type of ASC, instead of DEL. It will then be fixed format, rather than comma delimited but gets rid of your quotes I believe.

Will Web-Focus accept such a file? Also have you confirmed it's not happy with the "quoted" file?

http://www.cs.yorku.ca/~seela/db2/import.html

Cheers
Greg

 

[PeterG5]

Greg,

Thanks for your perseverence.

1. When importing a comma-delimited file, Focus (a) needs the fields to be comma separated (b) will handle single quotes (or no quotes) around fields and (c) needs a ,$ at the very end as an end-of-record indicator.

2. It can handle 'fixed' files also - files with fields in regular columns, which are parsed using a file description.
I've tried this as well but can't seem to get an output file of type 'ASC', even though I've coded 'OF ASC' and 'TO ASC'. I get error messages when I do this and I'm note sure if my DB/2 SQL supports it or maybe I've just got the syntax wrong.

Regards, Peter.

 

[sathyarams]

Can you put this script in a bat file ...
As an example , create a file C:\statements.bat

db2 connect to sample > C:\temp\1.out
db2 -x "select * from syscat.triggers" > C:\temp\myfile.del
db2 terminate > C:\temp\1.out

and then from a command line do

db2cmd /i /w /c statements.bat

then you will get your output in myfile.del and the 'junk' stuff in C:\temp\1.out which you can ignore ...

HTH

Sathyaram


For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

 

[MarcLodge]

Peter,
I'm going off at a slight angle here, but I used to do Focus a few years ago (about 15 actually!) and recall that it was very powerful at getting hold of and processing data. I had a feeling that there was a function that dealt with this sort of thing within Focus, as I seem to recall similar problems with IMS databases.

I therefore had a look on the Information Builders website, and found some stuff on STRTOKEN which looks as if you may be able to do something from within WebFocus.

Please accept my apologies if you have already investigated this option and found that it is not feasible. It was an interesting trip down memory lane for me though!

The reference I found was here:

http://documentation.informationbui...ml/html_wf_52/wf_funct_520/maintainchar18.htm

Hope this might help
Marc