Forum Discussion

JollyRoger01's avatar
JollyRoger01
Helper III
5 years ago
Solved

How to split a CSV file into tables based on blank rows

I have a CSV file that has multiple tables in that are seperated vertically. I want to know how to import these into tables (preferrably in 1 or 2 steps) based on a blank (or multiple blank) row/s.

 

So the following:

csv.csv

2021-05-012021-05-022021-05-032021-05-04
ApplesBananaOrangeApple
BananaAppleMangoWatermelon
    
01-04-202102-04-202103-04-202104-04-2021
SunflowerPoppyRoseDaffodil
LillySunflowerCornflowerPoppy

 

Would end up like:

Table1

2021-05-012021-05-022021-05-032021-05-04
ApplesBananaOrangeApple
BananaAppleMangoWatermelon

 

Table2

01-04-202102-04-202103-04-202104-04-2021
SunflowerPoppyRoseDaffodil
LillySunflowerCornflowerPoppy

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi JollyRoger01 

     

    I missed that multiple tables...you need to group the rows into different tables, another way of increasing index, you can take from here

     

    let
    Source = Csv.Document(File.Contents("C:\Users\----\Book1.csv"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    checkBlank = Table.CombineColumns(Source, Table.ColumnNames(Source), (x)=> if List.MatchesAll(x,each _ = "") then 1 else 0,"check"),
    #"Added Index" = Table.AddIndexColumn(checkBlank, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Group", each if [Index]=0 then 0 
else if [check] = 0 then List.Sum(List.FirstN( checkBlank[check], [Index])) 
else -1),
    SouceIndex = Table.AddIndexColumn(Source, "Index", 0,1),
    #"Merged Queries" = Table.NestedJoin(SouceIndex, {"Index"}, #"Added Custom", {"Index"}, "Group", JoinKind.LeftOuter),
    #"Expanded Group" = Table.ExpandTableColumn(#"Merged Queries", "Group", {"check", "Group"}, {"check", "Group"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Group", each ([check] = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "check"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Group"}, {{"allrows", each _, type table }})
in
    #"Grouped Rows"

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi JollyRoger01 

     

    So you have no idea how many blank rows, I have tried one way

    first table

    let
    Source = Csv.Document(File.Contents("C:\Users\----\Book1.csv"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    checkBlank = Table.CombineColumns( Source, Table.ColumnNames(Source), (x)=> if List.MatchesAll(x,each _ = "") then 1 else 0,"check"),
    firstTable = Table.FirstN(Source, List.PositionOf( checkBlank[check],1,0))
in
    firstTable

     

    second table

    let
    Source = Csv.Document(File.Contents("C:\Users\----\Book1.csv"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    checkBlank = Table.CombineColumns( Source, Table.ColumnNames(Source), (x)=> if List.MatchesAll(x,each _ = "") then 1 else 0,"check"),
    secondTable = Table.Skip(Source, List.PositionOf( checkBlank[check],1, List.Count( List.Select( checkBlank[check], each _=1))-1)+1)
in
    secondTable
    • JollyRoger01's avatar
      JollyRoger01
      Helper III
      5 years ago

      Yes that looks like it would work, however I don't know how many blank rows and hence how many tables there will be. I have tried to index each none blank section and increment them here: How-to-increment-a-number-variable-if-column-value-equals thinking that I could somehow use this info to create indexed tables, but so far no luck. This is a common csv output format from many of the tools we use at work so I thought there would be some sort of standardised way to do this.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi JollyRoger01 

         

        I missed that multiple tables...you need to group the rows into different tables, another way of increasing index, you can take from here

         

        let
    Source = Csv.Document(File.Contents("C:\Users\----\Book1.csv"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    checkBlank = Table.CombineColumns(Source, Table.ColumnNames(Source), (x)=> if List.MatchesAll(x,each _ = "") then 1 else 0,"check"),
    #"Added Index" = Table.AddIndexColumn(checkBlank, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Group", each if [Index]=0 then 0 
else if [check] = 0 then List.Sum(List.FirstN( checkBlank[check], [Index])) 
else -1),
    SouceIndex = Table.AddIndexColumn(Source, "Index", 0,1),
    #"Merged Queries" = Table.NestedJoin(SouceIndex, {"Index"}, #"Added Custom", {"Index"}, "Group", JoinKind.LeftOuter),
    #"Expanded Group" = Table.ExpandTableColumn(#"Merged Queries", "Group", {"check", "Group"}, {"check", "Group"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Group", each ([check] = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "check"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Group"}, {{"allrows", each _, type table }})
in
    #"Grouped Rows"

         

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Your two resulting tables are very similar (date headers with 1+ rows of category values).  It would probably be better to unpivot and append them to result in one table.  That will likely make your visualization/analysis easier.  Would that work?  If so, the community could suggest a specific query solution given your data.

     

    Pat

     

    • JollyRoger01's avatar
      JollyRoger01
      Helper III
      5 years ago

      I see what you mean, though unfortunately the info is unrelated and I need them all in seperate tables.