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
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.