Question description:
This user has given permission to use the problem statement for this
blog.
How do I combine multiple text files into a single worksheet but with different columns for each files? I need 2 columns for each file and all the files need to be aligned side by side. For example, I need data in txt1 to be loaded into column A and B, txt2 in column C and D, and txt3 in column E and F, and so on. I have total 60 txt files to be combined into a single spreadsheet.
Solved by E. C. in 24 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
21/06/2018 - 10:42
Hi
User
21/06/2018 - 10:43
hi
Excelchat Expert
21/06/2018 - 10:43
Welcome to Got it Pro
Excelchat Expert
21/06/2018 - 10:44
You will have to copy paste the data from each file and paste it into respective columns
User
21/06/2018 - 10:44
can you understand my question?
Excelchat Expert
21/06/2018 - 10:44
Yes i understand it
User
21/06/2018 - 10:45
Is there any other way? There are 60 files
Excelchat Expert
21/06/2018 - 10:46
I understand..may be we can do it through writing a macro code but unfortunately this platform does not support VBA/macros
User
21/06/2018 - 10:46
and i have to do it many times and each time I need to handle 60 files
User
21/06/2018 - 10:46
I thought the power query may be one solution
Excelchat Expert
21/06/2018 - 10:48
yes power query can also work but you will need to setup it such a way that you get the result that you want
User
21/06/2018 - 10:50
will you be able to help me to set up the power query?
Excelchat Expert
21/06/2018 - 10:51
https://www.powerquery.training/what-is-power-query/
Excelchat Expert
21/06/2018 - 10:52
This is the basic on power queries
Excelchat Expert
21/06/2018 - 10:53
First of all we will need to install power query
Excelchat Expert
21/06/2018 - 10:53
https://www.excelcampus.com/install-power-query/
User
21/06/2018 - 10:53
does it mention anything similar to what I want? like the arrangement of my data?
Excelchat Expert
21/06/2018 - 10:53
This will help with setting up of power query tab in the excel
Excelchat Expert
21/06/2018 - 10:54
not exactly
User
21/06/2018 - 10:54
I already have power query in my excel 2016
Excelchat Expert
21/06/2018 - 10:54
If i had this question i would have either used VBA or done it manually if it was a one time exercise
Excelchat Expert
21/06/2018 - 10:55
There could be one more method to it...
Excelchat Expert
21/06/2018 - 10:55
We can connect all the text files to excel so that their output comes in different columns
Excelchat Expert
21/06/2018 - 10:56
This would be the easiest method
User
21/06/2018 - 10:56
how to do that?
Excelchat Expert
21/06/2018 - 10:57
Select the cell where the first line of text from the file should be.
Use the Data/Get External Data/From File dialog to select the text file to import.
Format the imported text as required.
In the Import Data dialog that opens, click on Properties...
Uncheck the Prompt for file name on refresh box.
Whenever the external file changes, click the Data/Get External Data/Refresh All button.
Excelchat Expert
21/06/2018 - 10:58
i think this should help you, you can embed the output of text files in excel through this way
User
21/06/2018 - 11:02
Do I need to do it for each text file? Or I can do it one time for all 60 files?
Excelchat Expert
21/06/2018 - 11:03
This we will have to do for each text file..
Excelchat Expert
21/06/2018 - 11:03
We can refresh it so that next data is displayed once the text files are updated
User
21/06/2018 - 11:05
can you show me how to do it? I can upload 2 txt files.
Excelchat Expert
21/06/2018 - 11:05
I have mentioned the steps, we will need to follow them
Excelchat Expert
21/06/2018 - 11:05
I think it cannot be done on google sheets
Excelchat Expert
21/06/2018 - 11:06
Even if i do it on excel at my end it won't help you
Excelchat Expert
21/06/2018 - 11:06
Hope you understand
User
21/06/2018 - 11:06
I see. Thank you for your help.
Excelchat Expert
21/06/2018 - 11:06
Hope this session has helped you
Excelchat Expert
21/06/2018 - 11:06
Please do give good rating and reviews..
Excelchat Expert
21/06/2018 - 11:07
It would really help a lot
Excelchat Expert
21/06/2018 - 11:07
Have a great day
Excelchat Expert
21/06/2018 - 11:07
Take Care
User
21/06/2018 - 11:07
thank and you too. bye now.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
