What is the best way of loading data which has ENTER/new line (ie, ^M)
character as the part of the data?
say I have data as follows for table TAB1 (cust#, datetime, memo,
child)
7579, "2001-09-14 15:06:26", "sample memo with ^M
New Line^M
",1234
Thanks, 5 8171
Prince Kumar wrote: What is the best way of loading data which has ENTER/new line (ie, ^M) character as the part of the data?
say I have data as follows for table TAB1 (cust#, datetime, memo, child)
7579, "2001-09-14 15:06:26", "sample memo with ^M New Line^M ",1234
Use the load option MODIFIED BY DELPRIORITYCHAR
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Thanks Ian.
That should fix the issue.
Prince.
Ian <ia*****@mobileaudio.com> wrote in message news:<3f**********@corp.newsgroups.com>... Prince Kumar wrote:
What is the best way of loading data which has ENTER/new line (ie, ^M) character as the part of the data?
say I have data as follows for table TAB1 (cust#, datetime, memo, child)
7579, "2001-09-14 15:06:26", "sample memo with ^M New Line^M ",1234
Use the load option MODIFIED BY DELPRIORITYCHAR
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Thanks Ian, that worked perfect.
Now I have another issue when loading "" to a char field.
table test (
cust# integer
rec# integer
status char(1) -- allows only 'T', 'F' or null
)
11557,1,"T"
11611,1,"T"
11612,1,"" --> has to be loaded as NULL, but getting loaded as
chr(32), a blank space
11613,1,"F"
When loading the data, the "" values are getting loaded as ' ' [ie
chr(32)]. Is there any modifier to direct the LOAD util to load this
as NULL?
Thanks,
Prince.
Ian <ia*****@mobileaudio.com> wrote in message news:<3f**********@corp.newsgroups.com>... Prince Kumar wrote:
What is the best way of loading data which has ENTER/new line (ie, ^M) character as the part of the data?
say I have data as follows for table TAB1 (cust#, datetime, memo, child)
7579, "2001-09-14 15:06:26", "sample memo with ^M New Line^M ",1234
Use the load option MODIFIED BY DELPRIORITYCHAR
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Prince Kumar wrote: 11557,1,"T" 11611,1,"T" 11612,1,"" --> has to be loaded as NULL, but getting loaded as chr(32), a blank space 11613,1,"F"
When loading the data, the "" values are getting loaded as ' ' [ie chr(32)]. Is there any modifier to direct the LOAD util to load this as NULL?
This is a data format issue, not a LOAD issue.
DB2 treats "" as a 0-length string because it has character delimiters.
A value with no character delimiters will be loaded as NULL. Your
record should look like this:
11612,1,
If you had another int column after STATUS, your record might look like:
11612,1,,14
Good luck,
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Thanks Ian,
Thats what I end up doing finally. I had a program which unloads the
data from oracle. I fixed the progam, not to enclose the data within
quotes if it is null.
Prince.
Ian <ia*****@mobileaudio.com> wrote in message news:<3f**********@corp.newsgroups.com>... Prince Kumar wrote:
11557,1,"T" 11611,1,"T" 11612,1,"" --> has to be loaded as NULL, but getting loaded as chr(32), a blank space 11613,1,"F"
When loading the data, the "" values are getting loaded as ' ' [ie chr(32)]. Is there any modifier to direct the LOAD util to load this as NULL?
This is a data format issue, not a LOAD issue.
DB2 treats "" as a 0-length string because it has character delimiters. A value with no character delimiters will be loaded as NULL. Your record should look like this:
11612,1,
If you had another int column after STATUS, your record might look like:
11612,1,,14
Good luck,
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 100,000 Newsgroups - 19 Different Servers! =----- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: jen_designs |
last post by:
I have a html page with an embeded video and a drop list. What I would
like to do is have the user select a video from the drop list, then
replace the embeded video on the screen. Is there a way...
|
by: Johnson, Shaunn |
last post by:
Howdy:
Running PostgreSQL 7.3.3 on RedHat Linux 7.2
I'm trying to load data from a text file that
has an odd character in it (^@). From what I
could tell, it's a NULL character. How can I...
|
by: Uthuras |
last post by:
Greetings,
Machine : Pentium IV
Os Windows 2000 server
Product : DB2 UDB Release : 7.2
We are fail to load the following data file format into db2 database
table that has long varchar...
|
by: B. Wood |
last post by:
I have a small program (see below) that demonstrates an error I am
getting when I build the program on a linux platform.
I first initialize an unsigned char array (lines 16).
I then initialize...
|
by: Julia Hu |
last post by:
Hi,
I have a datagrid, and in different rows I need to
programmatically bind different type of controls and load
data into these controls. For example,in the first row I
need to bind data into a...
|
by: tojigneshshah |
last post by:
Hi,
I am loading data from ascii delimiter file and some of the rows are
getting rejected while loading.
1.0|11487.0|FQ|105061.0|332735.0|01|X.NNIE HATFIELD|1992-06-25
00:00:00|1992-...
|
by: 3Dfelix |
last post by:
Hello, I'm starting with Visual Web Developper and ASP.NET 2.0.
In order to test my remote server, I created an "hello word" aplication,
that is working on local server. I have copied all...
|
by: imam888 |
last post by:
How do I load "varchar for bit" data in hexadecimal format into a DB2
table ?
Table definition
===========
create table test
( i integer, v varchar for bit data (16))
db2 "IMPORT FROM...
|
by: =?Utf-8?B?dHRocm9uZQ==?= |
last post by:
Hi,
I am querying sql server and loading the resulting data into Excel.
For character and integer data types, it's working well, but when I pull
decimal data and attempt to load it into Excel...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
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...
|
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: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
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: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
| |