Import from several CSVs to separate sheets in a workbook

[Gary Clutterbuck]

Hi,
I am relatively new to PowerQuery so please forgive me if I am asking something that is relatively straightforward, or conversely something that PowerQuery isn't suitable for.

I have a number of CSV files in a folder, that I wish to import into a single Excel file, but the CSV tables need to be on separate Excel sheets as the tables don't contain similar data. Is this possible using PowerQuery? I found a macro onine to do it, but I'd like to clean up the data as I import it. The files will be refreshed on a monthly interval, and so I thought PowerQuery might be the best tool to do this.

Thanks for your help.

Gary

 

[Dan Bliss]

Gary, for PowerQuery to load several files correctly, the data format must be exactly the same: the same number of columns with the same data for each column, each files's column with the same data type. In the example below, File A & B can be loaded together, but not with File C. Notice that file C has its text and date-time fields in different columns, and is missing Column4.

File A
Column1 is date-time
Column2 is text
Column3 is whole number
Column4 is Currency

File B
Column1 is date-time
Column2 is text
Column3 is whole number
Column4 is Currency

File C
Column1 is text
Column2 is date-time
Column3 is whole number

One strategy would be to load the similar ones together, and load the dissimilar ones separately. Transform the dissimilar files until they match all the others, then append the transformed dissimilar queries to the similar queries, and you'll have one query with all your data.

If your adventurous, the Table.Schema(table as table) function will generate on column information: Name, Position, typeName, Kind, etc you need to compare and match data. And if you create two queries, Table.Schema(Table_A) and Table.Schema(Table_C), merge the queries, then run a Pivot Table on the result, you can get a nice table showing all column properties, highlighting any differences between the two sets of files.