question

WaiLoonHo-3004 avatar image
0 Votes"
WaiLoonHo-3004 asked MelissaMa-msft commented

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

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.


sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @WaiLoonHo-3004,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
cooldadtx avatar image
0 Votes"
cooldadtx answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @WaiLoonHo-3004,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @WaiLoonHo-3004,

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.