union to .txt file

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hi all,
I have a union query that needs to be exported to a .txt
file for use in a different system. This is all straight
forward and working fine apart from one little problem.
I need to get rid of the last line in the text file to
simplify things. It seems that somewhere along the line, a
carriage return is being added at the end of the last
record.
Can anyone help with this problem?
Thanks in advance,
Dave
 
Any chance that your union query is returning an empty record for that last
record? Seems very unusual to have a carriage return just be added to the
last record.

Post the SQL of your query and let's see what it's doing.
 
Hi Ken,
I've attached the SQL below.

SELECT *
from aliasDeletes

UNION select *
from alias
ORDER BY [add/del] DESC;

The results from this query are exported to a flat file
which is then uploaded into a DB2 table. The last line,
even though there is nothing in it, is creating a whole
record in the DB2 table which is empty.

Thanks for your assistance.
Dave
 
Hi Dave and Ken,

Access's export routine automatically terminates the last record with
RLF, and I don't know any way of preventing it. If you can't tweak DB2's
import routine you can just delete the terminal CRLF, e.g. with this
Perl command; (if Perl isn't on your machine it's probably on the one
that runs DB2):

perl -pe "chomp; $_ = $.==1? qq($_): qq(\n$_);" filename.txt > new.txt

For an all-Access solution you could export the file in the usual way
and then use the VBA file I/O commands to open it and process it line by
line, outputting the CRLF at the beginning of each record after the
first rather than at the end of every record; or else write VBA to open
a recordset on your query and work through that, assembling the fields
into a fixed width string and writing them to disk without the terminal
CRLF.


Hi Ken,
I've attached the SQL below.

SELECT *
from aliasDeletes

UNION select *
from alias
ORDER BY [add/del] DESC;

The results from this query are exported to a flat file
which is then uploaded into a DB2 table. The last line,
even though there is nothing in it, is creating a whole
record in the DB2 table which is empty.

Thanks for your assistance.
Dave
-----Original Message-----
Any chance that your union query is returning an empty record for that last
record? Seems very unusual to have a carriage return just be added to the
last record.

Post the SQL of your query and let's see what it's doing.

--
Ken Snell
<MS ACCESS MVP>




.
Click to expand...
Click to expand...
 
Thanks for the info, John. I've not noted this behavior before (or at least
not been bothered by it!).

--
Ken Snell
<MS ACCESS MVP>

John Nurick said:
Hi Dave and Ken,

Access's export routine automatically terminates the last record with
RLF, and I don't know any way of preventing it. If you can't tweak DB2's
import routine you can just delete the terminal CRLF, e.g. with this
Perl command; (if Perl isn't on your machine it's probably on the one
that runs DB2):

perl -pe "chomp; $_ = $.==1? qq($_): qq(\n$_);" filename.txt > new.txt

For an all-Access solution you could export the file in the usual way
and then use the VBA file I/O commands to open it and process it line by
line, outputting the CRLF at the beginning of each record after the
first rather than at the end of every record; or else write VBA to open
a recordset on your query and work through that, assembling the fields
into a fixed width string and writing them to disk without the terminal
CRLF.


Hi Ken,
I've attached the SQL below.

SELECT *
from aliasDeletes

UNION select *
from alias
ORDER BY [add/del] DESC;

The results from this query are exported to a flat file
which is then uploaded into a DB2 table. The last line,
even though there is nothing in it, is creating a whole
record in the DB2 table which is empty.

Thanks for your assistance.
Dave
-----Original Message-----
Any chance that your union query is returning an empty record for that last
record? Seems very unusual to have a carriage return just be added to the
last record.

Post the SQL of your query and let's see what it's doing.

--
Ken Snell
<MS ACCESS MVP>

Hi all,
I have a union query that needs to be exported to a .txt
file for use in a different system. This is all straight
forward and working fine apart from one little problem.
I need to get rid of the last line in the text file to
simplify things. It seems that somewhere along the line, a
carriage return is being added at the end of the last
record.
Can anyone help with this problem?
Thanks in advance,
Dave


.
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the info, John. I've not noted this behavior before (or at least
not been bothered by it!).
Click to expand...

No worries, Ken. Most textfile software seems not to care whether or not
there's a record separator at the end of the last record, but there are
exceptions and this DB2 import routine seems to be among them.
 
Thanks for the help on this one guys. Sorry for the tardy
response, but I've been away.
I thought the VBA way would work, but wanted to know if
there was a simpler fix. It appears VBA is the way to go
here.
Dave
-----Original Message-----
Thanks for the info, John. I've not noted this behavior before (or at least
not been bothered by it!).
Click to expand...

No worries, Ken. Most textfile software seems not to care whether or not
there's a record separator at the end of the last record, but there are
exceptions and this DB2 import routine seems to be among them.

--
John Nurick [Microsoft Access MVP]

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