473,320 Members | 1,978 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.

load data with embeded New Line character

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,
Nov 12 '05 #1
5 8171
Ian
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! =-----
Nov 12 '05 #2
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! =-----

Nov 12 '05 #3
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! =-----

Nov 12 '05 #4
Ian
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! =-----
Nov 12 '05 #5
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! =-----

Nov 12 '05 #6

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

Similar topics

2
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...
1
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...
1
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...
7
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...
18
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...
7
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-...
2
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...
2
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...
1
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...
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...
1
isladogs
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...
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)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....
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
0
isladogs
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...

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.