How to pull data from TXT file.

[intradayteam]

okay, I know this can be done, but i dont know how.

I have a huge text file containing data entries like the following.

Executive: Name 1
Mobile: 9824209211
Executive: Name 2
Mobile: 9888888888
Company 1 Name
Member No: S-133/I/L
Category: Food work, Cattle work
Year of Established: 1983
Address: Some address here line 1
Some address here line 2
Some address here line 3
Some address here line 4
Phone: 22222222
Fax: 33333333
Email: some1@email.com
Product: Some product 1, Some other product 2.


Executive: Name 3
Mobile: 999999999
Designation: Manager
Executive: Name 4
Mobile: 9777777777
Designation: Director
Company 2 Name
Member No: S-134/I/L
Category: other work
Year of Established: 1985
Address: Some address here line 1
Some address here line 2
Some address here line 3
Some address here line 4
Phone: 44444444
Fax: 55555555
Email: some2@email.com
Website: www.website.com
Product: Some product, Some other product.

1. Above i have differentiated the details of 2 companies by paragraph (enter on keyboard), but original data has no paragraph.
2. As you can see, both the company details have some common things (Executive, Mobile). But second company details have some extra fields like (Designation, Website)
3. Addresses are in various range, some has 2-3 lines and some has 4-5 lines.

What i am willing to do in excel !

[TABLE="******* 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Company Name[/TD]
[TD]Executive[/TD]
[TD]Designation[/TD]
[TD]Mobile[/TD]
[TD]Executive[/TD]
[TD]Designation[/TD]
[TD]Mobile[/TD]
[TD]Member No[/TD]
[TD]Category[/TD]
[TD]Year of Established[/TD]
[TD]Address[/TD]
[TD]Phone[/TD]
[TD]Fax[/TD]
[TD]Email[/TD]
[TD]Website[/TD]
[TD]Product[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Company 1 Name[/TD]
[TD]Name 1[/TD]
[TD][/TD]
[TD]9824209211[/TD]
[TD]Name 2[/TD]
[TD][/TD]
[TD]9888888888[/TD]
[TD]S-133/I/L[/TD]
[TD]Food work, Cattle work[/TD]
[TD]1983[/TD]
[TD]Some address here line 1
Some address here line 2
Some address here line 3
Some address here line 4
[/TD]
[TD]22222222[/TD]
[TD]33333333[/TD]
[TD]some1@email.com[/TD]
[TD][/TD]
[TD]Some product 1, Some other product 2.[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Company 2 Name[/TD]
[TD]Name 3[/TD]
[TD]Manager[/TD]
[TD]999999999[/TD]
[TD]Name 4[/TD]
[TD]Director[/TD]
[TD]9777777777[/TD]
[TD]S-134/I/L[/TD]
[TD]other work[/TD]
[TD]1985[/TD]
[TD]Some address here line 1
Some address here line 2
Some address here line 3
Some address here line 4
[/TD]
[TD]44444444[/TD]
[TD]55555555[/TD]
[TD]some2@email.com[/TD]
[TD]www.website.com[/TD]
[TD]Some product, Some other product.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


and so on.

I dont know how to pull data in excel from text file, and arrange it like this. I know this is possible.

Any help would be great.

I can even send/upload the ORIGINAL text file, which is less than 1MB.

 

[eric00132]

read info https://support.office.com/en-us/ar...sv-files-5250ac4c-663c-47ce-937b-339e391393ba

 

[igold]

Hi intradayteam,


See what this does for you.

Before you run it on your Final.txt, please note that there a break in the data at company "The Indian Hume Pipe Co Ltd ~", which will have to be removed or the code will fail at the break.

if you use CTRL+F and search for the company you will see it after the address line.

Let me know your thoughts...

Sub TextFile()


    Dim row_number As Long, Lrow As Long, row_numberNC As Long
    Dim LineFromFile As Variant
    Dim col_number As Long
    Dim FilePath As String
    Dim ct As Integer
    Dim LastCell
    
    Application.ScreenUpdating = False
    On Error Resume Next
    FilePath = "G:\Files\Excel VBA\Final.txt"
    Close #1
    Open FilePath For Input As #1
    row_number = 0
    col_number = 0
    Range("a1").Select
    ct = 1
    Lrow = 0
    Do Until EOF(1)
        Line Input #1, LineFromFile
        Dim LineItems As Variant: LineItems = Split(LineFromFile, ":")
            If Right(LineItems(0), 1) = "~" Then
                Range("A" & Lrow + 2).Select
                ActiveCell.Offset(row_number, 0).Value = "Company"
                ActiveCell.Offset(row_number + 1).Value = LineItems(0)
                col_number = 0
                ct = 1
                GoTo NewRec
                GoTo NoColon
            End If
            If LineFromFile = "" Then GoTo Return1
            'If LineItems(0) = "Rawmaterial" Then GoTo Return1
            
            If col_number = 8 And LineItems(0) = "Website" Then
                col_number = 24
                ActiveCell.Offset(0, col_number).Value = LineItems(0)
                col_number = 8
                GoTo Return1
            End If
            If Left(LineItems(0), 3) = "www" Then
                col_number = 24
                ActiveCell.Offset(row_number + 1, col_number).Value = LineItems(0)
                col_number = 8
                GoTo Return1
            End If
            
            If UBound(LineItems) = 0 And Right(LineItems(0), 1) <> "~" Then
                ct = ct + 1
                col_number = col_number - 1
                row_numberNC = row_number + ct
                ActiveCell.Offset(row_numberNC, col_number).Value = LineItems(0)
                GoTo NoColon
            End If
    ActiveCell.Offset(row_number, col_number).Value = LineItems(0)
    ActiveCell.Offset(row_number + 1, col_number).Value = LineItems(1)
NoColon:
NewRec:
    col_number = col_number + 1
    Lrow = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row, _
            Cells.Find(What:="*", SearchOrder:=xlByColumns, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column).Row
    GoTo Return1
    'row_number = 0
    'col_number = 0
    'ct = 1
    'Range("A" & Lrow + 2).Select
    'End If
Return1:


    Loop
    GoTo cleanup
Web:
    
    ActiveCell.Offset(0, 13).Value = "Website"
    GoTo Return1
    
cleanup:
    Close #1
    Rows("1:1").Delete
    Application.ScreenUpdating = True
End Sub

I hope this helps!

igold

 

[igold]

I forgot to mention that you will have to change the filepath at the top of the code to suit your needs....

 

[intradayteam]

@igold, Thanks for all the precious time you spend for this thing, but unfortunately the results are not as per requirement. Heading (Company Name, Member No. etc) repeats itself for each set of data, which is not required. Column counts are not fixed. And the biggest issue is, Addresses must fit in ONE CELL. Pls check the example given below.

@István Hirsch, Let me explain it in simple words.

Say, i have a data in plain text file, like in the format below. There is no seperator between two data sets. "~" sign is always there after company name.

M A Enterprises ~
Member No: M-551/IV/A
Category: Food and vegitables
Year of Established: 1984
Address: Address line 1
Address Line 2
Address Line 3
Address Line 4
Address Line 5
Phone: 11111111, 22222222
Fax: 33333333
Email: somemail1@gmail.com
Website:www.somewebsite1.com
Executive1: Mr. Andrew
Designation: Owner
Mobile: 9999999999
Executive2: Rahul Bhai
Designation: Director
Mobile: 3333333333
Product: food product processing
Rawmaterial: Ss Hot Rolled
A B Enterprises ~
Member No: M-552/IV/A
Category: Food and vegitables
Address: Address line 1
Address Line 2
Address Line 3
Address Line 4
Address Line 5
Phone: 44444444, 55555555
Executive1: Mr. John
Mobile: 1010101010
Executive2: Sam Benjamin
Designation: Director
Mobile: 1212121212
Product: food product processing
Rawmaterial: bla bla 1
C D Enterprises ~
Member No: M-553/IV/A
Category: Food and vegitables
Year of Established: 1986
Address: Address line 1
Address Line 2
Address Line 3
Address Line 4
Address Line 5
Phone: 66666666, 77777777
Fax: 88888888
Email: somemail2@gmail.com
Website:www.somewebsite2.com
Executive1: Mr. Joseph
Designation: President
Mobile: 5105105101
Executive2: John Smith
Designation: Manager
Mobile: 4564564565
Product: food product processing
Rawmaterial: bla bla 2

What am i willing to achieve will look like below.

[TABLE="class: grid, ******* 500"]
<tbody>[TR]
[TD][TABLE="******* 64"]
<tbody>[TR]
[TD="class: xl65, ******* 64"]Company Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="******* 64"]
<tbody>[TR]
[TD="class: xl65, ******* 64"]Member No[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="******* 64"]
<tbody>[TR]
[TD="class: xl65, ******* 64"]Category[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="******* 64"]
<tbody>[TR]
[TD="class: xl65, ******* 64"]Year of Established[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="******* 64"]
<tbody>[TR]
[TD="class: xl65, ******* 64"]Address[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="******* 64"]
<tbody>[TR]
[TD="class: xl65, ******* 64"]Phone[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Fax[/TD]
[TD]Email[/TD]
[TD]Website[/TD]
[TD]Executive1[/TD]
[TD]Designation[/TD]
[TD]Mobile[/TD]
[TD]Executive2[/TD]
[TD]Designation[/TD]
[TD]Mobile[/TD]
[TD]Product[/TD]
[TD]Rawmaterial[/TD]
[/TR]
[TR]
[TD]M A Enterprises ~[/TD]
[TD]M-551/IV/A[/TD]
[TD]Food and vegitables[/TD]
[TD]1984[/TD]
[TD]Address line 1, Address line 2, Address line 3, Address line 4, Address line 5[/TD]
[TD]11111111, 22222222[/TD]
[TD]33333333[/TD]
[TD]somemail1@gmail.com[/TD]
[TD]www.somewebsite1.com[/TD]
[TD]Mr. Andrew[/TD]
[TD]Owner[/TD]
[TD]9999999999[/TD]
[TD]Rahul Bhai[/TD]
[TD]Director[/TD]
[TD]3333333333[/TD]
[TD]food product processing[/TD]
[TD]Ss Hot Rolled[/TD]
[/TR]
[TR]
[TD]A B Enterprises ~[/TD]
[TD]M-552/IV/A[/TD]
[TD]Food and vegitables[/TD]
[TD][/TD]
[TD]Address line 1, Address line 2, Address line 3, Address line 4, Address line 5[/TD]
[TD]44444444, 55555555[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr. John[/TD]
[TD][/TD]
[TD]1010101010[/TD]
[TD]Sam Benjamin[/TD]
[TD]Director[/TD]
[TD]1212121212[/TD]
[TD]food product processing[/TD]
[TD]bla bla 1[/TD]
[/TR]
[TR]
[TD]C D Enterprises ~[/TD]
[TD]M-553/IV/A[/TD]
[TD]Food and vegitables[/TD]
[TD]1986[/TD]
[TD]Address line 1, Address line 2, Address line 3, Address line 4, Address line 5[/TD]
[TD]66666666, 77777777[/TD]
[TD]88888888[/TD]
[TD]somemail2@gmail.com[/TD]
[TD]www.somewebsite2.com[/TD]
[TD]Mr. Joseph[/TD]
[TD]President[/TD]
[TD]5105105101[/TD]
[TD]John Smith[/TD]
[TD]Manager[/TD]
[TD]4564564565[/TD]
[TD]food product processing[/TD]
[TD]bla bla 2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


When there is no data for a particular field in a set of data, then it should remain blank.

Hope this clears the doubt.

Waiting for the solution.

Regards

 

[igold]

Hi intradayteam,

Unfortunately your data does not lend itself well to what your requirements are. There is going to have to be a tremendous amount of manipulation of data to arrive at your desired results.

Keeping only one header is doable. The real problem is that your data is not consistent from record to record. The fact that the five address lines do not have a field in front of each line adds another twist of getting all five lines into one cell. Also unlike what you show above the website address is not on the same line as the field "Website:" which further complicates things. Even your company name is not represented by the proper field with a colon to parse by.

I have some ideas but they will have to wait until tomorrow. However you may have to be flexible in your requirements.

Regards,

igold

 

[intradayteam]

@igold, I think i found the solution. Someone suggested the below code for my text file. Try this with the TEXT file i sent you in PM.

Sub sTexttoExcel()


'Input File Path
filePath = "C:\CustomerData.txt"


Dim fso As FileSystemObject
Set fso = New FileSystemObject
Set txtStream = fso.OpenTextFile(filePath, ForReading, False)


'Initialise Row and Column count
RowCount = 0
coloumncount = 0


'Loop through contents of text file
Do While Not txtStream.AtEndOfStream
    cellcontent = txtStream.ReadLine


    'Check if it is a company name
    If InStr(1, cellcontent, "~", vbTextCompare) = 0 Then
        coloumncount = coloumncount + 1
        Cells(RowCount, coloumncount) = cellcontent
    Else
        RowCount = RowCount + 1
        coloumncount = 1
        Cells(RowCount, coloumncount) = cellcontent
    End If


Loop
txtStream.Close


End Sub

At first hand it was giving error, then i came to know, Within Excel you need to set a reference to the VB script run-time library. The relevant file is usually located at \Windows\System32\scrrun.dll
Here are steps to avoid error,
-To reference this file, load the Visual Basic Editor (ALT+F11)
-Select Tools > References from the drop-down menu
-A listbox of available references will be displayed
-Tick the check-box next to 'Microsoft Scripting Runtime'
-The full name and path of the scrrun.dll file will be displayed below the listbox
-Click on the OK button.

Now, this code doesnt fulfill the exact purpose, but it gives me each set of data in single row. Once the data is in excel, i can use REPLACE function to add delimetres before/after field names to get data in coloumns, then i can use INDEX, MATCH or IF function to populate/filter each column. Currently i am running out of time, will try this at evening. Now it looks like, i can get desired results.

Million Thanks to @igold, and other members.
Regards

 

[igold]

Hi intradayteam,

First, thanks for the feedback. I appreciate it.

I ran this code and it does (as you said) lay your data out into a single line. I am not sure how easy it is going to be to manipulate from this layout to your requirements, but if you can do it, that is great.

At this point I am not going to pursue the thoughts I had to re-write my code. If however you find that you are still unable to get where you need to be, let me know, and I will be happy to jump back in and help.

Regards,

igold

 

[intradayteam]

Thanks for all the help.

Good news, Target Achieved :D

First, i pulled text data in Excel by suggested code (the one i got from somewhere else and posted few posts above). This way i got each entry in single row. Then I copied this again to a text file and made some replacements, like "~" sign before and after "Field Names". Then again i pulled this data in excel with "~" as a delimeter. Finally applied IF function and got desired results.

Once again thanks everybody.