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:"abcdefghijklmnopqrstuvwxyz";
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:"abcdefghijklmnopqrstuvwxyz";
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
- column name contain ":" example a:4, s27, etc
- 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.