Convert Several csv files into xlsx from different folders

[kevin67]

Hello

I hope that someone can assist me on the following. I have several csv files from different folders and I need to delete duplicate records base on condition from column 1 and then convert them into xlsx. In the folders I will have both the csv and xlsx
The files name is the current date.csv in all the folders.

Any suggestion will be greatly appreciated.

Thank you

 

[GraH]

Sounds like a fun job for Power Query (Get & Transform). Given you have Office 2016 that's native available.

1. Start via the data ribbon: Get Data -> From File -> From Folder
2. Browse and select folder
3. Edit and combine files
4. Filter on .csv (as you would in Excel)
5. Filter out repeated headers
6. Load as connection only
7. Repeat for each folder
8. Then finally Get Data -> Combine Queries -> Append
9. Select all initial queries
10. Load to Excel Table

All this is do-able with mouse clicks.

 

[kevin67]

Sounds like a fun job for Power Query (Get & Transform). Given you have Office 2016 that's native available.

1. Start via the data ribbon: Get Data -> From File -> From Folder
2. Browse and select folder
3. Edit and combine files
4. Filter on .csv (as you would in Excel)
5. Filter out repeated headers
6. Load as connection only
7. Repeat for each folder
8. Then finally Get Data -> Combine Queries -> Append
9. Select all initial queries
10. Load to Excel Table

All this is do-able with mouse clicks.

Thanks for your solution I am looking if this is possible in VBA because I have 15 folders to update

 

[GraH]

FYI, obviously it is possible via VBA (but that's not me). Note that PQ runs a script (much like VBA) and if new files appear they get appended after a simple refresh.

 

[ArtisticOwl]

Sounds like a fun job for Power Query (Get & Transform). Given you have Office 2016 that's native available.

1. Start via the data ribbon: Get Data -> From File -> From Folder
2. Browse and select folder
3. Edit and combine files
4. Filter on .csv (as you would in Excel)
5. Filter out repeated headers
6. Load as connection only My Wegmans Connect
7. Repeat for each folder
8. Then finally Get Data -> Combine Queries -> Append
9. Select all initial queries
10. Load to Excel Table

All this is do-able with mouse clicks.

Thanks for this. This worked exactly as expected.