vfp to json 1

[DominicProgrammer]

Hi Good guys,

wud someone assist me track this foxpro code out, why is the json file coming with a "," @ the end of the file even if its coded not to?

** paytranf
Select transfile
pack
Set Deleted On
Set Relation To Code Into Type
GO top
SET ORDER TO empcode
Count To Xreccs2
Strtofile([[]+Chr(13)+lcCRLF, [c:\apps\infojson\transactions.txt], .F.)
SCAN
	SCATTER memvar
	zno=alltr(str(100-(recno()/recc()*100),13,4))
	WAIT window nowait 'Retrieving Employee Transactions.... '+zno+' % remaining'+chr(13)+'Empl No:'+alltr(m.empno)+chr(13)+'Code:'+'-'+alltr(m.code) at 34,20 timeout 1
	Strtofile([{]+ lcCRLF, [c:\apps\infojson\transactions.txt], .T.)
	Strtofile([	"employee_id":] +'"'+Alltrim(m.empno)+'"'+','+ lcCRLF, [c:\apps\infojson\transactions.txt], .T.)
	Strtofile([	"period":] +'"'+Dtoc(m.periodate)+'"'+','+ lcCRLF, [c:\apps\infojson\transactions.txt], .T.)
	Strtofile([	"transactions":] +" ["+ lcCRLF, [c:\apps\infojson\transactions.txt], .T.)
	Strtofile([{]+Chr(13)+[ "code"]+': '+Alltrim(transfile.code)+','+lcCRLF, [c:\apps\infojson\transactions.txt], .T.)
	Strtofile([  "name":] +'"'+Alltrim(Type.fulldesc)+'"'+','+ lcCRLF, [c:\apps\infojson\transactions.txt], .T.)
	Strtofile([  "hours":] +ALLTRIM(transform(transfile.hrs,[999999999.99]))+','+ lcCRLF, [c:\apps\infojson\transactions.txt], .T.)
	Strtofile([  "amount":] +ALLTRIM(transform(transfile.amount,[999999999.99]))+ lcCRLF, [c:\apps\infojson\transactions.txt], .T.)
	Strtofile([  }]+ lcCRLF, [c:\apps\infojson\transactions.txt], .T.)
	Strtofile([] +']'+ lcCRLF, [c:\apps\infojson\transactions.txt], .T.)  
	Strtofile([  }]+Iif(Recno()<Xreccs2,',', '')+ lcCRLF, [c:\apps\infojson\employees.txt], .T.)
ENDSCAN
Strtofile([] +']'+ lcCRLF, [c:\apps\infojson\transactions.txt], .T.)  && close object

Dominic

 

[Chris Miller]

Iif(Recno()<Xreccs2,',', '')

You expect Recno()<Xreccs2 to become false and thus IIF to return the empty string when the last record is output. Under the circumstances you provide by doing a PACK I'd agree that the last Recno() should be equal to Xreccs2 and also be Reccount(), but only if you wouldn't use an index to order the data.

You do SET ORDER TO empcode and so Recno() will jump up and down depending on how records are ordered by the index, Recno() always is the physical record number within the DBF file and sorting by index means accessing records in the necessary order of recnos, not rearranging the physical positions of records.

You will seldom end with Recno()=Reccount() at the last record of the scan loop when you scan in an index order. Chances are 1:Reccount() the last physical record also is the last record in a specific order.

Look into your output file, you must have one or even multiple elements without a comma somewhere in the middle, depending on where the record with Recno()=Reccount()=Xreccs2 sorts into the output. You can find this record easier by looking at the data in the last physical DBF record you can move to with GO Reccount() or GO BOTTOM when no order is set.

I also spot another problem, though, your last STRTOFILE goes into [c:\apps\infojson\employees.txt] instead of [c:\apps\infojson\transactions.txt].

Besides, your code would all become a lot simpler to read and maintain if you'd not generate this line by line, but record by record with TEXT..ENDTEXT, which could also write out to a file when you SET TEXTMERGE to the output file.

And then you likely wouldn't have made this error of writing the last bit of each record into another file. Maybe I'm wrong here and this is intended but then clearly the last comma in the [c:\apps\infojson\transactions.txt] file will always be there as there is no IIF preventing to output a comma in the last line of what you write into that file, you only try to prevent a comma in the last line of [c:\apps\infojson\employees.txt].



Chriss

 

[DominicProgrammer]

HI Chris

Thanks for your feedback much appreciated, i have actually set the employees.txt bug, how may i rewrite it to prevent the last comma? this is my expected json file:

[
    {
        "employee_id": "BSC001",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 101,
                "name": "BASIC PAY",
                "hours": 0.00,
                "amount": 10850.00
            }
        ]
    },
    {
        "employee_id": "BSC001",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 508,
                "name": "ADVANCE",
                "hours": 0.00,
                "amount": 2500.00
            }
        ]
    },
    {
        "employee_id": "BSC002",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 101,
                "name": "BASIC PAY",
                "hours": 0.00,
                "amount": 10150.00
            }
        ]
    },
    {
        "employee_id": "BSC002",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 508,
                "name": "ADVANCE",
                "hours": 0.00,
                "amount": 2500.00
            }
        ]
    },
    {
        "employee_id": "BSC003",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 101,
                "name": "BASIC PAY",
                "hours": 0.00,
                "amount": 12500.00
            }
        ]
    },
    {
        "employee_id": "BSC003",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 508,
                "name": "ADVANCE",
                "hours": 0.00,
                "amount": 3500.00
            }
        ]
    },
    {
        "employee_id": "BSC004",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 101,
                "name": "BASIC PAY",
                "hours": 0.00,
                "amount": 12000.00
            }
        ]
    },
    {
        "employee_id": "BSC004",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 508,
                "name": "ADVANCE",
                "hours": 0.00,
                "amount": 2500.00
            }
        ]
    },
    {
        "employee_id": "BSC005",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 101,
                "name": "BASIC PAY",
                "hours": 0.00,
                "amount": 10500.00
            }
        ]
    },
    {
        "employee_id": "BSC006",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 101,
                "name": "BASIC PAY",
                "hours": 0.00,
                "amount": 10500.00
            }
        ]
    },
    {
        "employee_id": "BSC006",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 508,
                "name": "ADVANCE",
                "hours": 0.00,
                "amount": 2500.00
            }
        ]
    },
    {
        "employee_id": "BSC007",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 101,
                "name": "BASIC PAY",
                "hours": 0.00,
                "amount": 10150.00
            }
        ]
    },
    {
        "employee_id": "BSC007",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 508,
                "name": "ADVANCE",
                "hours": 0.00,
                "amount": 2500.00
            }
        ]
    },
    {
        "employee_id": "BSC008",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 101,
                "name": "BASIC PAY",
                "hours": 0.00,
                "amount": 10150.00
            }
        ]
    },
    {
        "employee_id": "BSC008",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 508,
                "name": "ADVANCE",
                "hours": 0.00,
                "amount": 2500.00
            }
        ]
	"period": "30/06/2023",
        "transactions": [
            {
                "code": 101,
                "name": "BASIC PAY",
                "hours": 0.00,
                "amount": 11607.00
            }
        ]
    },
    {
        "employee_id": "BSC010",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 508,
                "name": "ADVANCE",
                "hours": 0.00,
                "amount": 3000.00
            }
        ]
    },
    {
        "employee_id": "BSC012",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 101,
                "name": "BASIC PAY",
                "hours": 0.00,
                "amount": 13467.00
            }
        ]
    },
    {
        "employee_id": "BSC012",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 508,
                "name": "ADVANCE",
                "hours": 0.00,
                "amount": 3000.00
            }
        ]
    },
    {
        "employee_id": "BSC013",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 101,
                "name": "BASIC PAY",
                "hours": 0.00,
                "amount": 12400.00
            }
        ]
    },
    {
        "employee_id": "BSC013",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 508,
                "name": "ADVANCE",
                "hours": 0.00,
                "amount": 3000.00
            }
        ]
    },
    {
        "employee_id": "BSC014",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 508,
                "name": "ADVANCE",
                "hours": 0.00,
                "amount": 3000.00
            }
        ]
    },
    {
        "employee_id": "BSC015",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 508,
                "name": "ADVANCE",
                "hours": 0.00,
                "amount": 3000.00
            }
        ]
    },
    {
        "employee_id": "BSC016",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 508,
                "name": "ADVANCE",
                "hours": 0.00,
                "amount": 2500.00
            }
        ]
    },
    {
        "employee_id": "BSC019",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 508,
                "name": "ADVANCE",
                "hours": 0.00,
                "amount": 2500.00
            }
        ]
    },
    {
        "employee_id": "BSC020",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 508,
                "name": "ADVANCE",
                "hours": 0.00,
                "amount": 2500.00
            }
        ]
    },
    {
        "employee_id": "BSC022",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 508,
                "name": "ADVANCE",
                "hours": 0.00,
                "amount": 3000.00
            }
        ]
    }
{
        "employee_id": "BSG417",
        "period": "30/06/2023",
        "transactions": [
            {
                "code": 101,
                "name": "BASIC PAY",
                "hours": 0.00,
                "amount": 5950.00
            }
        ]
    }
]

 

[Chris Miller]

Well, given the way scanning in index order works, you have no simple indicator of when you reach the last record in order. So it would be advisable to find out first, before going into the scan loop.

So:

...
SET ORDER TO empcode
Go Bottom
lnLastrecno=Recno()
...

And then use IIF(Recno()=lnLastrecno,'',',')


Chriss

 

[Chris Miller]

Another idea is to probe, whether you get to EOF(), after you SKIP 1.

So

SKIP 1
llLastRecord = EOF()
SKIP -1

Notice, in whatever iteration/record you are within the scan loop, you never have EOF(), you always can SKIP 1 more. The scan loop ends, if EOF() will be reached by ENDSCAN, which does SKIP 1. So you can always SKIP 1 without error and then make the test the endscan would do, checking EOF(). Just also SKIP -1 to not only go through each second record. You need to do this, before you output with or without the comma, obviously, and you need to do the SKIP -1 before outputting the record, otherwise you'd output the next record and not the current record.

Chriss

 

[Chris Miller]

Another idea is you establish your own counter:

lnScanningRecord = 0
Scan
   lnScanningRecord = lnScanningRecord+1
   ...
   ...IIF(lnScanningRecord < Xreccs2,',','')...
Endscan

Chriss

 

[Chris Miller]

Another idea is accumulating all the text into a variable and instead of appending a comma prefix a comma before the opening curly bracket {. After having all that, remove the first comma.

Chriss

 

[DominicProgrammer]

Hi Chris.

You are a savior, this is all i had to do.

CODE
lnScanningRecord = 0
Scan
   lnScanningRecord = lnScanningRecord+1
   ...
   ...IIF(lnScanningRecord < Xreccs2,',','')...
Endscan

It worked and now i can post my entire desktop payroll into Mysql hosted online. Thanks a bunch buddy.

Dominic

 

[mplaza]

Hello Dominic, better use nfJson ( just download from

https://github.com/VFPX/nfJson/archive/refs/heads/master.zip

)
and set path to nfJson folder to start using it.

Check this sample using northwind:

*--------------------------------------------
* using nfJson to create json
* from parent-child cursors:
*--------------------------------------------

Clear
Close Data

Open Database Home()+'samples\northwind\northwind'

Select Top 2 orderid,customerid,orderdate ;
	from orders Order By 1 ;
	into Cursor cursample

* Turn parent cursor to object.rows using nfCursorToObject:

oOrders = nfcursortoobject() && returns an object with a rows array

* get child records for each row:

For Each oOrder In oOrders.Rows

	Select * From orderdetails ;
		where orderid = oorder.orderid ;
		into Cursor curdet

* use nfCursorToObject for child records:
	oOrderdetail = nfcursortoobject()

* then copy the 'rows' array to parent Object:
	AddProperty(oOrder,"orderDetail(1)",Null)
	Acopy(oorderdetail.Rows,oorder.orderdetail)

Endfor

* we have the object Orders, but
* since you want only the array part [],
* you need to pass oOrders.Rows to nfJsonCreate like this:

Acopy(oorders.Rows,arows)

* then:
myjson =  nfjsoncreate(@m.arows,.T.)

? m.myjson

Marco Plaza
@nfoxProject

https://www.github.com/nfoxdev

 

[Chris Miller]

Another suggestion would be using SQL Passthrough or other ways of conecting to MySQL aand uploading data to it. And if you go thorugh export/import, VFP has an easy way to create CSV files (one line of code with COPY TO TYPE CSV or DELIMTED)) and a little more complicated way to create XML with CursorToXML().

And there is a whole book about MySQL and VFP:

https://www.hentzenwerke.com/catalog/mysqlvfp.htm

I usually find it easiest to go the direct route, but who knows what's all this is based on, you may provide the json to an already existing way of forwarding that to MySQL.

And dipping your toe into the water is simple:
1. Have a DSN to connect to MySQL.

2. Use it this way from VFP

h = SQLConnect('DSNNAME')
If h>0
   SQLExec(h,'insert into `table` values (...)')
   SQLDisconnect(h)
Else
   AError(laError)
   ? laError[1,2]
Endif

There's more to say, with CURSORSETPROP you can prepare a workarea to support committing a batch of records with one TABLEUPDATE(), eventually. The point is not that this is only a oneliner again, there are some steps to do before, the point is you don't have to insert record by record only, as inserts only allow.

There's a medium complex approach using SQLPrepare and then using a parameterized insert (so still using the record by record approach) with a SCAN loop like you used to generate the JSON, but with a prepared statement this works faster and does not go into a JSON file but straight to the MySQL server.

Chriss