import txt file

[Guest]

Hi everyone,
I need to import a txt file as follow:
0120311011320
02BDC7832033600005010234560001000.000200000100.000001100.00CSH0001100.00
0000000.00 0000000.0
02SPC2319033600107020234560001500.000300000150.000001650.00CSH0000100.00NET0001500.00CCD0000050.00
032003110101020000200002750.00

there are 4 lines in total
01 - is the header id
02 - is the content id (i.e. there are 2 different data)
03 - is the trailer id

how do i import this txt file into EXCEL file. Is there any easier way to do
it? all i need is to import them and get the value behind CSH, NET, CCD and
calculate the total by CSH, NET, CCD. for example, behind the CSH is
0001100.00 i.e. $1100.00 ...and then i need to calculate the total i.e. CSH
$1200.00 NET $1500.00 CCD $50.00

Anyone can help?

thanks for your reading!

 

[Dave Peterson]

I'd import it all into column A and split it apart later.

When you do File|open, just select your file, but shiftclick on the open
button. (It avoids the text to columns wizard).

Then I'd insert a new row 1.
Then I'd insert new columns A:C.

In A1, put: CSH
B1, put: NET
C1, put: CCD
(Your input data will start in D2.)

Then in A2, put:
=IF(ISERROR(SEARCH(A$1,$D2)),"",--MID($D2,SEARCH(A$1,$D2)+3,10))

And drag to the right (cells B2:C2).
Format the cells to show 2 decimals (if you want)
and drag down.

 

[Guest]

Hi Dave,
Thanks for you reply.
Infact I need to extract this file every day and store it into one work
sheet to have the grand total for a week. Since the format is fixed and the
amount for CSH starting from 63, NET starting from 76 and CCD starting from
89 (if the line ID is 02 (content) and the grand total starts from 25(if the
line ID is 03)

Is there any method e.g. query or search function so I can put them into the
cell itself and then just simply import or run the query, choose the relevant
file and then extract the value automatically??

Thank you for your reading?

By the way there is an error coming out when i enter the formula as "invalid
type" i believe is the diferent type between the MID and the serach function.

xxcha75j

 

[Dave Peterson]

You could open the file as plain old text and read each line in and extract
values that way.

I'm not sure if you have multiple detail lines for each total, but this should
give you some idea:

Option Explicit
Sub testme01()

Dim myNumber As Long
Dim myLine As String
Dim myFileName As String
Dim FileNum As Long
Dim oRow As Long
Dim wks As Worksheet

Set wks = Workbooks.Add(1).Worksheets(1)

myFileName = "C:\my documents\excel\text.txt"
FileNum = FreeFile

wks.Range("a1").Resize(1, 4).Value _
= Array("CSH", "NET", "CCD", "Total")

oRow = 1
If Dir(myFileName) = "" Then 'not found
MsgBox "File is missing!"
Exit Sub
Else
Close FileNum
Open myFileName For Input As FileNum
Do While Not EOF(FileNum)
Line Input #FileNum, myLine
If Left(myLine, 2) = "02" Then
oRow = oRow + 1
wks.Cells(oRow, "A").Value = Mid(myLine, 63, 10)
wks.Cells(oRow, "B").Value = Mid(myLine, 76, 10)
wks.Cells(oRow, "C").Value = Mid(myLine, 89, 10)
ElseIf Left(myLine, 2) = "03" Then
oRow = oRow + 1
wks.Cells(oRow, "D").Value = Mid(myLine, 25, 10)
End If
Loop
Close FileNum
End If

End Sub

Hi Dave,
Thanks for you reply.
Infact I need to extract this file every day and store it into one work
sheet to have the grand total for a week. Since the format is fixed and the
amount for CSH starting from 63, NET starting from 76 and CCD starting from
89 (if the line ID is 02 (content) and the grand total starts from 25(if the
line ID is 03)

Is there any method e.g. query or search function so I can put them into the
cell itself and then just simply import or run the query, choose the relevant
file and then extract the value automatically??

Thank you for your reading?

By the way there is an error coming out when i enter the formula as "invalid
type" i believe is the diferent type between the MID and the serach function.

xxcha75j