在“读取"之前跳过6行.进入powerquery [英] Skip 6 rows before "reading" into powerquery

问题描述

我正在尝试自动化一些报告，这些报告是基于Netsuite(我们的ERP软件)的CSV导出而构建的.这些文件永远不会正确地直接导入PowerQuery中，因为有6行是标题"行.这些标题行的逗号数量不正确，因此PowerQuery仅显示1列数据.我目前正在使用Notepad ++打开文件，并删除这6行，然后将文件导入PowerQuery.

I am trying to automate a few reports that are built off of CSV exports from Netsuite (our ERP software). The files never import directly into PowerQuery correctly because there are 6 rows that are "header" rows. These header rows do not have the correct amount of commas so PowerQuery only shows 1 column of data. I currently am opening the files with Notepad++ and deleting those 6 rows and then importing the file into PowerQuery.

是否可以使用PowerQuery代码跳过前6行，以便正确读取csv?以下是我正在使用的数据的示例.

Is there a way to skip the first 6 rows using PowerQuery code so the csv is read correctly? Below is an example of the data I am working with.

Fake Cocoa Company LLC
"Fake Cocoa Company, LLC (Consolidated)"
Sales Order Detail - Hotel - BH
"January 1, 2016 - December 31, 2016"

"Options: Show ZerosFilters: Customer/Project (equal to FCC - Hotel Hotel ), Validated Status (not equal to Cancelled, Closed )"
Document Number ,Date ,Ship To ,Item: Description (Sales) ,Quantity ,Validated Status ,Unit Price ,Aggregate Amount 
Sales Orders,,,,,,,
669,9/15/2016,Receiving - CCLV Hotel 2880 Some Place Blvd South Hotel Hotel Some Place CA 91089,100% Country Caf  Liquid Cocoa,5,Billed,$75.68,$378.40
660,,,,,,,
,9/15/2016,Receiving - MAIN OCEAN Hotel 4300 Some Place Blvd SO Some Place CA 91089,100% Country Caf  Liquid Cocoa,10,Billed,$7.68,$75.80
,9/15/2016,Receiving - MAIN OCEAN Hotel 4300 Some Place Blvd SO Some Place CA 91089,Fake Cocoa Grand - Whole Bean 5/5LB,8,Billed,$17.80,$72.00
,9/15/2016,Receiving - MAIN OCEAN Hotel 4300 Some Place Blvd SO Some Place CA 91089,Fake Cocoa Grand 28/9oz,6,Billed,$5.54,$39.24
,9/15/2016,Receiving - MAIN OCEAN Hotel 4300 Some Place Blvd SO Some Place CA 91089,Fake Cocoa Grand 42/2oz,4,Billed,$1.32,$7.28
,9/15/2016,Receiving - MAIN OCEAN Hotel 4300 Some Place Blvd SO Some Place CA 91089,Fake Cocoa Caf - Whole Bean 5/5LB,2,Billed,$2.80,$28.00
Total - 660,,,,,,,"$203.32"

推荐答案

使用本机CSV PowerQuery解析器

let
    file_path = "C:\your_path\csv.txt",
    file = File.Contents(file_path),
    src = Lines.FromBinary(file),
    skip = List.Skip(src,6),
    combine = Text.Combine(skip, "#(lf)"),
    csv = Csv.Document(combine),
    promote = Table.PromoteHeaders(csv)
in
    promote

这篇关于在“读取"之前跳过6行.进入powerquery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！