csv with records over multiple lines

[Syndicate_Admin]

Hi, 
I'm having trouble finding out how to extract data from a csv with multiple rows of fields.
After hours of googling and trying, I give up and hope the community has a solution. 
Possibly I might overlook something completely?

Source file is an exported .csv that looks like this:

I tried to get the .csv in Excel and directly in PowerQuery. 

I'm no rockstar with this kind of data files, but I hope to find a way to get all fields in separate columns and only a 1 line record

[v-yanjiang-msft]

Hi @Syndicate_Admin ,

Is your problem solved? If so, would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

[Syndicate_Admin]

Hi, 

I don't have any clue about the Lines.FromBinary (yet, good resources are welcome), but this m-code does what I was looking for. 
I will need to replicate for other exports that come out of the system like this.
So, thnxs a million for setting me up: this will definitely help me learn forward too 🙂

[AlexisOlson · Accepted solution]

I think this one is better to handle a bit more manually than working with the built-in CSV function.

What I did was load in every line using Lines.FromBinary, combine all the lines into one big string, then replace "#(lf);;" with just ";" so that the multiple lines that belong together are combined into one.

I loaded this big string into a single table cell and split that cell into rows using a double linefeed as the delimiter (this took care of the blank lines). Then I split the resulting rows into columns using a semicolon as the delimiter.

To finish up, I promoted the first row to be used as headers and set the column types using Detect Data Type.

let
    Source = Folder.Files("C:\Users\aolson\Downloads"),
    TextFile = Source{[#"Folder Path"="C:\Users\aolson\Downloads\",Name="facturen.txt"]}[Content],
    #"Imported Text" = #table(1,{{Text.Replace(Text.Combine(Lines.FromBinary(TextFile,null,null,1252),"#(lf)"),"#(lf);;",";")}}),
    #"Split Text Into Rows" = Table.ExpandListColumn(Table.TransformColumns(#"Imported Text", {{"Column1", Splitter.SplitTextByDelimiter("#(lf)#(lf)", QuoteStyle.Csv)}}), "Column1"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Split Text Into Rows", "Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), 10),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fact.", Int64.Type}, {"Boekstuknr.", Int64.Type}, {"Deb.nr.", Int64.Type}, {"Debiteurnaam", type text}, {"Categorie", type text}, {"Factuurdatum", type date}, {"Vervaldatum", type date}, {"Bedrag excl. btw", type number}, {"Btw", type number}, {"Totaalbedrag", type number}})
in
    #"Changed Type"

[serpiva64]

Hi,

take a look at the file.

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution !

[Syndicate_Admin]

Yes, I can, but upload doesn't support .txt format, so I wrapped it in a zip file? 

[serpiva64]

Hi,

can you post some data, not a screenshot?