Remove text file header

[Cliff72]

I'm creating a database that will be uploading some text files into an
access table. The problem is that the text files have a header which
messes up my import specs. so what i have had to do is to open the
files and manually delete the data before importing them. Is there a
way i can programatically do this? ie programmatically edit the text
file and remove the surplus header text, and then import the file with
the DoCmd.TransferText function?

Below is a sample of the data that iam working with: The stuff that i
want deleted is between the "Å"
Å



� *************************************************
ViewDirect *************************************************
� *************************************************
ViewDirect *************************************************
�
**
**
� ** RECIPIENT: U703287 CLIFF
KING **
� ** USER-ID: U703287 Long
Beach **
� ** Accounts
Payable **
�
**
**
�
**
**
�
**
**
�
**
**
� ** REPORT ID: s123q31 BATCH REPORT
TOTALS **
�
**
**
� ** VERSION: 20070430
021155
**
�
**
**
� ** FROM HIERARCHY CODE:
FROM PAGE: 000001 **
�
**
**
� ** TO HIERARCHY CODE:
TO PAGE: @LAST **
�
**
**
� ** PAGE OUTPUT LIMIT:
000100
**
�
**
**
�
************************************************** ************************************************** **********

************************************************** ************************************************** **********

� DATE:
06/05/2007
� TIME:
14:11.51
Å
� 123 Long Beach COMPA PRJ/RPT P31 PAYMENT SYSTEM THE CO. DATE
04/30/07 TIME 01.58 WK13/PD04 PAGE 1
�
� RPT VPSI601.I708 BATCH SUMMARY
REPORT FORWARD TO: ACCOUNTING -
ACCOUNTS PAYABLE
� BATCH REFERENCE NUMBER : A77_41
�

Thanks

[Rich P]

Greetings,

Here is a programmatic approach to your problem (note: I steer away
from Import and Spec - this is much more reliable and efficient)
Sub ReadFile()
Dim DB As DAO.Database, RS As DAO.Recordset
Dim RS2 As DAO.Recordset
Dim str1 As String, str2() As String, i As Integer

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From tblA1"
DoCmd.RunSQL "Delete * From tblA"
Set DB = CurrentDb
Set RS = DB.OpenRecordset("tblA1")

Close #1
Open "C:\1A\testRead.txt" For Input As #1
i = 0
Do While Not EOF(1)
Line Input #1, str1
If i 1 Then
RS.AddNew
RS(0) = str1
RS.Update
End If
i = i + 1
Loop
Close #1

RS.MoveFirst
Set RS2 = DB.OpenRecordset("tblA")
Do While Not RS.EOF
str2 = Split(RS(0), "|")
RS2.AddNew
For i = 0 To UBound(str2) : RS2(i) = str2(i) : Next
RS2.Update
RS.MoveNext
Loop
DoCmd.SetWarnings True
End Sub

I create a test text file called textRead.txt and loaded it as follows:

Test File <-----this is the header line you don't want

Dick|Jane|Paul
Sue|Mary|Sam
Tom|June|May
Sun|Mun|Dun

I am using the Open method of VBA for reading text from textfiles. I
use the Line Input method to read each line as one string. Then I use
DAO code to write that line to tblA1 which contains only one field -
depending on the size of your data, I made this field a text field of
size 100. If you have more than 255 chars per line, you can make the
field a memo field and will work just fine. After I read the contents
of the textfile to tblA1, I loop through that table and parse out the
data - note that I use | pipe symbol as my delimeter. Now I can use the
string "Split" function to parse each line into a string array. Then I
read the contents of the array into tblA. Now I have parsed out my data
and omitted the dreadful Header line by counting 2 rows from the
textfile before I begin reading the data.

There is plenty of documentation in the help files on reading data from
text files. Just copy this code into a Standard code module and place
the mouse cursor of any object you have a question about and press the
F1 key and help will come up on that object.
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Larry Linson]

File I/O statements have been around since the olden days of Basic, and it's
not awfully difficult to Open the file, skip the extraneous information, and
then write a new file, which you use for the import.

Larry Linson
Microsoft Access MVP

<Cl*****@gmail.comwrote in message
news:11**********************@o5g2000hsb.googlegro ups.com...
I'm creating a database that will be uploading some text files into an
access table. The problem is that the text files have a header which
messes up my import specs. so what i have had to do is to open the
files and manually delete the data before importing them. Is there a
way i can programatically do this? ie programmatically edit the text
file and remove the surplus header text, and then import the file with
the DoCmd.TransferText function?

Below is a sample of the data that iam working with: The stuff that i
want deleted is between the "Å"
Å



� *************************************************
ViewDirect *************************************************
� *************************************************
ViewDirect *************************************************
�
**
**
� ** RECIPIENT: U703287 CLIFF
KING **
� ** USER-ID: U703287 Long
Beach **
� ** Accounts
Payable **
�
**
**
�
**
**
�
**
**
�
**
**
� ** REPORT ID: s123q31 BATCH REPORT
TOTALS **
�
**
**
� ** VERSION: 20070430
021155
**
�
**
**
� ** FROM HIERARCHY CODE:
FROM PAGE: 000001 **
�
**
**
� ** TO HIERARCHY CODE:
TO PAGE: @LAST **
�
**
**
� ** PAGE OUTPUT LIMIT:
000100
**
�
**
**
�
************************************************** ************************************************** **********

************************************************** ************************************************** **********

� DATE:
06/05/2007
� TIME:
14:11.51
Å
� 123 Long Beach COMPA PRJ/RPT P31 PAYMENT SYSTEM THE CO. DATE
04/30/07 TIME 01.58 WK13/PD04 PAGE 1
�
� RPT VPSI601.I708 BATCH SUMMARY
REPORT FORWARD TO: ACCOUNTING -
ACCOUNTS PAYABLE
� BATCH REFERENCE NUMBER : A77_41
�

Thanks