Extracting Data from multiple .txt files to be combined into 1 spreadsheet

ShadowSaxx

Board Regular
Joined
Nov 1, 2012
Messages
68
The data set I am working with is reported to me in about 200 .txt files. I have found this code:
posted by ddubnansky:
http://www.mrexcel.com/forum/excel-...xcel-2010-includes-files-sub-directories.html

to create a directory of the files and drop them into a single spreadsheet with the address to the individual files.

I would like to import the data from the .txt files using that address list and drop the data from all the files into 1 worksheet.
Each file has a different address, so the sample data below is from just 1 file within the folder:C:\Users\gudsa_000\Documents\JobCode Automation\Data
Sample data from file: C:\Users\gudsa_000\Documents\JobCode Automation\Data\20140512\DFITR496
Data as viewed in MS NotePad:
Total for CS: 718
Total for GC: 5
Total for ML: 1953
Total for RI: 434
Total for RP: 80621
Total for TR: 10
Total for VA: 1248
Total Page count 84989

I would like the data to populate as follows:
File Name File Folder Group Qty
DFITR496 20140512 CS 718
DFITR496 20140512 GC 5
DFITR496 20140512 ML 1953
DFITR496 20140512 RI 434
DFITR496 20140512 RP 80621
DFITR496 20140512 TR 10
DFITR496 20140512 VA 84989

I do not need the Total Page Count line.
This file reported 7 Groups, but the number of Groups being reported varies. One could have 7, the next could have 10, the next could have just 1. I need to loop through all the files and pull back the data from each file.

Does anyone have any suggestions on how to do this? If so, I would really appreciate any suggestions.

-SS
 
Last edited:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hey ShadowSaxx,

I am not sure if this is all possible, as I am not as experienced with VBA, but I know some of it is possible.

If I am correct you want the following to happen:
1. You want to copy and paste the information from a text file into an Excel worksheet
2. You want to run a macro to change the formating and enter additional information.
3. You want the name of the file and Folder to be placed into each line
4. You want to strip out the "Total for " and replace it with the File Name.
5. You also want to insert the File folder name after the File Name.
6. You want to remove the ":" which is found after each of the group names.

let me know if this is what you are looking for.

later

Ty


Is this correct?
 
Upvote 0
If I am correct you want the following to happen:
1. You want to copy and paste the information from a text file into an Excel worksheet =Yes
2. You want to run a macro to change the formating and enter additional information. =Yes
3. You want the name of the file and Folder to be placed into each line =Yes
4. You want to strip out the "Total for " and replace it with the File Name. =Yes
5. You also want to insert the File folder name after the File Name. =Yes
6. You want to remove the ":" which is found after each of the group names. =Yes

let me know if this is what you are looking for.

later

Ty


Is this correct?

I would also like each field to populate to separate cell
File Name File Folder Group Qty
DFITR496 20140512 CS 718
DFITR496 20140512 GC 5
DFITR496 20140512 ML 1953
DFITR496 20140512 RI 434
DFITR496 20140512 RP 80621
DFITR496 20140512 TR 10
DFITR496 20140512 VA 84989
 
Last edited:
Upvote 0
Are you manually copying the raw data from the text file into the excel worksheet? Or are you trying to find a solution in which to have excel do this for you?

I think one of the problems that will happen is getting the file name and folder name entered into the excel worksheet. As far as the populating different cells that shouldn't be too difficult as it is mostly a cosmetic issue. From my experience once the data is in the worksheet data manipulation is simple.

Could you give me an example of the data in the text file before you do anything to it. Also, a description of the steps you are currently taking in order to get the desired end result would be helpful as well.
 
Upvote 0
Hey ShadowSaxx,

So I have found some VBA that will copy the contents of the text file into the excel worksheet.

Now the only thing that we need to address is how to get the file name and folder name into the excel worksheet.

I am curious how the text file is created from the start. Is there a specific form that is filled out or is it hand typed by a person? One thing that would make this easier is if an additional step could be implemented that would enter the text file's name and path into the text file before you get it to move into excel. Then it is simply a matter of text manipulation to get the desired end result.

Let me know or privat message me so that we can reach the goal. If you have already found an answer please post your findings or give us all a link to the end result if found on another message base.

later

Ty
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top