Read data from text file and insert into datawindow and then into databse

Hi All,

I have a text file with only one column. I want to read it and insert data into database temp table. Then based on the indserted values in need to get data from different tables including temp table.

What is the best way to do this?
can i make a join statment with temp table?

Thanks,
Abhishek
vjabhiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sajuksCommented:
How many rows will the text file contain ? You could directly insert the txt file using the importfile ( either in a datastore or datawindow).Then check the values.
Are you planning to do this in a stored procedure ? You should be able to do the joins as needed.
0
sajuksCommented:
You could also do  a FileRead.Check the example in PB help file
0
vjabhiAuthor Commented:
There are going to be around 1000 rows but only one column.
i tried following, but this doesnt seems to work as return value i get is 0.

long ret
ret = dw_1.ImportFile ("C:\custextid.txt",1,1000,1,1,1)

Messagebox("rownum",String(ret))

Thanks,
Abhishek
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

sajuksCommented:
You want to import the whole data dont you ? then just use
ret = dw_1.ImportFile ("C:\custextid.txt")
Messagebox("rownum",String(ret))
0
diasroshanCommented:
HI Abhi,

another alternative is ...

long ret
ret = dw_1.ImportFile ("")

Messagebox("rownum",String(ret))

passing a null filename will prompt u to select a file.... select ur file and all the rows from ur file will get imported into the dw... all other arguments are ignored hence no point in passing them...

Let us know if u need more assistance...

Cheers,
Rosh
0
vjabhiAuthor Commented:
Hi Rosh

I tried the way u said but mesage displayed is 0. It never prompted to select any file.

Thanks,
Abhishek
0
sajuksCommented:
string ls_null

SetNull(ls_null)
dw_1.ImportFile(ls_null)
0
vjabhiAuthor Commented:
still messagebox prints 0
0
sajuksCommented:
Is your dataobject an external datawindow or based on some table
0
vjabhiAuthor Commented:
Hi Got it working..thanks..
now i have id in the data window1, now i want write a query which will result me data based on all these id in datawindow1

Thanks,
Abhishek

0
sajuksCommented:
Use the id as an retrieval argument and build the dataobject accordingly.
For eg the sql might look like select fname,lname from table where id = :al_id
where al_id is the passed argument obtained from the file import.
Then just do a dw_1.Retrieve(id)
0
vjabhiAuthor Commented:
Hi Saju..
thanks for help..
can u tell me how can i create temp table at run  time and insert all the data which i read from text file to datawindow into temp table.

Can define then another datawindow which takes data from some tables and this temp table?

Thanks,
Abhishek
0
sajuksCommented:
Create a permanent temporary table.Let the dw_1 dataobject be based on this temp table.
Then when you import the data to the datawindow you could directly update the dw itself issuing a lds_datastore.Update.
(prior to calling import file ensure that the table records are deleted ).  Now you could create another dataobject which has a join between the temp permannet table and the other permanent table.



0
vjabhiAuthor Commented:
Hi Saju,

i am trying following,
ret = dw_getextid.ImportFile(ls_nll)

If ret > 0 Then
    lUpdateStatus = dw_getextid.Update()
   if lUpdateStatus = 1 then
      commit using SQLCA;
   else
     
      RollBack using SQLCA;
      MessageBox( "Update", "Error in update" + &
                  "Error Message: " + SQLCA.SqlErrText )
      return 0
   end if

End if

I get error message saying, Datawindow doesnt have UPDATE capability.
what could be reason for this?

Thanks,
Abhi
0
sajuksCommented:
In the dataobject > rows > update properties > is it checked ?
The table needs to've a pk to allow update.
0
vjabhiAuthor Commented:
Hi,
I cant see any property like that.
i am using PB 6.5.
Table have only one coulmn and i have put index on that.
please suggest

Thanks,
Abhi
0
sajuksCommented:
Where did you check ? this is in the datawindow painter where you create the datawindow .
In the data window painter menu select Rows,Update Properties and
check the AllowUptades checkbox.
0
vjabhiAuthor Commented:
Hi,

can u tell me what i am mising here,
ret = dw_getextid.ImportFile(ls_nll)

If ret > 0 Then
      disconnect;
      SQLCA.DBMS = sDbms
      SQLCA.ServerName = ServerName
      SQLCA.Database = dbName
      SQLCA.logid = UserId
      SQLCA.logpass = Password
      CONNECT USING SQLCA;
      dw_getextid.SetTransObject(SQLCA)
      lUpdateStatus = dw_getextid.Update()
   if lUpdateStatus = 1 then
      commit using SQLCA;
   else
     
      RollBack using SQLCA;
      MessageBox( "Update", "Error in update" + &
                  "Error Message: " + SQLCA.SqlErrText )
      return 0
   end if

Erro rmessage i get saying,
Database Transaction Information not avaliable.
call setTrans or SetTransObject method

Thanks,
Abhi
0
sajuksCommented:
//constructor event
       SQLCA.DBMS = sDbms
     SQLCA.ServerName = ServerName
     SQLCA.Database = dbName
     SQLCA.logid = UserId
     SQLCA.logpass = Password
     CONNECT USING SQLCA;
     dw_getextid.SetTransObject(SQLCA)


in the clicked of the button
ret = dw_getextid.ImportFile(ls_nll)
If ret > 0 Then
     lUpdateStatus = dw_getextid.Update()
   if lUpdateStatus = 1 then
      commit using SQLCA;
   else
     
      RollBack using SQLCA;
      MessageBox( "Update", "Error in update" + &
                  "Error Message: " + SQLCA.SqlErrText )
     
   end if
end if

in the destructor event of the datawindow you could do the
disconnect();

0
vjabhiAuthor Commented:
still same issue,

actually i have use disconnect as before this my  connection was to different databse.

Thanks,
Abhi
0
sajuksCommented:
Instead of connecting to sqlca create another instance of transaction and connect using that method.
something like
transaction ln_tr
ln_tr = create transaction
  ln_trDBMS = sDbms
     ln_trServerName = ServerName
     ln_tr.Database = dbName
     ----

connect using  ln_tr;
dw_1.update(ln_tr)
disonnct using ln_tr
destroy ln_tr

and see if that works
0
vjabhiAuthor Commented:
still same problem.
why this issue when u set transaction object

Thanks,
Abhishek
0
sajuksCommented:
what  is the retrun value for  dw_getextid.SetTransObject(SQLCA) or dw_getextid.SetTransObject(ln_tr).
0
diasroshanCommented:
hi Abhi,

in the code pasted by sajuks have u done the following

connect using  ln_tr;

//the following set trans object line
dw_1.SetTransObject(ln_tr)

dw_1.update(ln_tr)
disonnct using ln_tr
destroy ln_tr


Cheers,
Rosh
0
vjabhiAuthor Commented:
hi,

its working some connection issue were there.
one quick one...
how do i delete table records prior to calling import file.

Thanks,
Abhishek
0
sajuksCommented:
issue a delete statement .:-)
delete from tablename ;
0
vjabhiAuthor Commented:
example please...
is it same as update thing??
0
sajuksCommented:
Just issue a simple delete command. I assume that the table name ,temporary table , is tmp_forimport.
then just do a delete from tmp_forimport; before importing.

In the click of the button for import


delete from tmp_forimport;
ret = dw_getextid.ImportFile(ls_nll)
if ret > 0 ....

You could also delete the table when not needed ie in the close of the window
0
vjabhiAuthor Commented:
Hi Saju,

all working fine now. thanks for help
I am reading connection from ini file.
it reads sometime.. and doesnt read some time...
this started only after i added new scrren with code for update datawindow.
if i dont cal this window its reads connection from ini else it doesnt.

Thanks,
Abhi
0
sajuksCommented:
Can you pinpoint in which case its not reading from the ini file ? You'd mentioned earlier that there are two different databases
involved.You disconenct from one then you connect to another for the import screen.After the close of the import screen do you reconnect to the other database and close this db connection ?
0
vjabhiAuthor Commented:
my question is .. everytime i read values from ini file when user open login screen. and i fill database server field. if user changes databse server i store that information in ini file on ok click.
Reading from ini file should happen all the times corret.
but when i work on GUI apart from mport screen everything is working as i want. But while running application if i use import screen then and then some other screens og GUI this doesnt happen.. it doesnt read ini file.
to read ini file i am writing..
sDbms = ProfileString("connection.ini","ABHI","dbms","")
ServerName = ProfileString("connection.ini","ABHI","servername","")
DBase = ProfileString("connection.ini","ABHI","DatabaseName","")

Thanks,
Abhishek

0
sajuksCommented:
I've a doubt though not that sure, when you are calling the import file i assume you are allowing the user to select the file.That means he/she will be selecting the file location .Is it possible then that the path of your ini file is not found ?
Am not certain about it, instead of selecting the file hardcode it ( importfile(c:\anc.txt) ) and see if the error is still there.
If yes then you need to specify  the current directory again.See if that helps.
0
vjabhiAuthor Commented:
Hey!!
u are right...
i am putting all dll, ini and exe in one folder.
how can i set current folder to this one as soon as user open application

Thanks,
Abhishek
0
sajuksCommented:
If you check in windows 98 iyou wont've this problem cos i believe it started with windows 2000 and such.
Also with pb9 and above you got a flag which you could set as false ( to indicate not to change the current directory).

What you could do is before selecting the  folder use DirList and a hidden listbox and static text to get
the name of the current directory and save it in a variable.After your import ( or update ) use DirList again with the
previously saved directory to set it back to the original directory.

something like this
Boolean Function wf_Get_directory( string as_pathname  by reference )
// Return the current directory pathname.
boolean lb_rtn

lb_rtn = lb_cache.DirList( "*.!!!", 1, st_cache )
as_pathname = st_cache.Text
RETURN lb_rtn

Boolean Function wf_Set_directory( string as_pathname  )
// Set the current directory.
boolean lb_rtn
string ls_directory

ls_directory = as_pathname + "\"
lb_rtn    = lb_cache.DirList( ls_directory, 0 )
RETURN lb_rtn

or USE API call
(1) Declare the external function:
Function Boolean of_SetCurrentDirectory32(String s_path) library "kernel32.dll"
alias for "SetCurrentDirectoryA"

(2) Use the external function:
lb_success = of_SetCurrentDirectory32("c:\mydir")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vjabhiAuthor Commented:
Thnaks ..
anyway i have made restiction to select file from ini file location.
Is it possible to click on listview coulm and sort based on that??

thanks,
Abhishek
0
sajuksCommented:
How would that help here ? Can you explain more ?
0
vjabhiAuthor Commented:
well.. as user will be running exe .. where i have ini file.. and the file  which i am importing so path will be same.
its just another query....
Is it possible to click on listview coulm and sort based on that column clicked??

thanks,
Abhishek
0
sajuksCommented:
another query another question ;-)
 Thats EE rules.
and yes it is possible to sort listview , check its function in the help file.
0
sajuksCommented:
Glad it got all sorted out.
Thanks for the points and grade.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.