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

Remove text file header

P: n/a
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

Jun 5 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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 ***
Jun 5 '07 #2

P: n/a
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
Jun 5 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.