Replacing table values with txt file VFP

Question

im trying to replace a phc table with information from a txtfile but im not getting the information on the screen.

re is the table i want to replace the values txtcabecalho is the imported file that people have to choose reciboc is the cursor that i've created to store the values from the txt file so i can replace them on re and show them to the screen sre is the screen of the table re.

    Local txtcabecalho
Use re
Delete all
txtcabecalho=getfile("txt")

Create cursor reciboc(rno n(10), rdata d(10), moeda c(3), no n(10), nome c(2), ccusto c(20), intid c(10), clbanco c(20), total float(19), totalmoeda float(19), ndoc n(3))


append from (txtcabecalho) delimited with tab
select reciboc
goto top
scan for !empty(reciboc.rno)

select re
append blank
replace re.rno with reciboc.rno 
replace re.rdata with reciboc.rdata
replace re.moeda with reciboc.moeda
replace re.no with reciboc.no
replace re.nome with reciboc.nome
replace re.ccusto with reciboc.ccusto
replace re.intid with reciboc.intid
replace re.clbanco with reciboc.clbanco
replace re.total with reciboc.total 
replace re.totalmoeda with reciboc.totalmoeda 
replace re.ndoc with reciboc.ndoc
select re
endscan

sre.refresh()

Thanks!

Updated

So this actually works if i crate a file of my own with the delimitianion i choose for example with tabs. Thi thing is the files that i need to import are like using spaces as spaceholders and got no delimitations from each other. Let me explain better. for example first 3 fields rno and rdata and moeda.

The txt files come like 22014-12-23EUR

Where 2 is the rno but has 9 spaces behind working like place holders for bigger numbers making the rno(10) Next is 2014-12-23 wich is right next to the rno with no delimitation wich does the rdata(10) Next is the moeda wich is EUR , again with no delimitation , being the moeda(3)

Many fields will be blank , and those the txt files come with spaces ocupying full length of the fields. Basically, field length is used as delimitation, and when empty , its filled with spaces.

Did u understood right? sorry im not an english native.

update

heres is some of the txt file 22014-11-27EUR 208 799,00 799,00 00

just keep in mind that before the first number 2 , there are 9 spaces. Thanks!

the rdata field wich is going to be filled with 2014-11-27 is rdata d(10)

edited

currently code:

Local txtcabecalho, fileconvert
Use re
zap
txtcabecalho=getfile("txt")
Create cursor reciboc(rno n(10), rdata d(10), moeda c(3), no n(10), nome c(2), ccusto c(20), intid c(10), gg c(20), chq c(20), clbanco c(20), total float(19), totalmoeda float(19), ndoc n(3))

fileconvert = filetostr(txtcabecalho)
chrtran(fileconvert,'-','')
strtofile(fileconvert, txtcabecalho)

append from (txtcabecalho) type sdf
scan 
select re
append blank
replace rno with reciboc.rno 
replace rdata with reciboc.rdata
replace moeda with reciboc.moeda
replace no with reciboc.no
replace nome with reciboc.nome
replace ccusto with reciboc.ccusto
replace intid with reciboc.intid
replace clbanco with reciboc.clbanco
replace total with reciboc.total 
replace totalmoeda with reciboc.totalmoeda 
replace ndoc with reciboc.ndoc
select re

endscan
select reciboc
browse title "resultado"

reindex
sre.refresh()

what i get is this is what i get

stil ltrying to figure out i really don't know aff

for example : firt row should be:

RNO 2 RDATA 2014-11-27 NO 208

EDITED

actually this made it ahah

fileconvert = filetostr(txtcabecalho)
fileconvert2 = strtran(fileconvert,'-','')
strtofile(fileconvert2, txtcabecalho)

if i need further help i'll update my question! Thank you so much guys!

EDITED

Doyou guys know if there is any problem converting from txt file float numbers to cursors?

I got for example 799,20 but i just show up as 799. Data type is set to totalmoeda f(19) . This must be pretty simple but i can't find it!

Answer 1

You are doing a SCAN loop which implies many rows from the reciboc table. However, in your "re" table, you just keep overwriting the same row with the same values, and if no record on file, you will have nothing anyhow. If you want to add them, you need to append to the "RE" table.

Now, another consideration is the "rno" column. Is that supposed to be an "ID" column such as your external text file may have updated values for each given "RNO" and you want to update if it does not exist yet, or add if it does NOT exist in the "re" table?

UPDATE for your question with tags and extra clarified elements...

If you have an index on you "RE" table on the "RNO" column, great. If not, and that is the key column that you want to update based on, I suggest creating an index on it...

From the VFP command window, build the index so it is always available

use RE exclusive
index on RNO tag RNO
use

Now, in your code, AFTER Your Append from delimited, and your cursor is prepared... I would set a relationship between the live table and your temp cursor and do the update like...

select reciboc index on RNO tag RNO && so the cursor has an index too.

selece RE set relation to RNO into RECIBOC

Now, the two tables are pointing to each other for whatever records match in each table. Now, replace FOR [scope]. Due to the relationship, as the records are cycled in the "RE" table, it will point to the same RNO record in the "RECIBOC" table if one exists. So the FOR condition is testing for NOT EOF() (end of file = no match found in RECIBOC. So NOT EOF() means there IS a record found.

replace rdata with reciboc.rdata,;
        moeda with reciboc.moeda,;
        no with reciboc.no,;
        nome with reciboc.nome,;
        ccusto with reciboc.ccusto,;
        intid with reciboc.intid,;
        clbanco with reciboc.clbanco,;
        total with reciboc.total,;
        totalmoeda with reciboc.totalmoeda,;
        ndoc with reciboc.ndoc ;
   FOR NOT EOF( "RECIBOC" )

finally, get any NEW "RNO" records into your "RE" table. Get all records NOT in your existing RE table based on matching "RNO" column.

select * ;
   from RECIBOC ;
   WHERE RNO NOT IN ( select RNO from RE );
   into cursor NewRecordsToAdd readwrite

Now, add them to the "RE" table. Since the columns are the same name, they will come over directly without explicit column name from/to reference.

select RE
append from dbf( "NewRecordsToAd" )
use in select( "NewRecordsToAdd" )   

Answer 2

Create Cursor reciboc(rno N(10), rdata d(10), moeda c(3), no N(10), ;
    Nome c(2), ccusto c(20), intid c(10), clbanco c(20), ;
    Total Float(19), totalmoeda Float(19), ndoc N(3))

Append From (m.txtcabecalho) Type Delimited With Blank

Select re
Append from (Dbf('reciboc'))

Above is the shorter version for what you are trying to do.

Answer 3

In your update what you are describing is a Fixed Width Data format or what is known as System Data Format (SDF). Then do not use delimited but SDF as the type:

Create Cursor reciboc(rno C(10), rdata C(10), moeda c(3), no C(10), ;
    Nome c(2), ccusto c(20), intid c(10), clbanco c(20), ;
    Total C(19), totalmoeda C(19), ndoc C(3))

Append From (m.txtcabecalho) Type SDF

alter table reciboc alter column rdata d && convert rdata column to date
* do other needed column type conversions

Select re
Append from (Dbf('reciboc'))

The sizes you use when creating your cursor should match with the sizes of data columns in your text file. After getting the whole data as text, alter columns to match the data types (I assumed your sizes were right) - d(10) was meaningless, and float is never a suggested type.

PS: If you can find FoxyClasses then it does have an importer class in it which handles much of the work for you (unfortunately I lost the server where I was serving the download). You can read the details in its chm file if you can find.

Answer 4

With exactly 9 spaces before 2 it works for me:

Create Cursor reciboc(rno C(10), rdata C(10), moeda c(3), no C(10), ;
    Nome c(2), ccusto c(20), intid c(10), clbanco c(20), ;
    Total C(19), totalmoeda C(19), ndoc C(3))

Append From (m.txtcabecalho) Type SDF
browse title "Before data type change"

Set Date YMD
Set Century ON

alter table reciboc alter column rdata d && convert rdata column to date
browse title "After date type change"

In case of SDF, keep in mind positions in text file specify field widths.