473,734 Members | 2,764 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

CSV Export: Force Trailing Comma?

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?
Mar 25 '06 #1
14 6439
PCD
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
re******@pcdata sheet.com
"bonehead" <se**********@h ere.net> wrote in message
news:e0******** **@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?

Mar 25 '06 #2
bonehead <se**********@h ere.net> wrote in
news:e0******** **@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.
Mar 25 '06 #3

"PCD" <no***@email.co m> schreef in bericht news:sg******** **********@news read1.news.atl. earthlink.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******@pcdata sheet.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******@pcdata sheet.com
"StopThisAdvert ising" <StopThisAdvert ising@DataShit> wrote in message
news:44******** **************@ text.nova.plane t.nl...

"PCD" <no***@email.co m> schreef in bericht
news:sg******** **********@news read1.news.atl. earthlink.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******@pcdata sheet.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******@sympa tico.ca> wrote in message
news:Xn******** **************@ 207.35.177.135. ..
bonehead <se**********@h ere.net> wrote in
news:e0******** **@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.

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******@sympa tico.ca> wrote in message
news:Xn******** **************@ 207.35.177.135. ..
bonehead <se**********@h ere.net> wrote in
news:e0******** **@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.

Mar 26 '06 #7
"PCD" <no***@email.co m> wrote in
news:In******** ***********@new sread1.news.atl .earthlink.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 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.


"Bob Quintal" <rq******@sympa tico.ca> wrote in message
news:Xn******** **************@ 207.35.177.135. ..
bonehead <se**********@h ere.net> wrote in
news:e0******** **@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.



--
Bob Quintal

PA is y I've altered my email address.
Mar 26 '06 #8
Bob Quintal wrote:
"PCD" <no***@email.co m> wrote in
news:In******** ***********@new sread1.news.atl .earthlink.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 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.

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******** **********@news svr14.news.prod igy.com:
Bob Quintal wrote:
"PCD" <no***@email.co m> wrote in
news:In******** ***********@new sread1.news.atl .earthlink.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
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.
>
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

27
4569
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
12851
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 that? Thanks for any help. Atse
5
11035
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 setting stDataSource and stFileName to the desired values. Most of the time it works, but occasionally, typically as code changes are being made to the module, the following message appears:
1
2161
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 allowed to re-import their archived data. This all works fine, until we have to update the application for international users. The original csv files sent to users contain dates and numbers in US format. However, once imported, the numbers appear...
7
3837
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 and CustomerPO -RecordType -CustomerID -> -OrderDate
5
30280
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 way to do it. I have searched this newsgroup and the only suggestion found was to cast all column to strings and do a union, or to add the first row afterwards and I saw one suggestion where it was to create a unix script. Does anybody knows...
1
1675
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 need it not prompt me for comma delimited or fixed length as i only need the comma delimited text. It automatically convert the table into comma delimited text. Thanks.
9
7712
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 is 8 characters long. This field needs to be exported as pic(15) padded in the front with 0's (zeros). The next field an ID name that is 15 characters that needs to be exported as pic(20) padded with trailing spaces. There are about 5 fields in...
6
9893
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, #ifdef HAVE_SOMETHING
0
8776
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9449
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9310
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9236
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9182
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6735
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6031
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4550
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2724
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.