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