jeffcoleky
Active Member
- Joined
- May 24, 2011
- Messages
- 274
I'm trying to get data from multiple TXT files into Excel. Each TXT file represents 1 row of data on the excel workbook. Each section of text the TXT file represents the corresponding column in that row.
For example: Everything after "Other Observed Names" would go in a column labeled "Other Observed Names" in the excel file. Or in this case, the value would be "SHERRY L JONES (01/01/1988 to 06/01/2016)
SHERRY G JONES (01/01/1988)" The stopping point for each value is when there is a empty line in the TXT file. Multiple lines of txt are copied to single cell in Excel.
Not every TXT file is the same. For example, it might not have a "Professional Licenses" section so the TXT file would be shorter. I only need the following:
So can anyone help me write a macro that would:
The macro should grab the data from every TXT file in C:\DATA and place it into a C:\OUTPUT\ExtractedData.xlsx file. Thanks for any help you can offer!
Here's some example raw Data:
Here are 5 TXT files of raw data:
Sample file 1: https://www.dropbox.com/s/aa2mmt9864k2h3d/sourcefile1.txt?dl=0
Sample file 2: https://www.dropbox.com/s/l7y27gxk7r6m21n/SourceFile2.txt?dl=0
Sample file 3: https://www.dropbox.com/s/9po1rzpbct3oc4u/SourceFile3.txt?dl=0
Sample file 4: https://www.dropbox.com/s/cwm9w79fyd5e8ti/SourceFile4.txt?dl=0
Sample file 5: https://www.dropbox.com/s/sm0pzxfrdrk5xqh/SourceFile5.txt?dl=0
Here is an example ExtractedData.xlsx file on how it would look like: https://www.dropbox.com/s/7k40yca6fg72h8n/ExtractedData.xlsx?dl=0
Same file in Google Sheets: https://docs.google.com/spreadsheet...4xsSLh9tlkCl96qM1yLangTGKAY/edit#gid=11801720
PS. We also need anything after "FOR REAL ESTATE / FINANCIAL / BROKERAGE PURPOSES ONLY - " but before an empty row. EG: "6006 MARKET ST ANN ARBOR MI 48191 JEFFERSON-People-Search-201905227751"
For example: Everything after "Other Observed Names" would go in a column labeled "Other Observed Names" in the excel file. Or in this case, the value would be "SHERRY L JONES (01/01/1988 to 06/01/2016)
SHERRY G JONES (01/01/1988)" The stopping point for each value is when there is a empty line in the TXT file. Multiple lines of txt are copied to single cell in Excel.
Not every TXT file is the same. For example, it might not have a "Professional Licenses" section so the TXT file would be shorter. I only need the following:
- First Line
- Dates of Birth
- Dates at Searched Location:
- Professional Licenses
- Possible Relatives
- Indicators
- Possible Phones
- Possible Emails
- Address History
So can anyone help me write a macro that would:
- open each txt file
- search for each of the specified values
- copy everything after it through the empty line
- then paste it in the corresponding column
The macro should grab the data from every TXT file in C:\DATA and place it into a C:\OUTPUT\ExtractedData.xlsx file. Thanks for any help you can offer!
Here's some example raw Data:
Code:
FOR REAL ESTATE / FINANCIAL / BROKERAGE PURPOSES ONLY - 6006 MARKET ST ANN ARBOR MI 48191 JEFFERSON-People-Search-201905227751
SHERRY C JONES, 66 Years Old (ANN ARBOR, KY) 6006 MARKET ST, ANN ARBOR, MI 48191-1940 (JEFFERSON COUNTY) (04/1986 to 05/24/2019)
SHERRY C JONES (04/01/1980 to 04/07/2019)
SHERRY COX JONES (12/29/2014 to 08/20/2018)
SHERRY KAY JONES (12/05/2012)
Other Observed Names
SHERRY L JONES (01/01/1988 to 06/01/2016)
SHERRY G JONES (01/01/1988)
Dates of Birth
DOB: 09/1952
Age: 66
Other DOB: 09/1949
Age: 69
Dates at Searched Location: 04/1986 to 05/24/2019
Professional Licenses
SHERRY JONES
6006 MARKET ST, ANN ARBOR, MI 48191-1940 (JEFFERSON COUNTY)
NURSE
Kentucky 1041467
First Issued: 08/1974
Possible Relatives
Allen R JONES 01/1949 Age: 70
Brent JONES 05/1986 Age: 44
Tamara Jeanine Mcgill 01/1988 Age: 41
Indicators
Bankruptcies: None Found
Liens: 24 Found, Latest in 2018
Judgments: 1 Found, Latest in 1996
Utilities: 2 Found
Cities
ANN ARBOR, MI (04/1980 to 05/24/2019)
Possible Phones
(652) 448-1295 (CT) (Mobile) (90%)
(777) 821-9798 (ET) (Mobile) (86%)
(777) 241-5866 (ET) (LandLine) (78%)
(777) 442-4484 (ET) (Mobile) (66%)
(777) 562-4680 (ET) (LandLine) (66%)
(777) 241-4815 (ET) (LandLine) (66%)
(652) 724-6894 (CT) (LandLine) (42%)
(777) 614-5167 (ET) (VoIP) (15%)
(777) 290-7192 (ET) (VoIP) (4%)
(227) 886-8976 (CT) (LandLine) (4%)
Counties
Jefferson County, MI (04/1980 to 05/24/2019)
Possible Emails
slf9135@gmail.org
s9115@hotmail.org
saJONES@erols.org
sherrysJONES@hotmail.org
mmqdswr@google.org
Address History (5)
6006 MARKET AVE, ANN ARBOR, MI 48191-1940 (JEFFERSON COUNTY) (04/1986 to 05/24/2019)
Subdivision Name: FERNBROOK
7417 N JENEVIEVES ST, ANN ARBOR, MI 48128-1749 (JEFFERSON COUNTY) (06/01/2016 to 08/20/2018)
5512 GASKINS ST, ANN ARBOR, MI 48129-2957 (JEFFERSON COUNTY) (05/24/2016 to 05/24/2016)
8444 SHEPHERDSVILLE AVE, ANN ARBOR, MI 48119-4540 (JEFFERSON COUNTY) (04/1980 to 01/24/2004)
8444 SHEPHERDSVILLE AVE # OLD, ANN ARBOR, MI 48119-4540 (JEFFERSON COUNTY) (04/01/1980 to 04/01/1980)
05/27/2019Here are 5 TXT files of raw data:
Sample file 1: https://www.dropbox.com/s/aa2mmt9864k2h3d/sourcefile1.txt?dl=0
Sample file 2: https://www.dropbox.com/s/l7y27gxk7r6m21n/SourceFile2.txt?dl=0
Sample file 3: https://www.dropbox.com/s/9po1rzpbct3oc4u/SourceFile3.txt?dl=0
Sample file 4: https://www.dropbox.com/s/cwm9w79fyd5e8ti/SourceFile4.txt?dl=0
Sample file 5: https://www.dropbox.com/s/sm0pzxfrdrk5xqh/SourceFile5.txt?dl=0
Here is an example ExtractedData.xlsx file on how it would look like: https://www.dropbox.com/s/7k40yca6fg72h8n/ExtractedData.xlsx?dl=0
Same file in Google Sheets: https://docs.google.com/spreadsheet...4xsSLh9tlkCl96qM1yLangTGKAY/edit#gid=11801720
PS. We also need anything after "FOR REAL ESTATE / FINANCIAL / BROKERAGE PURPOSES ONLY - " but before an empty row. EG: "6006 MARKET ST ANN ARBOR MI 48191 JEFFERSON-People-Search-201905227751"
