SQL Loader problem

Hi,
So i change the table and loader file to be CHAR, remade the table, and I still have the same bad file:
see below
tim
+++++++++++++
"xxx.xxx.net",
"XXX.xxx.net_1184032683",
"Filesystem_EB_Windows",
"MS-Windows-NT (13)",
"(none specified)",
"root",
"(none specified)",
"Daily",
"CINC (4)",
"8 days (10)",
"Tue Jul 10 2007 02:58:03 (1184032683)",
"279 second(s)",
"Wed Jul 18 2007 02:58:03 (1184723883)",
"no",
"no",
"362361",
"3773",
"1",
"2",
"-1 -1 -1 -1 -1 -1 -1 -1 -1 -1",
"no",
"Filesystem_EB_Windows_1184032683_INCR.f",
"Filesystem_EB_Windows_1183780228_FULL.f",
"(none specified)",
"0x0",
"0",
"4",
"Wed Jul 11 2007 02:58:03 (1184119083)",
"(none specified)",
"no",
"no",
"0",
"no",
"(none specified)",
"Thu Jan 01 1970 01:00:00 (0)",
"Thu Jan 01 1970 01:00:00 (0)",
"0",
"0",
"0",
"Standard (0)",
"2695915",
"0",
"0",
"1",
"0  (Regular)",
"1969145",
"0",
"Thu Jan 01 1970 01:00:00 (0)",
"1",
"TIR (-1)",
"2633",

1. what is the new control file?
2. did you delete the old bad file?

Yes, i delete the old bad file


new control file:

Load data
infile 'c:\symantec\23.txt'
append into table tim.D_DATA
(Client CHAR  Terminated by "," enclosed by '"' ,
 Backup_id CHAR  Terminated by "," enclosed by '"' ,
 Policy_type CHAR  Terminated by "," enclosed by '"' ,
 Proxy_client CHAR  Terminated by "," enclosed by '"' ,
 Creator CHAR  Terminated by "," enclosed by '"' ,
 Name_1 CHAR  Terminated by "," enclosed by '"' ,
 Sched_label CHAR  Terminated by "," enclosed by '"' ,
 Schedule_Type CHAR  Terminated by "," enclosed by '"' ,
 Retention_Level CHAR  Terminated by "," enclosed by '"' ,
 Backup_Time CHAR  Terminated by "," enclosed by '"' ,
 Elapsed_Time CHAR  Terminated by "," enclosed by '"' ,
 Expiration_Time CHAR  Terminated by "," enclosed by '"' ,
 Compressed CHAR  Terminated by "," enclosed by '"' ,
 Encrypted_1  CHAR  Terminated by "," enclosed by '"' ,
 Kilobytes CHAR Terminated by "," enclosed by '"' ,
 Number_of_Files CHAR Terminated by "," enclosed by '"' ,
 Number_of_Copies CHAR Terminated by "," enclosed by '"' ,
 Number_of_Fragments CHAR Terminated by "," enclosed by '"' ,
 Histogram  CHAR  Terminated by "," enclosed by '"' ,
 DB_Compressed CHAR  Terminated by "," enclosed by '"' ,
 Files_File_Name CHAR  Terminated by "," enclosed by '"' ,
 Previous_Backup_Files_File_Nam  CHAR  Terminated by "," enclosed by '"' ,
 SW_Version  CHAR  Terminated by "," enclosed by '"' ,
 Options  CHAR  Terminated by "," enclosed by '"' ,
 MPX  CHAR Terminated by "," enclosed by '"' ,
 TIR_Info  CHAR  Terminated by "," enclosed by '"' ,
 TIR_Expiration  CHAR  Terminated by "," enclosed by '"' ,
 Keyword  CHAR  Terminated by "," enclosed by '"' ,
 Ext_Security CHAR  Terminated by "," enclosed by '"' ,
 File_Restore_Raw  CHAR  Terminated by "," enclosed by '"' ,
 Image_Dump_Level  CHAR  Terminated by "," enclosed by '"' ,
 File_System_Only  CHAR  Terminated by "," enclosed by '"' ,
Object_Descriptor  CHAR  Terminated by "," enclosed by '"' ,
Previous_BI_Time  CHAR  Terminated by "," enclosed by '"' ,
BI_Full_Time  CHAR  Terminated by "," enclosed by '"' ,
Request_Pid  CHAR  Terminated by "," enclosed by '"' ,
Backup_Status  CHAR  Terminated by "," enclosed by '"' ,
Stream_Number  CHAR  Terminated by "," enclosed by '"' ,
Backup_Copy  CHAR  Terminated by "," enclosed by '"' ,
Files_File_size  CHAR Terminated by "," enclosed by '"' ,
PFI_type  CHAR  Terminated by "," enclosed by '"' ,
IMAGE_ATTRIBUTE   CHAR  Terminated by "," enclosed by '"' ,
Primary_Copy   CHAR  Terminated by "," enclosed by '"' ,
Image_Type  CHAR  Terminated by "," enclosed by '"' ,
Job_ID  CHAR Terminated by "," enclosed by '"' ,
Num_Resumes  CHAR  Terminated by "," enclosed by '"' ,
Resume_Expiration  CHAR  Terminated by "," enclosed by '"' ,
Copy_number_3  CHAR Terminated by "," enclosed by '"' ,
Fragment_3 CHAR Terminated by "," enclosed by '"' ,
Kilobytes_3 CHAR Terminated by "," enclosed by '"' ,
Remainder_3  CHAR  Terminated by "," enclosed by '"' ,
Media_Type  CHAR  Terminated by "," enclosed by '"' ,
Density  CHAR  Terminated by "," enclosed by '"' ,
File_Num  CHAR Terminated by "," enclosed by '"' ,
ID_1 CHAR  Terminated by "," enclosed by '"' ,
Host_1  CHAR  Terminated by "," enclosed by '"' ,
Block_Size_1  CHAR  Terminated by "," enclosed by '"' ,
Offset_1  CHAR  Terminated by "," enclosed by '"' ,
Dev_Written_1 CHAR  Terminated by "," enclosed by '"' ,
Flags_1  CHAR  Terminated by "," enclosed by '"' ,
Media_Descriptor_1  CHAR  Terminated by "," enclosed by '"' ,
checkpoint_1  CHAR  Terminated by "," enclosed by '"' ,
resume_num_1 CHAR  Terminated by "," enclosed by '"' ,
Copy_number_1  CHAR  Terminated by "," enclosed by '"' ,
Fragment_1  CHAR Terminated by "," enclosed by '"' ,
Kilobytes_1  CHAR Terminated by "," enclosed by '"' ,
Remainder_1 CHAR  Terminated by "," enclosed by '"' ,
Media_Type_1  CHAR  Terminated by "," enclosed by '"' ,
Density_1 CHAR  Terminated by "," enclosed by '"' ,
File_Num_1 CHAR  Terminated by "," enclosed by '"' ,
ID_2  CHAR  Terminated by "," enclosed by '"' ,
Host_2  CHAR  Terminated by "," enclosed by '"' ,
Block_Size_2  CHAR Terminated by "," enclosed by '"' ,
Offset_2  CHAR  Terminated by "," enclosed by '"' ,
Media_Date_2  CHAR  Terminated by "," enclosed by '"' ,
Dev_Written_2 CHAR  Terminated by "," enclosed by '"' ,
Flags_2  CHAR  Terminated by "," enclosed by '"' ,
Media_Descriptor_2  CHAR  Terminated by "," enclosed by '"' ,
Expiration_Time_2  CHAR  Terminated by "," enclosed by '"' ,
MPX_2  CHAR  Terminated by "," enclosed by '"' ,
retention_lvl_2 CHAR  Terminated by "," enclosed by '"' ,
checkpoint_2  CHAR  Terminated by "," enclosed by '"' ,
resume_num_2  CHAR  Terminated by "," enclosed by '"' ,
END_OF_RECORD   CHAR  Terminated by ","
  )

1. You still have more data (86) than records (84)
2. Change your control file header to something like

load data
infile 'c:\symantec\23.txt'
append
concatenate 84    <-- or however many fields you have
into table tim.D_DATA
trailing nullcols
( client char ..............
)

The key here is the 'concatenate' part since your data is all on different lines.

Thanks for that, its nearly working correctly.
is there a way that i can read 86 lines in, then stop reading data until one record after
"Hello",
Basiclly "Hello" is the end of the record, I need the first 86 lines, after that its dropable. then the next record starts.
thanks
tim

Or, if it was easier, keep reading, (I can build the table with 300 col)  the data until the "Hello" is found, then move to the next record, would also be possible,
tim

i think you shouid go for Oracle file handling (UTL_FILE) for this operation.

do you have an example of this please?

I am not sure I understand whether "Hello" is an actual field or not. However, your sample text has "Hello;

is that a typo? It really should be symmetrical, i.e., "Hello"

Then you could simply change your control file to read "Hello" into your last field in the record (which I assume is END_OF_RECORD), like so:

END_OF_RECORD   CHAR  Terminated by "," enclosed by '"' ,

just like the other fields. Then (because of the concatenate 86 clause) you will be reading 86 lines into a record, and the 87th line will be the first field in a new record.

>>is that a typo? It really should be symmetrical, i.e., "Hello"

"Hello",

oops. Forgot the comma there.

Hi,
Hello, is the word i added into the source file so that i know where the end of each record is. The problem is that some of my records are longer than the 86 lines so i need to stop loading the data after line 85 until the work Hello is found then load that in to 86, and then the data is correct again for field 1.

Tim