Populate an Excel file from a txt file

[Jaredhott]

Good afternoon,

I get an email in this format based on how many fields a person fills out in a questionnaire

First_Name: jim
Last_Name: bond
Address: 820 west
City: mytown
State: al
Zip: 11111
Email: myname@gmail.com
Height: 6'3"
Weight: 210
Date_of_Birth: 3/31/95
Married: no
Father_Name: Roy Rogers
Father_Cell_Phone: 111111
Mother_Name: May Rogers
Mothers_Cell_Phone: 18175

i want to populate this info into an excel file so that if a person fills out a questionnaire I can populate the rows that they fill out into an excel document with the field names at the top and the field values down the column like this:

[TABLE="width: 217"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]First_Name[/TD]
[TD]Last_Name[/TD]
[TD]Address[/TD]
[/TR]
[TR]
[TD]jim[/TD]
[TD]bond[/TD]
[TD]820 west[/TD]
[/TR]
[TR]
[TD]bill[/TD]
[TD]player[/TD]
[TD]555 main[/TD]
[/TR]
[TR]
[TD]steve[/TD]
[TD]taylor[/TD]
[TD]44 1st [/TD]
[/TR]
</tbody>[/TABLE]

it would be great to figure out how to automate it from the email but for right now i can copy the email into a txt file manually and import it to excel. i just want the import to match the fields and insert the value going down the column. let me know if you guys have an idea how to do something like this.

 

[igold]

Hi JaredHott,

Try this code. Just be sure to change the path to where you save your text file with the name of the file. It worked per your specification with the text file shown below. I pasted a second set of info to be sure that if you wanted to copy your emails to just one text it would work correctly. There is probably code out there that will read the email directly but I don't have it nor have I tried to research it.

The text file:

First_Name: jim
Last_Name: bond
Address: 820 west
City: mytown
State: al
Zip: 11111
Email: myname@gmail.com
Height: 6'3"
Weight: 210
Date_of_Birth: 3/31/95
Married: no
Father_Name: Roy Rogers
Father_Cell_Phone: 111111
Mother_Name: May Rogers
Mothers_Cell_Phone: 18175
First_Name: Joe
Last_Name: Namath
Address: 500 East
City: Yourtown
State: NY
Zip: 11111
Email: myname@live.com
Height: 6'3"
Weight:
Date_of_Birth: 1/1/50
Married: Yes
Father_Name: Roy Rogers
Father_Cell_Phone: 111111
Mother_Name: May Rogers
Mothers_Cell_Phone: 18175

Sub TextFile()


    Dim row_number As Single
    Dim LineFromFile As Variant
    Dim col_number As Single
    Dim FilePath As String
    
    FilePath = "C:\Users\Me\Desktop\Test22.txt"
    Close #1
    Open FilePath For Input As #1
    row_number = 0
    col_number = 0
    Range("a1").Select


    Do Until EOF(1)
        Line Input #1, LineFromFile
        Dim LineItems As Variant: LineItems = Split(LineFromFile, ":")
        ActiveCell.Offset(0, col_number).Value = LineItems(0)
        ActiveCell.Offset(row_number + 1, col_number).Value = LineItems(1)
        col_number = col_number + 1
        If col_number = 15 Then row_number = row_number + 1
        If col_number = 15 Then col_number = 0
    Loop


    Close #1
    Cells.Columns.AutoFit
End Sub

I hope this helps!

igold

 

[Jaredhott]

Thank you so much this helped a lot and will really help us here. I just have one question. I did not put all the possible fields on that example and when a field that wasnt on the example was filled in it did not create a new column. I have below every possible field that could show up can we incorporate that into the code? even if it leaves alot of them blank most of the time that will help us.

First_Name: Cory
Last_Name: Miller
Address: 1234 Thatone Drive
City: Marshalltown
State: Iowa
Zip: 50158
Email: Cory.Miller@dsu.edu
Phone: 1234567890
Cell_Phone: 1234567890
Height: 5'2"
Weight: 310
Date_of_Birth: 09/17/1903
Married: yes
Father_Name: Lance Leipold
Father_Email: Lance.Leipold
Father_Cell_Phone: 3214567890
Father_Occupation: Football Coach
Mother_Name: Mrs. Leipold
Mothers_Email: Mrs.Leipold
Mothers_Cell_Phone: 2134567890
Mother_Occupation: Football Coach's wife
Would_like_to_visit: No
High_School: Marshalltown High School
School_Phone: 4561237890
Graduation_Date: 1921
High_School_GPA: 1.3
Class_Rank: 5634
ACT: 12
SAT: 2100
College_Interest: Sports Psychology
Previous_College: University of Northern Iowa
Previous_College_GPA: 1.2
Previous_College_Hours_Passed: 3
Hours_Passed_in_last_two_semesters: 0
Previous_College_Years_of_Eligibility: 4
Mens_Basketball_Info:
Mens_Basketball_High_School_Coach: Jerry
Mens_Basketball_Coach_Phone: 7890123456
AAU_Coach_Name: Tom
AAU_Coach_Phone: 3451267890
Positions_Played: Center
Scoring_Average: 45ppg
FG_Percentage: .003
Other_Stats: 0.01 Assists per game
Mens_Basketball_Athletic_Honors: All-World
Mens_Basketball_Game_Tapes_Available: no
Link_to_highlight_film: https://www.facebook.com/cory.miller.946?fref=ts
Baseball_Info:
Baseball_High_School_Coach: Jerry
Baseball_Coach_Phone: 8472634512
Position: Pitcher
Bat: switch
Throws: right
Sixty_Yard_Time: 57 seconds
Years_Lettered: 0
Team_Record_Won: 56
Team_Record_Lost: 1
AB: 400
R: 105
H: 210
2B: 56
3B: 13
HR: 48
RBI: 130
BB: 120
SO: 90
SB: 52
BA: .100
PO: 46
ASST: 390
E: 5000
DP: 100
FA: 1
Pitching_GP: 1
Pitching_IP: 1
Pitching_Hits: 21
Pitching_Runs: 20
Pitching_ER: 20
Pitching_ERA: 180
Pitching_BB: 3
Pitching_SO: 0
Pitching_Won: 0
Pitching_Lost: 1
Baseball_Athletic_Honors: All-apartment Senior Year
Baseball_Game_Tapes_Available: no
Mens_Cross_Country_Info:
Mens_Cross_Country_High_School_Coach: Tom
Mens_Cross_Country_Coach_Phone: 8374651290
XC_Events_and_Times: Marathon: 2 minutes
Mens_Cross_Country_Athletic_Honors: All-office
Mens_Cross_Country_Game_Tapes_Available: no
Football_Info:
Football_High_School_Coach: Nick Saban
Football_Coach_Phone: 4821342536
Offensive_Position: QB
Defensive_Position: DT
Enrollment_Year: 1921
bench: 600
squat: 135
vertical: 10
forty_yard_Dash_Time: 27.31
Other_Sports_Interested: All of them
Other_Colleges_Interested: All of them
Football_Athletic_Honors: All-block Senior year
Football_Game_Tapes_Available: no
twitter_handle: @Coach_Miller21
Link_to_facebook_account: https://www.facebook.com/cory.miller.946?fref=ts
Mens_Track_&_Field_Info:
Mens_Track_&_Field_High_School_Coach: Bruce Jenner
Mens_Track_&_Field_Coach_Phone: 8372456912
Mens_Track_&_Field_Athletic_Honors: All-world
Mens_Track_&_Field_Game_Tapes_Available: no
PlayNAIA_ID: 48723

Hi JaredHott,

Try this code. Just be sure to change the path to where you save your text file with the name of the file. It worked per your specification with the text file shown below. I pasted a second set of info to be sure that if you wanted to copy your emails to just one text it would work correctly. There is probably code out there that will read the email directly but I don't have it nor have I tried to research it.

The text file:

First_Name: jim
Last_Name: bond
Address: 820 west
City: mytown
State: al
Zip: 11111
Email: myname@gmail.com
Height: 6'3"
Weight: 210
Date_of_Birth: 3/31/95
Married: no
Father_Name: Roy Rogers
Father_Cell_Phone: 111111
Mother_Name: May Rogers
Mothers_Cell_Phone: 18175
First_Name: Joe
Last_Name: Namath
Address: 500 East
City: Yourtown
State: NY
Zip: 11111
Email: myname@live.com
Height: 6'3"
Weight:
Date_of_Birth: 1/1/50
Married: Yes
Father_Name: Roy Rogers
Father_Cell_Phone: 111111
Mother_Name: May Rogers
Mothers_Cell_Phone: 18175

Sub TextFile()


    Dim row_number As Single
    Dim LineFromFile As Variant
    Dim col_number As Single
    Dim FilePath As String
    
    FilePath = "C:\Users\Me\Desktop\Test22.txt"
    Close #1
    Open FilePath For Input As #1
    row_number = 0
    col_number = 0
    Range("a1").Select


    Do Until EOF(1)
        Line Input #1, LineFromFile
        Dim LineItems As Variant: LineItems = Split(LineFromFile, ":")
        ActiveCell.Offset(0, col_number).Value = LineItems(0)
        ActiveCell.Offset(row_number + 1, col_number).Value = LineItems(1)
        col_number = col_number + 1
        If col_number = 15 Then row_number = row_number + 1
        If col_number = 15 Then col_number = 0
    Loop


    Close #1
    Cells.Columns.AutoFit
End Sub

I hope this helps!

igold

 

[igold]

Hi,

The key is consistency. For the code to work you have to copy/import the same number of fields (with the colon) all the time, even if the response is blank. In my example I purposely left the weight blank in the second set of data.

To change the code to work with all your fields, count your number of fields and then where you see the number 15 in the two lines near the bottom of my code, change that to the number of fields that you are going always import. If you count the fields in your original post you will see that it had 15 fields...

Let me know how you make out.

Regards,

igold

 

[igold]

In your second post with all your fields near the end:

Mens_Track_&_Field_High_School_Coach: Bruce Jenner

Who is Bruce Jenner???

 

[Jaredhott]

Would there be anyway to just read the text until the field. The way the email comes through to me doesnt show a field if it is left unused so there will never be a consistent number of fields. Thanks for your help.

Hi,

The key is consistency. For the code to work you have to copy/import the same number of fields (with the colon) all the time, even if the response is blank. In my example I purposely left the weight blank in the second set of data.

To change the code to work with all your fields, count your number of fields and then where you see the number 15 in the two lines near the bottom of my code, change that to the number of fields that you are going always import. If you count the fields in your original post you will see that it had 15 fields...

Let me know how you make out.

Regards,

igold

 

[igold]

Hi,

Perhaps if you could show me the way that two different emails might come in to you, I could come up with a solution for the number of fields. I really have not done a lot with .txt to Excel, and pretty much what I gave you is the current extent of my knowledge.

That being said, I am always learning and am willing to poke around for a solution.

igold

 

[Jaredhott]

Below are two examples. As you can see the first person did not put his high school coaches name so the field did not appear in the email that was sent to me. Only the fields filled out by the individual appear to me in the email.

First_Name: James
Last_Name: Name
Address: 1110 Main Street
City: North
State: AL
Zip: 11111
Email: myemail
Phone: 402-800-0618
Cell_Phone: 402-111-1111
Height: 5'9"
Weight: 185
Date_of_Birth: 1-19-1990
Married: no
Father_Name: Richard
Father_Email: myemail
Father_Cell_Phone: 111-111-1111
Father_Occupation: Radio
Mother_Name: S
Mothers_Email: myemail
Mothers_Cell_Phone: 111-111-1111
Mother_Occupation: Teacher
Would_like_to_visit: Yes
High_School: Myschool
School_Phone: 411-111-1111
Graduation_Date: 2012
High_School_GPA: 1.0

First_Name: John Last_Name: Name Address: 513 Main City: east State: SD Zip: 11111 Email: myemail Cell_Phone: 111-111-1111 Height: 6'1 1/2 Weight: 210 Date_of_Birth: 3/4/91 Married: no Father_Name: Gary Father_Email: myemail Father_Cell_Phone: 111-111-1111 High_School: Baltic School_Phone: 111-1111 Graduation_Date: 2017 High_School_GPA: 3.04 Class_Rank: 16 College_Interest: Computer Networking Football_Info: Football_High_School_Coach: Jim Name Offensive_Position: fullback/tight-end Defensive_Position: DT/MLB Enrollment_Year: 2018 bench: 225 squat: 350 vertical: 22 inch forty_yard_Dash_Time: Not sure Other_Colleges_Interested: NDL twitter_handle: Don't have twitter Link_to_facebook_account: Don't have facebook

<tbody>
</tbody>

 

[igold]

I am not sure but perhaps, if possible, the easiest fix may be if you could alter the way the emails come back to you so that all fields are returned whether they have been populated or not...

 

[igold]

Errant post