Home » SQL & PL/SQL » 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 Go to next message
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 Go to previous messageGo to next message
Frank Naude
Messages: 4502
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 Go to previous messageGo to next message
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 #313547 is a reply to message #313544] Sun, 13 April 2008 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

quoting Ana (anacedent):
Those who live by the GUI, die by the GUI.

Regards
Michel
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 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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:
COL3=R_DATE,1,53,22
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 that
980AAB 168 2/1/2008 1 1 2/1/2008 12:05:00 AM 1 91132
isn't exactly the output we are talking about (column CL_ID should start at position 106, and THIS line is far from being ~120 characters long).

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 Go to previous messageGo to next message
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
Re: TOAD i don't know how to load .dat file [message #313571 is a reply to message #313480] Sun, 13 April 2008 17:22 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Or maybe I should wait a little longer until someone who knows this stuff shows up ...
Your time would be better spent by RTFM; specifically
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/toc.htm

You need to be smarter than the tool(s), you are using.
Re: TOAD i don't know how to load .dat file [message #313617 is a reply to message #313569] Sun, 13 April 2008 23:48 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
kowalsky wrote on Mon, 14 April 2008 00:12
it's going to take me a few hours until I nail it.

Probably, but you'll use that knowledge next time and you'll, actually, benefit from this situation.
Re: TOAD i don't know how to load .dat file [message #313813 is a reply to message #313569] Mon, 14 April 2008 12:57 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
If you want to use TOAD, then go back and export it as delimited and read the following link for how to import:

http://asktoad.com/DWiki/doku.php/faq/answers/general#i_have_a_flat_file_.csv_tab_delimited_etc._and_i_want_to_import_the_data_to_a_t able._what_is_the_best_way_to_do_that_using_toad
Previous Topic: SP2-0310
Next Topic: spool append
Goto Forum:
  


Current Time: Fri Dec 09 03:58:57 CST 2016

Total time taken to generate the page: 0.12624 seconds