Forum Discussion
How to load this file format from a folder
Hello,
I have a somewhat special CSV file which works fine to process as long as I only have 1 such file. Unfortunately I have to import several of such files since the exporting system can't handle all data at once.
The format is like this:
TYPE1
Header1.1,Header1.2,Header1.3
1,2,3
4,5,6
TYPE2
Header2.1,Header2.2,Header2.3,Header2.4
1,2,3,4
5,6,7,8
TYPE3
Header3.1,Header3.2
1,2
3,4
To get this split into 3 different tables, one for each data type, I simply add a conditional column like
= Table.AddColumn(Source, "Datatype", each if [Column1] = "TYPE1" then "Type 1" else if [Column1] = "TYPE2" then "Type 2" else if [Column1] = "TYPE3" then "Type 3" else if [Column1] = "" then "" else nulland then run fill down on that new column.
After that I create new tables with a filter like
= Table.SelectRows(Source, each ([Datatype] = "Type 1"))
This all works perfectly fine as long as I have one such file. As soon as I try this with a folder data source, I struggle with the headers in each of the files since in the one file version its always the second line where I can remove the first line and promote headers.
Here is an example how my data looks with the folder import:
TYPE2,,,,Type 2
Header2.1,Header2.2,Header2.3,Header2.4,Type 2
1,2,3,4,Type 2
5,6,7,8,Type 2
TYPE2,,,,Type 2
Header2.1,Header2.2,Header2.3,Header2.4,Type 2
1,2,3,4,Type 2
5,6,7,8,Type 2
TYPE2,,,,Type 2
Header2.1,Header2.2,Header2.3,Header2.4,Type 2
1,2,3,4,Type 2
5,6,7,8,Type 2
Any idea how I can get rid of these headers "in the middle" of my data?
Hi Zap,
if you import your files from the folder, you can apply this function to the Content-column in there:
// Transform File (Parameter1 as binary) => let Source = Csv.Document(Parameter1,[Delimiter=",", Columns = 20, Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each if List.Contains({"GEWICHT", "TEMPERATUR", "Benutzerangaben", "Zeitraum", "Kategorie", "BLUTZUCKER", "BLUTDRUCK", "PULSOXY"}, [Datenexportdetails]) then [Datenexportdetails] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}) in #"Filled Down"Key trick here is to add a column to all of your data that indicates which table/category the current row belongs to.
You can work from there to filter and split it out to your desired tables.
BTW: I've set the Colums-parameter in the Csv.Document-function to 20 to cater for more columns in the future. You can reduce it if that's not necessary.
15 Replies
ImkeF , Can you help?
Hi Anonymous ,
Not sure I fully understand what you're doing here. But if you're using the combine binaries action, then a section with functions and sample quieres will be created. In there is a query called "Transform Sample Binary from Combine Binaries" which contains a step that promotes the headers.
Delete that step (so that the headers are in the first row) and then delete this first row of data.
After that, the column names will all be the same and you can append the data.
Hello Imke,
I am not sure how that would work. The problem is that my data source puts several CSV tables into one file, so my example above is in one file. Promoting headers is not possible since each section has different headers and I only can promote headers after I have the separate tables filtered by my custom column Datatype.
Zap.
Hi Anonymous
starting to make sense to me know. You have to add a column that checks for the existence of a special keyword that is only contained in the headers. Return null if it is not found and then filter out those rows.
To check the entries from one row, you use the function "Record.FieldValues". It returns a list of all the values in the row.
So for example:
List.Contains( Record.FieldValues(_), "YourHeaderString")
would return true if a match of "YourHeaderString" is found.
