Forum Discussion
Solved
Combine CSV Files with Uneven Columns & Junk Rows
I have 4 different .csv files that sit in a folder. I want to combine all of these. But there is a catch, the first 2 files have 4 top junk rows that need to be removed and 5 columns. And the las...
Hello, asif999
let Source = Folder.Files(<path_to_your_folder>), file_names = List.Buffer(Table.AddColumn(Source, "fp", each [Folder Path] & [Name])[fp]), fx_csv = (path as text) as table => let csv = Csv.Document(File.Contents(path),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]), remove_lines = Table.Skip( csv, List.PositionOf( csv[Column2], "Settlement ID")), promote = Table.PromoteHeaders(remove_lines) in promote, files = Table.Combine(List.Transform(file_names, fx_csv)), reorder = Table.ReorderColumns(files,{"Date", "Settlement ID", "closing fees", "promo rebates", "TDS", "total"}) in reorder
Hello, asif999
let
Source = Folder.Files(<path_to_your_folder>),
file_names = List.Buffer(Table.AddColumn(Source, "fp", each [Folder Path] & [Name])[fp]),
fx_csv = (path as text) as table =>
let
csv = Csv.Document(File.Contents(path),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
remove_lines = Table.Skip( csv, List.PositionOf( csv[Column2], "Settlement ID")),
promote = Table.PromoteHeaders(remove_lines)
in
promote,
files = Table.Combine(List.Transform(file_names, fx_csv)),
reorder = Table.ReorderColumns(files,{"Date", "Settlement ID", "closing fees", "promo rebates", "TDS", "total"})
in
reorder
Thank AlienSx for your answer.
I have done it this way.
let
Source = Folder.Files("B:\Excel Learning\Power Query Learning\1. Combining CSV Files with Uneven Columns & Junk Rows\CSV Files"),
Table_Cleaned = Table.AddColumn(Source, "Custom", each
let
MyTable = Csv.Document([Content]),
MyList = MyTable[Column1],
Position_Of_Date = List.PositionOf(MyList,"Date"),
My_Table_Cleaned = Table.Skip(MyTable,Position_Of_Date),
Promote_Headers = Table.PromoteHeaders(My_Table_Cleaned)
in
Promote_Headers),
Col_Names = Table.AddColumn(Table_Cleaned, "Custom.1", each Table.ColumnNames([Custom])),
Distinct_Col_Names = List.Distinct(List.Combine(Col_Names[Custom.1])),
Expaned_Table = Table.ExpandTableColumn(Table.SelectColumns(Col_Names,"Custom"),"Custom",Distinct_Col_Names)
in
Expaned_Table
I wouldn't be so confident about "Date" column in Position_Of_Date step because you have some junk data above. What if List.PositionOf finds "Date" text in there?
It won't ever find the date in junk rows. But I will consider your suggestion to remain 100% safe. I will use the second-column header instead.