Import Excel To Database Table By Column Mapping

May 24, 2013

Using oracle forms 6i i want to carray out following task.

suppose i've table with following fields namely

loc_id, del_date, del_time, del_id

and i've excel sheet with following details

dateiddtimedid
---------------------------------------------------------------------
20-03-13 loc120:30:45RDT-01
25-03-13 loc215:25:30RTD-02
26-03-13 loc310:15:20RTD-03

want to import this excel data to my oracle table but l_id in my table is in first place but in excel sheet it is in second place, del_date in my table is in second place but in excel sheet it is in first place.

next time i may get excel sheet which will be like this

did lid date dtime
--------------------------------------------------------------------------------
DEL-01loc1 20-03-1320:30:45
DEL-03loc2 25-03-1315:25:30

This excel sheet also have different oreder than my database table and i want to import it to my database table using forms 6i. yes, i suppose to use ole2 package but my problem is how to map the columns.

I've already design a form i required i can attach it or provide screen shot of the same.

View 1 Replies


ADVERTISEMENT

Forms :: Import Excel By Column Mapping Using Oracle 6i

May 25, 2013

I want to imopert excel data by coloumn mapping to oracle table using forms 6i". for this I've created a form which is having following details.

1. path, file name text field and button called browse. when the form load. click on browse botton, and user will be asked to go to specific drive and select the excel file. The file path is stored in path text field and file name is displayed on file name text field.

2. After doing this I've porvide a button called column. after selecting the file click on column button. Once the button is pressed all column heading of the selected excel file get displayed on the text fields which i've provide on the form.

3. In frot of each column headig field I've provide list box which contains database table column heading. Now user have to select table column according to his excel column. (i.e I am asking user to map his excel column with oracle table column.)

4. Once the maping from user is done, I've provide button called process. once the button is pressed all the data from excel is to get insert in to database colmum.

what i've achive -
- User is able to select file path and file name get displayed.
- Columns heding of excel get displayed on the form.
problem area
-In the process button code column heading get change dynamically but records i.e actual data does not.
-I am able to insert records into table, but only when excel column heading sequence is same as database table column.

excel column = table column
-----------------------------------------------------------------
route = route
date = del_date
time = del_time
id = tran_id

In above situation record get insert into table. But when only excel column sequence change that time data is not inserted into table. If required i can upload my form and sample excel sheet also provide table script.

View 4 Replies View Related

Client Tools :: Import Excel File To Database Table Using TOAD?

Jan 16, 2011

want to load data from an excel file to a database table in Oracle. I am using Oracle 11 and the excel file has 3 columns as compared to 5 columns in the destination table. I want to generate sequential nos also for the table.

View 7 Replies View Related

Export/Import/SQL Loader :: How To Import Data From Excel File To Table Through Procedure

Jul 2, 2012

How to import data from excel(.xls) file to data base table

I have excel sheet(.xls) data details, I neet to upload details to data base table using procedure

excel sheet is not CSV file, so SQL Loader is not using

any alternative solution for this issue

View 3 Replies View Related

Export/Import/SQL Loader :: IMPDP Mapping To A Single Tablespace

Oct 30, 2012

I have a dumpfile from a database with hundreds of tablespaces. Do I need to remap all of them on impdp or is there a way to point all tables to a default tablespace? I mean, the source database has 200 tablespaces. The target database just 1.

View 2 Replies View Related

SQL & PL/SQL :: Import Excel File To Table

Jan 10, 2013

I have two columns in excel which i need to import in oracle table , but the problem is one column is of type date , i want the same date format to be maintained in table too.

View 8 Replies View Related

Import Data From Excel To Oracle Table Using Toad

Dec 26, 2012

how to import data from Excel sheet (inside server) to oracle DB.

Explanation : I need to automate this work, whenever i get an excel sheets with table values, i need to import the table values in to oracle DB automatically.I need an immediate solution for this.

View 4 Replies View Related

Client Tools :: Import Of Data From Excel File Into Table?

May 28, 2012

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. what will be the next step that we should filllow.

View 3 Replies View Related

Server Utilities :: Import Table From Another Database By Using Database Link?

Mar 17, 2010

i would like to import a table from another database by using database link.

impdp muba/muba tables=FUNCTION_NO directory=testdump NETWORK_LINK=DBLINK1

i created the public database link as system user

it gave error like

Import: Release 10.2.0.1.0 - Production on Wednesday, 17 March, 2010 11:07:02
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
Starting "MUBA"."SYS_IMPORT_TABLE_01": muba/******** tables=FUNCTION_NO directory=testdump NETWORK_LINK=DBLINK1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

[code]....

after that i try to import as system user like

impdp system/passwd tables=FUNCTION_NO schemas=muba directory=testdump NETWORK_LINK=DBLINK1

but error like

Import: Release 10.2.0.1.0 - Production on Wednesday, 17 March, 2010 11:27:43
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
UDI-00010: multiple job modes requested, schema and tables.

when i use IMP utility

imp system/passwd file=dump.dmp fromuser=userA touser=userB log=dmplog.log but when i use IMPDP what is the parameter equal to fromuser & touser

View 3 Replies View Related

Server Utilities :: Export Excel Sheet Data In Database Table?

Jul 17, 2012

i want to export excel sheet in database table, so i have converted excel file in .csv file(comma delimated)and made control file, then i started sqlldr by double clicking on it. path is-D:oracleproduct10.2.0client_1BIN

i run this command from cmd-

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:UsersNeetesh>sqlldr scott/tiger@localdb control=c:/users/neetesh/scott_data.
ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jul 17 17:20:33 2012

[code]....

and i attached the .ctl file. and .csv file is stored on same directory as .ctl file, why oracle couldn't find the .ctl file.

View 21 Replies View Related

SQL & PL/SQL :: Single Excel File For Emp Table Department Wise In Different Sheets Of Excel

Mar 30, 2012

I need to generate a single excel file which each department details in seperate sheets of the excel file, each sheet should ne named with department no (ex :Dept_10, Dept_20,Dept_30) and the main excel should be named as Emp_details.

Header row and total row should be in bold.I have created 3 individual excels thru toad and moved them to single excel and renamed the sheets.

Can we programatically do this thru SQL or PL/SQL, does Oracle provide any build packages for excel.As excel files cannot be uploaded,I am unable to upload the excel file I generated manually.

select to_char(empno) empno,ename,job,mgr,hiredate,
nvl(sal,0) sal,nvl(comm,0) comm,nvl(sal,0) + nvl(comm,0) "Total Income" ,deptno
from emp
where deptno = 10
union all
select 'Total',null,null,null,null,sum(nvl(sal,0)),sum(nvl(comm,0)),sum(nvl(sal,0))+sum(nvl(comm,0)),null
from emp
where deptno = 10;

View 1 Replies View Related

Forms :: Import Excel?

Apr 27, 2010

if there is a way to import excel to form in Oracle Apps Server running on UNIX using forms program unit?wanted to know where do we need to place the file on the server and how do we need to invoke the excel.

View 29 Replies View Related

SQL & PL/SQL :: Excel Data Import Into Oracle?

Jun 25, 2012

how excel data import into oracle

View 1 Replies View Related

Forms :: Import Data From Excel To Oracle?

Jun 21, 2013

I have designed a form to import data from an excel worksheet into an Oracle Form and It works pretty fine.

I use either Ole2.get_char_property or Ole2.get_num_property depending to the property of the column to read data but there are some columns used by both property which means that database is designed as VARCHAR2. The user can maintain either a numeric or characteristic data there. So I am looking for a solution to be able to make the program more flexible that he can read the property of the cell in excel file and decide what to use between Ole2.get_char_property and Ole2.get_num_property to read from excel file properly.

solve the problem regarding reading from Varchar2 columns.

View 1 Replies View Related

Forms :: How To Import Excel Sheet To Oracle

Sep 18, 2004

How To import data through forms from excel.I have an excel sheet and i have to import these sheet to oracle.but through forms how should i do.

View -1 Replies View Related

Export/Import/SQL Loader :: Import Table Without Messing Up Existing Data In Table

Sep 6, 2012

table already exist & its little data too, may have to imp rest of lost data, is this the right command?

imp SYSTEM/password FILE=file.dmp FROMUSER=black TOUSER=blake TABLES=(vcr_mappings, tablename2) ignore=Y CONSTRAINTS=n

scenerio2 (if have to drop & recreate the entire table) is this the right command?

imp SYSTEM/password FILE=file.dmp FROMUSER=black TOUSER=blake TABLES=(vcr_mappings, tablename2) ignore=Y

just for single table imp

View 2 Replies View Related

Client Tools :: Import With SQL Developer V 2.1.1.64 And Excel Files

Jun 3, 2011

Anyway, I do not have much experience with databases (with MS Excel I do) but most of the problems I have been able to resolve by myself.I am using a TNS database.

I want to import data from SAP to the database, but we are having some problems with the connection and so on.Anyway, the only way to get the data into the database is to key them in manually or to import them.I have been using the import function (I had a lot of trouble with date formats) and it worked:I selected the "Import Data", the corresponsing file and then have been able to see an extract of the data. It worked and I have been able to import data from an Excel file to the database.

But an error occured: Some lines have not been copied.So I wanted to reimport them.But from this point of time I have not been able to open any Excel file anymore...When I select the file no extract/preview is given anymore and the file seems to be empty.I cannot select tabs of the Excel file or anything like this.

I restarted the computer, established a new connection to the database and even reinstalled my client.It seems that one setting has changed.I am still able to import the data from *.csv files, but this is not as comfortable that using Excel.

View 9 Replies View Related

Import Oracle 10g Database Table Into 8i On Linux Platform?

Apr 13, 2013

can we import oracle 10g database table into oracle 8i database on linux platform?

View 7 Replies View Related

Server Utilities :: Import Millions Of Data From Excel To Oracle 11g?

Mar 6, 2013

how to import millions of data from excel to oracle?

View 8 Replies View Related

Server Utilities :: Import Data Type Date From Excel To Oracle

Feb 20, 2013

I have imported data from excel to oracle 11g. But i found an error like

"Ensure format is entered for datatypes 'Date' and 'TIMESTAMP' on data type pane".

after that i try to modify type date in oracle become 'dd-mmm-yyyy'

View 14 Replies View Related

Server Administration :: Cyrillic Character Set - Import A Table Of Data To Another Database

Aug 31, 2011

I'm try to import a table of data (character set: CL8ISO8859P5) to another database (character set: AL32UTF8) using exp/imp utility.After the import, all Cyrillic text was corrupted!

View 5 Replies View Related

Export/Import/SQL Loader :: Table Import Takes Long Time And Still Running?

Jun 22, 2012

MY DB Version: 10.2.o

OS: Windows Server 2003

I am trying to import on table which i have the export dump file which i take using expdp previously when i load that table on the same host

by using below command:

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

after that i zip that dump and move it to external usb and now i need that table i copy that table and unzip that that dump

Command i am using to do the import is :

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

But the query of import is still runing even not showing any amount of rows to be imported.

i already make the tablespace in which the table was previosuly before dropping but when i check the sapce of tablespace that is also not consuming one error i got preiviously while performing this task is:

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "CDR"."SYS_IMPORT_TABLE_03" successfully loaded/unloaded
Starting "CDR"."SYS_IMPORT_TABLE_03":  cdr/********@tsiindia directory=TEST_DIR dumpfile=CAT_IN_DATA_042012.DMP tables=CAT_IN_DATA_042012 logfile=impdpCAT_IN_DATA_042012.log

[code]....

i check streams_pool_size it will show zero and then i make it to 48M and after that

SQL> show parameter streams_pool_size;
NAME                                 TYPE        VALUE
-----------
streams_pool_size                    big integer 48M

But still it takes time

View 13 Replies View Related

Export/Import/SQL Loader :: Import To New Table That Has Additional Fields

Dec 22, 2012

I am trying to migrate a table to a new table that has the field sequence changed and also has a new field added. My main question is if it is possible to have datapump add values to the new field in the target table.For example:

-original table has fields a, b, d, c
-new table has fields b, c, d, a, e

I want to load the new table and also include adding values for field e. In this case, field e is a year field, so it should be loaded with '2012'..Does datapump have the ability to do this? Is reorganizing the fields going to cause me any problems? We are on oracle version 11.2.0.3

View 7 Replies View Related

SQL & PL/SQL :: Find Out Column Value In All Table In Whole Database

May 19, 2010

I need the column name and table name which is having the particular value in the whole database.

E.g. :We have 'Scott' value in emp table emp_name column. and we have lot of tables. here we need emp_name and emp as emp table is having scott value in emP_name column.

View 14 Replies View Related

SQL & PL/SQL :: Particular Column Of A Table Has Been Used In What All Database Objects

Jan 11, 2011

Is it possible that we can know that a particular column of a table has been used in what all data base objects?

i.e. like emp.empno is used in which all procedures/packages/triggers etc.

I would like to know the data dictionary view

View 15 Replies View Related

Forms :: How To Pick Excel File And Get Data In Column

Jun 18, 2013

How to pick excel data in oracle form 6i. and data show in forms column ?

View 1 Replies View Related

SQL & PL/SQL :: Predefined Function Names As Column Name In Database Table

Apr 9, 2012

Why Oracle allowing the Built-in function names as column name in data base table

Ex :

create table to_char
(to_number number(8) , to_char char(20) , to_date date ,substr varchar2(20))

View 3 Replies View Related

Forms :: Provide Excel Sheet With Column Header Only In Oracle 6i

Jun 6, 2013

I want to provide excel sheet with column header only to user, with the push button of oracle forms.(i.e user suppose to get the excel sheet when he press the button on the form).

View 3 Replies View Related

Forms :: How To Store Word / Excel Documents To BLOB Column

Mar 29, 2011

My client system is WinXP.I want to store a Word/Excel document to Oracle Blob column and choosed the command named "piecewise.exe",it seems a free tool on the web, to store the document to oracle,but occausionally failed.So I need alternative which is stable and available.

View 1 Replies View Related

Server Utilities :: Copy Table With NCLOB Column Into Another Database (10g To 11g)?

Dec 4, 2012

I need to copy one table which is having NCLOB column into our new DB(11g) from old DB (10g). As this table having huge records (1 crore) it is difficult to use direct insert statement (INSERT INTO ... SELECT * FROM @remotedb). I don't have DBA privilege to use loader.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved