Home » Developer & Programmer » Forms » Creating text file from Forms 6i
Creating text file from Forms 6i [message #399647] Thu, 23 April 2009 05:15 Go to next message
mubeenmoin
Messages: 15
Registered: April 2009
Location: KUWAIT
Junior Member
With below written code i can create TEXT no problem n can even get data in that
My problem is when i view tht TEXT FILE all the columns values are stuck therez no space between them and even when it start with new record it continus in same line


DECLARE
OUT_FILE TEXT_IO.FILE_TYPE;
X QUANTITY_IN_STORES%rowtype;
ITEM VARCHAR2(10);
QUANTITY VARCHAR2(10);
QTY_IN_PIECE VARCHAR2(10);

Cursor C1 IS SELECT ITEM_UID,QTY,QUANTITY_IN_PIECE FROM QUANTITY_IN_STORES;
BEGIN
OUT_FILE := TEXT_IO.FOPEN('C:\STORES.TXT','W');

FOR X IN C1
LOOP

ITEM := NVL(X.ITEM_UID,' ');
TEXT_IO.PUT(OUT_FILE,ITEM);

QUANTITY := NVL(X.QTY,' ');
TEXT_IO.PUT(OUT_FILE,QUANTITY);

QTY_IN_PIECE:= NVL(X.QUANTITY_IN_PIECE,' ');
TEXT_IO.PUT(OUT_FILE,QTY_IN_PIECE);

END LOOP;
TEXT_IO.FCLOSE(OUT_FILE);
MESSAGE('Text File Generated..');

END;


OUTPUT I AM GETTING IN TEXT FILE WHEN I OPEN IS AS BELOW

1178.5167421305000

This are 2 records first record 1178.51674 and second record 21305000

i am trying to get in this format
1 178.5 674
2 130 5000
Re: Creating text file from Forms 6i [message #399670 is a reply to message #399647] Thu, 23 April 2009 07:31 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
To Get multiple lines it's easiest to build up a string that represents a whole line then use text_io.put_line.

To put spaces between items you'd concatenate a space in.

And next time you post code could you use code tags please - details of how to do this can be found in the forum guide.

And don't declare record types for cursor FOR LOOPs - oracle doesn't use them.
Re: Creating text file from Forms 6i [message #399725 is a reply to message #399670] Thu, 23 April 2009 14:09 Go to previous messageGo to next message
mubeenmoin
Messages: 15
Registered: April 2009
Location: KUWAIT
Junior Member
thank you appreciate for your reply i tried doing wat u hd mentioned but i doesnot works with me
i have the codes written above can u modify that and get me
Re: Creating text file from Forms 6i [message #399855 is a reply to message #399647] Fri, 24 April 2009 04:58 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Do you need a data like that


Quote:
6009,THREAD C 7929,64,,
6010,THREAD C 7969,64,,
6011,THREAD C 9760,64,47,
6012,THREAD C 8570,64,21,
6013,THREAD C 8940,64,23,
6014,THREAD C 9750,64,,
6015,THREAD C 9911,64,6,
6016,THRAD C 9493,64,20,
6017,THREAD C 7953,64,,
6018,THREAD C 7932,64,7,
6019,THREAD C 7939,64,,
6020,THREAD C 9770,64,50,
3010,THREE PIN PLUG,21.5,15,



Re: Creating text file from Forms 6i [message #399857 is a reply to message #399725] Fri, 24 April 2009 05:06 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
mubeenmoin wrote on Thu, 23 April 2009 20:09
thank you appreciate for your reply i tried doing wat u hd mentioned but i doesnot works with me
i have the codes written above can u modify that and get me



Post what you tried (in code tags) and the output you got and we'll try and show you what you're doing wrong.
Re: Creating text file from Forms 6i [message #399858 is a reply to message #399647] Fri, 24 April 2009 05:10 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member

Do you need import data or Export data ?

if you need to export data with CVC file i suggest its will take lot of time if your record exceed 10000



Shahzaib Ismail
Re: Creating text file from Forms 6i [message #399863 is a reply to message #399647] Fri, 24 April 2009 05:27 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
I create Export data with that querry


Quote:


Declare
v_File Text_IO.File_Type;
v_Rows Number := 0;
Cursor TxtIO is
Select item_no,item_name,item2.rate iterate,item2.quantity iteqty,item2.trans_item itetrans,purchase_date,
item_no_pur,item_name_pur,purchase2.challan_no purcha,supplier_id_pur,supplier_name_pur,
purchase2.quantity purqty,purchase2.rate purrate,purchase2.amount puramt,trans_pur,
saledate,item_no_sale,item_name_sale,sale2.challan_no slacha,sale2.quantity slaqty,sale2.rate slarate,
sale2.amount slaamt,
customer_id_sale,customer_name_sale,trans_sale,
customer_id,customer_name,trans_cust,customer_tel_no,customer_address,customer_contact_person,customer_fax_no,customer_contact_no,
supplier_id,supplier_name,supplier_tel_no,supplier_address,supplier_fax_no,contract_no_person
from item2,purchase2,sale2,customer,supplier;
v_filename VARCHAR2(256);
Begin
v_filename := GET_FILE_NAME(File_Filter=> ' TXT Files (*.txt)|*.txt| CSV Files (*.csv)|*.csv| ALL Files (*.*)|*.*|',
Dialog_Type=> Save_FIle,File_Name=> 'Backup.txt', Directory_Name=> 'C:\');
if v_FileName is not Null then
v_File := Text_IO.FOpen(v_FileName,'W');
For TXIo in TxtIo Loop
v_Rows := v_Rows + 1;
Text_Io.Put(v_File,TxIO.item_no||','||TxIO.item_name||','||TxIo.iterate||','||TxIo.iteqty||
','||TxIo.itetrans||','||TxIo.purchase_date||','||TxIO.purqty||','||TxIo.purrate||','
||TxIo.puramt||
','||TxIo.trans_pur||','||TxIo.item_no_pur||','||TxIO.item_name_pur
||','||TxIo.purcha||','||TxIo.supplier_id_pur||
','||TxIo.supplier_name_pur||
','||TxIo.saledate||','||TxIo.item_no_sale||','||TxIO.item_name_sale
||','||TxIo.slacha||','||TxIo.slaqty||
','||TxIo.slarate||','||TxIo.slaamt||','||TxIO.customer_id_sale
||','||TxIo.customer_name_sale||','||TxIo.trans_sale
||','||TxIo.customer_id||','||TxIo.customer_name||
','||TxIo.trans_cust||','||TxIo.customer_tel_no||','||TxIO.customer_address
||','||TxIo.customer_contact_person||','||TxIo.customer_fax_no
||','||TxIo.customer_contact_no
||TxIo.supplier_id||','||TxIo.supplier_name||','||TxIO.supplier_tel_no
||','||TxIo.supplier_address
||','||TxIo.supplier_fax_no ||','||TxIo.contract_no_person

);
Text_IO.New_Line(v_File);
End Loop;
Text_IO.FClose(v_File);
Message('Data exported in File '||v_FileName||' with '||v_Rows||' rows');
set_item_property('BTN_IMP', enabled, PROPERTY_TRUE);
End If;
End;




In bold corrector its a select statement in these statement you write a column name which you need to export and in skyblue color write your column name after TxIo.your column name.

also create a expdata Procedure Bodu

Quote:
PROCEDURE ExpData(p_BlockName in Varchar2, p_FileName in Varchar2) IS
v_File Text_IO.File_Type;
v_Item_Val VARCHAR2(500);
v_Last_Item VARCHAR2(30);
v_First_Item Varchar2(30);
v_Rows Number := 0;
Begin
If p_FileName is Not Null then
v_File := Text_IO.FOpen(p_FileName,'W');
GO_BLOCK(p_BlockName);
GO_RECORD(1);
v_last_item:=p_BlockName||'.'||GET_BLOCK_PROPERTY(p_BlockName,LAST_ITEM);
v_first_item:=p_BlockName||'.'||GET_BLOCK_PROPERTY(p_BlockName,FIRST_ITEM);
LOOP
GO_ITEM(v_first_item);
v_Rows := v_Rows + 1;
LOOP
v_Item_Val := v_Item_Val||Name_In(:SYSTEM.CURSOR_ITEM)||',';
If :System.Cursor_Item = v_Last_Item then
v_Item_Val := Rtrim(v_Item_Val,',');
Exit;
End If;
Next_Item;
End Loop;
Text_IO.Put_Line(v_File,v_Item_Val);
v_Item_Val := Null;
EXIT WHEN
:SYSTEM.LAST_RECORD = 'TRUE';
Next_Record;
END LOOP;
Text_IO.FClose(v_File);
Message('Data exported in File '||p_FileName|| ' with '||v_Rows||' Rows');
End if;
End;


This will export your data in Text format,Csv format

But i suggest if you want to convert under then 1000 record this will help you other wise its will take time and size is well


Hope this will help you


Thanks


Shahzaib Ismail
Re: Creating text file from Forms 6i [message #400005 is a reply to message #399647] Fri, 24 April 2009 23:55 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Quote:
Text_Io.Put(v_File,TxIO.item_no||','||TxIO.item_name||','||TxIo.iterate||','||TxIo.iteqty||
','||TxIo.itetrans||','||TxIo.purchase_date||','||TxIO.purqty||','||TxIo.purrate||','
||TxIo.puramt||
','||TxIo.trans_pur||','||TxIo.item_no_pur||','||TxIO.item_name_pur
||','||TxIo.purcha||','||TxIo.supplier_id_pur||
','||TxIo.supplier_name_pur||
','||TxIo.saledate||','||TxIo.item_no_sale||','||TxIO.item_name_sale
||','||TxIo.slacha||','||TxIo.slaqty||
','||TxIo.slarate||','||TxIo.slaamt||','||TxIO.customer_id_sale
||','||TxIo.customer_name_sale||','||TxIo.trans_sale
||','||TxIo.customer_id||','||TxIo.customer_name||
','||TxIo.trans_cust||','||TxIo.customer_tel_no||','||TxIO.customer_address
||','||TxIo.customer_contact_person||','||TxIo.customer_fax_no
||','||TxIo.customer_contact_no
||TxIo.supplier_id||','||TxIo.supplier_name||','||TxIO.supplier_tel_no
||','||TxIo.supplier_address
||','||TxIo.supplier_fax_no ||','||TxIo.contract_no_person



Replace ||','|| to ||' '||

after that you will see your record which you mansion earlier

Thanks


Shahzaib Ismail
Re: Creating text file from Forms 6i [message #400093 is a reply to message #399647] Sun, 26 April 2009 00:20 Go to previous message
mubeenmoin
Messages: 15
Registered: April 2009
Location: KUWAIT
Junior Member
thank you everyone it worked wat i wz luking for
thank you a lot
Previous Topic: Function vs. Procedure
Next Topic: SSO
Goto Forum:
  


Current Time: Wed Dec 07 22:46:16 CST 2016

Total time taken to generate the page: 0.08899 seconds