Hello,
Is there a way to export the data from some tables, along with their
corresponding column names? I know I can use "export" command to export
the data from a table to a DEL file (the users need to be able to read
the file directly, which leaves the choice of a DEL file), but only the
data will be in the file. The users would like to see the column names
side by side with the data as well. I wonder if there is an easy way to
do so without too much coding.
Thanks. 13 27577
Use ixf format will have table DDL, include the column name. But not
readable directly.
The easy way is like this
1. db2 export to tablename.del
2. db2 select from tablename > column.del
3. copy column.del + tablename.del
mjf wrote: Hello, Is there a way to export the data from some tables, along with their corresponding column names? I know I can use "export" command to export the data from a table to a DEL file (the users need to be able to read the file directly, which leaves the choice of a DEL file), but only the data will be in the file. The users would like to see the column names side by side with the data as well. I wonder if there is an easy way to do so without too much coding. Thanks.
You may be able to use a UNION query that SELECTs the COLUMN names on
top.
SELECT 'Column1', 'Column 2', Column 3'....
UNION ALL
SELECT ......
B.
Hello Brian, the method you suggested is very promising. Thanks a lot!
I did it to a test table that has the following DDL:
db2 "describe table test"
Column Type Type
name schema name Length
Scale Nulls
------------------------------ --------- ------------------ --------
----- ------
COL1 SYSIBM SMALLINT 2
0 Yes
COL2 SYSIBM VARCHAR 5
0 Yes
COL3 SYSIBM VARCHAR 5
0 Yes
db2 "export to test.del of del modified by chardel'' coldel; decpt,
select col1, 'col1 ', col2, 'col2 ', col3, 'col3 ' from test"
The export was successful, and here is the content in test.del:
1;'col1 ';'test1';'col2 ';'val1';'col3 '
2;'col1 ';'test2';'col2 ';'val2';'col3 '
3;'col1 ';'test3';'col2 ';'val3';'col3 '
I added the blanks in the column names so the users can spot each pair
of value/column-name easily. When there are lots of columns in the
table, the above would wrap around to next lines and made it hard to
read, though. Do you know any way of formatting it like the following?
1;'col1 ';
'test1';'col2 ';
'val1';'col3 '
2;'col1 ';
'test2';'col2 ';
'val2';'col3 '
3;'col1 ';
'test3';
'col2 ';
'val3';'col3 '
I need to set up a daily cron job to do such a file, so I need to do
everything automatically. I guess I can always write a PERL script to
separate the pairs, but wonder if there is yet another easy way to do
it.
Thanks again.
I don't think so. It's a bit beyond my knowledge of db2.
If you are using a cron job, perl seems easy enough, or any of those
nifty tools that filter columns in order.
B.
mjf wrote: Hello Brian, the method you suggested is very promising. Thanks a lot! I did it to a test table that has the following DDL:
db2 "describe table test"
Column Type Type name schema name Length Scale Nulls ------------------------------ --------- ------------------ -------- ----- ------ COL1 SYSIBM SMALLINT 2 0 Yes COL2 SYSIBM VARCHAR 5 0 Yes COL3 SYSIBM VARCHAR 5 0 Yes
db2 "export to test.del of del modified by chardel'' coldel; decpt, select col1, 'col1 ', col2, 'col2 ', col3, 'col3 ' from test"
The export was successful, and here is the content in test.del: 1;'col1 ';'test1';'col2 ';'val1';'col3 ' 2;'col1 ';'test2';'col2 ';'val2';'col3 ' 3;'col1 ';'test3';'col2 ';'val3';'col3 '
I added the blanks in the column names so the users can spot each pair of value/column-name easily. When there are lots of columns in the table, the above would wrap around to next lines and made it hard to read, though. Do you know any way of formatting it like the following?
1;'col1 '; 'test1';'col2 '; 'val1';'col3 '
2;'col1 '; 'test2';'col2 '; 'val2';'col3 '
3;'col1 '; 'test3'; 'col2 '; 'val3';'col3 '
You could simply concatenate all values into a single, long string and then
use some logic to insert the line breaks. A dedicated UDF comes to mind.
I would probably code it in Java or C/C++ as it is a bit easier to iterate
over strings that way.
--
Knut Stolze
DB2 Information Integration Development
IBM Germany
If he's going to use a UDF, wouldn't a FOR loop do it?
Someting like:
FOR Loop AS SELECT col1, col2, ...
Long_String = Long_String || Loop.Col1 || Loop.Col2...
END
Or would that take too long?
B.
In article <11************ **********@o13g 2000cwo.googleg roups.com>,
mjf (mi****@excite. com) says... db2 "export to test.del of del modified by chardel'' coldel; decpt, select col1, 'col1 ', col2, 'col2 ', col3, 'col3 ' from test"
On windows I would add a column with x'0D0A' to get what you want, on
Unix X'0A' should work.
This should work (not tested):
db2 "export to test.del of del
modified by chardel'' coldel; decpt,
select col1, 'col1 ', x'0A',
col2, 'col2 ', x'0A',
col3, 'col3 '
from test"
It's almost there!
I ran the following statement:
db2 "export to test.del of del modified by chardel'' coldel; decpt,
select 'col1', col1, x'0A', 'col2', col2, x'0A', 'col3', col3 from
test"
And the result is:
'col1';1;'
';'col2';'test1 ';'
';'col3';'val1'
'col1';2;'
';'col2';'test2 ';'
';'col3';'val2'
'col1';3;'
';'col2';'test3 ';'
';'col3';'val3'
I guess that's the best we can do without using Java or C/C++?
Thank all of you very much for your help!
I posted the above reply but didn't realize someone else has logged on
to gmail on the same machine and the email and nickname of the message
became his.
By the way, does anyone know how to delete a posted message? I'd like
to delete the above message and re-post it using my own id.
Thanks.
mjf This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Joe Gazda |
last post by:
I'm a relative newbie to PHP, but have been able to put together some PHP
code to generate a CSV/XLS file from a Query result. Now, I would like to
include custom column names instead of the MySQL column table names. I know
that there are codes to generate tabs and carriage returns, but can't find
anything about including "commas" in a string to output to the file to
separate the custom field names. I'd appreciate some help with a line of...
|
by: Bart Van der Donck |
last post by:
Hello,
I am using MySQL 4.0.
Say that I have a table named "mytable" having a column "ID" and a
column "test columnname".
When dumping:
mysqldump --opt DATABASE -uUSER -hHOST -pPASS > myfile.txt
|
by: noor |
last post by:
Hi
I have been working since 2 days to device a method to export sql table
into csv format. I have tried using bcp with format option to keep the
column names but I'm unable to transfer the file with column names. and
also I'm having problems on columns having decimal data.
Can any one suggest me how to automate data transfer(by using SP) and
retaining column names.
Thanks
Noor
|
by: John T. McCraw |
last post by:
How can I retrieve just the column names from an Access table. I don't
need the data, just the column names.
|
by: Joe Griffith |
last post by:
I'm using a Win Forms Data Grid View control in unbound mode.
When I add columns using the wizard the first item is the column name.
Everything works fine.
However, if you return to the columns collection and look at the
poperties of an individual column the name property is not in the list.
Is there a way to change (or even find) the name of a column in a data
grid view other than looking at it in the *.Designer.vb file?
| |
by: seddy |
last post by:
Hello !
I`m kinda new to it so I found this `job` very hard therefor I ask for Your help.
So, the thing is...
I have this XML file ( http://www.izishop.net/export.php )
which I need to open with php and read it, and insert some fields those fields on my MySQL database.
Well... i found some usefull informations which might help me here:
(http://www.thescripts.com/forum/thread2005.html)
Take that data
|
by: Designing Solutions WD |
last post by:
Hello,
I have some questions on my options available.
I have to export some tables to csv files to enable another department
to process the files. What I need is a way to do this in ms sql
though a stored proc with quoted identifiers and column names as
heads. I cannot figure out how to do this.
Can anybody give me some options that would be the best options.
|
by: sylviasepeng |
last post by:
Hi,
I am beginner in Oracle and have the following problem.
am working with reports and forms and now have to add a column and when i check the select statement to see if the the column has been selected i get this sort of a query: select DATA.product_description
DATA.product_code
from (select gl.product_description
...
|
by: christianlott1 |
last post by:
I want to provide users with an interface to create a custom merge
(all in Access, not Word). User will put in a set of brackets ("<>")
in a memo field and when they click the merge button it will grab the
fields listed in a tabular subform and merge it with their memo text.
I was able to retrieve the code to list the column names of a table.
I constructed the code to iterate the tabular subform rows and replace
the brackets with the row...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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...
| |
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...
|
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,...
|
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...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |