Home » Other » Client Tools » TOAD i don't know how to load .dat file (oracle 8.1.7 + Toad 6 .dat file oracle 10g + Toad 9)
| TOAD i don't know how to load .dat file [message #313480] |
Sat, 12 April 2008 18:40  |
kowalsky
Messages: 37
Registered: May 2003
|
Member |
|
|
hi all,
I have got some big .dat files plus an .spk file for each.
How exactly am I supposed to load the files in my table using TOAD? The tables are the same, part of the data has been uploaded using a different method.
Now I'm stuck with the .dat files, I tried all I found in TOAD at Database Import. Am I missing something? Can anyone tell me exactly what is the sequence?
thanks,
kowalsky
|
|
|
|
| Re: TOAD i don't know how to load .dat file [message #313523 is a reply to message #313480] |
Sun, 13 April 2008 07:47  |
Frank Naude
Messages: 4579
Registered: April 1998
|
Senior Member |
|
|
Can you please provide more info: What are in those .dat and .spk files? What utility was used to produce them? Where did you get these files from? Can you ask for documentation/ upload instructions?
If it's plain text files, you can probably use SQL*Loader to load them. Export dump files can be loaded using the imp or impdp utilities.
|
|
|
|
| Re: TOAD i don't know how to load .dat file [message #313544 is a reply to message #313480] |
Sun, 13 April 2008 10:00 |
kowalsky
Messages: 37
Registered: May 2003
|
Member |
|
|
I ran a query in TOAD; in the window where the results are displayed I right clicked and selected Save as Flat File. I had a choice of creating this spk file, contents are shown below:
LINESPERRECORD=1
COL1=SRC_SOURCE,1,1,32
COL2=CNTR_ID,1,33,20
COL3=R_DATE,1,53,22
COL4=R_HOUR,1,75,3
COL5=R_INTERVAL,1,78,3
COL6=R_DATETIME,1,81,22
COL7=VERSION,1,103,3
COL8=CL_ID,1,106,20
Then I saved the data as the file1.dat - I did open it, it has only the data but it seems it is not delimited by any character - it's probably spaced out as per the spk above(32 chars for the first field starting at position 1, 20 chars for the second field starting at position 2 and so on).
980AAB 168 2/1/2008 1 1 2/1/2008 12:05:00 AM 1 91132
So, I guess I could use sqlloader after all. However, I thought that TOAD is supposed to help exactly in this type of situation - for the export, as I described above, it went very fast and easy. Shouldn't it be just as easy to load this file back with just a few clicks?
Thanks,
kowalsky
|
|
|
|
|
|
| Re: TOAD i don't know how to load .dat file [message #313568 is a reply to message #313547] |
Sun, 13 April 2008 16:16 |
 |
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Amazing ... simply amazing.
| Quote: | I had a choice of creating this spk file
|
Why did you do it that way? Do you KNOW what you want to do? Do you know your INPUT and desired OUTPUT? Do you know what you NEED to accomplish this task?
Lucky us that you didn't have a choice to launch a nuclear missile.
Why didn't you use EXPORT and IMPORT utilities in the first place? These are created for data moving ... But OK, you chose to do it differently. Only if you knew how. Why didn't you choose "Save As" option instead, and saved output as CSV file, or even INSERT STATEMENTS? But noooo, you had to choose "this spk file" (and only God knows what SPK is).
OK, this is one of the SPK file lines:As far as I can tell, it says that the third column's name is R_DATE and it can be found in the DAT file starting at position 53 and its length is 22.
It also means that980AAB 168 2/1/2008 1 1 2/1/2008 12:05:00 AM 1 91132
Now, what to do with this information? Almost nothing; you should precreate a table in order to load information stored in the DAT file. You do know column names, but have no idea what their datatypes are. You *might* try to guess (ID is probably a number, R_DATE is probably a date, etc. but - how can you be sure?). You might, though create all columns as VARCHAR2(some_large_enough_size) but - is this what you really want to do? Your new schema would be useless for anything than "SELECT * FROM this_table".
If you still have a chance to repeat this procedure, take a look at the "SQL Loader" tab of the "Export to flat file" option. It should contain the whole SQL*Loader control file which might provide some additional information.
A conclusion? Think it over; I believe this is NOT what you want to do. Or, at least, not as you are doing it now.
|
|
|
|
| Re: TOAD i don't know how to load .dat file [message #313569 is a reply to message #313480] |
Sun, 13 April 2008 17:12 |
kowalsky
Messages: 37
Registered: May 2003
|
Member |
|
|
Thank you very much for your answers, unfortunatelly nothing I can use.
I will just have to do it the old way using sqlloader and the damn control file and it's going to take me a few hours until I nail it.
Or maybe I should wait a little longer until someone who knows this stuff shows up ...
thanks,
kowalsky
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 06:19:31 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
