Import entire Folder as text

[bobaol]

Hello, I have a lot of files with a lot of dirty data. I want PowerQuery to import everything as text, regardless of the data. I tried this, but it is not working. I don't need PowerQuery to parse the data, although I want to keep the file name in column A.
let
Source = Folder.Files("C:\Temp2\6267b"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
#"Expanded Table Column1"
''---------------------------------------------------------
I tried this for a single file, and it works, but I have a few thousand files.
let
/* Get the raw line by line contents of the file, preventing PQ from interpreting it */
fnRawFileContents = (fullpath as text) as table =>
let
Value = Table.FromList(Lines.FromBinary(File.Contents(fullpath)),Splitter.SplitByNothing())
in Value,

/* Use function to load file contents */
Source = fnRawFileContents("C:\Temp2\count_Distinct_6267\test6266.csv")
/*Source = fnRawFileContents("C:\Temp2\count_Distinct_6267\")*/


any help is appreciated. Thanks in advance.

 

[AlienSx]

let
    Source = Folder.Files("C:\Temp2\6267b"),
    csv_files = Table.SelectRows(Source, each ([Extension] = ".csv"))[[Content], [Name]],
    lines = Table.TransformColumns(csv_files, {"Content", Lines.FromBinary})
in
    lines

then expand list column to get your lines of text.

 

[bobaol]

Hi AlienSx, thanks so much for your reply. The code above only provides the file name in column A. Will you be able to provide the complete code so it imports all the data and have the file name in column A? all the data can simply go into column B. here is a sample of the data:

CODE_LEVEL

1​

2​

3​

4​

Thank you.

 

[AlienSx]

what "Column A" are you talking about? Source = Folder.Files... gives you a table of file contents and with several attributes like Name, Content etc. Then csv_files selects all files with .csv extension while progression ([[Content], [Name]]) selects only Name (file name) and Content columns of Source step. Then lines step simply reads all text lines in your csv files and creates lists out of them. I am sorry, I don't get your message at all.

 

[bobaol]

Hi AlienSx, thanks again for the response. When I ran the code you provided, it only returns the name of the file, it is not returning the data in the file. so, for example, in the first file, i want to return the name of the file in row2 to row6, and in column B2 to B6, return the data inside the file. sorry for the confusion. thank you.

 

[AlienSx]

@bobaol I am sorry, I still don't follow you. You wrote that you don't want to do any transformations, just read text from your (csv?) files. I downloaded simple csv file from internet. Placed it into some folder "tmp". Here is what happens next in PQ:

my code reads the content of that file. I am sorry, I am lost.

 

[bobaol]

Thanks for the images. I'll try it.