| import of data from Excel file into table [message #555730] |
Mon, 28 May 2012 01:24  |
 |
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 884 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 #556047 is a reply to message #555968] |
Wed, 30 May 2012 14:15  |
 |
Littlefoot
Messages: 21782
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?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
