473,320 Members | 1,990 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,320 software developers and data experts.

export and dateformat to produce CSV file

Hello,

If I export a file containing date field,
export to "/james/j.txt" of DEL
select a1,adate from a

The date is represented in YYYY/MM/DD format as expected.

Output I need is to be in "yyyymmdd" without delimiter. MODIFIED BY
DATESISO option has a delimiter.

I am getting the same ouput despite using the following:
export to "/james/j.txt" of DEL
select a1,replace(char(adate),'/','') from a
From using just the select statement and running from command line with

-z option, I am able to redirect output to a file, but it is not in CSV
format. This is a big SQL and I do not want to manually insert commas
to separate in select statement.

Can someone throw light on this. Specifically for the export with
modified by, and for using select in command line and get a CSV format.
Thanks a bunch.

Nov 12 '05 #1
2 11414

<hi****@gmail.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...
Hello,

If I export a file containing date field,
export to "/james/j.txt" of DEL
select a1,adate from a

The date is represented in YYYY/MM/DD format as expected.

Output I need is to be in "yyyymmdd" without delimiter. MODIFIED BY
DATESISO option has a delimiter.

I am getting the same ouput despite using the following:
export to "/james/j.txt" of DEL
select a1,replace(char(adate),'/','') from a
From using just the select statement and running from command line with

-z option, I am able to redirect output to a file, but it is not in CSV
format. This is a big SQL and I do not want to manually insert commas
to separate in select statement.

Can someone throw light on this. Specifically for the export with
modified by, and for using select in command line and get a CSV format.
Thanks a bunch.


The DB2 documentation sheds some light on this - specifically footnote 3:

http://publib.boulder.ibm.com/infoce...e/r0011046.htm

If you're using DB2 v8 FP2 or later, the following command will do what you
want:

EXPORT TO "/james/j.txt" OF DEL
MODIFIED BY TIMESTAMPFORMAT="YYYYMMDD"
SELECT a1, adate FROM a

If you have Perl, you can easily remove the delimiters using the following
command:

perl -pi -e 's#/##g' /james/j.txt

--
Matt Emmerton
Nov 12 '05 #2
EXPORT TO "/james/j.txt" OF DEL
MODIFIED BY nochardel
SELECT a1, adate FROM a

The above works fine:
I/p data:
c1 bdate
------------------
1 2005-01-28

2 2005-01-28

21 2005-01-28

22 2005-01-28

41 2005-01-28

42 2005-01-28
Output in csv format picked up in spreadsheet:

1 20050128
2 20050128
21 20050128
22 20050128
41 20050128
42 20050128
Matt Emmerton wrote:
<hi****@gmail.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...
Hello,

If I export a file containing date field,
export to "/james/j.txt" of DEL
select a1,adate from a

The date is represented in YYYY/MM/DD format as expected.

Output I need is to be in "yyyymmdd" without delimiter. MODIFIED BY
DATESISO option has a delimiter.

I am getting the same ouput despite using the following:
export to "/james/j.txt" of DEL
select a1,replace(char(adate),'/','') from a
From using just the select statement and running from command line
with -z option, I am able to redirect output to a file, but it is not in CSV format. This is a big SQL and I do not want to manually insert commas to separate in select statement.

Can someone throw light on this. Specifically for the export with
modified by, and for using select in command line and get a CSV format. Thanks a bunch.
The DB2 documentation sheds some light on this - specifically

footnote 3:
http://publib.boulder.ibm.com/infoce...e/r0011046.htm
If you're using DB2 v8 FP2 or later, the following command will do what you want:

EXPORT TO "/james/j.txt" OF DEL
MODIFIED BY TIMESTAMPFORMAT="YYYYMMDD"
SELECT a1, adate FROM a

If you have Perl, you can easily remove the delimiters using the following command:

perl -pi -e 's#/##g' /james/j.txt

--
Matt Emmerton


Nov 12 '05 #3

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

Similar topics

205
by: Jeremy Siek | last post by:
CALL FOR PAPERS/PARTICIPATION C++, Boost, and the Future of C++ Libraries Workshop at OOPSLA October 24-28, 2004 Vancouver, British Columbia, Canada http://tinyurl.com/4n5pf Submissions
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...
16
by: David Lauberts | last post by:
Hi Wonder if someone has some words of wisdom. I have a access 2002 form that contains 2 graph objects that overlay each other and would like to export them as a JPEG to use in a presentation....
3
by: Jorge Cecílio | last post by:
Hi! I would like to export some MS-Access reports output to pdf. However, the only possibility offered by Access (afaik) for me to export formatted output is snp (snapshot) (I use MS-Office...
4
by: newtophp2000 | last post by:
Hello, I receive a file containing some character fields along with a date. The date values in the file are formatted as "dd/mm/yy", that is 2-digit day, 2-digit month, and 2-digit year. The...
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: davidwelli | last post by:
Hello, I have a Access 200 format database that contains contact details and a picture for each record. The contact details are held in one table and the images are held in another as OLE...
1
zigman68
by: zigman68 | last post by:
I have a problem with the DateFormat() Function that is driving me nuts! I am downloading a csv and then parsing through it, then inserting into a SQL 2005 DB. Here is the problem. ... A number of...
2
by: Marisol2 | last post by:
I have some queries in Access 2003 db that I have setup to display as pivots. I can go into design pivot table view and click on and then click on Export to Office MicroSoft Excel. The problem is I...
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: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.