import file programatically

[Newbie]

I have a text file that is not in a state to just import i.e. the data is
not in columns - the job no is centred on the document and there are
underlines that I don't want importing
eg

Postion 50 Line 1: Job No: 1234567
Postion 1 Line 2: Analysis Code (len 5)
Position 35 Line 2: Cost (99999999.99)
Position 63 Line 2: Sales (99999999.99)

The lines to be imported always start with a 0 or a 7 except for the Job No.

How can I programmatically import this file so that it gives me 4 columns
Col 1 = Job No
Col 2 = Analysis Code
Col 3 = Cost or Sales


The job No needs to be repeated in col 1 on each line until the next job no
is found.

Thanks

The file looks like this
Job No: 1234567
00123 99.99
00356 100.99
70000 1000.00

I would like the import to look like this:
1234567 00123 99.99
1234567 00356 100.99
1234567 70000 1000.00

 

[Guest]

Hi,

well, what you can do is read it in directly into one column (using Text
Import - data Menu) then split it up from there using Filter and Find with
pattern matching...

we built a re-usable tool thet reads a text file of un-normalised
(unstructured) data (of 450,000 lines!) into a buffer, then uses regular
expressions and xml to normalise it before transforming it into csv and using
ADO/ODBC to import it - but that may be a little too much work for what you
need.

To read how to treat text file data as database tables, read this article on
the MSDN about Schema Files ("Much ADO about Text Files" article):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting03092004.asp

I hope that gives you a start

Philip

 

[Newbie]

Thanks.
I have managed to do it using the following code:

Sub ImportDat()
Dim strLine As String
Dim i As Long
Dim r As Long
Dim strJobNo As String
Dim ValidLine As Boolean
Dim lJob As Long

ActiveSheet.Select
i = 1
r = 1
Close
Open "C:\Jobs.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, strLine

If Mid(strLine, 46, 3) = "Job" Then
strJobNo = Mid(strLine, 50, 8)
GoTo lEnd
ElseIf Left(strLine, 1) = "0" Then
lJob = 2
ValidLine = True
ElseIf Left(strLine, 1) = "7" Then
lJob = 3
ValidLine = True
Else
lJob = 0
ValidLine = False

GoTo lEnd
End If

If ValidLine = True Then
If lJob = 2 Then
ActiveSheet.Cells(r, 1).Value = strJobNo
ActiveSheet.Cells(r, 2).Value = "'" & Left(strLine, 5)
ActiveSheet.Cells(r, 3).Value = Mid(strLine, 45, 16)
ElseIf lJob = 3 Then
ActiveSheet.Cells(r, 1).Value = strJobNo
ActiveSheet.Cells(r, 2).Value = "'" & Left(strLine, 5)
ActiveSheet.Cells(r, 3).Value = Mid(strLine, 106, 15)
End If
End If
r = r + 1
lEnd:
i = i + 1
Loop
Close #1
End Sub