Flat File to Existing Table

[vafishing]

Is it possible to take a flat file in a .txt or .tab format and have it opened in an existing table in powerbuilder. I have an existing window object and now I want to read a flat file into the existing window object. As I add new records I need to be able to count the number of new records in the window object. Has anyone done a script similar to what I am asking. I have experimented with something like

GotFileName = GetFileOpen()
CustomerFile=FileOpen(filepath)
ReadResult=FileRead(CustomerFile,CustomerRecord)

I looked for a website that gives example code for opening a flat file to a window object but was unsuccesful

 

[wsmall73]

Yeah we do this all the time. A tab delimited text file is very simple to import into a datawindow and subsequently saved into your database table. Define your columns for your datawindow based on your flat file and then use
dw_employee.ImportFile("D:\EMPLOYEE.TXT&quot (straight out of the help file) to import the file. You can also use import string if you need to do some more strigent error checking upon import. You can use the below to select the file to import.

string docname, named
integer value
value = GetFileOpenName("Select File", &
+ docname, named, "DOC", &
+ "Text Files (*.TXT),*.TXT," &
+ "Doc Files (*.DOC),*.DOC&quot

IF value = 1 THEN FileOpen(docname)

hope this helps.

 

[vafishing]

wssamll73, Thanks, I have started on this. I built a dw called dw_customer with the following: LName,FName,Address,City,State,Zip,SourceID. I have pulled the .txt file from its web location, saved it in my local folder name ccCustomer. The .txt file is named Customer1 The path would be c:/ccCustomer/Customer1. I need to account for duplicates so if the source ID pulled from the .txt file matches a sourceID already in the dw it should not pull the record from the flat file. I also need to account for blank spaces. Based on the code you provided correct me if I am wrong. Would the code go as follows:
string docname, named
integer value
value = GetFileOpenName("Select File", &
+ docname, named, "Customer1", &
+ "Text Files (*.TXT),*.TXT," &
+ "Doc Files (*.DOC),*.DOC&quot

IF value = 1 THEN FileOpen(docname)
Also since I am sort of a newbie at pulling flat files to a dw, where in the dw would the above code go (ie in the doubleclicked area?)

 

[wsmall73]

I personally would handle the duplicates when you save the records from the database to the server.This will ensure that if they load the same file a second time that the records won't be inserted into the database. Are you using SQL Server for your back end? (Use a stored procedure to insert the records checking for dupes).

Your quite a ways off here... your code is going to look something like

In the clicked event of a button

//This snippet of code will provide you with a window to
//select the text file that you wish to load..
//It will saved the file name in the variable docname...

string docname, named, ls_one_line
integer value, i_ret, i_ret_one
value = GetFileOpenName("Select File", &
+ docname, named, "DOC", &
+ "Text Files (*.TXT),*.TXT," &
+ "Doc Files (*.DOC),*.DOC&quot
if value <=0 then return

//Two options now... pull the entire file into the
//datawindow and handle dupes in your insert into the
//database...using the following

i_ret=dw_customer.ImportFile(docname)
If i_ret=<= 0 Then
Messagebox('ERROR','Error importing file'+String(docname),Exclamation!)
return
ELSE
Messagebox('SUCCESS','File import complete!',Exclamation!
END IF

//The second option is to open the file and read
//individuals lines... more difficult in my opinion but does
//have use in other less standard imports

i_ret=FileOpen(docname,LineMode!,Read!,LockRead!)

//See help file for fileread...
DO While FileRead(i_ret,ls_one_line) <> -100
i_ret_one=dw_customer.ImportString(ls_one_line)
//Error check return codes...
//-1 startrow value supplied is greater than the
//number of rows in the string
// -3 Invalid argument
// -4 Invalid input
// -9 PowerBuilder or the user canceled import
//because data failed validation

LOOP