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.
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.
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.
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).
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.
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.
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.
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.
I want to export the oracle data into an excel sheet. I have written the code by using UTL_FILE package. but i am getting the output as shown in the screen shot(without formatting the column size as the width of the data it has). But I want the output column width to be set according to the size of the data automatically.
In the OWB mapping,I needed to transfer data in lob table1 to lob table2 .Both tables are in 2 different schema.The table1 has blob and clob columns.The table 2 has same structure like table 1 and the table 2 is empty.
I have created the mapping and connected directly from the source table to the target table.Also , I have created a error table for the target table table. As for the data rule method,I customized the error table to log the non unique rows.
When I start validating the mapping . I got the following error
VLD-3200 Deduplicator cannot contain LOB type columns
How to log the error in error table for transferring lob type table1 to lob type table 2
I have task. I am using oracle forms 6i. I want to import excel data to oracle forms(its common task using ole2 package). But this time I want to map the columns i.e my database table having 5 columns. and the excel file is having 2 or 3 columns then i suppose to map those columns and accordingly insert it to my table.
So far i have import column heading of excel to oracle forms, then i've provide list item for mapping each column. so that user can map excel column to database columns. Now I am confuse how to write the code so that selected columns should get inserted into database.
-more details
I have table with columns id, name, location, address, plan. in those columns i need to insert records form excel. user having a excel with 3 columns col1, col2, col3. on the form i've fetch column headers of excel and in front of that i've provide database column list , so user can match excel column with database column. e.g.
COL1 --> list value of database column COL2 -->list value of database column COL3 -->list value of database column
Once user map those column i want to insert those values into my database table (table with columns id, name, location, address, plan). and i am confuse about this code.
I have been using F60util.pll to convert a form contents directly. But in Forms 9i , this library is giving an error. Which alternative should i use for F60util.PLL.
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.
In my form i am uploading one excel file Input.xls which has inventory detail. in this file on column is Hyperlinked , when i click on it opens a linked file.
From this file i am generating an Output.xls file which shows links too. How can i get that link and set it to Output.xls file. More in my Input.xls file some rows are colored with grey color which shows detail of diamond piece which are on hold.
I had a form in oracle 6i with which I can generate an excel sheet when clicked a button(by using ole2 built in)...this form I have converted to oracle forms 11g and replaced the ole2 with client_ole2 and added webutil library...but the excel is not generated as in 6i.
How to integrate excel with oracle forms ? that it looks like a detail block. If i give 20 in a test item if i click on a button it should display 20 records in a from 10g that records look like excel records .
I have written the following code to Quit Excel But This removes Excel from the desktop but the application still runs. If you view the task manager you see all the excel application there taking up all the recourse.
I am trying to translate some input data given in MS Excel into Oracle Forms. The Excel sheet consists of usage of the SUMIF() to calculate the values. I am struggling to implement this in Oracle Forms.
I am attaching a screenshot with some sample data set to explain my problem in detail.
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.
I have been requested to create an excel sheet report in my Forms application (10.1.2.0.2) with data grouped in a pie chart, something like the attached pic (if you cant open go to this link: [URL]
I'm able to create the excel sheet by using OLE2 tool, but I dont know how to insert my pie chart from code.
i want to ask that is there any way to use the excel formulas on oracle forms, reports and database level. for example if we want to use IRR formula of excel to use in oracle forms, database and report.
download / upload data from / to database through oracle forms 6i which is deployed in oracle apps 11i.I am using DDE/OLE2 but it is downloading if form is stand alone but when i post this form in my UNIX server, it is not downloading.
Oracle apps is in UNIX and i want excel to be in client (windows).what procedure to be used.