CSV Export: Force Trailing Comma?

[bonehead]

Greetings,

I'm using the DoCmd.TransferT ext 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?

[PCD]

Re: CSV Export: Force Trailing Comma?

In your query, use a calculated field that looks like this:
RevisedLastFiel d:[LastFieldname] & ","

Uncheck the 'Show' box in the LastFieldName column and be sure the 'Show'
box is checked in the RevisedLastFiel d column. You want to export
RevisedLastFiel d 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
resource@pcdata sheet.com


"bonehead" <sendmenospam@h ere.net> wrote in message
news:e04c95$n74 $1@gondor.sdsu. edu...[color=blue]
> Greetings,
>
> I'm using the DoCmd.TransferT ext 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?[/color]

[Bob Quintal]

Re: CSV Export: Force Trailing Comma?

bonehead <sendmenospam@h ere.net> wrote in
news:e04c95$n74 $1@gondor.sdsu. edu:
[color=blue]
> Greetings,
>
> I'm using the DoCmd.TransferT ext 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?
>[/color]
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.

[StopThisAdvertising]

Re: CSV Export: Force Trailing Comma?


"PCD" <notmy@email.co m> schreef in bericht news:sgjVf.1776 3$S25.9597@news read1.news.atl. earthlink.net.. .

-- [color=blue]
> 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 ????
> resource@pcdata sheet.com
>
> [/color]

--
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

[PCD]

Re: CSV Export: Force Trailing Comma?

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
resource@pcdata sheet.com


"StopThisAdvert ising" <StopThisAdvert ising@DataShit> wrote in message
news:4425dda2$0 $2032$ba620dc5@ text.nova.plane t.nl...

"PCD" <notmy@email.co m> schreef in bericht
news:sgjVf.1776 3$S25.9597@news read1.news.atl. earthlink.net.. .

--[color=blue]
> 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 ????
> resource@pcdata sheet.com
>
>[/color]

--
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

[PCD]

Re: CSV Export: Force Trailing Comma?

What a "BONEHEAD" response!!!!


"Bob Quintal" <rquintal@sympa tico.ca> wrote in message
news:Xns9791B3A F38F2EBQuintal@ 207.35.177.135. ..[color=blue]
> bonehead <sendmenospam@h ere.net> wrote in
> news:e04c95$n74 $1@gondor.sdsu. edu:
>[color=green]
>> Greetings,
>>
>> I'm using the DoCmd.TransferT ext 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?
>>[/color]
> 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.[/color]

[PCD]

Re: CSV Export: Force Trailing Comma?

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" <rquintal@sympa tico.ca> wrote in message
news:Xns9791B3A F38F2EBQuintal@ 207.35.177.135. ..[color=blue]
> bonehead <sendmenospam@h ere.net> wrote in
> news:e04c95$n74 $1@gondor.sdsu. edu:
>[color=green]
>> Greetings,
>>
>> I'm using the DoCmd.TransferT ext 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?
>>[/color]
> 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.[/color]

[Bob Quintal]

Re: CSV Export: Force Trailing Comma?

"PCD" <notmy@email.co m> wrote in
news:InlVf.1781 4$S25.16819@new sread1.news.atl .earthlink.net:
[color=blue]
> 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!!
>[/color]

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 transferSpreads heet
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
RevisedLastFiel d:[LastFieldname] & ","
returns "PCD messed up," instead of "PCD messed up",

Put your money where your mouth is.

[color=blue]
>
> "Bob Quintal" <rquintal@sympa tico.ca> wrote in message
> news:Xns9791B3A F38F2EBQuintal@ 207.35.177.135. ..[color=green]
>> bonehead <sendmenospam@h ere.net> wrote in
>> news:e04c95$n74 $1@gondor.sdsu. edu:
>>[color=darkred]
>>> Greetings,
>>>
>>> I'm using the DoCmd.TransferT ext 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?
>>>[/color]
>> 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.[/color]
>
>
>[/color]



--
Bob Quintal

PA is y I've altered my email address.

[Randy Harris]

Re: CSV Export: Force Trailing Comma?

Bob Quintal wrote:[color=blue]
> "PCD" <notmy@email.co m> wrote in
> news:InlVf.1781 4$S25.16819@new sread1.news.atl .earthlink.net:
>[color=green]
>> 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!!
>>[/color]
>
> 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 transferSpreads heet
> 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
> RevisedLastFiel d:[LastFieldname] & ","
> returns "PCD messed up," instead of "PCD messed up",
>
> Put your money where your mouth is.
>[color=green][color=darkred]
>>>>
>>> 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.[/color][/color]
>
>[/color]


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.

[Bob Quintal]

Re: CSV Export: Force Trailing Comma?

Randy Harris <please@send.no .spam> wrote in
news:bJyVf.4262 4$_S7.5171@news svr14.news.prod igy.com:
[color=blue]
> Bob Quintal wrote:[color=green]
>> "PCD" <notmy@email.co m> wrote in
>> news:InlVf.1781 4$S25.16819@new sread1.news.atl .earthlink.net:
>>[color=darkred]
>>> 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!!
>>>[/color]
>>
>> 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
>> transferSpreads heet 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
>> RevisedLastFiel d:[LastFieldname] & ","
>> returns "PCD messed up," instead of "PCD messed up",
>>
>> Put your money where your mouth is.
>>[color=darkred]
>>>>>
>>>> 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.[/color]
>>
>>[/color]
>
>
> 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.
>[/color]
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.

[PCD]

Re: CSV Export: Force Trailing Comma?

<<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" <rquintal@sympa tico.ca> wrote in message
news:Xns9792471 0D4AAEBQuintal@ 207.35.177.135. ..[color=blue]
> "PCD" <notmy@email.co m> wrote in
> news:InlVf.1781 4$S25.16819@new sread1.news.atl .earthlink.net:
>[color=green]
>> 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!!
>>[/color]
>
> 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 transferSpreads heet
> 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
> RevisedLastFiel d:[LastFieldname] & ","
> returns "PCD messed up," instead of "PCD messed up",
>
> Put your money where your mouth is.
>
>[color=green]
>>
>> "Bob Quintal" <rquintal@sympa tico.ca> wrote in message
>> news:Xns9791B3A F38F2EBQuintal@ 207.35.177.135. ..[color=darkred]
>>> bonehead <sendmenospam@h ere.net> wrote in
>>> news:e04c95$n74 $1@gondor.sdsu. edu:
>>>
>>>> Greetings,
>>>>
>>>> I'm using the DoCmd.TransferT ext 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.[/color]
>>
>>
>>[/color]
>
>
>
> --
> Bob Quintal
>
> PA is y I've altered my email address.[/color]

[Bob Quintal]

Re: CSV Export: Force Trailing Comma?

"PCD" <notmy@email.co m> wrote in
news:48GVf.1051 3$sL2.2439@news read2.news.atl. earthlink.net:
[color=blue]
> <<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.
>[/color]
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.

[color=blue]
> 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.
>[/color]
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.
[color=blue]
> It would have been just as stupid as your other code!!![/color]
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?[color=blue]
>
> Kiss Arno R and his pal Randy Harris A$$s!!![/color]

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.

[PCD]

Re: CSV Export: Force Trailing Comma?

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 !!!

[color=blue]
>[/color]
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" <rquintal@sympa tico.ca> wrote in message
news:Xns9792C96 38BF21BQuintal@ 207.35.177.135. ..[color=blue]
> "PCD" <notmy@email.co m> wrote in
> news:48GVf.1051 3$sL2.2439@news read2.news.atl. earthlink.net:
>[color=green]
>> <<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.
>>[/color]
> 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.
>
>[color=green]
>> 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.
>>[/color]
> 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.
>[color=green]
>> It would have been just as stupid as your other code!!![/color]
> 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?[color=green]
>>
>> Kiss Arno R and his pal Randy Harris A$$s!!![/color]
>
> 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.[/color]

[Bob Quintal]

Re: CSV Export: Force Trailing Comma?

"PCD" <notmy@email.co m> wrote in
news:dLHVf.1825 4$S25.6857@news read1.news.atl. earthlink.net:
[color=blue]
> 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"!
>[/color]
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.

[PCD]

Re: CSV Export: Force Trailing Comma?

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" <rquintal@sympa tico.ca> wrote in message
news:Xns9792E20 5DF97BQuintal@2 07.35.177.135.. .[color=blue]
> "PCD" <notmy@email.co m> wrote in
> news:dLHVf.1825 4$S25.6857@news read1.news.atl. earthlink.net:
>[color=green]
>> 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"!
>>[/color]
> 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.[/color]