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

CSV Export: Force Trailing Comma?

P: n/a
Greetings,

I'm using the DoCmd.TransferText method to export the results of a MS
Access query to a csv file. The csv will then be used to load an Oracle
table.

In other systems such as TOAD for Oracle, it's possible to force an
additional comma delimiter after the last column, if the column is empty
on a particular row. Oracle requires this additional comma on empty
rightmost columns, for importing purposes.

I am unable to see how to do this in the Visual Basic TransferText
method, or in the MS Access export specifications.

Can this be done in VB/Access, and how?
Mar 25 '06 #1
Share this Question
Share on Google+
14 Replies


P: n/a
PCD
In your query, use a calculated field that looks like this:
RevisedLastField:[LastFieldname] & ","

Uncheck the 'Show' box in the LastFieldName column and be sure the 'Show'
box is checked in the RevisedLastField column. You want to export
RevisedLastField rather than LastFieldName.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1150 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com
"bonehead" <se**********@here.net> wrote in message
news:e0**********@gondor.sdsu.edu...
Greetings,

I'm using the DoCmd.TransferText method to export the results of a MS
Access query to a csv file. The csv will then be used to load an Oracle
table.

In other systems such as TOAD for Oracle, it's possible to force an
additional comma delimiter after the last column, if the column is empty
on a particular row. Oracle requires this additional comma on empty
rightmost columns, for importing purposes.

I am unable to see how to do this in the Visual Basic TransferText method,
or in the MS Access export specifications.

Can this be done in VB/Access, and how?

Mar 25 '06 #2

P: n/a
bonehead <se**********@here.net> wrote in
news:e0**********@gondor.sdsu.edu:
Greetings,

I'm using the DoCmd.TransferText method to export the results
of a MS Access query to a csv file. The csv will then be used
to load an Oracle table.

In other systems such as TOAD for Oracle, it's possible to
force an additional comma delimiter after the last column, if
the column is empty on a particular row. Oracle requires this
additional comma on empty rightmost columns, for importing
purposes.

I am unable to see how to do this in the Visual Basic
TransferText method, or in the MS Access export
specifications.

Can this be done in VB/Access, and how?

Access always puts the correct number of commas. If you need an
additional comma at the end of every row, add null as the
expression for the calculated rightmost field in the query.
--
Bob Quintal

PA is y I've altered my email address.
Mar 25 '06 #3

P: n/a

"PCD" <no***@email.com> schreef in bericht news:sg******************@newsread1.news.atl.earth link.net...

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications 'Resource ????
Over 1150 users have come to me from the newsgroups requesting help '1150 users ????
re******@pcdatasheet.com



--
To Steve:
Over 575!! users from the newsgroups have visited the website to read what kind of a 'resource' you are... (rapidly increasing..)

To the original poster:

Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R
Mar 26 '06 #4

P: n/a
PCD
All you are doing is INDEED showing every newsgroup reader that you are
nothing but an OBSESSED, egocentric A$$. You revel in tracking every post I
make in the newsgroups and do not make any contributions to our newsgroup.
Take the advise that was given to you by a poster not too long
ago and grow up and get a life. We don't need the likes of you in our
newsgroup! Just get lost and quit filling our newsgroup with your slanderous
garbage!!!!!!

INDEED!!!
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1150 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com
"StopThisAdvertising" <StopThisAdvertising@DataShit> wrote in message
news:44**********************@text.nova.planet.nl. ..

"PCD" <no***@email.com> schreef in bericht
news:sg******************@newsread1.news.atl.earth link.net...

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
'Resource ????
Over 1150 users have come to me from the newsgroups requesting help
'1150 users ????
re******@pcdatasheet.com


--
To Steve:
Over 575!! users from the newsgroups have visited the website to read what
kind of a 'resource' you are... (rapidly increasing..)

To the original poster:

Most people here have a common belief that the newsgroups are for *free
exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell
his services.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R
Mar 26 '06 #5

P: n/a
PCD
What a "BONEHEAD" response!!!!
"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
bonehead <se**********@here.net> wrote in
news:e0**********@gondor.sdsu.edu:
Greetings,

I'm using the DoCmd.TransferText method to export the results
of a MS Access query to a csv file. The csv will then be used
to load an Oracle table.

In other systems such as TOAD for Oracle, it's possible to
force an additional comma delimiter after the last column, if
the column is empty on a particular row. Oracle requires this
additional comma on empty rightmost columns, for importing
purposes.

I am unable to see how to do this in the Visual Basic
TransferText method, or in the MS Access export
specifications.

Can this be done in VB/Access, and how?

Access always puts the correct number of commas. If you need an
additional comma at the end of every row, add null as the
expression for the calculated rightmost field in the query.
--
Bob Quintal

PA is y I've altered my email address.

Mar 26 '06 #6

P: n/a
PCD
Why didn't you advise the OP to write one field at a time to his CSV file?
You thought that was the correct way to export a couple of hundred thousand
records to Excel!!
"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
bonehead <se**********@here.net> wrote in
news:e0**********@gondor.sdsu.edu:
Greetings,

I'm using the DoCmd.TransferText method to export the results
of a MS Access query to a csv file. The csv will then be used
to load an Oracle table.

In other systems such as TOAD for Oracle, it's possible to
force an additional comma delimiter after the last column, if
the column is empty on a particular row. Oracle requires this
additional comma on empty rightmost columns, for importing
purposes.

I am unable to see how to do this in the Visual Basic
TransferText method, or in the MS Access export
specifications.

Can this be done in VB/Access, and how?

Access always puts the correct number of commas. If you need an
additional comma at the end of every row, add null as the
expression for the calculated rightmost field in the query.
--
Bob Quintal

PA is y I've altered my email address.

Mar 26 '06 #7

P: n/a
"PCD" <no***@email.com> wrote in
news:In*******************@newsread1.news.atl.eart hlink.net:
Why didn't you advise the OP to write one field at a time to
his CSV file? You thought that was the correct way to export a
couple of hundred thousand records to Excel!!

Look, Pretty Crappy Developer (PCD), I offered a solution that
met all the constraints.It's not the fastest, but it would work.
Code which satisfies customer requirements without errors is
always the correct way.

It's also obvious to me that you lack the knowledge that you can
write field by field to Excel when the transferSpreadsheet
method fails.

Let us see some code for your solution which would properly
execute writing a single recordset to multiple Excel worksheets.

As for your suggestion in this thread, it has a bug.
If the export specification requires a text qualifier
RevisedLastField:[LastFieldname] & ","
returns "PCD messed up," instead of "PCD messed up",

Put your money where your mouth is.


"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
bonehead <se**********@here.net> wrote in
news:e0**********@gondor.sdsu.edu:
Greetings,

I'm using the DoCmd.TransferText method to export the
results of a MS Access query to a csv file. The csv will
then be used to load an Oracle table.

In other systems such as TOAD for Oracle, it's possible to
force an additional comma delimiter after the last column,
if the column is empty on a particular row. Oracle requires
this additional comma on empty rightmost columns, for
importing purposes.

I am unable to see how to do this in the Visual Basic
TransferText method, or in the MS Access export
specifications.

Can this be done in VB/Access, and how?

Access always puts the correct number of commas. If you need
an additional comma at the end of every row, add null as the
expression for the calculated rightmost field in the query.
--
Bob Quintal

PA is y I've altered my email address.



--
Bob Quintal

PA is y I've altered my email address.
Mar 26 '06 #8

P: n/a
Bob Quintal wrote:
"PCD" <no***@email.com> wrote in
news:In*******************@newsread1.news.atl.eart hlink.net:
Why didn't you advise the OP to write one field at a time to
his CSV file? You thought that was the correct way to export a
couple of hundred thousand records to Excel!!


Look, Pretty Crappy Developer (PCD), I offered a solution that
met all the constraints.It's not the fastest, but it would work.
Code which satisfies customer requirements without errors is
always the correct way.

It's also obvious to me that you lack the knowledge that you can
write field by field to Excel when the transferSpreadsheet
method fails.

Let us see some code for your solution which would properly
execute writing a single recordset to multiple Excel worksheets.

As for your suggestion in this thread, it has a bug.
If the export specification requires a text qualifier
RevisedLastField:[LastFieldname] & ","
returns "PCD messed up," instead of "PCD messed up",

Put your money where your mouth is.

Access always puts the correct number of commas. If you need
an additional comma at the end of every row, add null as the
expression for the calculated rightmost field in the query.
--
Bob Quintal

PA is y I've altered my email address.


Bob,

We're all trying to keep this PCD mess as unobtrusive as is practical, I
suspect that's why no one has responded. I felt, however, it was
justified in this case.

I certainly noted, and suspect most everyone else did, that in both
cases referenced here, you offered a solution that would work, and
neither of PCD's would have.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
Mar 26 '06 #9

P: n/a
Randy Harris <pl****@send.no.spam> wrote in
news:bJ******************@newssvr14.news.prodigy.c om:
Bob Quintal wrote:
"PCD" <no***@email.com> wrote in
news:In*******************@newsread1.news.atl.eart hlink.net:
Why didn't you advise the OP to write one field at a time to
his CSV file? You thought that was the correct way to export
a couple of hundred thousand records to Excel!!


Look, Pretty Crappy Developer (PCD), I offered a solution
that met all the constraints.It's not the fastest, but it
would work. Code which satisfies customer requirements
without errors is always the correct way.

It's also obvious to me that you lack the knowledge that you
can write field by field to Excel when the
transferSpreadsheet method fails.

Let us see some code for your solution which would properly
execute writing a single recordset to multiple Excel
worksheets.

As for your suggestion in this thread, it has a bug.
If the export specification requires a text qualifier
RevisedLastField:[LastFieldname] & ","
returns "PCD messed up," instead of "PCD messed up",

Put your money where your mouth is.
>
Access always puts the correct number of commas. If you
need an additional comma at the end of every row, add null
as the expression for the calculated rightmost field in the
query.
--
Bob Quintal

PA is y I've altered my email address.


Bob,

We're all trying to keep this PCD mess as unobtrusive as is
practical, I suspect that's why no one has responded. I felt,
however, it was justified in this case.

I certainly noted, and suspect most everyone else did, that in
both cases referenced here, you offered a solution that would
work, and neither of PCD's would have.

Randy, I don't wanna fan any flames either, but when Pretty
Crummy Developer (PCD) starts posting wrong information, I try
to post a working solution, if I know of one. I'll limit myself
to that. I'm sure people come looking for 'working' solutions.

There is no information worse than wrong information.

--
Bob Quintal

PA is y I've altered my email address.
Mar 26 '06 #10

P: n/a
PCD
<<Code which satisfies customer requirements without errors is always the
correct way.>>

So, Stupid A$$, you think that the correct way to export a couple of hundred
thousand records to Excel
is to write one field at a time.

Why didn't you offer him an alternative way to export his couple hundred
thousand records and tell him to write a couple of hundred thousand lines of
code to write one field at a time. That would have satisfied the OP's
requirements too and according to your definition would have been correct.

It would have been just as stupid as your other code!!!

Kiss Arno R and his pal Randy Harris A$$s!!!

PCD

"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
"PCD" <no***@email.com> wrote in
news:In*******************@newsread1.news.atl.eart hlink.net:
Why didn't you advise the OP to write one field at a time to
his CSV file? You thought that was the correct way to export a
couple of hundred thousand records to Excel!!


Look, Pretty Crappy Developer (PCD), I offered a solution that
met all the constraints.It's not the fastest, but it would work.
Code which satisfies customer requirements without errors is
always the correct way.

It's also obvious to me that you lack the knowledge that you can
write field by field to Excel when the transferSpreadsheet
method fails.

Let us see some code for your solution which would properly
execute writing a single recordset to multiple Excel worksheets.

As for your suggestion in this thread, it has a bug.
If the export specification requires a text qualifier
RevisedLastField:[LastFieldname] & ","
returns "PCD messed up," instead of "PCD messed up",

Put your money where your mouth is.


"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
bonehead <se**********@here.net> wrote in
news:e0**********@gondor.sdsu.edu:

Greetings,

I'm using the DoCmd.TransferText method to export the
results of a MS Access query to a csv file. The csv will
then be used to load an Oracle table.

In other systems such as TOAD for Oracle, it's possible to
force an additional comma delimiter after the last column,
if the column is empty on a particular row. Oracle requires
this additional comma on empty rightmost columns, for
importing purposes.

I am unable to see how to do this in the Visual Basic
TransferText method, or in the MS Access export
specifications.

Can this be done in VB/Access, and how?

Access always puts the correct number of commas. If you need
an additional comma at the end of every row, add null as the
expression for the calculated rightmost field in the query.
--
Bob Quintal

PA is y I've altered my email address.



--
Bob Quintal

PA is y I've altered my email address.

Mar 27 '06 #11

P: n/a
"PCD" <no***@email.com> wrote in
news:48******************@newsread2.news.atl.earth link.net:
<<Code which satisfies customer requirements without errors is
always the correct way.>>

So, Stupid A$$, you think that the correct way to export a
couple of hundred thousand records to Excel
is to write one field at a time.
If I'm a stupid A$$, you must be a colostomy. Know what that is,
Pretty Crappy Developer? It's an artificial a$$hole. you aren't
even good enough to be a real A$$hole.

Iterating through collection of records using a loop is a well
documented technique.

Put your money where your A$$///mouthhole is and post some
working code to
1) split a large recordset into 64Ki blocks.
2) create a series of new Excel worksheets in an existing Excel
workbook, the number dependent on the number of 64Ki blocks to
be written.,
3) copy the 64Ki record blocks to the worksheets.

Why didn't you offer him an alternative way to export his
couple hundred thousand records and tell him to write a couple
of hundred thousand lines of code to write one field at a
time. That would have satisfied the OP's requirements too and
according to your definition would have been correct.
All it takes is about 30 lines of code. You must beeven worse
than a Pretty Crappy Developer if you budget a couple of hundred
thousand lines of code for such a routine.
It would have been just as stupid as your other code!!! And again, put your money where your mouth is and tell us why
it's yourt opinion that it's stupid code.

Or are you just jealous that I gave another correct answer,
ruining your chance to fleece another paying customer?
Kiss Arno R and his pal Randy Harris A$$s!!!


Naw, that's not my style. I kiss the girls and make them cry.

--
Bob Quintal

PA is y I've altered my email address.
Mar 27 '06 #12

P: n/a
PCD
So, Stupid A$$, You are now saying iterating through collection of a couple
of hundred thousand records and writing one field at a time to Excel is a
well documented technique. Well I am sure all the readers of the newsgroups
would like to see that documentation. The only place anyone would see such a
stupid technique would be on your website!!! You probably have a couple of
hundred thousand lines of
code to write one field at a time to export a couple hundred thousand
records to Excel on your website too since you feel that anything that works
is "smart coding"!

Kiss Arno R and his pal Randy Harris A$$s!!!

And by the way .... those are not cries you heard. They are rolling laughter
at your stupid recommendations!!!

If I'm a stupid A$$, you must be a colostomy. Know what that is,
Pretty Crappy Developer? It's an artificial a$$hole. you aren't
even good enough to be a real A$$hole.
"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135... "PCD" <no***@email.com> wrote in
news:48******************@newsread2.news.atl.earth link.net:
<<Code which satisfies customer requirements without errors is
always the correct way.>>

So, Stupid A$$, you think that the correct way to export a
couple of hundred thousand records to Excel
is to write one field at a time.

If I'm a stupid A$$, you must be a colostomy. Know what that is,
Pretty Crappy Developer? It's an artificial a$$hole. you aren't
even good enough to be a real A$$hole.

Iterating through collection of records using a loop is a well
documented technique.

Put your money where your A$$///mouthhole is and post some
working code to
1) split a large recordset into 64Ki blocks.
2) create a series of new Excel worksheets in an existing Excel
workbook, the number dependent on the number of 64Ki blocks to
be written.,
3) copy the 64Ki record blocks to the worksheets.

Why didn't you offer him an alternative way to export his
couple hundred thousand records and tell him to write a couple
of hundred thousand lines of code to write one field at a
time. That would have satisfied the OP's requirements too and
according to your definition would have been correct.

All it takes is about 30 lines of code. You must beeven worse
than a Pretty Crappy Developer if you budget a couple of hundred
thousand lines of code for such a routine.
It would have been just as stupid as your other code!!!

And again, put your money where your mouth is and tell us why
it's yourt opinion that it's stupid code.

Or are you just jealous that I gave another correct answer,
ruining your chance to fleece another paying customer?

Kiss Arno R and his pal Randy Harris A$$s!!!


Naw, that's not my style. I kiss the girls and make them cry.

--
Bob Quintal

PA is y I've altered my email address.

Mar 27 '06 #13

P: n/a
"PCD" <no***@email.com> wrote in
news:dL******************@newsread1.news.atl.earth link.net:
So, Stupid A$$, You are now saying iterating through
collection of a couple of hundred thousand records and writing
one field at a time to Excel is a well documented technique.
Well I am sure all the readers of the newsgroups would like to
see that documentation. The only place anyone would see such a
stupid technique would be on your website!!! You probably have
a couple of hundred thousand lines of
code to write one field at a time to export a couple hundred
thousand records to Excel on your website too since you feel
that anything that works is "smart coding"!

First of all, I do not have a website. What difference does it
make if we talk of Excel or a textfile or a SQL server table.
The data gets moved a character at a time, usually even one bit
at a time over a network. You calling me an A$$ only adds to the
proof that you don't know what you are doing. You have no
business posting answers in this newsgroup.

Go away. Don't go away mad, just go away.

--
Bob Quintal

PA is y I've altered my email address.
Mar 27 '06 #14

P: n/a
PCD
Hey, Hey, Hey ........

Today at mid morning there's a new post with the title "Code To Export To
Multiple Excel Worksheets Within Single Workbook". Here's a chance for you
to shine again!!! Why don't you recommend to the OP your "WELL DOCUMENTED"
technique of exporting one field at a time even if there are a couple of
hundred thousand records. According to you this is a perfectly correct
solution (even if the OP grows old waiting for it to complete). Arno R might
even complement you on your brilliant solution, INDEED!!!

PCD


"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn*********************@207.35.177.135...
"PCD" <no***@email.com> wrote in
news:dL******************@newsread1.news.atl.earth link.net:
So, Stupid A$$, You are now saying iterating through
collection of a couple of hundred thousand records and writing
one field at a time to Excel is a well documented technique.
Well I am sure all the readers of the newsgroups would like to
see that documentation. The only place anyone would see such a
stupid technique would be on your website!!! You probably have
a couple of hundred thousand lines of
code to write one field at a time to export a couple hundred
thousand records to Excel on your website too since you feel
that anything that works is "smart coding"!

First of all, I do not have a website. What difference does it
make if we talk of Excel or a textfile or a SQL server table.
The data gets moved a character at a time, usually even one bit
at a time over a network. You calling me an A$$ only adds to the
proof that you don't know what you are doing. You have no
business posting answers in this newsgroup.

Go away. Don't go away mad, just go away.

--
Bob Quintal

PA is y I've altered my email address.

Mar 27 '06 #15

This discussion thread is closed

Replies have been disabled for this discussion.