Load twenty fixed width text files into Excel

The Sandbox Software/Technology
21

I see I forgot to update the title of the thread, too. :sheesh: The internet is hard.

22

RN :laughing:

23

Together with Maphistos_Sidekickā€™s simple DOS command & the macro from How to import multiple text files to multiple sheets? (extendoffice.com) I was able to get all of the files easily loaded into a nice Excel workbook.

Then, I carefully read the email with this assignment where boss said that I only needed to worry about two of the files and :head_smack:. Oh well, Iā€™m still a better person for having gone through it.

Thanks, everybody!

24

Power Query

Takes about 5 seconds to point a query at the first file. Maybe 10-15 s to navigate and make sure it is pulling the data correctly. Then another minute or so to copy the query and edit the source filename 19 times. Now you have an XL file with 20 tabs each and about 5 minutes of activity.

25

Sounds like something a power user would use.

Serious question (Iā€™ll probably just go google it, but Iā€™ll ask anyway): Is Power Query a stand alone software package or is it a part of a recent version of Office or what? How does one get their hands on something like that?

26

Probably would take longer to answer and procure than your project would take to do.

27

It used to be an add-in but is now standard. I have 2019 as well and it is standard.

It is on your Data ribbon

image

All of the icons on the left are ways to reference data in your query as the initial source.

1 Like
28

If you use the ā€œFrom Text/CSVā€ option you can change it to look at All Files which would allow you to pull in the .xyz text file.

1 Like
29

Thanks for that, @Kenny. One of our consultants used it for a project that they sent back to us and, well, letā€™s just say that :exploding_head: my mind was blown.