S
SuzyQ
I have a need to write data to a file in a fixed length or delimited file.
How can I write data to disk in a format that I can specify?
How can I write data to disk in a format that I can specify?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
L
Larry Linson
In "classic Access" (Access 2003 and earlier and assuming that the data you
want to write to a file as fixed length or as a delimited file is in either
an Access Table or an Access Query):
(1) In the Database Window, select the Table or Query,
(2) In the File Menu, click Export
(3) In the Save As dialog that pops up, in the "Save as Type" box, choose
"Text Files (*.txt, *.csv, *.tab, *.asc)"
(4) In the Export Text Wizard that pops up, you can choose "Delimited" or
"Fixed", and, with the prompts that follow, you have outstanding control
over the format, particularly if you use the "Advanced" button.
It may take a little "exploration" to get it just as you want it
(particularly in fixed format), but the only problems I've had with it was
when I was receiving files and they were not consistent in their formatting
(which meant that my saved format had to be changed with some frequency).
This could also be done with VBA code, using DAO (or even <UGH!> ADO) to
read the Table or Query, and File I/O statements in VBA to write to the
file, but that would be a lot more time and effort for any reasonably
"normal" export.
Larry Linson
Microsoft Office Access MVP
__________ Information from ESET Smart Security, version of virus signature database 4022 (20090420) __________
The message was checked by ESET Smart Security.
http://www.eset.com
want to write to a file as fixed length or as a delimited file is in either
an Access Table or an Access Query):
(1) In the Database Window, select the Table or Query,
(2) In the File Menu, click Export
(3) In the Save As dialog that pops up, in the "Save as Type" box, choose
"Text Files (*.txt, *.csv, *.tab, *.asc)"
(4) In the Export Text Wizard that pops up, you can choose "Delimited" or
"Fixed", and, with the prompts that follow, you have outstanding control
over the format, particularly if you use the "Advanced" button.
It may take a little "exploration" to get it just as you want it
(particularly in fixed format), but the only problems I've had with it was
when I was receiving files and they were not consistent in their formatting
(which meant that my saved format had to be changed with some frequency).
This could also be done with VBA code, using DAO (or even <UGH!> ADO) to
read the Table or Query, and File I/O statements in VBA to write to the
file, but that would be a lot more time and effort for any reasonably
"normal" export.
Larry Linson
Microsoft Office Access MVP
SuzyQ said:I have a need to write data to a file in a fixed length or delimited file.
How can I write data to disk in a format that I can specify?
__________ Information from ESET Smart Security, version of virus
signature database 4022 (20090420) __________
The message was checked by ESET Smart Security.
http://www.eset.com
__________ Information from ESET Smart Security, version of virus signature database 4022 (20090420) __________
The message was checked by ESET Smart Security.
http://www.eset.com
D
Dorian
Why do you need to do this?
Normally to transfer data from Access you would use the 'transfertext'
command (look in Access Help). Or, you can do a File--Export.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
Normally to transfer data from Access you would use the 'transfertext'
command (look in Access Help). Or, you can do a File--Export.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
S
SuzyQ
needs to be done in code, not via the menu
Larry Linson said:In "classic Access" (Access 2003 and earlier and assuming that the data you
want to write to a file as fixed length or as a delimited file is in either
an Access Table or an Access Query):
(1) In the Database Window, select the Table or Query,
(2) In the File Menu, click Export
(3) In the Save As dialog that pops up, in the "Save as Type" box, choose
"Text Files (*.txt, *.csv, *.tab, *.asc)"
(4) In the Export Text Wizard that pops up, you can choose "Delimited" or
"Fixed", and, with the prompts that follow, you have outstanding control
over the format, particularly if you use the "Advanced" button.
It may take a little "exploration" to get it just as you want it
(particularly in fixed format), but the only problems I've had with it was
when I was receiving files and they were not consistent in their formatting
(which meant that my saved format had to be changed with some frequency).
This could also be done with VBA code, using DAO (or even <UGH!> ADO) to
read the Table or Query, and File I/O statements in VBA to write to the
file, but that would be a lot more time and effort for any reasonably
"normal" export.
Larry Linson
Microsoft Office Access MVP
__________ Information from ESET Smart Security, version of virus signature database 4022 (20090420) __________
The message was checked by ESET Smart Security.
http://www.eset.com
S
SuzyQ
I can get it to be comma delimited using transfertext, but how do I get it
fixed width using transfertext? I need it to create a specific file format
that can be imported to relic of a system.
fixed width using transfertext? I need it to create a specific file format
that can be imported to relic of a system.
A
Armen Stein
If it's a really complex format, especially one with different record
formats on each line, you may need to use VBA file input/output
functions. Not for the timid, but you can read/write pretty much
anything from/to a file. We've done it for for shop floor machinery,
lab results, legacy accounting systems, etc.
See http://www.applecore99.com/gen/gen029.asp for an overview of
available functions. It's pretty sparse though - maybe someone else
has a good reference for these functions?
Armen Stein
Microsoft Access MVP
www.JStreetTech.com
formats on each line, you may need to use VBA file input/output
functions. Not for the timid, but you can read/write pretty much
anything from/to a file. We've done it for for shop floor machinery,
lab results, legacy accounting systems, etc.
See http://www.applecore99.com/gen/gen029.asp for an overview of
available functions. It's pretty sparse though - maybe someone else
has a good reference for these functions?
Armen Stein
Microsoft Access MVP
www.JStreetTech.com
P
Piet Linden
I can get it to be comma delimited using transfertext, but how do I get it
fixed width using transfertext? I need it to create a specific file format
that can be imported to relic of a system.
So you mean you found the TransferText / Export but you missed the
delimited option?
S
SuzyQ
no, I found the transfertext tool and I produced a delimited file, but I want
to produce a fixed width file.
to produce a fixed width file.
S
SuzyQ
I'm not timid. I've done this many time in foxpro, but need to know how in
vb. I'll check out the link you gave.
vb. I'll check out the link you gave.
T
Tony Toews [MVP]
Armen Stein said:See http://www.applecore99.com/gen/gen029.asp for an overview of
available functions. It's pretty sparse though - maybe someone else
has a good reference for these functions?
There's also Put and Get but I'm having trouble finding them in the
A97 and newer help.
That page, while sparse, looks decent.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
G
Graham Mandeno
Hi Suzy
If you use the export wizard once to set up a fixed width export and specify
all the field widths etc, you can save the export specification you have
defined. Then, when you use TransferText, you specify acExportFixed as the
first argument and provide the name of your saved specification as the
second argument.
If you use the export wizard once to set up a fixed width export and specify
all the field widths etc, you can save the export specification you have
defined. Then, when you use TransferText, you specify acExportFixed as the
first argument and provide the name of your saved specification as the
second argument.