powershell - 通过 PowerShell 将 CSV 文件转换为复杂的 XML 数组
问题描述
我正在将 CSV 数据转换为 XML 格式，然后这些数据将被加载到多部分表单中，以通过 API 请求发送。目前，我很难为我的数据获得正确的布局。
这是我当前的数据集：
"order_num","delivery_type","account_num","customer_code","customer_location","customer_name","customer_address","customer_city","customer_state","customer_zip","phone1","delivery_date","cod","sku","quantity","description","line_taxes","amount","category_name","open","close","time_window_open","time_window_close","size_1","size_2","size_3","category_1_size_1","category_1_size_2","category_1_size_3","category_2_size_1","category_2_size_2","category_2_size_3","category_3_size_1","category_3_size_2","category_3_size_3","comment_1","comment_2","comment_3","latitude","longitude",
"3238204","Delivery","SOUTH"," 10309"," ","MADISON BISTRO","135 N. COURT STREET","WAMPSVILLE","NY","13163","3152800228","07/09/19"," "," 13075","1","BACON E.Z. PAN EXTRA SMOKE 10-12/LB"," ","81.57","Frozen","900","2000","900","1300",".84","21.00","1.00"," "," "," "," "," "," "," "," "," ","DRIVER BRING CURB RAMP"," "," ","43.0805","-75.7077",
"3238204","Delivery","SOUTH"," 10309"," ","MADISON BISTRO","135 N. COURT STREET","WAMPSVILLE","NY","13163","3152800228","07/09/19"," "," 22027","1","CORNED BEEF ROUND COOKED"," ","43.73","Refer","900","2000","900","1300",".35","12.82","1.00"," "," "," "," "," "," "," "," "," ","DRIVER BRING CURB RAMP"," "," ","43.0805","-75.7077",
"3238204","Delivery","SOUTH"," 10309"," ","MADISON BISTRO","135 N. COURT STREET","WAMPSVILLE","NY","13163","3152800228","07/09/19"," "," 25052","1","CHEESE CHEDDAR SHARP WHITE BLOCK"," ","32.70","Refer","900","2000","900","1300",".23","10.22","1.00"," "," "," "," "," "," "," "," "," ","DRIVER BRING CURB RAMP"," "," ","43.0805","-75.7077",
"3238217","Delivery","SOUTH"," 13093"," ","OLD SCHOOL BAR AND GRILL","600 CULVER AVE SUITE 3","UTICA","NY","13501","3157971980","07/09/19"," "," 12123","4","BEEF BUTT TENDERS FROZEN"," ","220.91","Frozen","830","2100","900","1300","3.44","141.64","4.00"," "," "," "," "," "," "," "," "," "," "," "," ","43.0902","-75.1987",
"3238217","Delivery","SOUTH"," 13093"," ","OLD SCHOOL BAR AND GRILL","600 CULVER AVE SUITE 3","UTICA","NY","13501","3157971980","07/09/19"," "," 88184","1","PEPPERS LONG HOT"," ","58.90","Refer","830","2100","900","1300","1.39","21.75","1.00"," "," "," "," "," "," "," "," "," "," "," "," ","43.0902","-75.1987",
"3238217","Delivery","SOUTH"," 13093"," ","OLD SCHOOL BAR AND GRILL","600 CULVER AVE SUITE 3","UTICA","NY","13501","3157971980","07/09/19"," "," 88137","10","LETTUCE ROMAINE CHOPPED"," ","22.81","Refer","830","2100","900","1300","10.32","130.00","10.00"," "," "," "," "," "," "," "," "," "," "," "," ","43.0902","-75.1987",
以下是我目前使用的 PowerShell 代码：
$OrderFile = "C:\users\mvincenty\desktop\Order Data2.csv"
$OrderData = Import-Csv -path $OrderFile
$OutputXML = "C:\users\mvincenty\desktop\orders.xml"
$TemplateCust = @'
<service_orders>
<service_order>
<account_name>$($item.customer_name)</account_name>
<order_number>$($item.order_num)</order_number>
<delivery_type>$($item.delivery_type)</delivery_type>
<customer_code>$($item.customer_code)</customer_code>
<delivery_date>$($item.delivery_date)</delivery_date>
<cod>$($item.cod)</cod>
<note></note>
<customer>
<number>$($item.customer_code)</number>
<delivery_type>$($item.delivery_type)</delivery_type>
<customer_name>$($item.customer_name)</customer_name>
<address1>$($item.customer_address)</address1>
<city>$($item.customer_city)</city>
<state>$($item.customer_state)</state>
<zip>$($item.customer_zip)</zip>
<phone1>$($item.phone1)</phone1>
<open_time>$($item.open)</open_time>
<close_time>$($item.close)</close_time>
<window_start_time_1>$($item.time_Window_open)</window_start_time_1>
<window_end_time_1>$($item.time_Window_close)</window_end_time_1>
<latitude>$($item.latitude)</latitude>
<longitude>$($item.longitude)</longitude>
<category_1_size_1>$($item.category_1_size_1)</category_1_size_1>
<category_1_size_2>$($item.category_1_size_2)</category_1_size_2>
<category_1_size_3>$($item.category_1_size_3)</category_1_size_3>
<category_2_size_1>$($item.category_2_size_1)</category_2_size_1>
<category_2_size_2>$($item.category_2_size_2)</category_2_size_2>
<category_2_size_3>$($item.category_2_size_3)</category_2_size_3>
<category_3_size_1>$($item.category_3_size_1)</category_3_size_1>
<category_3_size_2>$($item.category_3_size_2)</category_3_size_2>
<category_3_size_3>$($item.category_3_size_3)</category_3_size_3>
</customer>
<line_items>
$($items -join "`n")
</line_items>
</service_order>
</service_orders>
'@
$TemplateItems = @'
<line_item>
<serial_number>$($item.sku)</serial_number>
<quantity>$($item.quantity)</quantity>
<amount>$($item.amount)</amount>
<description>$($item.description)</description>
<line_taxes>$($item.line_taxes)</line_taxes>
<category_name>$($item.category_name)</category_name>
<size_1>$($item.size_1)</size_1>
<size_2>$($item.size_2)</size_2>
<size_3>$($item.size_3)</size_3>
</line_item>
'@
$xml = $OrderData | Group-Object order_num -ov grp| ForEach-Object {
$items = foreach ($item in $_.Group) {
$ExecutionContext.InvokeCommand.ExpandString($TemplateItems)
}
$ExecutionContext.InvokeCommand.ExpandString($TemplateCust)
}
$xml |Out-File $OutputXML
这是我目前得到的输出：
<service_orders>
<service_order>
<account_name>MADISON BISTRO</account_name>
<order_number>3238204</order_number>
<delivery_type>Delivery</delivery_type>
<customer_code> 10309</customer_code>
<delivery_date>07/09/19</delivery_date>
<cod> </cod>
<note></note>
<customer>
<number> 10309</number>
<delivery_type>Delivery</delivery_type>
<customer_name>MADISON BISTRO</customer_name>
<address1>135 N. COURT STREET</address1>
<city>WAMPSVILLE</city>
<state>NY</state>
<zip>13163</zip>
<phone1>3152800228</phone1>
<open_time>900</open_time>
<close_time>2000</close_time>
<window_start_time_1>900</window_start_time_1>
<window_end_time_1>1300</window_end_time_1>
<latitude>43.0805</latitude>
<longitude>-75.7077</longitude>
<category_1_size_1> </category_1_size_1>
<category_1_size_2> </category_1_size_2>
<category_1_size_3> </category_1_size_3>
<category_2_size_1> </category_2_size_1>
<category_2_size_2> </category_2_size_2>
<category_2_size_3> </category_2_size_3>
<category_3_size_1> </category_3_size_1>
<category_3_size_2> </category_3_size_2>
<category_3_size_3> </category_3_size_3>
</customer>
<line_items>
<line_item>
<serial_number> 13075</serial_number>
<quantity>1</quantity>
<amount>81.57</amount>
<description>BACON E.Z. PAN EXTRA SMOKE 10-12/LB</description>
<line_taxes> </line_taxes>
<category_name>Frozen</category_name>
<size_1>.84</size_1>
<size_2>21.00</size_2>
<size_3>1.00</size_3>
</line_item>
<line_item>
<serial_number> 22027</serial_number>
<quantity>1</quantity>
<amount>43.73</amount>
<description>CORNED BEEF ROUND COOKED</description>
<line_taxes> </line_taxes>
<category_name>Refer</category_name>
<size_1>.35</size_1>
<size_2>12.82</size_2>
<size_3>1.00</size_3>
</line_item>
<line_item>
<serial_number> 25052</serial_number>
<quantity>1</quantity>
<amount>32.70</amount>
<description>CHEESE CHEDDAR SHARP WHITE BLOCK</description>
<line_taxes> </line_taxes>
<category_name>Refer</category_name>
<size_1>.23</size_1>
<size_2>10.22</size_2>
<size_3>1.00</size_3>
</line_item>
</line_items>
</service_order>
</service_orders>
<service_orders>
<service_order>
<account_name>OLD SCHOOL BAR AND GRILL</account_name>
<order_number>3238217</order_number>
<delivery_type>Delivery</delivery_type>
<customer_code> 13093</customer_code>
<delivery_date>07/09/19</delivery_date>
<cod> </cod>
<note></note>
<customer>
<number> 13093</number>
<delivery_type>Delivery</delivery_type>
<customer_name>OLD SCHOOL BAR AND GRILL</customer_name>
<address1>600 CULVER AVE SUITE 3</address1>
<city>UTICA</city>
<state>NY</state>
<zip>13501</zip>
<phone1>3157971980</phone1>
<open_time>830</open_time>
<close_time>2100</close_time>
<window_start_time_1>900</window_start_time_1>
<window_end_time_1>1300</window_end_time_1>
<latitude>43.0902</latitude>
<longitude>-75.1987</longitude>
<category_1_size_1> </category_1_size_1>
<category_1_size_2> </category_1_size_2>
<category_1_size_3> </category_1_size_3>
<category_2_size_1> </category_2_size_1>
<category_2_size_2> </category_2_size_2>
<category_2_size_3> </category_2_size_3>
<category_3_size_1> </category_3_size_1>
<category_3_size_2> </category_3_size_2>
<category_3_size_3> </category_3_size_3>
</customer>
<line_items>
<line_item>
<serial_number> 12123</serial_number>
<quantity>4</quantity>
<amount>220.91</amount>
<description>BEEF BUTT TENDERS FROZEN</description>
<line_taxes> </line_taxes>
<category_name>Frozen</category_name>
<size_1>3.44</size_1>
<size_2>141.64</size_2>
<size_3>4.00</size_3>
</line_item>
<line_item>
<serial_number> 88184</serial_number>
<quantity>1</quantity>
<amount>58.90</amount>
<description>PEPPERS LONG HOT</description>
<line_taxes> </line_taxes>
<category_name>Refer</category_name>
<size_1>1.39</size_1>
<size_2>21.75</size_2>
<size_3>1.00</size_3>
</line_item>
<line_item>
<serial_number> 88137</serial_number>
<quantity>10</quantity>
<amount>22.81</amount>
<description>LETTUCE ROMAINE CHOPPED</description>
<line_taxes> </line_taxes>
<category_name>Refer</category_name>
<size_1>10.32</size_1>
<size_2>130.00</size_2>
<size_3>10.00</size_3>
</line_item>
</line_items>
</service_order>
</service_orders>
但我正在寻找的输出是：在 services_orders 下组合的所有 service_orders
<service_orders>
<service_order>
<account_name>MADISON BISTRO</account_name>
<order_number>3238204</order_number>
<delivery_type>Delivery</delivery_type>
<customer_code> 10309</customer_code>
<delivery_date>07/09/19</delivery_date>
<cod> </cod>
<note></note>
<customer>
<number> 10309</number>
<delivery_type>Delivery</delivery_type>
<customer_name>MADISON BISTRO</customer_name>
<address1>135 N. COURT STREET</address1>
<city>WAMPSVILLE</city>
<state>NY</state>
<zip>13163</zip>
<phone1>3152800228</phone1>
<open_time>900</open_time>
<close_time>2000</close_time>
<window_start_time_1>900</window_start_time_1>
<window_end_time_1>1300</window_end_time_1>
<latitude>43.0805</latitude>
<longitude>-75.7077</longitude>
<category_1_size_1> </category_1_size_1>
<category_1_size_2> </category_1_size_2>
<category_1_size_3> </category_1_size_3>
<category_2_size_1> </category_2_size_1>
<category_2_size_2> </category_2_size_2>
<category_2_size_3> </category_2_size_3>
<category_3_size_1> </category_3_size_1>
<category_3_size_2> </category_3_size_2>
<category_3_size_3> </category_3_size_3>
</customer>
<line_items>
<line_item>
<serial_number> 13075</serial_number>
<quantity>1</quantity>
<amount>81.57</amount>
<description>BACON E.Z. PAN EXTRA SMOKE 10-12/LB</description>
<line_taxes> </line_taxes>
<category_name>Frozen</category_name>
<size_1>.84</size_1>
<size_2>21.00</size_2>
<size_3>1.00</size_3>
</line_item>
<line_item>
<serial_number> 22027</serial_number>
<quantity>1</quantity>
<amount>43.73</amount>
<description>CORNED BEEF ROUND COOKED</description>
<line_taxes> </line_taxes>
<category_name>Refer</category_name>
<size_1>.35</size_1>
<size_2>12.82</size_2>
<size_3>1.00</size_3>
</line_item>
<line_item>
<serial_number> 25052</serial_number>
<quantity>1</quantity>
<amount>32.70</amount>
<description>CHEESE CHEDDAR SHARP WHITE BLOCK</description>
<line_taxes> </line_taxes>
<category_name>Refer</category_name>
<size_1>.23</size_1>
<size_2>10.22</size_2>
<size_3>1.00</size_3>
</line_item>
</line_items>
</service_order>
<service_order>
<account_name>OLD SCHOOL BAR AND GRILL</account_name>
<order_number>3238217</order_number>
<delivery_type>Delivery</delivery_type>
<customer_code> 13093</customer_code>
<delivery_date>07/09/19</delivery_date>
<cod> </cod>
<note></note>
<customer>
<number> 13093</number>
<delivery_type>Delivery</delivery_type>
<customer_name>OLD SCHOOL BAR AND GRILL</customer_name>
<address1>600 CULVER AVE SUITE 3</address1>
<city>UTICA</city>
<state>NY</state>
<zip>13501</zip>
<phone1>3157971980</phone1>
<open_time>830</open_time>
<close_time>2100</close_time>
<window_start_time_1>900</window_start_time_1>
<window_end_time_1>1300</window_end_time_1>
<latitude>43.0902</latitude>
<longitude>-75.1987</longitude>
<category_1_size_1> </category_1_size_1>
<category_1_size_2> </category_1_size_2>
<category_1_size_3> </category_1_size_3>
<category_2_size_1> </category_2_size_1>
<category_2_size_2> </category_2_size_2>
<category_2_size_3> </category_2_size_3>
<category_3_size_1> </category_3_size_1>
<category_3_size_2> </category_3_size_2>
<category_3_size_3> </category_3_size_3>
</customer>
<line_items>
<line_item>
<serial_number> 12123</serial_number>
<quantity>4</quantity>
<amount>220.91</amount>
<description>BEEF BUTT TENDERS FROZEN</description>
<line_taxes> </line_taxes>
<category_name>Frozen</category_name>
<size_1>3.44</size_1>
<size_2>141.64</size_2>
<size_3>4.00</size_3>
</line_item>
<line_item>
<serial_number> 88184</serial_number>
<quantity>1</quantity>
<amount>58.90</amount>
<description>PEPPERS LONG HOT</description>
<line_taxes> </line_taxes>
<category_name>Refer</category_name>
<size_1>1.39</size_1>
<size_2>21.75</size_2>
<size_3>1.00</size_3>
</line_item>
<line_item>
<serial_number> 88137</serial_number>
<quantity>10</quantity>
<amount>22.81</amount>
<description>LETTUCE ROMAINE CHOPPED</description>
<line_taxes> </line_taxes>
<category_name>Refer</category_name>
<size_1>10.32</size_1>
<size_2>130.00</size_2>
<size_3>10.00</size_3>
</line_item>
</line_items>
</service_order>
</service_orders>
解决方案
如果我们继续遵循您的模板格式，那么您可以添加另一个模板/变量来处理外部标签。
$OrderFile = "C:\users\mvincenty\desktop\Order Data2.csv"
$OrderData = Import-Csv -path $OrderFile
$OutputXML = "C:\users\mvincenty\desktop\orders.xml"
$TemplateOuter = @'
<service_orders>
$($xml -join "`r`n")
</service_orders>
'@
$TemplateCust = @'
<service_order>
<account_name>$($item.customer_name)</account_name>
<order_number>$($item.order_num)</order_number>
<delivery_type>$($item.delivery_type)</delivery_type>
<customer_code>$($item.customer_code)</customer_code>
<delivery_date>$($item.delivery_date)</delivery_date>
<cod>$($item.cod)</cod>
<note></note>
<customer>
<number>$($item.customer_code)</number>
<delivery_type>$($item.delivery_type)</delivery_type>
<customer_name>$($item.customer_name)</customer_name>
<address1>$($item.customer_address)</address1>
<city>$($item.customer_city)</city>
<state>$($item.customer_state)</state>
<zip>$($item.customer_zip)</zip>
<phone1>$($item.phone1)</phone1>
<open_time>$($item.open)</open_time>
<close_time>$($item.close)</close_time>
<window_start_time_1>$($item.time_Window_open)</window_start_time_1>
<window_end_time_1>$($item.time_Window_close)</window_end_time_1>
<latitude>$($item.latitude)</latitude>
<longitude>$($item.longitude)</longitude>
<category_1_size_1>$($item.category_1_size_1)</category_1_size_1>
<category_1_size_2>$($item.category_1_size_2)</category_1_size_2>
<category_1_size_3>$($item.category_1_size_3)</category_1_size_3>
<category_2_size_1>$($item.category_2_size_1)</category_2_size_1>
<category_2_size_2>$($item.category_2_size_2)</category_2_size_2>
<category_2_size_3>$($item.category_2_size_3)</category_2_size_3>
<category_3_size_1>$($item.category_3_size_1)</category_3_size_1>
<category_3_size_2>$($item.category_3_size_2)</category_3_size_2>
<category_3_size_3>$($item.category_3_size_3)</category_3_size_3>
</customer>
<line_items>
$($items -join "`n")
</line_items>
</service_order>
'@
$TemplateItems = @'
<line_item>
<serial_number>$($item.sku)</serial_number>
<quantity>$($item.quantity)</quantity>
<amount>$($item.amount)</amount>
<description>$($item.description)</description>
<line_taxes>$($item.line_taxes)</line_taxes>
<category_name>$($item.category_name)</category_name>
<size_1>$($item.size_1)</size_1>
<size_2>$($item.size_2)</size_2>
<size_3>$($item.size_3)</size_3>
</line_item>
'@
$xml = $OrderData | Group-Object order_num -ov grp| ForEach-Object {
$items = foreach ($item in $_.Group) {
$ExecutionContext.InvokeCommand.ExpandString($TemplateItems)
}
$ExecutionContext.InvokeCommand.ExpandString($TemplateCust)
}
$xml = $ExecutionContext.InvokeCommand.ExpandString($TemplateOuter)
$xml | Out-File $OutputXML
原始代码的显着变化：
我从. $TemplateOuter_<service_orders>$TemplateCust
解释：
$TemplateOuter包含<service_orders>外部标签。在开始的结束标签之间是$xml不包括那些外部标签的输出。我不得不使用$()子表达式运算符，因为这里的字符串 ( @''@) 不会扩展变量。使用运算符的原因-join是，当您对数组进行字符串化时$xml，它会将每个数组元素与一个空格连接起来。代码中的-join而是在元素之间创建一个 CRLF。
推荐阅读
- java - 如何使用模块化 Maven 项目管理版本号？
- python - 用于复制 CSV 文件并保存为 XLSX 文件的 Python 脚本
- jsf - 如何在 PrimeFaces 7.0 上获取 applicationContext 或 context-root 名称
- python - 如何将 2 个 ROS 主题合并为 1 个？
- excel-2010 - 将下拉菜单连接到 Excel 切片器
- windows - Windows 应用程序 - RDLC 报告 - reportviewer 2015 - 字体显示模糊？
- javascript - 为什么 HTTPs 节点库中的回调被调用两次？如何预防？
- ios - CIPerspectiveCorrection 按原样返回图像，未拉伸
- java - 如何对对象数组中的双精度值进行排序
- python - 如何在 python 中为大型 spark 数据帧计算 kendall 的 tau？