讀取 JSON 檔案。
選擇變數
ReadOnlyVariables 選擇檔案來源。
輸出欄位設定
新增要輸出的欄位名稱與資料型態。
注意：欄位的資料類型需與資料庫類型一致，若不一致在 insert 資料庫時會出現錯誤。
編輯指令碼
- 定義輸出 string Class：C# 讀取 json 檔案有一定格式，若字串中包含字串，需在另外定義裡面那層的資料行。若不知怎麼撰寫輸出字串，可參考 https://json2csharp.com/ json 檔案轉成 C# Class，但要先將 json 檔去頭去尾，如下方 example json 範例。
- 讀取 json 檔案並指定到輸出資料行。
重點整理：
- 詳細反序列化教學，可參考：http://vito-note.blogspot.com/2013/01/json.html。
- 若 json 第一層向下還有包其他字串，該字串也要建立 class（如 membership_tier），class 要在第一層實體化，如以下：
public MembershipTier membership_tier { get; set; }
- 若 json 字串中第一層為陣列，記得要去頭去尾再做反序列化。C# 在讀取 json 檔案時，不可以出現 null，如果字串中包含 null，記得使用 Replace() 將 null 取代為空字串。
- 如果 json 標頭包含非 C# 可使用的符號，如「-」，可用兩種方式解決：
在 class 中使用 [JsonProperty("zh-hant")]，但此方法對特定版本的 .NET 無效。
使用 Replace() 把標頭取代為其他文字。
- 若輸出的字串是在第一層字串底下，可用以下方式表達。
OutPutBufferBuffer.membertier = customer.membership_tier.name_translations.ZhHant;
- API 丟出來的時間型態通常為 DateTimeOffSet & UTC+0 時區，若要在程式中直接轉為系統時區，可用 ToLocalTime() 轉換，並用 ToString("yyyy-MM-dd HH:mm:ss") 標準化時間格式。
Original JSON File
{
"items": [
{
"id": "613ddb00ebbd52000183edb4",
"name": "XXX",
"email": "XXX@gmail.com",
"gender": "female",
"birthday": "2000-03-01",
"memo": null,
"phones": [
"0900000000"
],
"phone": "0900000000",
"phone_country_code": null,
"mobile_phone": "0900000000",
"mobile_phone_verified": true,
"mobile_phone_country_calling_code": "886",
"locale_code": "zh-hant",
"order_count": 2,
"orders_total_sum": {
"cents": 3260,
"currency_symbol": "NT$",
"currency_iso": "TWD",
"label": "NT$3,260",
"dollars": 3260.0
},
"is_member": true,
"is_blacklisted": false,
"is_subscribed_marketing_email": true,
"credit_balance": 0,
"member_point_balance": 8698,
"custom_data": [],
"membership_tier": {
"level": 3,
"name_translations": {
"zh-hant": "WU黑卡"
},
"status": "active",
"id": "60d593cd1c03d2002cc92c95"
},
"delivery_addresses": [
{
"city": "台北市",
"postcode": "104",
"address_1": "XXXXXX",
"address_2": "XXXX",
"recipient_name": "XXX",
"recipient_phone": "0900000000",
"recipient_phone_country_code": null,
"country": "TW",
"state": "",
"key": null,
"layer1": null,
"layer2": null,
"layer3": null,
"district": null,
"logistic_codes": []
}
],
"subscribed_email_types": [
"marketing.news"
],
"ref_user_id": null,
"line_id": null,
"facebook_id": null,
"updated_at": "2021-09-24T14:48:24.918+00:00",
"created_at": "2021-09-12T10:48:32.542+00:00",
"current_sign_in_at": "2021-09-23T05:49:52.677+00:00",
"last_sign_in_at": "2021-09-15T02:05:59.031+00:00",
"registered_at": "2019-12-12T16:00:00.000+00:00",
"created_by": "admin",
"tags": [],
"customer_authentication_linkings": [],
"tier_expires_at": "2022-09-12T15:59:59.999+00:00",
"utm_data": {},
"referral_code": null
}
],
"pagination": {
"current_page": 1,
"per_page": 24,
"total_pages": 1,
"total_count": 1
}
}example json
[
{
"id": "613ddb00ebbd52000183edb4",
"name": "XXX",
"email": "XXX@gmail.com",
"gender": "female",
"birthday": "2000-03-01",
"memo": null,
"phones": [
"0900000000"
],
"phone": "0900000000",
"phone_country_code": null,
"mobile_phone": "0900000000",
"mobile_phone_verified": true,
"mobile_phone_country_calling_code": "886",
"locale_code": "zh-hant",
"order_count": 2,
"orders_total_sum": {
"cents": 3260,
"currency_symbol": "NT$",
"currency_iso": "TWD",
"label": "NT$3,260",
"dollars": 3260.0
},
"is_member": true,
"is_blacklisted": false,
"is_subscribed_marketing_email": true,
"credit_balance": 0,
"member_point_balance": 8698,
"custom_data": [],
"membership_tier": {
"level": 3,
"name_translations": {
"zh-hant": "WU黑卡"
},
"status": "active",
"id": "60d593cd1c03d2002cc92c95"
},
"delivery_addresses": [
{
"city": "台北市",
"postcode": "104",
"address_1": "XXX",
"address_2": "XXX",
"recipient_name": "XXX",
"recipient_phone": "0900000000",
"recipient_phone_country_code": null,
"country": "TW",
"state": "",
"key": null,
"layer1": null,
"layer2": null,
"layer3": null,
"district": null,
"logistic_codes": []
}
],
"subscribed_email_types": [
"marketing.news"
],
"ref_user_id": null,
"line_id": null,
"facebook_id": null,
"updated_at": "2021-09-24T14:48:24.918+00:00",
"created_at": "2021-09-12T10:48:32.542+00:00",
"current_sign_in_at": "2021-09-23T05:49:52.677+00:00",
"last_sign_in_at": "2021-09-15T02:05:59.031+00:00",
"registered_at": "2019-12-12T16:00:00.000+00:00",
"created_by": "admin",
"tags": [],
"customer_authentication_linkings": [],
"tier_expires_at": "2022-09-12T15:59:59.999+00:00",
"utm_data": {},
"referral_code": null
}
]Customer.cs
public class OrdersTotalSum
{
public int cents { get; set; }
public string currency_symbol { get; set; }
public string currency_iso { get; set; }
public string label { get; set; }
public decimal dollars { get; set; }
}
public class NameTranslations
{
//[JsonProperty("zh-hant")]
public string ZhHant { get; set; }
}
public class MembershipTier
{
public int level { get; set; }
public NameTranslations name_translations { get; set; }
public string status { get; set; }
public string id { get; set; }
}
public class UtmData
{
}
public class Root
{
public string id { get; set; }
public string name { get; set; }
public string email { get; set; }
public string gender { get; set; }
public string birthday { get; set; }
public string memo { get; set; }
public List<string> phones { get; set; }
public string phone { get; set; }
public string phone_country_code { get; set; }
public string mobile_phone { get; set; }
public string mobile_phone_verified { get; set; }
public string mobile_phone_country_calling_code { get; set; }
public string locale_code { get; set; }
public int order_count { get; set; }
public OrdersTotalSum orders_total_sum { get; set; }
public string is_member { get; set; }
public string is_blacklisted { get; set; }
public string is_subscribed_marketing_email { get; set; }
public int credit_balance { get; set; }
public int member_point_balance { get; set; }
public List<object> custom_data { get; set; }
public MembershipTier membership_tier { get; set; }
//public List<DeliveryAddress> delivery_addresses { get; set; }
public List<string> subscribed_email_types { get; set; }
public string ref_user_id { get; set; }
public string line_id { get; set; }
public string facebook_id { get; set; }
public DateTimeOffset updated_at { get; set; }
public DateTimeOffset created_at { get; set; }
public DateTimeOffset current_sign_in_at { get; set; }
public DateTimeOffset last_sign_in_at { get; set; }
public DateTimeOffset registered_at { get; set; }
public string created_by { get; set; }
public List<object> tags { get; set; }
public List<object> customer_authentication_linkings { get; set; }
public DateTimeOffset tier_expires_at { get; set; }
public UtmData utm_data { get; set; }
public string referral_code { get; set; }
}main.cs
// ...
using System.IO;
using System.Web.Script.Serialization;
using System.Windows.Forms; // debug可用
using SC_923baafe8d8c41aabccc7207e1e95ea2;
// ...
public override void CreateNewOutputRows()
{
/*
Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
*/
string file = File.ReadAllText(Variables.SourceFileName);
String jsonFileContent = file.Substring(9, file.LastIndexOf("pagination")-11)
.Replace(@"""membership_tier"":null", @"""membership_tier"":{""level"":0,""name_translations"":{""zh-hant"":""""},""status"":""active"",""id"":""""}")
.Replace("null", @"""""")
.Replace(@"""zh-hant"":", @"""ZhHant"":");
//String jsonFileContent = file.Replace("null", @"""""").Replace(@"""zh-hant"":", @"""ZhHant"":");
MessageBox.Show(jsonFileContent);
JavaScriptSerializer js = new JavaScriptSerializer();
js.MaxJsonLength = 2048000000;
try
{
List<Root> customers = js.Deserialize<List<Root>>(jsonFileContent);
if (customers == null)
{
MessageBox.Show("List is null !!");
}
foreach (var customer in customers)
{
OutPutBufferBuffer.AddRow();
OutPutBufferBuffer.id = customer.id;
OutPutBufferBuffer.name = customer.name;
OutPutBufferBuffer.email = customer.email;
OutPutBufferBuffer.gender = customer.gender;
OutPutBufferBuffer.birthday = customer.birthday;
OutPutBufferBuffer.memo = customer.memo;
//OutPutBufferBuffer.phones = customer.phones;
OutPutBufferBuffer.mobilephone = customer.mobile_phone;
OutPutBufferBuffer.mobilephoneverified = customer.mobile_phone_verified;
OutPutBufferBuffer.mobilephonecountrycallingcode = customer.mobile_phone_country_calling_code;
OutPutBufferBuffer.localecode = customer.locale_code;
OutPutBufferBuffer.ordercount = customer.order_count;
//OutPutBufferBuffer.orderstotalsum = customer.orders_total_sum;
OutPutBufferBuffer.ismember = customer.is_member;
OutPutBufferBuffer.isblacklisted = customer.is_blacklisted;
OutPutBufferBuffer.issubscribedmarketingemail = customer.is_subscribed_marketing_email;
OutPutBufferBuffer.memberpointbalance = customer.member_point_balance;
//OutPutBufferBuffer.customdata = customer.custom_data;
OutPutBufferBuffer.membertier = customer.membership_tier.name_translations.ZhHant;
//OutPutBufferBuffer.deliveryaddress = customer.delivery_address;
//OutPutBufferBuffer.subscribedemailtypes = customer.subscribed_email_types;
OutPutBufferBuffer.refuserid = customer.ref_user_id;
OutPutBufferBuffer.lineid = customer.line_id;
OutPutBufferBuffer.facebookid = customer.facebook_id;
OutPutBufferBuffer.updatedat = customer.updated_at.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss");
OutPutBufferBuffer.createdat = customer.created_at.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss");
OutPutBufferBuffer.currentsigninat = customer.current_sign_in_at.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss");
OutPutBufferBuffer.lastsigninat = customer.last_sign_in_at.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss");
OutPutBufferBuffer.registeredat = customer.registered_at.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss");
OutPutBufferBuffer.createdby = customer.created_by;
//OutPutBufferBuffer.tags = customer.tags;
//OutPutBufferBuffer.customerauthenticationlinkings = customer.customer_authentication_linkings;
OutPutBufferBuffer.tierexpireat = customer.tier_expires_at.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}只有自己摸索過，才能內化轉換成力量。