473,288 Members | 1,704 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

CSV Export: Force Trailing Comma?

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

"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
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
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
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
"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
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
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
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
"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
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
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

27
by: Alberto Vera | last post by:
Hello: I have the next structure: How Can I make it using Python? How Can I update the value of 6?
14
by: atse | last post by:
Hi experts, I retrieve data from the database and display on ASP, then I export these data to a file, like Excel (the best) or text file. Is it possible? I think it is possible, but how can I do...
5
by: Tim Eliot | last post by:
Just wondering if anyone has hit the following issue and how you might have sorted it out. I am using the command: DoCmd.TransferText acExportMerge, , stDataSource, stFileName, True after...
1
by: cab2 | last post by:
We currently have an application built in Access that takes in a csv file using docmd.transfertext. We allow users to export their data to a file for later use (csv format). Users are also...
7
by: mattc66 via AccessMonster.com | last post by:
I need to create an EDI 850 Record Set. The EDI 850 Record set I am creating consists of 2-5 seperate tables all linked by the CustomerID and CustomerPO. Tables - tblOrder - >Link CustomerID...
5
by: no_spam_for_gman | last post by:
I have been using the export command for quite some times to export comma delimited file. I have always been looking at how to generate the first row with the column header but never found a clean...
1
by: colleen1980 | last post by:
Hi: Can any one please tell me how to i export data thru VBA code as same when i right click on any table and then it ask me to export data as comma delimited. Can i do the same thru VBA code. I...
9
by: NEWSGROUPS | last post by:
I have data in a table in an Access 2000 database that needs to be exported to a formatted text file. For instance, the first field is an account number that is formatted in the table as text and...
6
by: Jan Sneeuwman | last post by:
Hello, I am working on a library in C++, and the library goes with a header file with a trailing comma at the end of some enumeration. Like this: enum Abc { ENUM_ONE, ENUM_TWO ENUM_THREE,...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.