Thanks, it is working for the mentioned format.
Is there any method for importing directly the spooled file from oracle without
changing the file format into the required format like using tab and newline.
thanks,
narasimha
-----Original Message-----
From: Andrey Hristov [mailto:[EMAIL PROTECTED]
Sent: Thu 9/30/2004 2:01 AM
To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS)
Cc: [EMAIL PROTECTED]
Subject: Re: importing data into mysql from oracle using a text file
Hi,
I did imported your data im my server but I had to do some changes to
sample.txt.
I have replaced in a text editor "<tab>\t" with empty string. <tab> is a real
tab
while \t is just a text. Additional change was to replace "\n" (which is text
but not
newline with empty string). The I did the following (before that I have
created the
table) :
mysql> load data local infile "/home/andrey/Desktop/sample2.txt" into table
ALARM FIELDS
ESCAPED BY '\\';
Query OK, 1 row affected, 1 warning (0.03 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1
mysql> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1261 | Row 1 doesn't contain data for all columns |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select * FROM ALARM;
+------------+------------+--------+------+------------+----------+-----------+--------------+---------+--------+
| ARRIVED | DETECTED | NAME | TYPE | ALARMLEVEL | VERIFIED | DISCLOSED |
CATEGORY_NUM | EVENTID | REASON |
+------------+------------+--------+------+------------+----------+-----------+--------------+---------+--------+
| 2004-09-27 | 2004-09-27 | alaram | 0 | 0 | NULL | 0 |
2 | 1 | NULL |
+------------+------------+--------+------+------------+----------+-----------+--------------+---------+--------+
1 row in set (0.02 sec)
You can see the warning since the number of fields was less the needed. Or
maybe you
wanted by having \n to express NULL? Last thing to do over sample.text is to
mark all
places where NULL should appear with \N .
Hope this helps,
Andrey
[EMAIL PROTECTED] wrote:
> Tha sample lines are like this in alarm.txt
>
> ARRIVED DETECTED NAME TYPE ALARMLEVEL V DISCLOSED
CATEGORY_NUM
> EVENTID
> --------- --------- -------------------- --------- ---------- - ---------
------------
> ---------
> REASON
>
> ------------------------------------------------------------
>
> 27-SEP-04 27-SEP-04 alaram 0 0 N 0
2
> 1
>
>
>
>
> 27-SEP-04 27-SEP-04 MiTel 0 0 N 0
2
> 2
>
>
> The above lines i am not able to insert into mysql.
>
> After changing the format as below(as in the sample.txt) i am able to insert
into mysql but not correclty. Couls you please help me in this.
>
>
> 2004-09-27 \t 2004-09-27 \t alaram \t 0 \t 0 \t N \t 0 \t 2 \t 1 \n
>
> thanks,
> narasimha
>
Confidentiality Notice
The information contained in this electronic message and any attachments to this
message are intended
for the exclusive use of the addressee(s) and may contain confidential or privileged
information. If
you are not the intended recipient, please notify the sender at Wipro or [EMAIL
PROTECTED] immediately
and destroy all copies of this message and any attachments.
