Read data into Excel from text file?

Topic

New Reply

Hi,

I need to read a text file into Excel sheet starting from Client Name and ending at 50:Instalments to have each values import into cell.

I have attached the sample text file and excel sample file.

In case if some has any better way or idea please tell me.

Hope some one will help me.

Thanks.

Reply | Quote

Replies

The TXT file is not in a format that is easy to import into Excel.

Do you want a record per page?
Do you want a page listing all people, then another page for their data?
Any other format?

cheers, Paul

p.s. I hope that’s not real data in that sample!

Reply | Quote

Thanks Paul T for the reply.

The TXT file is not in a format that is easy to import into Excel.
That’s true Paul but only available to me is this and i need to do it.

Do you want a record per page? Well if that is possible then manually data cleaning will be easy for me.

Do you want a page listing all people, then another page for their data?

Any other format?
Paul actually i am not in a position to say anything, infact if you have any way please help me.

cheers, Paul

p.s. I hope that’s not real data in that sample! Yes, but the format is intact.

One more thing i would like to mention that those xxxxx in data is not required to be appended in sheet.

Hope to have a favourable reply.

Reply | Quote

To import is the easy part
Sub ImportSampleTXT()
With ActiveSheet.QueryTables.Add(Connection:=”TEXT;C:yourfolderSAMPLE.txt”, _
Destination:=Range(“a1”))
.RefreshStyle = xlOverwriteCells
.Refresh BackgroundQuery:=False
End With

End Sub

Reply | Quote

Hi Don

Thanks for the alternative import method.
Very nice.

zeddy

Reply | Quote

If I had a file like that then I would use VBA to read it in. That’s obviously not a simple solution, but your file (as Paul says) is complicated.

Eliminate spare time: start programming PowerShell

Reply | Quote

If I had a file like that I would go back to the source that produced the txt file and see whether it had an option to ‘export’ it into a different format (like a spreadsheet)

zeddy

Reply | Quote

Why didn’t I think of that, zeddy?

Eliminate spare time: start programming PowerShell

Reply | Quote

Record per page would mean either manual copy / paste or code as access-mdb suggested.
How did you get the data into the spreadsheet in the first place?

My point about the data was you should never post personal data on the internet. If that was my data you posted I would be very unhappy.

cheers, Paul

Reply | Quote

fjohan,

Although labor intensive because of inconsistent delimiters and columns, it can still be done. In the textbox, enter the complete path of the text file as shown then click the Run Code button. Assuming that the structure of the text file does not change, the text from the text file will be imported into the spreadsheet then assigned to cells as indicated. I have completed the top half of data to see if this is what you are looking for.

39653-fjohan1

HTH,
Maud

P.S. Just noticed your note about the xxxxx

Reply | Quote

YIKES…you are TOO much, Maudibe!!!

Reply | Quote

Hi Maud

A stunning piece of work that deserves a proper thank you.
…I formatted the date cells [M2], [AM2], and [BC2], a very minor change.

zeddy

Reply | Quote

Hi fjohan

It would be great if you could post another example .txt file, just to make sure the format is consistent.
I want to test another solution, but would like another example.

zeddy

Reply | Quote

Zeddy,

Coming from you, that is a great compliment.

Thx

Reply | Quote

Hi Maud,

MARVELLOUS Maud,

It’s perfect and appends the data to cells, only the date ‘Sanction Date’ & ‘Closure Date’ gets converted, will have to maintain to date format.

First record is appended properly and how to append the next record of text file to sheet, the text file contains more records.

Many Thanks.

Reply | Quote

Hi fjohan

The ‘second’ record in your original uploaded file is not exactly the same format as the first record section (the second one has a different number of rows).
Does the number of rows vary for each Client????

zeddy

Reply | Quote

And will there always be only 2 clients per text file?

Zeddy, what do you have cookin’?

Reply | Quote

Hi Maud

..I’m trying to do it with under 30 lines of code.

zeddy

Reply | Quote

Considering all the inconsistencies in the text file, that would be a real feat of magic!

Reply | Quote

Hi,

Zeddy

Does the number of rows vary for each Client???? Yes.

Maudibe

And will there always be only 2 clients per text file?
On scrolling the text file i found 2 to 3 records exists.

How do we overcome this difficultly.

Thanks

Reply | Quote

Hi

The process I am working on will cope with any number of clients per text file.
But I would like another .txt file example (with more than 2 clients) to test with my system before I post my file.

zeddy

Reply | Quote

I am thinking that a good way to approach it would be to employ the Find or Search method using the column headers as the search criteria. If more than one instance (multiple clients) of the search criteria is present, then assign it to an array variable. I was able to find the end of the first client by looking for “Client Name….:”

Reply | Quote

Hi Maud

I’m using AutoFilter with “50:Instalments*” to count how many clients there are in the .txt file.
I’m allowing for up to 5 Guarantor addresses.
I’m allowing for up to 60 months for payments.

I’m nearly there with my processing.
16 lines of code so far.

zeddy

Reply | Quote

Zeddy,

I found something like this to work well:

Reply | Quote

Zeddy,

I found that something like this works well by using the column headers as keywords to find the values in the cells to the right of them:

Code:

Public Sub Find_Data()
With Worksheets(“Main”)
[COLOR=”#008000″]’————————————————
‘USE COLUMN HEADERS AS SEARCH CRITERIA[/COLOR]
For I = 1 To 56
    field = FindKeyword(.Cells(1, I)) [COLOR=”#008000″]’FIND SEARCH CRITERIA ON SHEET[/COLOR]
[COLOR=”#008000″]’————————————————
‘PLACE RESULT ON NEXT LINE UNDER COLUMN HEADER[/COLOR]
    If Not field = “” Then .Cells(2, I) = Range(field)
Next I
End With
End Sub


Public Function FindKeyword(word As Variant) As String
On Error Resume Next
With Worksheets(“sheet1″)
[COLOR=”#008000”]’————————————————
‘FIND KEYWORD ON SHEET[/COLOR]
Set Keyword = .Cells.Columns(“A:F”).Find(What:=word)
If Not Keyword Is Nothing Then
    Application.Goto Keyword, True
[COLOR=”#008000″]’————————————————
‘RETURN CELL CONTENTS OFFSET TO RIGHT OF KEYWORD[/COLOR]
    FindKeyword = ActiveCell.Offset(0, 1).Address
End If
End With
End Function

But inconsistencies like the 3 addresses underneath “Address” or the vertical list of “Due on” dates breaks the coding pattern

Reply | Quote

Maud,

“A foolish consistency is the hobgoblin of little minds.”
― Ralph Waldo Emerson, Self-Reliance

“Any inconsistency is the hobgoblin of programmers minds.”
— Retired Geek, WSL

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

How true, how true!

Reply | Quote

Hi

In the absence of another sample file from the OP, I’m preparing my own sample text file.
Will post it here when I’ve tested it

zeddy

Reply | Quote

I can’t help you, but I know who can.
If you come to a dead end you should go to ultraedit.com.
That will take you to the IDM Computer Solutions website.
I have bought their software on and off for the past decade; the only reason I don’t on a regular basis is I no longer look for the types of solutions that you now need.
Their software is not expensive; such as UltraEdit, UltraCompare, UltraStudio, etc; but the really good part is that if you give them a problem, they will come back with a solution, and apart from maybe buying some software, the solution will be free!

Reply | Quote

How many lines are you up to?

Reply | Quote

26 lines including the Sub and End Sub lines; 24 lines if you don’t count Sub and End Sub

..it’s gonna be tight.

zeddy

Reply | Quote

Hi Maud

Still no response from the OP, so I made my own test file.
The most tedious part is always creating sample text datafiles to test on.
Much better to have them from the person seeking help!

I have attached my workbook, along with 2 sample files.
I managed to keep the code to 30 lines of actual code.

Copy these files to a folder of your choice.
Then load the attached .xlsm file (with macros enabled).
Click the [fetch data] button on the [Data] sheet to select the source text file to process.
If you then select another source text file, the Client records will be appended below.

In my sample file, I changed a few numbers, had 1 to 4 Guarantors, and different numbers of payments.
(Check out the names in column [BJ] )

I deliberately included a separator headings row between Client records.

Hey, its not perfect yet, but it shows another way of doing it.

zeddy

Reply | Quote

A man of your word, “30 lines or less”. Very nice job Zeddy

Reply | Quote

Hi Maud

Thanks for the compliment!
Re post#17

that would be a real feat of magic!

I enjoyed that challenge!

zeddy

Reply | Quote