Exporting Access Table to .txt file

[benb]

Hi,

I am having some problems exporting an Access Table to a text file. The
problem I am having is that I need to have this table exported to a
preformatted text file. This is the format that I need the file to be
set up:


11900111100hello
200016454.32101
200016454.32101
200016454.32101
200016454.32101
200016454.32101
35

What makes this difficult for me is that the first character on here is
the record type, the next 6 characters is the date, then the time, then
file identification number.
On the second row we have the record type, employee id (5 characters),
and then total service.
the last line there is record type "3" , and record count.

Another thing about this table is that the data available is determined
by the record type.For example:
Record Type has a date, time, and file indentification number
Record Type 2 has a employee id, service number, and total service
number
Record Type 3 has a record count

Here is a look at the table and some sample data:

1 19000101 11:00 hello
2 000016 54.3210 1
2 000016 54.3210 1
2 000016 54.3210 1
2 000016 54.3210 1
2 000016 54.3210 1
3 5

Thanks in advance for your time and help.

Ben

 

[Joseph Meehan]

Hi,

I am having some problems exporting an Access Table to a text file.
The problem I am having is that I need to have this table exported to
a preformatted text file. This is the format that I need the file to
be set up:


11900111100hello
200016454.32101
200016454.32101
200016454.32101
200016454.32101
200016454.32101
35

What makes this difficult for me is that the first character on here
is the record type, the next 6 characters is the date, then the time,
then file identification number.
On the second row we have the record type, employee id (5 characters),
and then total service.
the last line there is record type "3" , and record count.

Another thing about this table is that the data available is
determined by the record type.For example:
Record Type has a date, time, and file indentification number
Record Type 2 has a employee id, service number, and total service
number
Record Type 3 has a record count

You really really need to correct the data format and normalize the
data. Anything you do is going to take a lot of work to get what you want
and it is likely to fail from time to time because of some sort of data
variation.

 

[Albert D. Kallal]

I don't see a probelm creating this text file at all.

11900111100hello
200016454.32101
200016454.32101
200016454.32101
200016454.32101
200016454.32101
35

What makes this difficult for me is that the first character on here is
the record type

So, the first charrater is a 1 in teh above...so, recordtype = 1

Ah, hum...you have 1, now it is record type 3? This is confusing.

Anyway, it not matters much all of these zillions of little details (I
likely wasting your time asking a 1000 questions).

You can't produce the above file using the export wizard (obviously).

however, you an simply open a text file for output.....

The follwing is complete air code off the top of my head...so, it likey has
syntax, and typo-os...but, it is a "shell" of starting point for what you
need...


Sub ReadTextFile

Dim strFile As String
Dim intF As Integer
dim rst as dao.RecordSet
dim strSql as string
dim strOutLine as string
dim intRecords as integer

strSql = "select * from tblCustomers where CustomerType = 2"

set rst = currentdb.OpenRecordSet(strSql)

if rst.recordCount > 0 then

strFile = "c:\my data\MyData.txt"
intF = FreeFile()
Open strFile For Output As #intF

strOutLine = "file type + file ideiciation naumber + bla bla bal"
' use format(datevalue,"yyyydd")

do while rst.Eof = false
strOutLine = "1" ' recordType
strOutLine = strOutLine & rst!EmployeeID
. etc .etc .etc
print #intf,strOutLine
intRecords = intRecords + 1
rst.MoveNext
loop

strOutLine = "3" & intReocrds
print #intF,strOutline

close intf
rst.Close

 

[benb]

Thank you all for your help. The reason the data is this way is becase
the first record type is a header and record type 2 is a trailer. This
data is imported into a table in this way. The reason there are so many
spaces and null values is because the header only consists of the date,
time, and file identification number. The trailer consits of just the
record count. If there is a better way to get this information in one
text file from say 2 or 3 different tables, then I am all for it.

 

[benb]

Thanks for all the help. I got it working.

Thank you all for your help. The reason the data is this way is becase
the first record type is a header and record type 2 is a trailer. This
data is imported into a table in this way. The reason there are so many
spaces and null values is because the header only consists of the date,
time, and file identification number. The trailer consits of just the
record count. If there is a better way to get this information in one
text file from say 2 or 3 different tables, then I am all for it.