Fix a Text file for Importing

[MoGrace]

I am trying to automate the conversion of this file to import into a table, and the yellow highlighted area has me stumped. Also the 2 red areas are columns that need to be removed.

I have the part for removing the header and footer sections but could use some help on what to do with the section where the fields run together.

NSI_INV2.txt

sqlrep.jpg

[Allen Klimeck]

Re: Fix a Text file for Importing

You could use strtran() to change "NET 30" to "NET_30 " and use word() to parse out each word you need from each line.

remove header and footer
strtran
read each line
alltrim
check if the word NET is in the line
word(line of text,1) get word 1
do the same for the next 2 words
word(line of text,-1," ") get last word
do the same for the next 3 words if the word NET is in the line or next 2 words if it isn't

Allen

[MoGrace]

Re: Fix a Text file for Importing

That's not a bad idea to get the terms as one word. I was thinking that adding a space after that would be helpful in finding the next column break. Also I could count the characters after finding "NET" and insert a space. Lemme try it...

[MoGrace]

Re: Fix a Text file for Importing

Here is what I have so far - the variable spacing is the killer...

dim in_file as c
dim in_str as c
dim newstr as c = ""
dim lncnt as n
dim lno as n
dim buf as p

in_file = a5.Get_Path()+chr(92)+"sql_rep.txt"
in_str = get_from_file(in_file)
lncnt = line_count(in_str)
'remove the footer first then the header then the blank lines
in_str = *line_remove(in_str,lncnt-3,4)
in_str = *line_remove(in_str,1,12)
in_str = remove_blank_lines(in_str)
lncnt = line_count(in_str)
lno = 0

buf = stringscanner.create(in_str)

REPEAT:
if lno = lncnt then
	showvar(newstr)
	end
end if
lno = lno+1
buf.GotoLineStart(lno)

if buf.GetLineText() = "" then
	goto REPEAT
end if

buf.Delete(6)
buf.ScanOver(10)
buf.Replace(";")
buf.ScanOver(8)
buf.Replace(";")
buf.Delete(7)
buf.SkipOverWhitespace()
buf.ScanOver(4)
buf.Replace(";")
buf.ReplaceString("NET 30",";NET 30;")
buf.SkipToCharacter("$")
buf.Replacestring("$",";")
buf.SkipToCharacter("$")
buf.ReplaceString("$","")
buf.Delete(9)
newstr = buf.GetToOffset()+buf.GetRemainder()
goto REPEAT

And here is the result:

160002    ;02/05/16; BNT ;4787    ;NET 30;19,208.46 ;6,773.18  
160003    ;02/05/16; FTC ;20688    ;NET 30;4,312.49 ;1,276.82  
160004    ;02/05/16; IQT ;3598    ;NET 30;13,859.66 ;4,157.90  
160005    ;02/12/16;BNT ;4787    ;NET 30;12,335.96 ;4,377.85  
160006    ;02/12/16; FTC ;20693    ;NET 30;4,481.75 ;1,211.96  
160007    ;02/12/16;IQT ;3598    ;NET 30;3,378.14 ;1,013.44  
160008    ;02/12/16;JUS ;029216    ;NET 30;800.13 ;400.07 
160009    ;02/12/16;SDI ;1614    2,039.90 ;815.96

Since I only need 6 of the 7 fields, I can just skip the mapping for #4 and use alltrim on #3 to remove the leading or trailing spaces. Unfortunately this report doesn't show that field #3 may have as many as 6 characters and not just the 3 shown here. Maybe someone has an idea on how to handle that?

Even so, I am open to a better way to do this!

I changed the name of the original file saved from Internet Explorer in case I needed to run it again, otherwise it is the same data. Note the last entry is missing the TERMS, and will need to be edited before importing.

[Tom Henkel]

Re: Fix a Text file for Importing

This may be taking you in a whole different direction, but we use a program called "Monarch" to "read" reports and then export data to a csv or dbf based on what areas of the report you want to grab data from. I honestly don't know the cost, but it is very powerful and does a nice job of extracting the information. We use it every day.

[GGandhi]

Re: Fix a Text file for Importing

I played with the downloaded file with this code

dim infile as c
infile = "c:\users\govindan\downloads\NSI_INV2.txt"
dim outfile as c
outfile = "c:\users\govindan\downloads\NSI_INV3.txt"
dim txt as c
file_pointer = file.open(infile,FILE_RW_EXCLUSIVE)
file_pointer2 = file.create(outfile,FILE_RW_EXCLUSIVE)
while .NOT. file_pointer.eof()
	txt = file_pointer.read_line() 
	dim txt2 as c = ""
	dim i as n
		for i = 1 to w_count(txt)
		txt2 = txt2 + word(txt,i)+"  "
		next
	t1 = substr(word(txt2,6),1,2)
	t2 = substr(word(txt2,6),3,2)
	txt2 = strtran(txt2,word(txt2,6),t1+" "+t2)
	txt2 = word(txt2,1)+" " +word(txt2,2)+" "+word(txt2,3)+" "+word(txt2,4)+" "+word(txt2,5)+"_"+word(txt2,6)+" "+word(txt2,7)+word(txt2,8)+"."+word(txt2,9)+" "+word(txt2,10)+word(txt2,11)+"."+word(txt2,12)
	file_pointer2.write_line(txt2)
end while

seems to look okay except there are two or three lines of extra characters and I am snot sure where they came from. no time to look now. I can at a later time.
I am not sure this is in anyway helpful

[Allen Klimeck]

Re: Fix a Text file for Importing

Robin, I made a table with this structure:

 
Po		Character	8	0	
Date		Date		8	0	
Factor		Character	8	0	
Terms		Character	8	0	
Qty		Character	10	0	
Subtotal	Character	10	0	
Total		Character	10	0

and than used this code to write the data to the table:

dim in_file as c
dim in_str as c
dim lncnt as n
dim Icount as n
dim vcline as C
dim vlnet as L
dim vptbl as P

in_file = "c:\users\ajk\downloads\NSI_INV2.txt"
in_str = get_from_file(in_file)
lncnt = line_count(in_str)
'remove the footer first then the header then the blank lines
in_str = *line_remove(in_str,lncnt-3,4)
in_str = *line_remove(in_str,1,12)
in_str = remove_blank_lines(in_str)
in_str = strtran(in_str,"NET 30","NET_30"+chr(32))'add additional strtran's as needed for other terms
lncnt = line_count(in_str)
vptbl = table.open("robin")
for Icount = 1 to lncnt
	vcline = alltrim(word(in_str,Icount,crlf()))
	vlnet = "NET" $ vcline
	vptbl.enter_begin()
	vptbl.po = word(vcline,1) 
	vptbl.date = ctod(word(vcline,2))
	vptbl.factor = word(vcline,3)
	vptbl.terms = if(vlnet,strtran(word(vcline,-4," "),"_"," "),"NO NET")
	vptbl.qty = word(vcline,-3," ")
	vptbl.subtotal = word(vcline,-2," ")
	vptbl.total = word(vcline,-1," ")
	vptbl.enter_end(.t.)	
	next
vptbl.close()

[MoGrace]

Re: Fix a Text file for Importing

@Allen - you are so clever! Replacing the missing data in the string is what I needed. I am bringing this into a temp table before adding to the main table so writing directly to the table makes the most sense as I can omit the data I don't need.

vptbl.terms = if(vlnet,strtran(word(vcline,-4," "),"_"," "),"NO NET") - Brilliant! Thanks

@Govindan - You got me thinking on this one, but the last 2 invoices were not working right because of the missing data and the variance in the numeric word size.

@Tom - I looked into Monarch a few years ago, a single license is now $500. Probably worth the price if I were doing lots more of this stuff.

Thanks to all of you - stringscanner 'works' - almost, but is tough to use.

[MoGrace]

Re: Fix a Text file for Importing

Here is my final script - and it is very fast on these few records

Dim infile as C
Dim in_str as C
Dim vcline as C
Dim ix as N
Dim lncnt as N
Dim vlnet as L
Dim r as P
Dim tbl as P
Dim tname as C

infile = a5.get_path()+chr(92)+"sql_rep.txt"
'infile = a5.get_path()+chr(92)+NSI_INV2.txt"
in_str = get_from_file(infile)
lncnt = line_count(in_str)
'remove the footer first then the header then the blank lines
in_str = *line_remove(in_str,lncnt-3,4)
in_str = *line_remove(in_str,1,12)
in_str = remove_blank_lines(in_str)
'add additional STRTRAN() as needed for other terms
in_str = strtran(in_str,"NET 30","NET_30"+chr(32))
'fix numeric fields so WORD() gets entire field
in_str = strtran(in_str,",","")
in_str = strtran(in_str,"$","")
lncnt = line_count(in_str)

'my udf to empty the temp table
on error goto ENDIT
zap_it("nsi_invrep")
tbl = table.open("nsi_invrep",FILE_RW_SHARED)
tname = tbl.name_get()
on error goto 0

'initiate dot variables
r.invno = ""
r.shipdate = {}
r.nsi_code = ""
r.terms = ""
r.quantity = 0
r.amount = 0
r.billtoline1 = ""

'NOTE Skips table input for PO and Subtotal

For ix = 1 to lncnt
	vcline = alltrim(word(in_str,ix,crlf()))
	Tln("vcline",vcline)
	vlnet = "NET" $ vcline
	r.invno = word(vcline,1)
	r.shipdate = ctod(word(vcline,2))
	r.nsi_code = word(vcline,3)
	r.terms = if(vlnet,strtran(word(vcline,-4," "),"_"," "),"*FIND!")
	r.quantity = val(word(vcline,-3," "))
	r.amount = val(word(vcline,-1," "))
	r.billtoline1 = lookup("qb_cust","nsi_code = "+quote(r.nsi_code),"bill_to_1")
	tbl.enter_record(r)
Next

ENDIT:
If table.isopen(tname) Then
	tbl.close()
End If
END

invreptbl.jpg

Now to turn it into a function and I am done.