| Oracle data export to MS Access [message #4659] |
Mon, 06 January 2003 09:13  |
Arun Jain
Messages: 1
Registered: January 2003
|
Junior Member |
|
|
Hi, I have a big oracle database and i want to export it to a ASCII format file. I tried importing a oracle dmp file into access etc but of no use. Can you please guide me as to how can i export the oracle database into an MS Access readable/ASCII format?
Thanks
Arun
|
|
|
|
| Re: Oracle data export to MS Access [message #4663 is a reply to message #4659] |
Mon, 06 January 2003 09:38  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
i dont know about MSACCESS READABLE file.
generally,
u spool your file into csv from oracle.
then
load the same into access.
for large volumes of data, i would prefer to use UTL_FILE
SQL> set head off
SQL> set feed off
SQL> spool c:mfyile.txt
SQL> select deptno||','||dname||','||loc from dept;
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
SQL> spool off
SQL> get c:mfyile.txt
1 SQL> select deptno||','||dname||','||loc from dept;
2 10,ACCOUNTING,NEW YORK
3 20,RESEARCH,DALLAS
4 30,SALES,CHICAGO
5 40,OPERATIONS,BOSTON
6* SQL> spool off
7 .
SQL>
|
|
|
|
| Re: Oracle data export to MS Access [message #4711 is a reply to message #4659] |
Thu, 09 January 2003 12:21 |
denni50
Messages: 18
Registered: November 2002
|
Junior Member |
|
|
How we generally do it is we spool the oracle data
to a list file(.lst)...then rename it a .txt file.
example:
spooled file name: DataFile.lst, rename DataFile.txt
Go to MS-Access then click on the 'open folder'...
search the drive/folder path where the .txt file is
located...next(at the bottom) select the 'file type'
..select "All Files(*.*)".
Click on DataFile.txt...then click 'Open'.
A Link Text Wizard pop-up menu will appear...click
'CANCEL'...you will now have created the .mdb database
for DataFile.txt.
Now you will import the data:
Go to File > Get External Data > import
select the drive/folder path and file type again...
highlight DataFile.txt > import...you will now
go through the import text wizard and select the
parameters to import the text file.
hth
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
