ravi.allam9

 Profile Answers by ravi.allam9 Questions by ravi.allam9

• Jun 4th, 2008

 

import/export

lanka_satya

 Profile Answers by lanka_satya 

• Jul 4th, 2008

 

Dear Kiran,
Tell me one thing you want me import the table or data in the
table.  ok what ever may be the position, see in oracle apart from oracle import and export we have got something called oracle SQLLOADER using this we can import data from external file to oracle database.  
According to your question you want to load data from MS-Aceess to Oracle. Here
we can do one thing.

See I am taking about to load the data from ms-access
assuming that tables have been already created in Oracle database.

(if this not correct you tell me context i will help you out)

Let me tell, the steps to migrate data from ms-access to oracle database assuming same tables are been created in
Oracle database:

Step1: Let us try export data from MS-Aceess to local
dump, obviously we can see data in excel format.....open each file and then save it into csv format or diractly you can export into
CSV format, any how your wish..

Step2: Now you have the raw data.
So create a folder and place all the source files into that folder

Step3: In oracle we have got sqlloader which loads data from external file to oracle tables using control as it instructs oracle loader as to waht data we need to load

Step4: Now your job is to write the control file

Step5:
Go to command prompt type the following command
sqlldr scott/tiger control=filename.ctl

how to write control file:

let us we have table called sample and columns are x,y

sample
x number,
y number

sample.ctl

load data
infile "d:ms_access_dumpsample.csv"
insert into table sample
fields terminated by ","
optionally enclosed by '"'
trailing nullcols(x,y)

after writing the above code save the file in the folder where you have src files, now go to command prompt:

cmd
d:ms_access_dumpdir
ms_access_dump>sqlldr scott/tiger control=sample.ctl

now data will be automatically populated into table.
friend if you have any
doubts please revert back to me.

anand_study

 Profile Answers by anand_study Questions by anand_study

• Sep 25th, 2008

 

This might help someone if need to get table into Oracle from MS Access table.

Option-1 - Use MS Access (2003) Export
1. Oracle Client and Oracle ODBC driver should be installed on your PC.
2. In ODBC Admin, create System DSN or File DSN for your Oracle database (use Oracle Driver)
3. Open MS Access database
4. Open MS Access table you want to migrate.
5. File Menu | Export
6. Dialog box opens, select ODBC Database () in "Save As Type" Combo/List Box at bottom
7. It will ask for the table name, so give table name as TEMP_YOURTABLENAME
8. Then it will ask for File Datasource or Machine Data Source so select System DSN or File DSN (created in step 2)
9. It will ask for Oracle userid and password.
10. TEMP_YOURTABLENAME table is created.

Sometimes it allows SELECT * from TEMP_YOURTABLENAME
but does not allow SELECT Col1, Col2 from TEMP_YOURTABLENAME etc..

Then manually create table YOURTABLE in Oracle appropriately and then use
INSERT INTO YOURTABLE (select * from TEMP_YOURTABLE);
COMMIT;


Option-2 - Use SQLLoader to import file
1. You need to know how to use SQL*Loader utility.
2. In Oracle first create table manually.
3. Open MS Access database
4. Open MS Access table you want to migrate the data.
5. Dialog box opens, select Text Files (*.txt, *.csv, *.tab...) in "Save As Type" Combo/List Box at bottom
6. Give file name as temp.csv or temp.tab (select appropriate folder to place your file to)
7. Click button "Export All"
8. Now data is available in csv ot tab file
9. Use SQL*Loader to import data from that file to your Oracle table you created in step 2.

michaelsmith035

• Jul 29th, 2011

 

If you successfully exported the data to text file , then you are very close!

In the export option of Microsoft Access, you shall see an option like "Export to ODBC database". If you click that, you shall be prompted for table name in the Oracle database.
In the next step you have to select the machine data source for oracle.