CODEWITHSUNDEEP
excelpowerquery
Kevin W
Kevin W

Reputation: 101

Use Power Query to grab top row of CSV files in a folder. Place in Excel

I would like to grab the first rows of all CSV files in a folder. I have read that power query would probably be best.

I have gone to Excel > Data > Get Data > From Folder > OK. That has brought me to a table of all the csvs in the folder. I would like to grab the first row of all of these files. I do not want to import all rows of the tables because it was way too many rows. It is also too many tables to do one by one. Please tell me what I should do next. Thank you!

First image is where I am, Second image is where I would like to be

enter image description here enter image description here

Upvotes: 0

Views: 1799

Answers (2)

chillin
chillin

Reputation: 4486

The approach below should give you a single table, wherein each column contains a given CSV's first row's values. It's not exactly what you've shown in your second image (namely, there are no blank columns in between each column of values), but it might still be okay for you.

  • You can parse a CSV with Csv.Document function (which should give you a table).
  • You can get the first row of the table (from the previous step) using:
    • Table.First and Record.FieldValues
    • or Table.PromoteHeaders and Table.ColumnNames

(It would make sense to create a custom function to do above the steps for you and then invoke the function for each CSV. See GetFirstRowOfCsv in code below.)

  • The function above returns a list (containing the CSV's first row's values). Calling the function for all your CSVs should give you a list of lists, which you can then combine into a single table with Table.FromColumns.

Overall, starting from the Folder.Files call, the code looks like:

let
    filesInFolder = Folder.Files("C:\Users\"),
    GetFirstRowOfCsv = (someFile as binary) as list => 
        let
            csv = Csv.Document(someFile, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
            promoted = Table.PromoteHeaders(csv, [PromoteAllScalars=true]),
            firstRow = Table.ColumnNames(promoted)
        in firstRow,
    firstRowExtracted = Table.AddColumn(filesInFolder, "firstRowExtracted", each GetFirstRowOfCsv([Content]), type list),
    combined = 
        let
            columns = firstRowExtracted[firstRowExtracted],
            headers = List.Transform(firstRowExtracted[Name], each Text.BeforeDelimiter(_, ".csv")),
            toTable = Table.FromColumns(columns, headers)
        in toTable
in
    combined

which gives me:

Output

  • The null values are because there were more values in the first row of my ActionLinkTemplate.csv than the first rows of the other CSVs.
  • You will need to change the folder path in the above code to whatever it is on your machine.

Upvotes: 1

Alexis Olson
Alexis Olson

Reputation: 40204

In the GUI, you can select the top N row(s) where you choose N. Then you can expand all remaining rows.

Upvotes: 0

Related Questions