Share via

I'm trying to import csv file that downloaded from WooCommerce, but not sure what is the data type for 1 of the main column in the file. Please help

Wai Loon Ho 1 Reputation point
2021-08-19T13:42:20.167+00:00

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.

Developer technologies Transact-SQL

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Michael Taylor 60,161 Reputation points
    2021-08-19T14:13:57.35+00:00

    That is not JSON or XML. I don't recognize it as any standard data format. You'll need to read it as simple text and then parse out the data you want. Honestly I think this is better handled outside T-SQL. I think SSIS would be a better choice to import the data if you need to get it into SQL. You could use SSIS to read the raw data, parse the data fields you need using a combination of SQL scripts and/or C#/VB script tasks and then put the data into the DB in the format you actually care about rather than trying to parse this on the fly.

    However if you want to parse on the fly (noting that it may be possible to incorrectly parse some values) then you're going to need multiple steps. I would start with finding the _gravity_form_lead value and then take everything after that. But I'm guessing here the format of a "line" is c:#[:"label"];

    You can parse this in a typical programming language (hence SSIS recommendation). Note that the label is optional and that in your example after a:7 it violates that rule as it has a subsequent value without the semicolon on the end. Here's a simple query to get to the start of the value you asked for. 

    SELECT SUBSTRING(@value, CHARINDEX('"_gravity_form_lead";', @value), 1000)

    But from this point on it gets hard as I suspect you only want the next value (a7) but that is delimited by curly braces. If you are guaranteed that the "inner" value won't have any curly braces then you can add additional CHARINDEX to find the closing curly and then use SUBSTRING to get just the raw value. Of course if you really only care about the inner object itself then once you find the "_gravity_form_lead" field then use CHARINDEX to find the open and close curly braces to get the inner data. This is all relying on the sample data you gave being correct though.

    0 comments
  2. MelissaMa-MSFT 24,221 Reputation points
    2021-08-20T03:20:35.177+00:00

    Hi @Wai Loon Ho ,

    Welcome to Microsoft Q&A!

    It could be better to deal with this data truncation in your CSV file.

    If above is not working, you could refer below TSQL method from below and check whether it is helpful to you. 

    create table #temp  
(ID int identity(1,1),  
_gravity_forms_history varchar(max))  
  
insert into #temp (_gravity_forms_history) values  
('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";}}'),  
	 ('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";}}')  
  
select SUBSTRING(_gravity_forms_history,CHARINDEX('s:18:"_gravity_form_lead";',_gravity_forms_history)+len('s:18:"_gravity_form_lead";'),CHARINDEX('s:18:"_gravity_form_data"',_gravity_forms_history)-CHARINDEX('s:18:"_gravity_form_lead";',_gravity_forms_history)-len('s:18:"_gravity_form_lead";')) from #temp

    Best regards,
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  3. ZoeHui-MSFT 41,491 Reputation points
    2021-08-20T06:34:45.007+00:00

    Hi @Wai Loon Ho ,

    You may also have a try via SSIS.

    Just a workaround that you could use token function in Derived Column to get the data you want in the second part data with "{}"

    {

         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:"";  
 }

    The prerequisite is that all your column data is composed of " {} " and the data you want is in the second "{}".

    Regards，

    Zoe

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues October

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.