I need to import data from approximately 30 individual TXT files into one CSV sheet. They are all formatted the same, so the Text Import Wizard should work. However, instead of running the Text Import Wizard 30 times, I would like to create a macro that will automatically run 30 times for each individual txt file in a single folder.

I am able to create a macro for a specific file, but I need help creating a macro that will run for each of the 30 txt files (unique file names) and then import the data into the next empty cell after the previous file import.


1 Answer 1


This is a job for Power Query. That's what it was invented for. It's available for all the last ten years' versions of Excel, starting with free add-ins for XL 2010 and XL 2013 and built into the application since XL 2016, named "Get & Transform".

Use the "From Folder" option in the data ribbon, then in the Power Query editor, remove the files you don't need (if any) by using filters. Then expand the remaining files.

All this can be done without writing a single line of code, just by clicking on ribbon icons.

And the saved query can be refreshed and repeated at any time if there is new data in the source folder.

  • Thank you so much! It took a bit of trial and error, but this process did work for me and is a great tool.
    – MTK
    Feb 16, 2021 at 22:06
  • Thanks for the feedback. Power Query is the best thing to happen to Excel in the last 20 years.
    – teylyn
    Feb 16, 2021 at 22:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.