August 19, 2021 at 1:47 pm
Hi
I have a woocommerce web site, I need to use the order data to generate report in MSSQL.
I everyday download the Orders data from WooCommerce and import into MSSQL.
But I have a problem with 1 of the column in the CSV file, the column header is _gravity_forms_history.
The value in the column "_gravity_forms_history" is as below.
Example 1
a:4:
{
s:27:"_gravity_form_cart_item_key";
s:32:"fd64dbd849b10f43b2ffaf5793a81686";
s:29:"_gravity_form_linked_entry_id";
i:147396;
s:18:"_gravity_form_lead";
a:7:
{
s:7:"form_id";
s:1:"1";
s:10:"source_url";
s:33:"https://staging/zh/product/0303l/";
s:2:"ip";
s:15:"202.186.222.119";
i:1;
s:9:"Wesley Gujin";
i:2;
s:10:"2021-08-20";
i:3;
s:5:"Other";
i:4;
s:25:"This is a test data";
}
s:18:"_gravity_form_data";a:28:{s:2:"id";s:1:"1";s:7:"bulk_id";i:0;s:13:"display_title";b:0;s:19:"display_description";b:0;s:25:"disable_woocommerce_price";s:2:"no";s:12:"price_before";s:0:"";s:11:"price_after";s:0:"";s:20:"disable_calculations";s:3:"yes";s:22:"disable_label_subtotal";s:2:"no";s:21:"disable_label_options";s:2:"no";s:19:"disable_label_total";s:2:"no";s:14:"disable_anchor";s:2:"no";s:14:"label_subtotal";s:8:"Subtotal";s:13:"label_options";s:7:"Options";s:11:"label_total";s:5:"Total";s:8:"use_ajax";s:2:"no";s:16:"enable_cart_edit";s:2:"no";s:23:"enable_cart_edit_remove";s:3:"yes";s:17:"keep_cart_entries";s:2:"no";s:18:"send_notifications";s:2:"no";s:31:"enable_cart_quantity_management";s:2:"no";s:19:"cart_quantity_field";s:0:"";s:22:"update_payment_details";s:2:"no";s:23:"display_totals_location";s:5:"after";s:24:"structured_data_override";s:2:"no";s:25:"structured_data_low_price";s:0:"";s:26:"structured_data_high_price";s:0:"";s:29:"structured_data_override_type";s:6:"append";}}
Example 2
a:4:
{
s:27:"_gravity_form_cart_item_key";
s:32:"4cce2b242234750791b749fa5ccecbae";
s:29:"_gravity_form_linked_entry_id";
i:147400;
s:18:"_gravity_form_lead";
a:7:
{
s:7:"form_id";
s:2:"14";
s:10:"source_url";
s:33:"https://puti.my/zh/product/0310l/";
s:2:"ip";
s:15:"202.186.222.119";
i:3;
s:15:"Simon Chew";
i:4;
s:10:"2021-08-20";
i:5;
s:19:"Please provide quotation";
i:6;
s:0:"";
}s:18:"_gravity_form_data";a:28:{s:2:"id";s:2:"14";s:7:"bulk_id";i:0;s:13:"display_title";b:0;s:19:"display_description";b:0;s:25:"disable_woocommerce_price";s:2:"no";s:12:"price_before";s:0:"";s:11:"price_after";s:0:"";s:20:"disable_calculations";s:3:"yes";s:22:"disable_label_subtotal";s:2:"no";s:21:"disable_label_options";s:2:"no";s:19:"disable_label_total";s:2:"no";s:14:"disable_anchor";s:2:"no";s:14:"label_subtotal";s:8:"Subtotal";s:13:"label_options";s:7:"Options";s:11:"label_total";s:5:"Total";s:8:"use_ajax";s:2:"no";s:16:"enable_cart_edit";s:2:"no";s:23:"enable_cart_edit_remove";s:3:"yes";s:17:"keep_cart_entries";s:2:"no";s:18:"send_notifications";s:2:"no";s:31:"enable_cart_quantity_management";s:2:"no";s:19:"cart_quantity_field";s:0:"";s:22:"update_payment_details";s:2:"no";s:23:"display_totals_location";s:5:"after";s:24:"structured_data_override";s:2:"no";s:25:"structured_data_low_price";s:0:"";s:26:"structured_data_high_price";s:0:"";s:29:"structured_data_override_type";s:6:"append";}}
Above is the sample data for column "_gravity_forms_history", and the data I needed the most is those structure data after "_gravity_form_lead" means is a:7.
I tried to use OPENJSON, but I hit error message "Msg 13609, Level 16, State 4, Line 86
JSON text is not properly formatted. Unexpected character 'a' is found at position 4."
I suspect this error is due to
1. column name contain ":" example a:4, s27, etc
2. column name dont have double quote ""
May I know how to I ready the data for a:7 after the "_gravity_form_lead"?
Really need help on this.
August 19, 2021 at 3:27 pm
It's not a csv. There is no single, consistent delimiter. This is invalid json. json keys/names should be enclosed in quotes. Those that contain delimiters must be enclosed in quotes.
If this is how woocommerce creates the json, you should submit a bug.
Do your fields have to contain colons?
In the meantime, you will probably either have to do a find & replace to fix the file so that it is proper json, or parse it into another format. In regex terms, you need to replace
[a-z]:[0-9]*
with
"[a-z]:[0-9]*"
(i.e., wrap the original expression in quotes -- not that the above is just pseudocode and is not the proper syntax with grouping required to actually return the original values wrapped in quotes)
August 19, 2021 at 3:37 pm
I see 2 potential options here:
1 - contact WooCommerce to see if they can fix their data export to be in proper JSON format
2 - parse the text before importing it.
For option 2, it really depends on how you are importing the data into SQL Server. If you are using SSIS, you can probably get by with a script component to change the data around. Alternately, you could import the data as is into a staging table on the SQL Server side and then parse it that way. Another way would be to build a .NET application to parse the file and import it.
Option 1 you can validate that their output isn't valid JSON by copy-pasting it to a JSON formatter such as:
https://formatjson.com/onefile/
That site, when you paste your JSON in will tell you what is wrong with the JSON input. Using that, we can see that the first problem is that the first character is not valid for a JSON object. It needs to start with a {. Next, it says that property keys must be double-quoted. As you start fixing those things, you will note that it complains about the ; character as it expects a comma. So the text needs to be in properly formatted JSON before you can use a JSON parser to work with it.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 23, 2021 at 1:19 am
Hi all
Thanks for the reply.
Actually I use gravity form in WooCommerce for customer to enter data when make order.
Currently everyday I download the Order CSV and Form data CVS from WooCommerce and use a C# with MSSQL to generate report for management.
This "_gravity_forms_history" is from Order CSV file, I'm not sure is that JSON type cause I'm new to JSON.
I need to call for help cause this field contain the data I needed, with this field, I dont need to download the Form data CSV.
If I can't use this field, then I need to continue download Form data CSV everyday for generate report.
I'm currently trying to read this field and use substring to get data. But dont think this is faster than the current way by merging the order csv and form csv to generate final report out.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply