Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Import Entire Worksheet Rather Than UsedRange

I have a need when connecting to an external Excel file (via Excel.Workbook) to import the entire worksheet including any leading blank rows or columns. The default behavior is to return only the Us...
Need Help
  • jbwtp's avatar
    jbwtp
    4 years ago

    Hi Anonymous,

     

    Try this one. I've optimised the performance by truncating unnecessary checks after the first header match is found, also limiting the number of rows to check for the header (in this case top 100 rows, which seems reasonably high). If you sure this number can be less than 100, change it to what you think is most appropriate. The smaller the number the quicker the code.

     

     

     

    let
    Source = Excel.Workbook(File.Contents("C:\...\Downloads\Test.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

    fTrimTable = (tbl as table, header as text) =>
        let 
            t = Table.Buffer(tbl),
            columns = List.Buffer(Table.ColumnNames(t)),
            rowsToCheck = 100,
            Column = List.Select(columns, each List.PositionOf(List.FirstN(Table.Column(t, _),rowsToCheck), header)>0){0},
            Row = List.PositionOf(Table.Column(t, Column), header),
            ScrollRows = Table.RemoveFirstN (t, Row),
            ScrollColumns = Table.SelectColumns(ScrollRows, List.RemoveFirstN(columns, List.PositionOf(columns, Column))),
            #"Promoted Headers" = Table.PromoteHeaders(ScrollColumns, [PromoteAllScalars=true])
        in
            #"Promoted Headers",
    
    Trimmed = fTrimTable(Sheet1_Sheet, "Header100")
in
   Trimmed

     

     

    I've tested it on a file with 600 columns and 2000 rows (about 12Mb), on my fairly old laptop it takes about 10-15 sec to refresh, much better then the older version.

     

    For records: the problem with the old code was that (a) it was loading the entire Excel file each time for each column checked for the position of the header, and (b) checking position of the header in the list of thousands of itemsis not quick, when it is not on hte list. Both not a big problem if the colunm is found quickly (ideal case it is in the A1 cell), but very quickly become one as the number of columns to check overgrow 3-5, in my testing for searhing of "Header100" - the 100th column - it took forever and I had to break the cyle. Optimised version does not have any of the above problems.

     

    Kind regards,

    John

     

jbwtp's avatar
jbwtp
Memorable Member
4 years ago

Hi Anonymous,

 

Would this be a help in resolving this problem?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bU67DsIwDPyXzh2wE8f2zsDOWHUIohIzgv/HF9QoqEiR5dzLtyzTNB/eOu/wZav37cn/2C9FsV3ft8fxw12T+pb7JnvMub4qfGQgNVvMYh4zUYnJgQ9SboSASLAZwplOSCAalSCMESoOfW4ub4h6GaWATDATaz/gjBZZf6SNQEdXVLHWVGGyKDEI0Z0UrAiKOiqqAGBcXz8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}}),
    
    fTrimTable = (t as table, header as text)=>
        let 
            FindFirstCell = List.Accumulate(Table.ColumnNames(t), null, (a, n)=> if List.PositionOf(Table.Column(t, n), header) > 0  then [Row= List.PositionOf(Table.Column(t, n), "Header1"), Column = n] else a),
            ScrollRows = Table.RemoveFirstN (t, FindFirstCell[Row]),
            ScrollColumns = Table.SelectColumns(ScrollRows, List.RemoveFirstN(Table.ColumnNames(ScrollRows), List.PositionOf(Table.ColumnNames(ScrollRows), FindFirstCell[Column]))),
            #"Promoted Headers" = Table.PromoteHeaders(ScrollColumns, [PromoteAllScalars=true])
        in
            #"Promoted Headers",
    
    Output = fTrimTable(#"Changed Type", "Header1")
in Output

 

The code searches for the left top corner of the data regardless of the spreadsheet structure (as long as the column name is unique, but can be re-written to take just a first appearance).

 

Kind regards,

John

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    jbwtp 

    Hi John,

    Using this approach would require I get header names from the user - that of the top left data column and the index column. Easy enough to do.

    However, it takes nearly 3 minutes to complete this query on a 450 column by 1,450 row worksheet.

    Directly deleting rows and columns based on user-provided input is nearly instantaneous so I'll have to stick with that approach for now.

    Thanks for providing this approach. I'll certainly add it to my toolbox.

    Jim.

    • jbwtp's avatar
      jbwtp
      Memorable Member
      4 years ago

      Hi Anonymous,

       

      Try this one. I've optimised the performance by truncating unnecessary checks after the first header match is found, also limiting the number of rows to check for the header (in this case top 100 rows, which seems reasonably high). If you sure this number can be less than 100, change it to what you think is most appropriate. The smaller the number the quicker the code.

       

       

       

      let
    Source = Excel.Workbook(File.Contents("C:\...\Downloads\Test.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

    fTrimTable = (tbl as table, header as text) =>
        let 
            t = Table.Buffer(tbl),
            columns = List.Buffer(Table.ColumnNames(t)),
            rowsToCheck = 100,
            Column = List.Select(columns, each List.PositionOf(List.FirstN(Table.Column(t, _),rowsToCheck), header)>0){0},
            Row = List.PositionOf(Table.Column(t, Column), header),
            ScrollRows = Table.RemoveFirstN (t, Row),
            ScrollColumns = Table.SelectColumns(ScrollRows, List.RemoveFirstN(columns, List.PositionOf(columns, Column))),
            #"Promoted Headers" = Table.PromoteHeaders(ScrollColumns, [PromoteAllScalars=true])
        in
            #"Promoted Headers",
    
    Trimmed = fTrimTable(Sheet1_Sheet, "Header100")
in
   Trimmed

       

       

      I've tested it on a file with 600 columns and 2000 rows (about 12Mb), on my fairly old laptop it takes about 10-15 sec to refresh, much better then the older version.

       

      For records: the problem with the old code was that (a) it was loading the entire Excel file each time for each column checked for the position of the header, and (b) checking position of the header in the list of thousands of itemsis not quick, when it is not on hte list. Both not a big problem if the colunm is found quickly (ideal case it is in the A1 cell), but very quickly become one as the number of columns to check overgrow 3-5, in my testing for searhing of "Header100" - the 100th column - it took forever and I had to break the cyle. Optimised version does not have any of the above problems.

       

      Kind regards,

      John

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        jbwtp 

        Success!
        I tried it on my notoriously badly-structured dataset and performance is no longer an issue.
        The user will now have to supply only one piece of information - the name of the top left header.
        I'll put the max row check number in a variable on a configuration sheet.
        Thanks for your solution John. I think this is a good general purpose approach that should find wide use.

        Jim.