I have over 100 text files and each of them has two columns of numbers, the columns are separated by space, they are stored under the same folder. The final goal is to import all of the text file into a single excelsheet, and i want only the second column of each text file showed up in the excelsheet. Is that possible to do? I have no knowledge about VBA or macro. please help! Thank you!
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
import multiple .txt files into one excel sheet with selected column
This can be achieved by using Power Query, which is a free Excel add-in (if you are using Excel 2016, it is included). If no one else can post a different solution (I am not aware of any), then you can install and use this. Is this an option for you?
Upvote
0
Thank you, i can find the power query under Data tab, would you please teach me how to import multiple text data with power query? Thank you
Upvote
0
Put all of your txt files in one folder (there should be nothing else in that folder).
Click on New Query --> From File --> From Folder
You should get a prompt for the folder. Browse to the folder and hit ok. This will open the power query window.
What you will see in this window is a table with the headers: Content, Name, Extension, etc.
Next to Content, there is a button with double arrows pointing down. Click it.
What you will now see is your data all combined together.
There are a few steps you have to take to clean it up.
If the first row of records you see are your column headers from the txt file, select "Use First Row as Headers" button in the Home menu (it is in the Transform section of the menu)
Next, you need to get rid of all the rows which are actually the headers from the other text files. What I mean is that if you have 100 text files with the headers "Col1" and "Col2", those headers are actually part of your data now and you need to remove them. Click on the filter arrow on any one of your headers, choose Text Filters, and then Does Not Equal. Type in the exact text of the column header and hit ok.
Last step to clean your data is to remove the first column. Select the column header, and then choose Remove Columns in the menu at the top.
To load the data to the sheet, click on the arrow underneath "Close & Load" and then select "Close & Load To..."
This will close Power Query and take you back to Excel, where you will have a pop up box titled "Load To"
Choose Table, select the location you want to load it to, and uncheck Add to Data Model.
That's it. If you ever need to update/add files, just add the txt files to the folder, and then Refresh-All.
Let me know if you run into issues.
Good luck.
Click on New Query --> From File --> From Folder
You should get a prompt for the folder. Browse to the folder and hit ok. This will open the power query window.
What you will see in this window is a table with the headers: Content, Name, Extension, etc.
Next to Content, there is a button with double arrows pointing down. Click it.
What you will now see is your data all combined together.
There are a few steps you have to take to clean it up.
If the first row of records you see are your column headers from the txt file, select "Use First Row as Headers" button in the Home menu (it is in the Transform section of the menu)
Next, you need to get rid of all the rows which are actually the headers from the other text files. What I mean is that if you have 100 text files with the headers "Col1" and "Col2", those headers are actually part of your data now and you need to remove them. Click on the filter arrow on any one of your headers, choose Text Filters, and then Does Not Equal. Type in the exact text of the column header and hit ok.
Last step to clean your data is to remove the first column. Select the column header, and then choose Remove Columns in the menu at the top.
To load the data to the sheet, click on the arrow underneath "Close & Load" and then select "Close & Load To..."
This will close Power Query and take you back to Excel, where you will have a pop up box titled "Load To"
Choose Table, select the location you want to load it to, and uncheck Add to Data Model.
That's it. If you ever need to update/add files, just add the txt files to the folder, and then Refresh-All.
Let me know if you run into issues.
Good luck.
Upvote
0
I appreciated very much. You explained very clear, it is very useful, now i can successfully merge my data together and get rid of the first column. I am almost there. The only problem is after i hit the button with double arrows to merge the those txt files, for each column, the data are showed one after another from top to bottom, is there any way i can separate the data into different column? I mean if there is 5 rows in each column each file, and there are 100 files, now i got still the same amount of column but 500 rows for each of them. is there anyway i can make them 5 rows each bu 500 columns?
Thank you for your kind help.
Thank you for your kind help.
Upvote
0
To be clear on your request, each file has 2 columns and 5 records. Instead of combining all records into 2 columns, you want all of the columns to be added individually, with 5 records total.
Can you post just 2 sample files? It is an interesting problem, and I'm not sure it is possible, but let me try it.
Upvote
0
Hi Thank you for being willing to try, it takes me a little while figuring out how to attach picture here.
Here are three text files that i have,
the first column in each file is the wavelength, i only need one of it, the second column is the data i actually wanted.
Here is the pattern i wanted it in excel:
The first column is the wavelength, the second ,third and the fourth columns are the second column from each text files.
These are just examples, i have hundreds of text files, so if i do it manually it will be a very tedious work. Is that possible achieve with power query?
expected to your answer!Thank you!
Here are three text files that i have,
the first column in each file is the wavelength, i only need one of it, the second column is the data i actually wanted.
Here is the pattern i wanted it in excel:
The first column is the wavelength, the second ,third and the fourth columns are the second column from each text files.
These are just examples, i have hundreds of text files, so if i do it manually it will be a very tedious work. Is that possible achieve with power query?
expected to your answer!Thank you!
Upvote
0
So the good news is that I was able to do this with csv files using the method found here:
Solved: Retain file name column when using Folder as a dat... - Microsoft Power BI Community
Is it possible to post 2 sample files (not screenshots, but the file themselves)? I would have to test it with your txt files since I don't have any to work with.
Solved: Retain file name column when using Folder as a dat... - Microsoft Power BI Community
Is it possible to post 2 sample files (not screenshots, but the file themselves)? I would have to test it with your txt files since I don't have any to work with.
Upvote
0
Hi Thank you, that is a very promising progress, i think the forum is not allowed to attach actual files, so here are three links of my text files to download.
https://drive.google.com/uc?id=0B5cPxTjIRfS3bDdaRUJOUElwNEU
https://drive.google.com/uc?id=0B5cPxTjIRfS3R1B5YlNVWnpCQVE
https://drive.google.com/uc?id=0B5cPxTjIRfS3LS1xMU94c3E4R3M
Good luck and please let me know if they work.
Thank you again.
https://drive.google.com/uc?id=0B5cPxTjIRfS3bDdaRUJOUElwNEU
https://drive.google.com/uc?id=0B5cPxTjIRfS3R1B5YlNVWnpCQVE
https://drive.google.com/uc?id=0B5cPxTjIRfS3LS1xMU94c3E4R3M
Good luck and please let me know if they work.
Thank you again.
Upvote
0
So here you go. In Power Query, click on "Advanced Editor" under the Home menu. Replace EVERYTHING that is there with the following code (change the source to the path of the folder containing your files):
Here is what the code does:
1) Set the source to your folder (this results in a list of files and associated data about the files themselves, not the contents)
2) Add a column which contains the contents of each file
3) Remove everything except the filename and contents columns
4) Change the data format from text to decimal numbers
5) Pivot the data so that each file has its own column, with the values from the second column underneath.
Hope this works for you.
Code:
let
Source = Folder.Files("C:\...\data"),
#"Added Column" = Table.AddColumn(Source, "Custom", each Csv.Document([Content],[Delimiter="#(tab)", Encoding=1252])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Column", "Custom", {"Column1", "Column2"}, {"Column1", "Column2"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Content", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Column1", type number}, {"Column2", type number}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Name]), "Name", "Column2")
in
#"Pivoted Column"Here is what the code does:
1) Set the source to your folder (this results in a list of files and associated data about the files themselves, not the contents)
2) Add a column which contains the contents of each file
3) Remove everything except the filename and contents columns
4) Change the data format from text to decimal numbers
5) Pivot the data so that each file has its own column, with the values from the second column underneath.
Hope this works for you.
Upvote
0
