Home » SQL & PL/SQL » Client Tools » import of data from Excel file into table (10.0.0.1)
import of data from Excel file into table [message #555730] Mon, 28 May 2012 01:24 Go to next message
harshitaankur
Messages: 19
Registered: April 2012
Location: Delhi
Junior Member
Hello sir,

Sir we are using toad.
We have a table partymast. we want to import the data into this table using excel file having extension .csv.
I am explaining all the things below:
select *from partymast

partyid partyname    accountname      aacname
aa        aa        10014000023367     Ashish

select * from master

masterid            mname
10014000023367      Ashish

aacname column in partymast table is fetching from master table mname column.

We have third table name IMPEXP
Iename                  iedesc                  ietype             iedef
import party master   import party master       imp         {ImportStart}
                                                            [Transid]=party
                                                             [ImportFile]=:
                                                             [FileType]=excel
                                                              PartyID=col_1
                                                               PartyCat=col_2
                                                               AccountName=col_3 
                                                                {ImportEnd}


Sir, I am enclosing the Excel file.Please tell me what will be the next step that we should filllow.

[EDITED by LF: applied [code] tags, hoping that the question will be easier to read]
  • Attachment: CBPL8oct.CSV
    (Size: 8.22KB, Downloaded 195 times)

[Updated on: Wed, 30 May 2012 13:51] by Moderator

Report message to a moderator

Re: import of data from Excel file into table [message #555734 is a reply to message #555730] Mon, 28 May 2012 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Sir we are using toad.


You are wrong.

Quote:
we want to import the data into this table using excel file having extension .csv.


This not a Excel file, this is a CSV file.
Use SQL*Loader or external table.

Regards
Michel
Re: import of data from Excel file into table [message #555968 is a reply to message #555734] Wed, 30 May 2012 04:56 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

We are not able to open the attached file.
Re: import of data from Excel file into table [message #556047 is a reply to message #555968] Wed, 30 May 2012 14:15 Go to previous message
Littlefoot
Messages: 21367
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I applied [code] tags in order to make the original message easier to read and, possibly, understand.

OK, the first part is simple - two tables, joined by PARTYMAST.AACNAME = MASTER.MNAME.

Then there's the third table. I'm not sure I understand its contents. What is the last column supposed to be - all those "{ImportStart}" and stuff. Is it ALL in the same record, or are there 8 records, where only the first one is complete, while the rest of records contain values only in the IEDEF column? It *looks like* some kind of a "control file" that contains instructions for ... what? TOAD? Which part of it? ... so that it would be able to load data stored in a CSV file.

What is that CSV file, really? It looks like a binary file (I tried to open it with a text editor - not readable. I don't want to open Excel files found on the internet, sorry). So yes, it *might* be a genuine Excel file.

I loaded quite a few Excel files via TOAD. True, not complex ones, everything was done in a few simple <Next> ... <Next> steps, but it worked OK. I'm quite sure that I'm incapable of making TOAD load data into two different tables (which is what you are asking, right? Load that CSV into PARTYMAST and MASTER tables?) at once. I think I would manage to do that with SQL*Loader or using external tables feature, but both of these options expect true CSV (comma separated values - which is a text document, ASCII, not binary) input file. But that's something Michel already said.

So, what do you think? Would you want to convert Excel into a textual file and, possibly, copy/paste several sample records, along with CREATE TABLE statements and explanation which parts of input file go into which tables and columns so that we could try to suggest a solution, or will you wait until someone who masters TOAD and can provide solution you are looking for?
Previous Topic: inserting a data with substitutuion variable
Next Topic: query o/p in html page
Goto Forum:
  


Current Time: Sun Feb 25 05:35:52 CST 2018

Total time taken to generate the page: 0.12203 seconds