Data from multiple csv file is not consolidating in Excel

Question

I'm trying to consolidate data from multiple csv files in excel sheet using Data-. New Query-.From File option.

However after doing "Combine and Load" data is not flowing completely in Excel.

Attached screenshot for your reference. You can see data is loaded only for 32 rows. However on the right side you can see that 430k+ rows are loaded

Please elaborate over what you mean with "data is not flowing completely". Maybe post sample data and the M code. — Ricardo Diaz, Dec 26 '19 at 14:48
I have around 390 csv files saved down in a folder. I m trying to consolidate data from all these 390 csv files into single excel sheet. I'm using "Data"- "New Query" -From File option in excel. — kundandalvi, Dec 26 '19 at 15:01
And how are these CSV filers structured? Do they all have the same (number of) columns? Should they be consolidate in a single sheet, or in 390 different sheets? — Luuk, Dec 26 '19 at 15:39
@Luuk these files have same columns(withoutcolumn headings) and it should be consolidated in single sheet. — kundandalvi, Dec 27 '19 at 18:24

score 1 · Answer 1 · answered Dec 27 '19 at 19:38

1

Combine files in Excel should be possible, but it's much easier done in a batch file.

see: Need to combine lots of files in a directory

and/or

Windows batch - concatenate multiple text files into one

answered Dec 27 '19 at 19:38

Luuk

4,913
3
17
21

score 0 · Answer 2 · answered Dec 31 '19 at 14:32

Try this

Data ... Get Data .. From Other Sources ... Blank Query .... Advanced Editor ...

Paste below over everything in box

let Source = Folder.Files("C:\directory\subdirectory\"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".csv")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}),
CSVtoTable= Table.TransformColumns(#"Removed Other Columns", {{"Content", each Csv.Document(_)}}),
MaxColumns = List.Max(List.Transform(CSVtoTable[Content], each Table.ColumnCount(_))),
#"Expanded Content" = Table.ExpandTableColumn(CSVtoTable, "Content", List.Transform({1..MaxColumns}, each "Column" & Number.ToText(_)))
in #"Expanded Content"

Change row 1 to reflect the path where your files will be stored

Hit 'Done'

What it does is

(a) read in all files in your specified location (b) filter for CSV files (c) erase extra columns (d) Convert each CSV file into its own table (e) count how many columns the tables have so that we know how many columns to expand to (f) expand the tables

Complete any additional transformations you need then do Close And Load

Data from multiple csv file is not consolidating in Excel

2 Answers2