excel - 在excel中打开xml表

Question

我正在尝试使用电源查询打开一个 xml 文件以实现 excel。但是，问题是它继续循环并创建超过 100 万行，文件大小增长到 100 GB。

我使用的代码是 let Source = Xml.Tables(File.Contents("C:\Users\anujk\Downloads\stock_export_full_for_anujkundu.xml")), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute: file_format", type text}, {"Attribute:version", type number}, {"Attribute:generated", type datetime}, {"Attribute:extensions", type text}}), #"Expanded products" = 表。 ExpandTableColumn(#"更改类型", "products", {"product", "Attribute:language", "Attribute:currency"}, {"products.product", "products.Attribute:language", "products.Attribute: currency"}), #"Expanded products.product" = Table.ExpandTableColumn(#"Expanded products", "products.product", {"producer", "类别”、“http://www.iai-shop.com/developers/iof/extensions.phtml”、“单位”、“系列”、“卡片”、“描述”、“价格”、“srp”、“尺寸”，“图像”，“参数”，“组”，“附件”，“属性：id”，“属性：vat”，“属性：类型”，“属性：producer_code_standard”，“属性：code_on_card”，“删除线_retail_price"}、{"products.product.producer"、"products.product.category"、"products.product.http://www.iai-shop.com/developers/iof/extensions.phtml"、"products. product.unit”、“products.product.series”、“products.product.card”、“products.product.description”、“products.product.价格”、“products.product.srp”、“products.product.sizes”、“products.product.images”、“products.product.parameters”、“products.product.group”、“products.product.attachments” 、“products.product.Attribute:id”、“products.product.Attribute:vat”、“products.product.Attribute:type”、“products.product.Attribute:producer_code_standard”、“products.product.Attribute:code_on_card” , "products.product.strikethrough_retail_price"}), #"Expanded products.product.category" = Table.ExpandTableColumn(#"Expanded products.product", "products.product.category", {"Attribute:id", "Attribute :name"}, {"products.product.category.Attribute:id", "products.product.category.Attribute:name"}), #"Expanded products.product.http://www.iai-shop.com/developers/iof/extensions.phtml" = Table.ExpandTableColumn(#"Expanded products.product.category", "products.product. http://www.iai-shop.com/developers/iof/extensions.phtml", {"iai_category"}, {"products.product.http://www.iai-shop.com/developers/iof/extensions .phtml.iai_cat"}), #"扩展产品.product.http://www.iai-shop.com/developers/iof/extensions.phtml.iai_cat" = Table.ExpandTableColumn(#"扩展产品.product.http ://www.iai-shop.com/developers/iof/extensions.phtml", "products.product.http://www.iai-shop.com/developers/iof/extensions.phtml.iai_cat", {"属性：id”，“属性：路径”}，{“products.product.http://www.iai-shop.com/developers/iof/extensions.phtml.iai_c.1”，"products.product.http://www.iai-shop.com/developers/iof/extensions.phtml.iai_c.2"}), #"扩展产品.product.series" = Table.ExpandTableColumn(#"扩展产品.product.http://www.iai-shop.com/developers/iof/extensions.phtml.iai_cat", "products.product.series", {"Attribute:id", "Attribute:name"}, {" products.product.series.Attribute:id", "products.product.series.Attribute:name"}), #"扩展 products.product.card" = Table.ExpandTableColumn(#"扩展 products.product.series", " products.product.card", {"Attribute:url"}, {"products.product.card.Attribute:url"}), #"扩展 products.product.description" = Table.ExpandTableColumn(#"扩展 products.product .card", "products.product.描述”、{“名称”、“版本”、“long_desc”、“short_desc”}、{“products.product.description.name”、“products.product.description.version”、“products.product.description.long_desc ", "products.product.description.short_desc"}), #"扩展 products.product.description.name" = Table.ExpandTableColumn(#"扩展 products.product.description", "products.product.description.name", {“元素：文本”、“http://www.w3.org/XML/1998/namespace”}、{“products.product.description.name.Element:Text”、“products.product.description.name。 http://www.w3.org/XML/1998/namespace"}), #"扩展产品.product.description.name.http://www.w3.org/XML/1998/namespace" = Table.ExpandTableColumn (#"扩展 products.product.description.name", "products.product.description.name.http://www.w3.org/XML/1998/namespace", {"Attribute:lang"}, {"products.product. description.name.http://www.w3.org/XML/1998/namespace.Attribute"}), #"Expanded products.product.description.version" = Table.ExpandTableColumn(#"Expanded products.product.description. name.http://www.w3.org/XML/1998/namespace", "products.product.description.version", {"Element:Text", "http://www.w3.org/XML/1998 /namespace"}, {"products.product.description.version.Element:Text", "products.product.description.version.http://www.w3.org/XML/1998/namespace"}), #"扩展 products.product.description.version.http://www.w3.org/XML/1998/namespace" = Table.ExpandTableColumn(#"扩展 products.product.description.version", "products.product.description.version.http://www.w3.org/XML/1998/namespace", {"Attribute:lang"}, {"products.product. description.version.http://www.w3.org/XML/1998/namespace.Attrib"}), #"Expanded products.product.description.long_desc" = Table.ExpandTableColumn(#"Expanded products.product.description.版本.http://www.w3.org/XML/1998/namespace", "products.product.description.long_desc", {"元素:文本", "http://www.w3.org/XML/1998 /namespace"}, {"products.product.description.long_desc.Element:Text", "products.product.description.long_desc.http://www.w3.org/XML/1998/namespace"}), #"扩展 products.product.description.long_desc.http://www.w3.org/XML/1998/namespace" = 表。ExpandTableColumn(#"扩展 products.product.description.long_desc", "products.product.description.long_desc.http://www.w3.org/XML/1998/namespace", {"Attribute:lang"}, {" products.product.description.long_desc.http://www.w3.org/XML/1998/namespace.Attr"}), #"扩展产品.product.description.short_desc" = Table.ExpandTableColumn(#"扩展产品。 product.description.long_desc.http://www.w3.org/XML/1998/namespace", "products.product.description.short_desc", {"Element:Text", "http://www.w3.org /XML/1998/namespace"}, {"products.product.description.short_desc.Element:Text", "products.product.description.short_desc.http://www.w3.org/XML/1998/namespace"} ), #"扩展产品.product.description.short_desc.http://www.w3.org/XML/1998/namespace" = Table.ExpandTableColumn(#"扩展 products.product.description.short_desc", "products.product.description.short_desc.http://www.w3.org/XML/1998/namespace" , {"Attribute:lang"}, {"products.product.description.short_desc.http://www.w3.org/XML/1998/namespace.Att"}), #"扩展 products.product.price" = Table.ExpandTableColumn(#"扩展 products.product.description.short_desc.http://www.w3.org/XML/1998/namespace", "products.product.price", {"Attribute:gross", "Attribute: net"}, {"products.product.price.Attribute:gross", "products.product.price.Attribute:net"}), #"Expanded products.product.srp" = Table.ExpandTableColumn(#"Expanded products. product.price", "products.product.srp",{"Attribute:gross", "Attribute:net"}, {"products.product.srp.Attribute:gross", "products.product.srp.Attribute:net"}), #"扩展 products.product.sizes" = Table.ExpandTableColumn(#"扩展 products.product.srp", "products.product.sizes", {"size"}, {"products.product.sizes.size"}), #"扩展 products.product.sizes .size" = Table.ExpandTableColumn(#"Expanded products.product.sizes", "products.product.sizes.size", {"stock", "price", "srp", "Attribute:id", "Attribute: text_id”、“属性：code_producer”、“属性：代码”、“属性：重量”}、{“products.product.sizes.size.stock”、“products.product.sizes.size.price”、“products.product.sizes.size.srp”、“products.product.sizes.size.Attribute:id”、“products.product.sizes.size.Attribute:text_id”、“products.product.sizes.size.Attribute:code_producer” , "products.product.sizes.size.Attribute:code", "products.product.sizes.size.Attribute:weight"}), #"扩展 products.product.sizes.size.stock" = Table.ExpandTableColumn(# "扩展 products.product.sizes.size", "products.product.sizes.size.stock", {"Attribute:id", "Attribute:quantity", "http://www.iai-shop.com/developers /iof/extensions.phtml"}, {"products.product.sizes.size.stock.Attribute:id", "products.product.sizes.size.stock.Attribute:quantity", "products.product.sizes.size .stock.http://www.iai-shop.com/developers/iof/extensi"}),#"Expanded products.product.sizes.size.stock.http://www.iai-shop.com/developers/iof/extensi" = Table.ExpandTableColumn(#"Expanded products.product.sizes.size.stock", "products.product.sizes.size.stock.http://www.iai-shop.com/developers/iof/extensi", {"Attribute:location_id", "Attribute:location_text_id"}, {"products.product. size.size.stock.http://www.iai-shop.com/developers/iof/exten.1", "products.product.sizes.size.stock.http://www.iai-shop.com/ developers/iof/exten.2"}), #"Expanded products.product.sizes.size.price" = Table.ExpandTableColumn(#"Expanded products.product.sizes.size.stock.http://www.iai- shop.com/developers/iof/extensi", "products.product.sizes.size.price", {"Attribute:gross", "Attribute:net"}, {"products.product.sizes.size.price.Attribute:gross", "products.product.sizes.size.price.Attribute:net"}), #"扩展 products.product.sizes.size.srp" = Table.ExpandTableColumn(# "扩展 products.product.sizes.size.price", "products.product.sizes.size.srp", {"Attribute:gross", "Attribute:net"}, {"products.product.sizes.size.srp .Attribute:gross", "products.product.sizes.size.srp.Attribute:net"}), #"Expanded products.product.images" = Table.ExpandTableColumn(#"Expanded products.product.sizes.size.srp ", "products.product.images", {"large", "icons"}, {"products.product.images.large", "products.product.images.icons"}), #"扩展 products.product。 images.large" = Table.ExpandTableColumn(#"扩展 products.product.images", "products.product.images.large", {"image"}, {"products.product.images.large.image"}), #"扩展 products.product.images.large。 image" = Table.ExpandTableColumn(#"扩展 products.product.images.large", "products.product.images.large.image", {"Attribute:url", "Attribute:date_changed", "Attribute:hash", “属性：宽度”、“属性：高度”}、{“products.product.images.large.image.Attribute:url”、“products.product.images.large.image.Attribute:date_changed”、“products.product .images.large.image.Attribute:hash", "products.product.images.large.image.Attribute:width", "products.product.images.large.image.Attribute:height"}), #"扩展产品。产品。parameters" = Table.ExpandTableColumn(#"扩展 products.product.images.large.image", "products.product.parameters", {"parameter"}, {"products.product.parameters.parameter"}), #"扩展 products.product.parameters.parameter" = Table.ExpandTableColumn(#"扩展 products.product.parameters", "products.product.parameters.parameter", {"value", "Attribute:type", "Attribute:id" 、“属性：优先级”、“属性：名称”}、{“products.product.parameters.parameter.value”、“products.product.parameters.parameter.Attribute:type”、“products.product.parameters.parameter。属性：id”、“products.product.parameters.parameter.Attribute：优先级”、“products.product.parameters.parameter.Attribute：name"}), #"Expanded products.product.parameters.parameter.value" = Table.ExpandTableColumn(#"Expanded products.product.parameters.parameter", "products.product.parameters.parameter.value", {"属性:id", "属性:优先级", "属性:名称"}, {"products.product.parameters.parameter.value.Attribute:id", "products.product.parameters.parameter.value.Attribute:priority", "products.product.parameters.parameter.value.Attribute:name"}), #"Expanded products.product.group" = Table.ExpandTableColumn(#"Expanded products.product.parameters.parameter.value", "products.product .group", {"group_by_parameter", "Attribute:id", "Attribute:first_product_id"}, {"products.product.group.group_by_parameter", "products.product.group.Attribute:id", "products.product.group.Attribute:first_product_id"}), #"Expanded products.product.group.group_by_parameter" = Table.ExpandTableColumn(#"Expanded products. product.group", "products.product.group.group_by_parameter", {"name", "product_value", "Attribute:id"}, {"products.product.group.group_by_parameter.name", "products.product.group .group_by_parameter.product_value", "products.product.group.group_by_parameter.Attribute:id"}), #"Expanded products.product.group.group_by_parameter.name" = Table.ExpandTableColumn(#"Expanded products.product.group.group_by_parameter ", "products.product.group.group_by_parameter.name", {"元素:文本", "http://www.w3.org/XML/1998/namespace"}, {"products.product.group.group_by_parameter.name.Element:Text", "products.product.group.group_by_parameter.name.http:// www.w3.org/XML/1998/namesp"}), #"扩展产品.product.group.group_by_parameter.name.http://www.w3.org/XML/1998/namesp" = Table.ExpandTableColumn(# "扩展 products.product.group.group_by_parameter.name", "products.product.group.group_by_parameter.name.http://www.w3.org/XML/1998/namesp", {"Attribute:lang"}, { "products.product.group.group_by_parameter.name.http://www.w3.org/XML/1998/name.1"}), #"扩展 products.product.group.group_by_parameter.product_value" = Table.ExpandTableColumn( #"扩展 products.product.group.group_by_parameter.name.http://www.w3.org/XML/1998/namesp", "products.product.group.group_by_parameter.product_value", {"name", "Attribute:id"}, {"products.product.group.group_by_parameter.product_value.name", "products.product.group.group_by_parameter.product_value.Attribute:id "}), #"Expanded products.product.attachments" = Table.ExpandTableColumn(#"Expanded products.product.group.group_by_parameter.product_value", "products.product.attachments", {"file"}, {"products. product.attachments.file"}), #"扩展 products.product.attachments.file" = Table.ExpandTableColumn(#"扩展 products.product.attachments", "products.product.attachments.file", {"name", “属性：url”、“属性：优先级”、“属性：attachment_file_extension”、“http://www.iai-shop.com/developers/iof/extensions.phtml"}, {"products.product.attachments.file.name", "products.product.attachments.file.Attribute:url", "products.product.attachments. file.Attribute:priority", "products.product.attachments.file.Attribute:attachment_file_extension", "products.product.attachments.file.http://www.iai-shop.com/developers/iof/extensi"}) , #"扩展 products.product.attachments.file.name" = Table.ExpandTableColumn(#"扩展 products.product.attachments.file", "products.product.attachments.file.name", {"Element:Text", "http://www.w3.org/XML/1998/namespace"}, {"products.product.attachments.file.name.Element:Text", "products.product.attachments.file.name.http:// /www.w3.org/XML/1998/namespace"}), #"扩展 products.product.attachments.file.name.http://www.w3.org/XML/1998/namespace" = Table.ExpandTableColumn(#"Expanded products.product.attachments.file.name", "products.product .attachments.file.name.http://www.w3.org/XML/1998/namespace", {"Attribute:lang"}, {"products.product.attachments.file.name.http://www. w3.org/XML/1998/namespace.Attr"}), #"扩展产品.product.attachments.file.http://www.iai-shop.com/developers/iof/extensi" = Table.ExpandTableColumn(# "扩展 products.product.attachments.file.name.http://www.w3.org/XML/1998/namespace", "products.product.attachments.file.http://www.iai-shop.com/开发人员/iof/extensi”，{“属性：启用”，“属性：attachment_file_type”，“属性：下载日志”}，{“products.product.attachments.file.http://www.iai-shop.com/developers/iof/exten.1", "products.product.attachments.file.http://www.iai-shop.com/developers/iof/ exten.2", "products.product.attachments.file.http://www.iai-shop.com/developers/iof/exten.3"}) 在#"扩展 products.product.attachments.file.http: //www.iai-shop.com/developers/iof/extensi"

标签： excelxmlpowerquery