Thanks for the question, Sherry.
Asked: April 03, 2020 - 8:11 pm UTC
Last updated: April 06, 2020 - 1:08 am UTC
Version: 1.0
Viewed 1000+ times
You Asked
I have a CSV data file that I load into MS Access. No problem doing that. I’ve got it all loaded.
My issue is that one of the records in the file contains a field that contains CSV data (JSON data) which is line item invoice data (ProductData) and it may contain up to 25 line items.
What I’m trying to do is parse out that field into multiple records in a different table all related by the number after “500” which is the transaction number.
Does anyone have any ideas on how to do this in Microsoft Access please?
Sample data:
500,3,30600052763,30128903358,"{""type"": ""reportingData"", ""merStoreId"": ""8135001"", ""productData"": [{""productCode"": ""001"", ""totalAmount"": ""20.00"", ""quantity"": ""16.000"", ""unitPrice"": ""1.25"", ""tax1Amount"": ""6.22""}, {""productCode"": ""102"", ""totalAmount"": ""14.00"", ""quantity"": ""1.000"", ""unitPrice"": ""14.00""}, {""productCode"": ""963"", ""totalAmount"": ""1.00"", ""quantity"": ""1.000"", ""unitPrice"": ""1.00"", ""tax1Amount"": ""0.00""}]}"
I’ve attached a sample of how I would like the table to look based on transaction number 3 above:
Tbl500
Transaction Number Second Field Third Field Type MerStoreID ProductCode TotalAmount Quantity UnitPrice Tax1Amount Tax2Amount
3 30600052763 0128903358 reportingData 8135001 001 20.00 16.000 1.25 6.22
3 30600052763 30128903358 reportingData 8135001 102 14.00 1.000 14.00
3 30600052763 30128903358 reportingData 8135001 963 1.00 1.000 1.00 0.00
and Connor said...
Is this answer out of date? If it is, please let us know via a Comment