473,320 Members | 1,949 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
9 2726
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Charles Ranch | last post by:
Hello, I'll bet this is an elementary question. I have a text file I am converting and posting to a SQL database. But the first 15 records in this text file are going into a NOTES field in the...
3
by: Jonathan Buckland | last post by:
Can someone give me an example how to append data without having to load the complete XML file. Is this possible? Jonathan
2
by: JMCN | last post by:
hi i have a general question regarding append queries in access 97. each week i need to update my table(tblonlinereg) with new or modified records. firstly, i import the text file into my...
3
by: Craig.Lee | last post by:
I am opening a text file with an ODBC connection. I want to append all the text data to a table. Going row by row and field by field takes too long. Does anyone know how I can either reference the...
1
by: Mark | last post by:
All, Users of the database I have built receive an automated text file from one of our systems at work via e-mail. A problem with the text file exists which I have no control over. The problem is...
3
by: blackd77 | last post by:
What I would LIKE to do is noted in the subject line. What I'm finding is that "edit SQL" appears to only be an option if I am creating a table. If I select "append to" the option to edit SQL...
4
by: MN | last post by:
I have to import a tab-delimited text file daily into Access through a macro. All of the data needs to be added to an existing table. Some of the data already exists but may be updated by the...
2
by: Gaby Sandoval | last post by:
I have this code. The user can read teh record from the text file just fine. They can click the NEXT button and it reads the next record (which is just the next line from the text file). If the...
0
by: Hags007 | last post by:
I have a XML file I am working with. This file has been created by hand and I now need to develop a PHP script that will create it in the same format. Here is what I have thus far: $query =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.