By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,190 Members | 764 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,190 IT Pros & Developers. It's quick & easy.

Append 2nd, 4th, 6th line of a text file to the 1st, 3rd, 5th line as a table

P: n/a
Hi,

Let me explain. I have been given a file that I need to bring into an
Access table.

Here is a snippet of the file.

100833983
1 MRS M I BATTY
1 000001 00833983
1 MR G S G SINGH SABHA
1 000002 01280570
1 MR M ANWAR
1 000003 01360043
1 MR C PAPARESTI
and so on.

At the end of each line is a hard return, therefore if I import this
into Access each row in the text file becomes a record.

All I need is the payee and the claim no in a format as shown below:

MRS M I BATTY 00833983
MR G S G SINGH SABHA 01280570
MR M ANWAR 01360043
MR C PAPARESTI 01664018

How can I get the second line added to the end of the first, and the
fourth added to the end of the third and so on.

Any help would be massively appreciated.

Cheers.

Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
ca**********@newcastle.gov.uk wrote:
Hi,

Let me explain. I have been given a file that I need to bring into an
Access table.

Here is a snippet of the file.

100833983
1 MRS M I BATTY
1 000001 00833983
1 MR G S G SINGH SABHA
1 000002 01280570
1 MR M ANWAR
1 000003 01360043
1 MR C PAPARESTI
and so on.

At the end of each line is a hard return, therefore if I import this
into Access each row in the text file becomes a record.

All I need is the payee and the claim no in a format as shown below:

MRS M I BATTY 00833983
MR G S G SINGH SABHA 01280570
MR M ANWAR 01360043
MR C PAPARESTI 01664018

How can I get the second line added to the end of the first, and the
fourth added to the end of the third and so on.

Any help would be massively appreciated.

Cheers.

Instead of using an import specification I'd use low-level commands to
read the file and process. Let's say the name is the first 30 chars and
the code field starts at the 31st position

Dim strFile As String
Dim strLine1 As String
Dim strLine2 As String
Dim strName As String
Dim strCode As String

strFile = "C:\Test\Test.Txt"
Open strFile For Input As #1

Do While Not EOF(1) ' Loop until end of file.
Line Input #1, strLine1
Line Input #1, strLine2

'process each line here
strName = Left(strLine1,30)
strCode = Mid(strLine2,31)

'do whatever, add recs, process recs...
Loop
Close #1
Nov 13 '05 #2

P: n/a
Thanks for the hint.

I have done it using the following code and it works fine, but I need
help on one more thing, and that is to ignore the first line of the
text file as it contains data that screws the process up as I don't
need it. Here is my program that works.

Private Sub Command2_Click()

Dim dbs As Database
Dim rst As Recordset
Dim strFile As String
Dim strLine1 As String
Dim strLine2 As String
Dim strRawPayee As String, strCleanPayee As String, strRawClaimRefNo As
String, strCleanClaimRefNo As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblTest")

strFile = "C:\SX3Stuff\Cheque1.Txt"
Open strFile For Input As #1

Do While Not EOF(1) ' Loop until end of file.
Line Input #1, strLine1
Line Input #1, strLine2

'process each line here
strRawPayee = Left(strLine1, 50)
strCleanPayee = Right(strRawPayee, 46)

strRawClaimRefNo = Right(strLine2, 58)
strCleanClaimRefNo = Trim(strRawClaimRefNo)

If Len(strCleanClaimRefNo) = 8 Then
If rst.BOF Then
rst.AddNew
rst!Payee = strCleanPayee
rst!ClaimRefNo = strCleanClaimRefNo
Else
rst.MoveLast
rst.AddNew
rst!Payee = strCleanPayee
rst!ClaimRefNo = strCleanClaimRefNo
End If
rst.Update
End If
Loop
Close #1

Set dbs = Nothing
Set rst = Nothing
End Sub

As you can see from the example data below that the first line of the
text file is100833983 and will be different for other text files. What
can I put into my program to ignore this line or how can I delete it
from the text file from my code.

Thanks again for your help.

Regards.

100833983
1 MRS M I BATTY
1 000001 00833983
1 MR G S G SINGH SABHA
1 000002 01280570
1 MR M ANWAR
1 000003 01360043
1 MR C PAPARESTI

Nov 13 '05 #3

P: n/a
ca**********@newcastle.gov.uk wrote:
Thanks for the hint.

I have done it using the following code and it works fine, but I need
help on one more thing, and that is to ignore the first line of the
text file as it contains data that screws the process up as I don't
need it. Here is my program that works.

Private Sub Command2_Click() 'I would set a counter or boolean
Dim blnFirstLine As Boolean
Dim dbs As Database
Dim rst As Recordset
Dim strFile As String
Dim strLine1 As String
Dim strLine2 As String
Dim strRawPayee As String, strCleanPayee As String, strRawClaimRefNo As
String, strCleanClaimRefNo As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblTest")

strFile = "C:\SX3Stuff\Cheque1.Txt"
Open strFile For Input As #1

Do While Not EOF(1) ' Loop until end of file. If Not blnFirstLine then
'read the first line and ignore the
'if/endif after that
Line Input #1, strLine1
blnFirstLine = True
Endif

I am not sure if strCleanClaimRefNo is correct in your case. You used
an example for the second line as
1 000002 01280570
If you are getting the last 58 characters, then trimming, I would think
youd get both 000002 and 01280570 and the length would never equal 8.
You might want to get the right 8 characters instead.

Line Input #1, strLine1
Line Input #1, strLine2

'process each line here
strRawPayee = Left(strLine1, 50)
strCleanPayee = Right(strRawPayee, 46)

strRawClaimRefNo = Right(strLine2, 58)
strCleanClaimRefNo = Trim(strRawClaimRefNo)

If Len(strCleanClaimRefNo) = 8 Then
If rst.BOF Then
rst.AddNew
rst!Payee = strCleanPayee
rst!ClaimRefNo = strCleanClaimRefNo
Else
rst.MoveLast
rst.AddNew
rst!Payee = strCleanPayee
rst!ClaimRefNo = strCleanClaimRefNo
End If
rst.Update
End If
Loop
Close #1

Set dbs = Nothing
Set rst = Nothing
End Sub

Nov 13 '05 #4

P: n/a
Hi,

The 0 on the 01280570 is the first character of the last 58 of that
line. The text file has 50 blank spaces after it. When I use this I
get just the 8 characters I want and the 50 blanks and use the trim to
remove the blanks.

I have implemented your suggestion for the blank line and it works a
treat. Thanks very much for your help, I really appreciate it ... it
is going to save me a lot of manual manipulation in Excel and will
allow me to automate this process.

Thanks again.

Cheers.

Nov 13 '05 #5

P: n/a
Sorry to keep posting about this issue, but you could probably help me
again.

Now that I have this program working to produce a table for all the
data in my cheque1.txt file, is there a way I can re-use it to read 3
other text files to import into another 3 tables rather than
duplicating the program 3 more times, i,e Set rst =
dbs.OpenRecordset("tblTest") how can I replace tbltest with other
tablenames and C:\SX3Stuff\Cheque1.Txt" how can I replace cheque1 with
other cheque? files

Cheers again.

Nov 13 '05 #6

P: n/a
ca**********@newcastle.gov.uk wrote:
Sorry to keep posting about this issue, but you could probably help me
again.

Now that I have this program working to produce a table for all the
data in my cheque1.txt file, is there a way I can re-use it to read 3
other text files to import into another 3 tables rather than
duplicating the program 3 more times, i,e Set rst =
dbs.OpenRecordset("tblTest") how can I replace tbltest with other
tablenames and C:\SX3Stuff\Cheque1.Txt" how can I replace cheque1 with
other cheque? files

Cheers again.

You need to pass arguments to the routine. Let's say the subroutine is
called OpenAndExtract. The sub line might look like this

Private Sub OpenAndExtract(strTable As String, strTextFile As String)

You pass to it the table name and text file name. Ex:
Dim strTable As String
Dim strFileName As String

'user selects from an option group
Select Case Me.Frame1
Case 1
strTable = "Table1"
strFileName = "C:\Test\Test1.Txt"
Case 2
strTable = "Table2"
strFileName = "C:\Test\Test2.Txt"
Case 3
strTable = "Table3"
strFileName = "C:\Test\Test3.Txt"
End Select

'now process table/create text file
OpenAndExtract strTable, strFileName
Nov 13 '05 #7

P: n/a
Thanks,

I will need to run each one straight after the other but with the info
you have given me I think I will be able to work out how to do it.
Thanks again for your help.

Cheers.

Nov 13 '05 #8

P: n/a
Hi again,

I am mistaken, I am having difficulty running the program 4 times, each
time using a different text file and table without duplicating the
program four times. Your help would again be appreciated. The program
is the same as above shown in Post 3.

Regards.

Nov 13 '05 #9

P: n/a
ca**********@newcastle.gov.uk wrote:
Hi again,

I am mistaken, I am having difficulty running the program 4 times, each
time using a different text file and table without duplicating the
program four times. Your help would again be appreciated. The program
is the same as above shown in Post 3.

Regards.


Private Sub Command2_Click()

Dim intFor As Integer

Dim strTable As String
Dim strFileName As String

For intFor = 1 to 4
'please change the table and text file names in this For loop
Select Case Me.Frame1
Case 1
strTable = "Table1"
strFileName = "C:\Test\Test1.Txt"
Case 2
strTable = "Table2"
strFileName = "C:\Test\Test2.Txt"
Case 3
strTable = "Table3"
strFileName = "C:\Test\Test3.Txt"
Case 4
strTable = "Table4"
strFileName = "C:\Test\Test4.Txt"
End Select

'now process table/create text file
OpenAndExtract strTable, strFileName
Next intFor

End Sub
Private Sub OpenAndExtract(strTable As String, strTextFile As String)
'I am using the code you supplied earlier. You may have made
'modifications I am unaware of.

Dim blnFirstLine As Boolean

Dim dbs As Database
Dim rst As Recordset
Dim strFile As String
Dim strLine1 As String
Dim strLine2 As String
Dim strRawPayee As String, strCleanPayee As String, _
strRawClaimRefNo As String, strCleanClaimRefNo As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTable,dbOpenDynaset)

Open strTextFile For Input As #1
Do While Not EOF(1) ' Loop until end of file.

If Not blnFirstLine then
'read the first line and ignore the
'if/endif after that
Line Input #1, strLine1
blnFirstLine = True
Endif

Line Input #1, strLine1
Line Input #1, strLine2

'process each line here
strRawPayee = Left(strLine1, 50)
strCleanPayee = Right(strRawPayee, 46)
strRawClaimRefNo = Right(strLine2, 58)
strCleanClaimRefNo = Trim(strRawClaimRefNo)
If Len(strCleanClaimRefNo) = 8 Then
If rst.BOF Then
rst.AddNew
rst!Payee = strCleanPayee
rst!ClaimRefNo = strCleanClaimRefNo
Else
rst.MoveLast
rst.AddNew
rst!Payee = strCleanPayee
rst!ClaimRefNo = strCleanClaimRefNo
End If
rst.Update
End If
Loop
Close #1
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub
Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.