Importing a txt file with common fields and multiple entries

[dougfunny]

Hello -

I have a text file that has multiple entries without delimiters but with multiple common fields. I would like to create a spreadsheet (Excel 2010 or 13) from this text file that lists the fields as column headers and a new row for each entry.

For example, picture the two entries on a txt file...

Business: Jims Plumbing
VendorID: 123
Zip Code: 54321

Business: Bob's Hardware
VendorID: 3

I would like to use VBA to directly pull the macro in excel and the result look like this...

I appreciate any guidance. Thank you.

 

[oldbrewer]

is the text file totally without paragraphs etc

eg Business: Jims Plumbing VendorID: 123 Zip Code: 54321 Business: Bob's Hardware VendorID: 3

if so you can break it up by determining where the word Business occurs

 

[dougfunny]

Thank you for the response. It is not quite bad as your example, but the text file's display is basically as I described above but with two columns. See example display below. I think the way that you describe is the way to go. Is there a way to do this using VBA?

 

[dougfunny]

Thought I would bring this question up to see if anyone is able to help.

Thanks.

 

[oldbrewer]

in your post 3 each business appears to be in separate paragraphs and appears to have line feeds or carriage returns - is this correct ?

 

[dougfunny]

There is paragraph-like separation between each vendor entry in the text file that is automatically generated from the program that creates the text file.

I apologize, but I do not know what line feeds or carriage returns are.

Thanks again.

 

[oldbrewer]

[TABLE="width: 672"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]business:bus1vendorid123zipcode:12345[/TD]
[TD]business:bus6vendorid128zipcode:12350[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]business:bus2vendorid124zipcode:12346[/TD]
[TD]business:bus7vendorid129zipcode:12351[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]business:bus3vendorid125zipcode:12347[/TD]
[TD]business:bus8vendorid130zipcode:12352[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]business:bus4vendorid126zipcode:12348[/TD]
[TD]business:bus9vendorid131zipcode:12353[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]business:bus5vendorid127zipcode:12349[/TD]
[TD]business:bus10vendorid132zipcode:12354[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]let us assume that the data is as above[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]what is required is[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]business[/TD]
[TD]vendorid[/TD]
[TD]zipcode[/TD]
[/TR]
[TR]
[TD]bus1[/TD]
[TD]123[/TD]
[TD]12345[/TD]
[/TR]
[TR]
[TD]bus6[/TD]
[TD]128[/TD]
[TD]12350[/TD]
[/TR]
[TR]
[TD]bus2[/TD]
[TD]124[/TD]
[TD]12346[/TD]
[/TR]
[TR]
[TD]bus7[/TD]
[TD]129[/TD]
[TD]12351[/TD]
[/TR]
[TR]
[TD]bus3[/TD]
[TD]125[/TD]
[TD]12347[/TD]
[/TR]
[TR]
[TD]bus8[/TD]
[TD]130[/TD]
[TD]12352[/TD]
[/TR]
[TR]
[TD]bus4[/TD]
[TD]126[/TD]
[TD]12348[/TD]
[/TR]
[TR]
[TD]bus9[/TD]
[TD]131[/TD]
[TD]12353[/TD]
[/TR]
[TR]
[TD]bus5[/TD]
[TD]127[/TD]
[TD]12349[/TD]
[/TR]
[TR]
[TD]bus10[/TD]
[TD]132[/TD]
[TD]12354[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]this was achieved with this macro[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]rr = 13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] For j = 1 To 5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] For k = 1 To 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] bus1 = InStr(Cells(j, k), "business")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] bus2 = 9 + InStr(Cells(j, k), "business")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] ven1 = InStr(Cells(j, k), "vendorid")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] ven2 = 8 + InStr(Cells(j, k), "vendorid")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] zip1 = InStr(Cells(j, k), "zipcode")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] zip2 = 8 + InStr(Cells(j, k), "zipcode")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Cells(rr, 1) = Mid(Cells(j, k), bus2, (ven1 - bus2))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Cells(rr, 2) = Mid(Cells(j, k), ven2, (zip1 - ven2))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Cells(rr, 3) = Mid(Cells(j, k), zip2, 99)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] rr = rr + 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Next k[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Next j[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]100 End Sub[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]