Hi, I have attached a database that can generate Json from a Query data.
But I am trying to get Json output from a query in a specific format. (Expected Json format attached)
My Database contains 3 tables
1.Bill - Contains Invoice no. and customer details
2.BillProducts - Contains products related to Invoices
3.BillPayments - Contains Payment type breakdown related to Invoices
Hi. I've only used the JsonConverter to parse JSON strings into VBA. I didn't know it can generate JSON from a query. Are you sure? I'll go check it again...
Hi. I've only used the JsonConverter to parse JSON strings into VBA. I didn't know it can generate JSON from a query. Are you sure? I'll go check it again...
Hi
First of all, thanks for alerting me to the fact that Tim Hall's code could also be used to create JSON.
However, as written, the code cannot handle subarrays such as those in your ItemList subarray
You can easily add the remaining fields you require by adding tblProducts to your query.
However, doing that will create a single array in your JSON which isn't what you want
Similarly, adding tblPayments will then include the two fields in your PaymentMode subarray but once again the output will be a 'flat' structure
Remember that Access cannot handle JSON files automatically.
In order to get your desired result, you will probably need to do one of the following:
1. Rewrite the code to handle the subarray. I suspect that could be a lot of work
2. Save the data as a CSV file and use a free online CSV to JSON converter
3. Save as CSV and export to Excel Get & Transform
4. Purchase a commercial app to handle the conversion
The best solution may depend on whether this is a one-off task or will be a regular event.
For info, my own JSON app can import JSON data into Access tables ... but not export data as JSON
EDIT: For info, the expected JSON file you supplied isn't a valid JSON file. For example:
a) the number field values should not have surrounding quotes
b) there shouldn't be a comma after the Amount Payable values as its the final field
c) I believe the PaymentModes subarray needs to be part of each record ...NOT listed separately
Hi
First of all, thanks for alerting me to the fact that Tim Hall's code could also be used to create JSON.
However, as written, the code cannot handle subarrays such as those in your ItemList subarray
You can easily add the remaining fields you require by adding tblProducts to your query.
However, doing that will create a single array in your JSON which isn't what you want
Similarly, adding tblPayments will then include the two fields in your PaymentMode subarray but once again the output will be a 'flat' structure
Remember that Access cannot handle JSON files automatically.
In order to get your desired result, you will probably need to do one of the following:
1. Rewrite the code to handle the subarray. I suspect that could be a lot of work
2. Save the data as a CSV file and use a free online CSV to JSON converter
3. Save as CSV and export to Excel Get & Transform
4. Purchase a commercial app to handle the conversion
The best solution may depend on whether this is a one-off task or will be a regular event.
For info, my own JSON app can import JSON data into Access tables ... but not export data as JSON
EDIT: For info, the expected JSON file you supplied isn't a valid JSON file. For example:
a) the number field values should not have surrounding quotes
b) there shouldn't be a comma after the Amount Payable values as its the final field
c) I believe the PaymentModes subarray needs to be part of each record ...NOT listed separately
Thanks for reply. Yes there are few mistakes in the expected json file as i typed it manually. So can you alter the query structure through which i can get the desired CSV file? This is going to be regular event, infact this process will be used to generate e-invoice from goverment prortal. We need to POST Invoice details in JSON and GET a uniqueID which goverment returns and put that on invoice issued. This will be used every time making a invoice.
The following query will give you a flat file CSV structure:
Code:
SELECT Bill.VouNo, Bill.CustomerName, Bill.Address, Bill.Phone, BillProducts.SerialNo, BillProducts.Product, BillProducts.Qty, BillProducts.Rate, BillProducts.AmountPayable, BillPayments.PaymentMode, BillPayments.AmountPaid
FROM (Bill INNER JOIN BillProducts ON Bill.VouNo = BillProducts.VouNo) INNER JOIN BillPayments ON Bill.VouNo = BillPayments.VouNo
WHERE (((Bill.VouNo)="INV-1"));
However that won't allow you to generate the JSON file you want direct from Access. Doing that will likely be a lot of work
Or, as already mentioned, use an external tool to do this
The following query will give you a flat file CSV structure:
Code:
SELECT Bill.VouNo, Bill.CustomerName, Bill.Address, Bill.Phone, BillProducts.SerialNo, BillProducts.Product, BillProducts.Qty, BillProducts.Rate, BillProducts.AmountPayable, BillPayments.PaymentMode, BillPayments.AmountPaid
FROM (Bill INNER JOIN BillProducts ON Bill.VouNo = BillProducts.VouNo) INNER JOIN BillPayments ON Bill.VouNo = BillPayments.VouNo
WHERE (((Bill.VouNo)="INV-1"));
However that won't allow you to generate the JSON file you want direct from Access. Doing that will likely be a lot of work
Or, as already mentioned, use an external tool to do this
I already tried with this flat query but didnt get desired result. I exported this flat query data to Excel and covnverted to JSON with online converter. This is what i got..
.Ofcourse this is not what i want. Please help me with a best possible solution. Atleast suggest or alter the mdb file so that i can get a excel output that can be converted to desired JSON.
That's exactly what I expected to happen.
I'm sorry but I've already given you the possible solutions I can think of.
Perhaps I'm wrong but to do this in Access will almost certainly be a lot of work (many hours!).
it really not something I have time to do at the moment even as a chargeable task.
At some point in the future, I may add code to my JSON app to allow it to export JSON files but that's not possible at the moment
Suggest you look into using Excel Get & Transform or PowerShell or even Power Automate Desktop
That's exactly what I expected to happen.
I'm sorry but I've already given you the possible solutions I can think of.
Perhaps I'm wrong but to do this in Access will almost certainly be a lot of work (many hours!).
it really not something I have time to do at the moment even as a chargeable task.
At some point in the future, I may add code to my JSON app to allow it to export JSON files but that's not possible at the moment
Suggest you look into using Excel Get & Transform or PowerShell or even Power Automate Desktop
Tim Hall's code is a great start into integrating JSON with Access but it does have limitations.
If you do find a wholly Access-based solution, I would be glad to hear of it.
But in the meantime I suggest you look into all the alternatives I suggested
Tim Hall's code is a great start into integrating JSON with Access but it does have limitations.
If you do find a wholly Access-based solution, I would be glad to hear of it.
But in the meantime I suggest you look into all the alternatives I suggested
I would have thought it relatively easy to produce JSON file from first principles, but you do need to understand the syntax you want to achieve, and I think that's what @isladogs was suggesting. The file example produced may not satisfy the requirements.
By first principles, I mean
Open text file
For each record
Write record tag open
Write {data}
Write record tag close
Close text file
'Open text file
'For each record
'Write record tag open
'Write {data}
'Write record tag close
'Close text file
'With appropriate sections of {data} as required
So finally the solution is change the sub ...... ?
Private Sub Command2_Click()
' Const SQL_SELECT As String = "SELECT * FROM Qry1;"
Dim http As Object
Dim coll As VBA.Collection
Dim dict As Scripting.Dictionary
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("zsqrytoJson")
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset()
Set qdf = Nothing
Set coll = New VBA.Collection
' Set db = CurrentDb
' Set rs = db.OpenRecordset(SQL_SELECT, dbOpenSnapshot)
If Not rs.BOF And Not rs.EOF Then
Do While Not rs.EOF
Set dict = New Scripting.Dictionary
For Each fld In rs.Fields
dict.ADD fld.Name, rs.Fields(fld.Name).Value
Next fld
coll.ADD dict
rs.MoveNext
Loop
End If
rs.Close
Set fld = Nothing
Set rs = Nothing
Set db = Nothing
Set dict = Nothing
'MsgBox JsonConverter.ConvertToJson(coll, Whitespace:=3)
JsonnResult = JsonConverter.ConvertToJson(coll, Whitespace:=3)
Set coll = Nothing
