VP6 filetostr help

[berniebogs]

Good Day!

Since i only use Visual Foxpro 6 and i cannot file any upgraded version of foxpro, can this file turn into a data base.

any tips u can give me?

in my case i convert it into a text file then read all contents of the file then create a cursor one line to read all contents and put it in a table.

i've use filetostr method but can this method direct into fields since xml file is also a text file?

 

[Olaf Doschke]

No, filetoSTR can read into a Strign.
Roll your own parsing using FOPEN and FREAD.

Or look at West Wind wwXML classes (

https://www.west-wind.com/wwxml.asp),

they were done at the stage of VFP not having CML functions.
Or use XML COM classes like Microsoft.XMLDOM.

Essentially all answer I already gave in thread184-1775516, where you find details about how to use Microsoft.XMLDOM.

Bye, Olaf.

 

[berniebogs]

PUBLIC TPATH, cFileString, CSCTR, CECTR, cStartTag, cEndTag, cXMLData
cFileString=filetostr("EGP000.XML")
cStartTag = "<Bol_reference>"
cEndTag = "</Bol_reference>"
CSCTR = "<Reference>"
CECTR = "</Reference>"

TPATH='D:\XML\'

sele 1
use gen
dele all
pack
sele 2
use bol
dele all
pack
sele 3
use ctn
dele all
pack

DO IFMBL
DO IFMCTR

PROCEDURE IFMBL

DO WHILE .T.
nTag1 = AT(cStartTag,cFileString)
nTag3 = AT(CSCTR,cFileString)
if nTag1 > 0
nTag2 = AT(cEndTag,cFileString)
nTag4 = AT(CECTR,cFileString)

cXMLData = SUBSTR(cFileString,(nTag1 + LEN(cStartTag)),(nTag2-nTag1)-LEN(cStartTag))
* CTRDAT = SUBSTR(cFileString,(nTag3 + LEN(CSCTR)),(nTag4-nTag3)-LEN(CSCTR))
?cXMLData
* ?CTRDAT

sele 2
use bol
appe blan
replace ifm_bl with cXMLData

* sele 3
* use ctn
* appe blan
* replace ifm_bl with cXMLData
* replace ifm_cntrno with CTRDAT


cFileString = SUBSTR(cFileString, nTag2 + LEN(cEndTag)) && nTag2 + LEN(cEndTag)
* cFileString = SUBSTR(cFileString, nTag4 + LEN(CECTR)) && nTag2 + LEN(cEndTag)

ELSE
EXIT
ENDIF
ENDDO

ENDPROC


PROCEDURE IFMCTR
DO WHILE .T.
nTag3 = AT(CSCTR,cFileString)
if nTag3 > 0
nTag4 = AT(CECTR,cFileString)

cXMLData1 = SUBSTR(cFileString,(nTag3 + LEN(CSCTR)),(nTag4-nTag3)-LEN(CSCTR))
?cXMLData1


sele 3
use ctn
appe blan
replace ifm_bl with cXMLData
replace ifm_cntrno with cXMLData1

cFileString = SUBSTR(cFileString, nTag4 + LEN(CECTR)) && nTag2 + LEN(cEndTag)
ELSE
EXIT
ENDIF
ENDDO
ENDPROC


so far i got this, but iam no programmer i just try and error until it works

 

[Olaf Doschke]

>so far i got this, but iam no programmer i just try and error until it wo

Well, then you need to hire one.

Bye, Olaf.

 

[berniebogs]

i dont need to hire one i just need tips cause its so confusing iam trying my best to learn.

i dont want this really, it so happen my boss died and i have to do it on my own..

 

[Olaf Doschke]

Well, that's unfortunate, but I don't see you exhaust all possibilities given.

You took the idea to search for start and endtag, but you even didn't put this into a loop as Mike did.
As you rather want a general XML to cursor conversion you should go wwXML and not use such specific single tag parsing, that only fits a need to extract one single field.

Bye, Olaf.

 

[JRB-Bldr]

Indeed we are here to assist/advise you on your specific VFP needs.

But when something is BUSINESS CRITICAL, people need to come to the realistic conclusion that there are times when hiring a professional is the SMART and MOST EFFICIENT/EFFECTIVE thing to do.

However if you want to continue working on this on your own, you need to look into the VFP Low-level commands such as FOPEN(), FGETS, FPUTS, etc.

Alternatively you could possibly use the FILETOSTR() command to read in the test file convert the string contents to a CSV formatted string by converting the text delimiters and write it out as such.
Then with the info in a CSV file format you could 'read' that into an already prepared recipient DBF file (look at the VFP Help system for APPEND FROM)

EDIT...
Well I did finally look at your attached file and saw that it was most definitely an XML file - and a very complex one. With that in mind, you can ignore my previous suggestion and look for a much more comprehensive approach - which very likely will require:
1. A significant knowledge of your own application-specific data table structures
2. How multiple of your application data tables might be related and/or inter-related
3. A reasonably good understanding of the VFP language.
With that in mind, I'll go back to suggesting that you seriously investigate finding a local VFP professional contractor to assist you in not just importing that file, but creating a routine/utility to import future files as well.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Just to be clear, when we talk about "hiring a professional", we do not necessarily mean that you advertise for a new staff member to come and work for your company as a salaried employee. In most cases, we are talking about finding a local consultant who can visit your office and give you some advice, in return for a one-off fee or a retainer of some kind.

The point that both Olaf and JRB have made is that, if the problem is business-critical and you don't have the expertise to solve it yourself, then you must be prepared to pay for someone to solve it for you.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

A company like yours not thinking strategically about the future sooner or later pays a high price. It sounds harsh and unfair, since you lost a life and not just a legacy computer, but we also can't do such amount of work just out of mere empathy for your situation.

From the latest experience I had, a software retailer company lost its developer of its main POS system and wasn't and isn't used to planning a change of the software with a consultant developer. I won't get into details, but they would be far better off with a new employee and I really think of such a step for your situation, no matter if your former boss only developed this software in spare times or as main task.

Just maintaining a software now is a job for someone already knowing FoxPro at least, you can't take both hurdles of not knowing programming and Foxpro at all, and also not knowing the inner workings of the software you're taking into maintenance, even if you have the advantage of knowing your business, that's not much of help, it's easier to expain your business to a developer than to learn programming, Foxpro and this software architechture and you certainly underestimate how long it even takes an expeerienced devleoper to even just read and understand someone else's code. At least one thing is for sure: As long as you don't have known bugs and no matter how good or poor the software is written, it is a reference and model of your business rules in itself.

But this is not just like taking over a restaurant in a certain spirit and kitchen with well known ingredients and recipes, every developer cooks its own soup and as you say your boss was the original developer, unless your company is a software vendor company I seldom see a boss of any other type of busiiness doing development as the main job, so software was developed as a side task, I guess. It's prejudice, but unfortunately that type of prejudice mainly turning out true, that this is not the best base to continue.

If your company hasn't reserves to cover such a risc having become a real case, this very well can end your business. What's certainly not working is tryig to cover up the loss with forum questions. If you need to be able to cover up changing rules or even laws and keep up with capabilities of competition, you need to have someone getting to know the software, if it is your major "asset" and unique selling proposition. And alone that, not even talking of changing the software, just understanding it, can take a long time alone.

If the situation is less alarming you might consider replacing the software overall. That also takes time, but the major concern then will be about the data, ie to move that to something like SQL Server and still keep things going. With data moved to a database server, you have a larger base of developers to choos from as either employees or consultants, who can contribute in developing a frontend for that backend.

Even with your concrete problem, you should not only think about extending VFP code, your goal is to import XML into your data, you find lots of C/C++/C#,Java,Database Developers, even Javascript, who will be able to turn XML into data and then to read that into DBF is less of a problem. So don't only try to find your salvation here. You're too stuck on details.

Bye, Olaf.

 

[mplaza]

It would be an easy task using nfXml -a vfp9 parser/serializer I shared on vfpx - , but it does not support vfp6 since it uses the 'empty' class ( you can fork and tweak it to use "line" ).

Even so, it includes the nfXpath() function wich outputs all the xpaths of any xml.
- I used it to get all the xpaths of your document and and help you with this task.

clear

local yourxml,xpaths,nl,sp,thispath

getXmlandXpaths(@yourxml,@xpaths)

o = createobject('msxml.domdocument')
o.loadxml(m.yourxml)
nl = 0
sp = 1


do while .t.

	nl = m.nl+1

	nchars = at(chr(13),substr(m.xpaths,m.sp))-1
	thispath = substr(m.xpaths,m.sp,m.nchars)
	sp = m.sp+m.nchars+2

	if empty(m.thispath) 
		exit
	endif

	thisnode = o.selectsinglenode(m.thispath)

	if vartype(m.thisnode) = 'O' and thisnode.haschildnodes and thisnode.firstchild.nodetype = 3 and !empty(thisnode.text)
		? thispath,'=',thisnode.text
	endif


enddo


function getxmlandxpaths( yourxml, xpaths )
text to yourxml noshow
<?xml version="1.0" encoding="UTF-8"?>
<TWM_Manifest>
    <Identification_segment>
        <Registry_number>EGP0000-17</Registry_number>
        <Customs_office_segment>
            <Code>P04</Code>
        </Customs_office_segment>
    </Identification_segment>
    <General_segment>
        <Master_information></Master_information>
        <Totals_segment>
            <Total_number_of_bols>29</Total_number_of_bols>
            <Total_number_of_containers>143</Total_number_of_containers>
        </Totals_segment>
        <Last_discharge></Last_discharge>
        <Arrival_segment>
            <Date_of_arrival>2017-03-13</Date_of_arrival>
            <Time_of_arrival>10:00:00</Time_of_arrival>
        </Arrival_segment>
        <Departure_segment>
            <Code>TWKHH</Code>
        </Departure_segment>
        <Destination_segment>
            <Code>PHBTG</Code>
        </Destination_segment>
        <Carrier_segment>
            <Code>EGP</Code>
        </Carrier_segment>
        <Transport_segment>
            <Name_of_transporter>CAPE FARO</Name_of_transporter>
            <Place_of_transporter></Place_of_transporter>
            <Mode_of_transport_segment>
                <Code>1</Code>
            </Mode_of_transport_segment>
            <Nationality_of_transport_segment>
                <Code>PH</Code>
            </Nationality_of_transport_segment>
            <Transporter_registration_segment>
                <Registration_number>9347712</Registration_number>
                <Registration_date>0000-00-00</Registration_date>
            </Transporter_registration_segment>
        </Transport_segment>
        <Tonnage_segment>
            <Gross_tonnage>15995.00</Gross_tonnage>
            <Net_tonnage>6251.00</Net_tonnage>
        </Tonnage_segment>
    </General_segment>
    <TWM_BOL>
        <Identification_segment>
            <Registry_number>EGP0011-17</Registry_number>
            <Bol_reference>EGLV020700045087</Bol_reference>
            <Customs_office_segment>
                <Code>P04</Code>
            </Customs_office_segment>
        </Identification_segment>
        <Bol_specific_segment>
            <Line_number>1</Line_number>
            <Previous_document_reference></Previous_document_reference>
            <Bol_Nature>23</Bol_Nature>
            <Unique_carrier_reference></Unique_carrier_reference>
            <Total_number_of_containers>1</Total_number_of_containers>
            <Total_gross_mass_manifested>9655.20</Total_gross_mass_manifested>
            <Volume_in_cubic_meters>60.26</Volume_in_cubic_meters>
            <Bol_type_segment>
                <Code>HBL</Code>
            </Bol_type_segment>
            <Exporter_segment>
                <Code></Code>
                <Name>JAD.</Name>
                <Address>366</Address>
            </Exporter_segment>
            <Consignee_segment>
                <Code></Code>
                <Name>JTG</Name>
                <Address>NES</Address>
            </Consignee_segment>
            <Notify_segment>
                <Code></Code>
                <Name>JT CTURING</Name>
                <Address>CES</Address>
            </Notify_segment>
            <Place_of_loading_segment>
                <Code>TWKHH</Code>
            </Place_of_loading_segment>
            <Place_of_unloading_segment>
                <Code>PHBTG</Code>
            </Place_of_unloading_segment>
            <Packages_segment>
                <Package_type_code>CT</Package_type_code>
                <Number_of_packages>432</Number_of_packages>
            </Packages_segment>
            <Shipping_segment>
                <Shipping_marks>Jaaaaaaaaaaa</Shipping_marks>
            </Shipping_segment>
            <Goods_segment>
                <Goods_description>FINAL</Goods_description>
            </Goods_segment>
            <Freight_segment>
                <Value></Value>
                <Currency></Currency>
                <Indicator_segment>
                    <Code></Code>
                </Indicator_segment>
            </Freight_segment>
            <Customs_segment>
                <Value></Value>
                <Currency></Currency>
            </Customs_segment>
            <Transport_segment>
                <Value></Value>
                <Currency></Currency>
            </Transport_segment>
            <Insurance_segment>
                <Value></Value>
                <Currency></Currency>
            </Insurance_segment>
            <Seals_segment>
                <Number_of_seals></Number_of_seals>
                <Marks_of_seals></Marks_of_seals>
                <Sealing_party_code></Sealing_party_code>
            </Seals_segment>
            <Information_segment>
                <Information_part_a></Information_part_a>
            </Information_segment>
            <Operations_segment>
                <Location_segment>
                    <Code></Code>
                    <Information></Information>
                </Location_segment>
            </Operations_segment>
        </Bol_specific_segment>
        <Container>
            <Reference>EITU1508730</Reference>
            <Number>1</Number>
            <Type>40</Type>
            <Empty_full>FCL</Empty_full>
            <Seals>EMCDNJ9856</Seals>
            <Sealing_party>OTH</Sealing_party>
        </Container>
    </TWM_BOL>
</TWM_Manifest>

ENDTEXT

*** following xpaths obtained using nfXpaths( yourxml ) - from vfpx's nfXml project -

text to xpaths noshow
/TWM_Manifest
/TWM_Manifest/Identification_segment
/TWM_Manifest/Identification_segment/Registry_number
/TWM_Manifest/Identification_segment/Customs_office_segment
/TWM_Manifest/Identification_segment/Customs_office_segment/Code
/TWM_Manifest/General_segment
/TWM_Manifest/General_segment/Master_information
/TWM_Manifest/General_segment/Totals_segment
/TWM_Manifest/General_segment/Totals_segment/Total_number_of_bols
/TWM_Manifest/General_segment/Totals_segment/Total_number_of_containers
/TWM_Manifest/General_segment/Last_discharge
/TWM_Manifest/General_segment/Arrival_segment
/TWM_Manifest/General_segment/Arrival_segment/Date_of_arrival
/TWM_Manifest/General_segment/Arrival_segment/Time_of_arrival
/TWM_Manifest/General_segment/Departure_segment
/TWM_Manifest/General_segment/Departure_segment/Code
/TWM_Manifest/General_segment/Destination_segment
/TWM_Manifest/General_segment/Destination_segment/Code
/TWM_Manifest/General_segment/Carrier_segment
/TWM_Manifest/General_segment/Carrier_segment/Code
/TWM_Manifest/General_segment/Transport_segment
/TWM_Manifest/General_segment/Transport_segment/Name_of_transporter
/TWM_Manifest/General_segment/Transport_segment/Place_of_transporter
/TWM_Manifest/General_segment/Transport_segment/Mode_of_transport_segment
/TWM_Manifest/General_segment/Transport_segment/Mode_of_transport_segment/Code
/TWM_Manifest/General_segment/Transport_segment/Nationality_of_transport_segment
/TWM_Manifest/General_segment/Transport_segment/Nationality_of_transport_segment/Code
/TWM_Manifest/General_segment/Transport_segment/Transporter_registration_segment
/TWM_Manifest/General_segment/Transport_segment/Transporter_registration_segment/Registration_number
/TWM_Manifest/General_segment/Transport_segment/Transporter_registration_segment/Registration_date
/TWM_Manifest/General_segment/Tonnage_segment
/TWM_Manifest/General_segment/Tonnage_segment/Gross_tonnage
/TWM_Manifest/General_segment/Tonnage_segment/Net_tonnage
/TWM_Manifest/TWM_BOL
/TWM_Manifest/TWM_BOL/Identification_segment
/TWM_Manifest/TWM_BOL/Identification_segment/Registry_number
/TWM_Manifest/TWM_BOL/Identification_segment/Bol_reference
/TWM_Manifest/TWM_BOL/Identification_segment/Customs_office_segment
/TWM_Manifest/TWM_BOL/Identification_segment/Customs_office_segment/Code
/TWM_Manifest/TWM_BOL/Bol_specific_segment
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Line_number
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Previous_document_reference
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Bol_Nature
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Unique_carrier_reference
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Total_number_of_containers
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Total_gross_mass_manifested
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Volume_in_cubic_meters
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Bol_type_segment
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Bol_type_segment/Code
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Exporter_segment
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Exporter_segment/Code
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Exporter_segment/Name
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Exporter_segment/Address
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Consignee_segment
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Consignee_segment/Code
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Consignee_segment/Name
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Consignee_segment/Address
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Notify_segment
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Notify_segment/Code
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Notify_segment/Name
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Notify_segment/Address
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Place_of_loading_segment
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Place_of_loading_segment/Code
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Place_of_unloading_segment
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Place_of_unloading_segment/Code
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Packages_segment
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Packages_segment/Package_type_code
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Packages_segment/Number_of_packages
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Shipping_segment
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Shipping_segment/Shipping_marks
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Goods_segment
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Goods_segment/Goods_description
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Freight_segment
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Freight_segment/Value
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Freight_segment/Currency
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Freight_segment/Indicator_segment
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Freight_segment/Indicator_segment/Code
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Customs_segment
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Customs_segment/Value
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Customs_segment/Currency
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Transport_segment
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Transport_segment/Value
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Transport_segment/Currency
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Insurance_segment
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Insurance_segment/Value
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Insurance_segment/Currency
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Seals_segment
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Seals_segment/Number_of_seals
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Seals_segment/Marks_of_seals
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Seals_segment/Sealing_party_code
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Information_segment
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Information_segment/Information_part_a
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Operations_segment
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Operations_segment/Location_segment
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Operations_segment/Location_segment/Code
/TWM_Manifest/TWM_BOL/Bol_specific_segment/Operations_segment/Location_segment/Information
/TWM_Manifest/TWM_BOL/Container
/TWM_Manifest/TWM_BOL/Container/Reference
/TWM_Manifest/TWM_BOL/Container/Number
/TWM_Manifest/TWM_BOL/Container/Type
/TWM_Manifest/TWM_BOL/Container/Empty_full
/TWM_Manifest/TWM_BOL/Container/Seals
/TWM_Manifest/TWM_BOL/Container/Sealing_party
ENDTEXT

Marco Plaza
@vfp2nofox