Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi guys, need help.
I need to read a folder that has many text files.
From those files, I want only the first line and ignore the other lines.
Then I need to get about 20 fields, that are positional, like:
column 1 to 3 = client ID
column 4 to 10 = client name
column 11 tp 17 = client state
Is that possible?
Thanks!
Solved! Go to Solution.
Hi @efilipe ,
How about this?
Or, you can refer to this post to get something like below and then transpose your table.
let
filesInFolder = Folder.Files("D:\Folder Test\"),
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(_, ".txt")),
toTable = Table.FromColumns(columns, headers)
in toTable,
#"Transposed Table" = Table.Transpose(combined)
in
#"Transposed Table"
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @efilipe ,
How about this?
Or, you can refer to this post to get something like below and then transpose your table.
let
filesInFolder = Folder.Files("D:\Folder Test\"),
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(_, ".txt")),
toTable = Table.FromColumns(columns, headers)
in toTable,
#"Transposed Table" = Table.Transpose(combined)
in
#"Transposed Table"
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I just realized I can split the text after importing. So the problem is getting only ethe first line of each text file.
User | Count |
---|---|
98 | |
90 | |
84 | |
71 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |