Exporting to a Text file

  • Thread starter Thread starter Rica
  • Start date Start date
R

Rica

Hi,

I'm trying to export the query result to a Text file. It
saves with horizontal lines and pipe delimited lines
(vertical ). Though I do need the pipe delimited lines
which separate the columns but how can I get away with the
horizontal lines between the rows and around the data.

Any help would be wonderful.

Thanks in advance.

Rica
 
Hi Rica,

This happens if you check the "Save formatted" box when you export a
query to a textfile.

Instead, if you want a standard pipe-delimited file (e.g.
FirstName|LastName|Address|City|State|ZIP
Jane|Bloggs|123 Acacia Avenue|Big Little City|TX|77777
export as a delimited file using | as the delimiter.

If you want something like this - like "save formatted" but without the
horizontal lines and enclosing box -
FirstName |LastName |Address |....
Jane |Bloggs |123 Acacia Avenue |....
you can add "dummy" calculated fields like this
Sep1: "|"
to generate the pipe characters, and then export it as a fixed-width
file.
 
Thanks, for your response but I still have the problem.
When I click 'Save As' without checking on 'Save
formatted' I don't get a Text Wizard. I get a message
(output box) with the Title as 'Export Text Wizard' and
the message as 'Too few parameters. Expected 1.'

I'm using Access 2000 and this is at my work place. But in
at home where I'm using Access 2000 it seems to work fine.
Please if anyone could help it would be great.

Thanks,
Rica
-----Original Message-----
Hi Rica,

This happens if you check the "Save formatted" box when you export a
query to a textfile.

Instead, if you want a standard pipe-delimited file (e.g.
FirstName|LastName|Address|City|State|ZIP
Jane|Bloggs|123 Acacia Avenue|Big Little City|TX|77777
export as a delimited file using | as the delimiter.

If you want something like this - like "save formatted" but without the
horizontal lines and enclosing box -
FirstName |LastName
Click to expand...
|Address |....
Jane |Bloggs |123 Acacia Avenue |....
you can add "dummy" calculated fields like this
Sep1: "|"
to generate the pipe characters, and then export it as a fixed-width
file.



Hi,

I'm trying to export the query result to a Text file. It
saves with horizontal lines and pipe delimited lines
(vertical ). Though I do need the pipe delimited lines
which separate the columns but how can I get away with the
horizontal lines between the rows and around the data.

Any help would be wonderful.

Thanks in advance.

Rica
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Hi Rica,

This sounds like the problem described in this article: "Too Few
parameters. Expected X" Error Message When You Export Parameter Query as
Text http://support.microsoft.com/default.aspx?scid=kb;en-us;269671



Thanks, for your response but I still have the problem.
When I click 'Save As' without checking on 'Save
formatted' I don't get a Text Wizard. I get a message
(output box) with the Title as 'Export Text Wizard' and
the message as 'Too few parameters. Expected 1.'

I'm using Access 2000 and this is at my work place. But in
at home where I'm using Access 2000 it seems to work fine.
Please if anyone could help it would be great.

Thanks,
Rica
-----Original Message-----
Hi Rica,

This happens if you check the "Save formatted" box when you export a
query to a textfile.

Instead, if you want a standard pipe-delimited file (e.g.
FirstName|LastName|Address|City|State|ZIP
Jane|Bloggs|123 Acacia Avenue|Big Little City|TX|77777
export as a delimited file using | as the delimiter.

If you want something like this - like "save formatted" but without the
horizontal lines and enclosing box -
FirstName |LastName
Click to expand...
|Address |....
Jane |Bloggs |123 Acacia Avenue |....
you can add "dummy" calculated fields like this
Sep1: "|"
to generate the pipe characters, and then export it as a fixed-width
file.



Hi,

I'm trying to export the query result to a Text file. It
saves with horizontal lines and pipe delimited lines
(vertical ). Though I do need the pipe delimited lines
which separate the columns but how can I get away with the
horizontal lines between the rows and around the data.

Any help would be wonderful.

Thanks in advance.

Rica
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Hi John,

I put in the code in my Query which is ... Select ...
INTO [Text;FMT=Delimited;HDR=No;DATABASE=C:\Named;].
[Check#txt]
The above code puts the output in the text file in that
location. But can it
1)ask with a Input box for a location that the user wants
to save in the text file?
2) ask the user to name the fie with the help of an Input
box?
3) do without a header as it shows the header here.
4) put in pipe delimiter instead of a semicolon as a
delimiter
As I do not want the user to go in and change the code.
Would this be possible? I need help and thanks for such a
quick reponse.

Rica
-----Original Message-----
Hi Rica,

This sounds like the problem described in this article: "Too Few
parameters. Expected X" Error Message When You Export Parameter Query as
Text http://support.microsoft.com/default.aspx?scid=kb;en- us;269671
Thanks, for your response but I still have the problem.
When I click 'Save As' without checking on 'Save
formatted' I don't get a Text Wizard. I get a message
(output box) with the Title as 'Export Text Wizard' and
the message as 'Too few parameters. Expected 1.'

I'm using Access 2000 and this is at my work place. But in
at home where I'm using Access 2000 it seems to work fine.
Please if anyone could help it would be great.

Thanks,
Rica
-----Original Message-----
Hi Rica,

This happens if you check the "Save formatted" box when you export a
query to a textfile.

Instead, if you want a standard pipe-delimited file (e.g.
FirstName|LastName|Address|City|State|ZIP
Jane|Bloggs|123 Acacia Avenue|Big Little City|TX|77777
export as a delimited file using | as the delimiter.

If you want something like this - like "save formatted" but without the
horizontal lines and enclosing box -
FirstName |LastName
Click to expand...
|Address |....
Jane |Bloggs |123 Acacia Avenue |....
you can add "dummy" calculated fields like this
Sep1: "|"
to generate the pipe characters, and then export it as
Click to expand...
a
fixed-width
file.


On Fri, 16 Jan 2004 11:54:48 -0800, "Rica"

Hi,

I'm trying to export the query result to a Text file. It
saves with horizontal lines and pipe delimited lines
(vertical ). Though I do need the pipe delimited lines
which separate the columns but how can I get away with the
horizontal lines between the rows and around the data.

Any help would be wonderful.

Thanks in advance.

Rica

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Hi Rica,

I'm not sure what's the best approach in these circumstances, and
because of this and other problems with Access 2000 I upgraded direct
from Access 97 to 2002 so have no real experience with that version.

You sayIf that's the case then it seems that the computer at home may have a
later Office or Windows service pack than the one in the office. It's
almost certainly worth installing the latest Office 2000 service pack.
And - if the computers are running Windows 2000 - it seems that Windows
2000 Service Pack 3 updates some of the Windows database access
components that are used by Access, so installing that may make the
computers work alike.

Otherwise, I think the best approach would be to revise your application
to avoid using a parameter query. Instead of using the SELECT ... INTO
with parameter, I'd go back to a straight SELECT query without
parameter, and write VBA code to modify the SQL of this query as needed.

E.g. if your original query was
SELECT * FROM MyTable WHERE State = '[Please enter state]';
change it to
SELECT * FROM MyTable;

and then have your code insert the appropriate WHERE clause (using the
SQL property of the QueryDef object that represents the query), e.g.

WHERE State = 'TX'

This way you can export the query in the normal way using
DoCmd.TransferText, with a custom export specification to give the pipe
delimiter and so on.

To let the user specify filename and location, use the VBA code at
http://www.mvps.org/access/api/api0001.htm to collect the name and path
so you can pass it to TransferText.


Hi John,

I put in the code in my Query which is ... Select ...
INTO [Text;FMT=Delimited;HDR=No;DATABASE=C:\Named;].
[Check#txt]
The above code puts the output in the text file in that
location. But can it
1)ask with a Input box for a location that the user wants
to save in the text file?
2) ask the user to name the fie with the help of an Input
box?
3) do without a header as it shows the header here.
4) put in pipe delimiter instead of a semicolon as a
delimiter
As I do not want the user to go in and change the code.
Would this be possible? I need help and thanks for such a
quick reponse.

Rica
-----Original Message-----
Hi Rica,

This sounds like the problem described in this article: "Too Few
parameters. Expected X" Error Message When You Export Parameter Query as
Text http://support.microsoft.com/default.aspx?scid=kb;en- us;269671
Thanks, for your response but I still have the problem.
When I click 'Save As' without checking on 'Save
formatted' I don't get a Text Wizard. I get a message
(output box) with the Title as 'Export Text Wizard' and
the message as 'Too few parameters. Expected 1.'

I'm using Access 2000 and this is at my work place. But in
at home where I'm using Access 2000 it seems to work fine.
Please if anyone could help it would be great.

Thanks,
Rica
-----Original Message-----
Hi Rica,

This happens if you check the "Save formatted" box when
you export a
query to a textfile.

Instead, if you want a standard pipe-delimited file (e.g.
FirstName|LastName|Address|City|State|ZIP
Jane|Bloggs|123 Acacia Avenue|Big Little
City|TX|77777
export as a delimited file using | as the delimiter.

If you want something like this - like "save formatted"
but without the
horizontal lines and enclosing box -
FirstName |LastName
|Address |....
Jane |Bloggs |123 Acacia
Avenue |....
you can add "dummy" calculated fields like this
Sep1: "|"
to generate the pipe characters, and then export it as a
fixed-width
file.


On Fri, 16 Jan 2004 11:54:48 -0800, "Rica"

Hi,

I'm trying to export the query result to a Text file. It
saves with horizontal lines and pipe delimited lines
(vertical ). Though I do need the pipe delimited lines
which separate the columns but how can I get away with
the
horizontal lines between the rows and around the data.

Any help would be wonderful.

Thanks in advance.

Rica

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Hi John,

I have tried using the code you gave me which is
Select ...
INTO [Text;FMT=Delimited;HDR=No;DATABASE=C:\Named;].
[Check#txt]
Click to expand...
Click to expand...
.. It works fine. I have tried to tweak some of the things
in schema.ini file for date which is working great now.
Can you help me to get the output where my Text or Char is
without the double quotes("") since I have pipe delimited
all fields I do not want quotes for the text. Please help!

Thank you
-----Original Message-----
Hi Rica,

I'm not sure what's the best approach in these circumstances, and
because of this and other problems with Access 2000 I upgraded direct
from Access 97 to 2002 so have no real experience with that version.

You say fine.
If that's the case then it seems that the computer at home may have a
later Office or Windows service pack than the one in the office. It's
almost certainly worth installing the latest Office 2000 service pack.
And - if the computers are running Windows 2000 - it seems that Windows
2000 Service Pack 3 updates some of the Windows database access
components that are used by Access, so installing that may make the
computers work alike.

Otherwise, I think the best approach would be to revise your application
to avoid using a parameter query. Instead of using the SELECT ... INTO
with parameter, I'd go back to a straight SELECT query without
parameter, and write VBA code to modify the SQL of this query as needed.

E.g. if your original query was
SELECT * FROM MyTable WHERE State = '[Please enter state]';
change it to
SELECT * FROM MyTable;

and then have your code insert the appropriate WHERE clause (using the
SQL property of the QueryDef object that represents the query), e.g.

WHERE State = 'TX'

This way you can export the query in the normal way using
DoCmd.TransferText, with a custom export specification to give the pipe
delimiter and so on.

To let the user specify filename and location, use the VBA code at
http://www.mvps.org/access/api/api0001.htm to collect the name and path
so you can pass it to TransferText.


Hi John,

I put in the code in my Query which is ... Select ...
INTO [Text;FMT=Delimited;HDR=No;DATABASE=C:\Named;].
[Check#txt]
The above code puts the output in the text file in that
location. But can it
1)ask with a Input box for a location that the user wants
to save in the text file?
2) ask the user to name the fie with the help of an Input
box?
3) do without a header as it shows the header here.
4) put in pipe delimiter instead of a semicolon as a
delimiter
As I do not want the user to go in and change the code.
Would this be possible? I need help and thanks for such a
quick reponse.

Rica
-----Original Message-----
Hi Rica,

This sounds like the problem described in this article: "Too Few
parameters. Expected X" Error Message When You Export Parameter Query as
Text http://support.microsoft.com/default.aspx?
Click to expand...
scid=kb;en-
us;269671
On Tue, 20 Jan 2004 11:15:16 -0800,

Thanks, for your response but I still have the problem.
When I click 'Save As' without checking on 'Save
formatted' I don't get a Text Wizard. I get a message
(output box) with the Title as 'Export Text Wizard' and
the message as 'Too few parameters. Expected 1.'

I'm using Access 2000 and this is at my work place.
Click to expand...
But
in
at home where I'm using Access 2000 it seems to work fine.
Please if anyone could help it would be great.

Thanks,
Rica
-----Original Message-----
Hi Rica,

This happens if you check the "Save formatted" box when
you export a
query to a textfile.

Instead, if you want a standard pipe-delimited file (e.g.
FirstName|LastName|Address|City|State|ZIP
Jane|Bloggs|123 Acacia Avenue|Big Little
City|TX|77777
export as a delimited file using | as the delimiter.

If you want something like this - like "save formatted"
but without the
horizontal lines and enclosing box -
FirstName |LastName
|Address |....
Jane |Bloggs |123 Acacia
Avenue |....
you can add "dummy" calculated fields like this
Sep1: "|"
to generate the pipe characters, and then export it
Click to expand...
as
a
fixed-width
file.


On Fri, 16 Jan 2004 11:54:48 -0800, "Rica"

Hi,

I'm trying to export the query result to a Text
Click to expand...
file.
It
saves with horizontal lines and pipe delimited lines
(vertical ). Though I do need the pipe delimited lines
which separate the columns but how can I get away with
the
horizontal lines between the rows and around the data.

Any help would be wonderful.

Thanks in advance.

Rica

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Hi Rica,

Again, I don't know for sure. I'm away from home and documentation, but
IIRC there's a setting in schema.ini that controls whether or not text
fields are enclosed in quotes. Alternatively, I suspect that if
schema.ini is present you may not need the FMT=Delimited specifier in
the query: this may be causing the quotes to be added.

Hi John,

I have tried using the code you gave me which is
Select ...
INTO [Text;FMT=Delimited;HDR=No;DATABASE=C:\Named;].
[Check#txt]
Click to expand...
Click to expand...
. It works fine. I have tried to tweak some of the things
in schema.ini file for date which is working great now.
Can you help me to get the output where my Text or Char is
without the double quotes("") since I have pipe delimited
all fields I do not want quotes for the text. Please help!

Thank you
-----Original Message-----
Hi Rica,

I'm not sure what's the best approach in these circumstances, and
because of this and other problems with Access 2000 I upgraded direct
from Access 97 to 2002 so have no real experience with that version.

You say
I'm using Access 2000 and this is at my work place. But in
at home where I'm using Access 2000 it seems to work
Click to expand...
fine.
If that's the case then it seems that the computer at home may have a
later Office or Windows service pack than the one in the office. It's
almost certainly worth installing the latest Office 2000 service pack.
And - if the computers are running Windows 2000 - it seems that Windows
2000 Service Pack 3 updates some of the Windows database access
components that are used by Access, so installing that may make the
computers work alike.

Otherwise, I think the best approach would be to revise your application
to avoid using a parameter query. Instead of using the SELECT ... INTO
with parameter, I'd go back to a straight SELECT query without
parameter, and write VBA code to modify the SQL of this query as needed.

E.g. if your original query was
SELECT * FROM MyTable WHERE State = '[Please enter state]';
change it to
SELECT * FROM MyTable;

and then have your code insert the appropriate WHERE clause (using the
SQL property of the QueryDef object that represents the query), e.g.

WHERE State = 'TX'

This way you can export the query in the normal way using
DoCmd.TransferText, with a custom export specification to give the pipe
delimiter and so on.

To let the user specify filename and location, use the VBA code at
http://www.mvps.org/access/api/api0001.htm to collect the name and path
so you can pass it to TransferText.


Hi John,

I put in the code in my Query which is ... Select ...
INTO [Text;FMT=Delimited;HDR=No;DATABASE=C:\Named;].
[Check#txt]
The above code puts the output in the text file in that
location. But can it
1)ask with a Input box for a location that the user wants
to save in the text file?
2) ask the user to name the fie with the help of an Input
box?
3) do without a header as it shows the header here.
4) put in pipe delimiter instead of a semicolon as a
delimiter
As I do not want the user to go in and change the code.
Would this be possible? I need help and thanks for such a
quick reponse.

Rica
-----Original Message-----
Hi Rica,

This sounds like the problem described in this
article: "Too Few
parameters. Expected X" Error Message When You Export
Parameter Query as
Text http://support.microsoft.com/default.aspx? scid=kb;en-
us;269671



On Tue, 20 Jan 2004 11:15:16 -0800,

Thanks, for your response but I still have the problem.
When I click 'Save As' without checking on 'Save
formatted' I don't get a Text Wizard. I get a message
(output box) with the Title as 'Export Text Wizard' and
the message as 'Too few parameters. Expected 1.'

I'm using Access 2000 and this is at my work place. But
in
at home where I'm using Access 2000 it seems to work
fine.
Please if anyone could help it would be great.

Thanks,
Rica
-----Original Message-----
Hi Rica,

This happens if you check the "Save formatted" box when
you export a
query to a textfile.

Instead, if you want a standard pipe-delimited file
(e.g.
FirstName|LastName|Address|City|State|ZIP
Jane|Bloggs|123 Acacia Avenue|Big Little
City|TX|77777
export as a delimited file using | as the delimiter.

If you want something like this - like "save formatted"
but without the
horizontal lines and enclosing box -
FirstName |LastName
|Address |....
Jane |Bloggs |123 Acacia
Avenue |....
you can add "dummy" calculated fields like this
Sep1: "|"
to generate the pipe characters, and then export it as
a
fixed-width
file.


On Fri, 16 Jan 2004 11:54:48 -0800, "Rica"

Hi,

I'm trying to export the query result to a Text file.
It
saves with horizontal lines and pipe delimited lines
(vertical ). Though I do need the pipe delimited
lines
which separate the columns but how can I get away with
the
horizontal lines between the rows and around the data.

Any help would be wonderful.

Thanks in advance.

Rica

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Back
Top