Convert data captured Excel into Oracle database via SQL

[BenjaminLim]

Have anyone tried the above.

I need help to convert columns of data captured in Excel files into Oracle database via SQL synthax.

Please advice.

Thanks.

Benjamin

 

[THoey]

Couple of ways, but neither use real SQL.

1. Link the source Excel spreadsheets and the destination Oracle tables to an Ms. Access database. Write Queries to transport the data.

2. Either export the data as comma delimited or link to Ms. Access again and export as fixed length. Then, use SQL*Loader to import the data into your Oracle database.

If one of these sound good for you, reply back and we can help... Terry

 

[BenjaminLim]

THoey,

Could you explain how both work. I am trying to determine is the easier way (quick & error free if possible)

The constraint I am face, I need to get the work tested in a development database & has no access to production database. My users has access to production database however when they link to MS Access, they could only read tables from Oracle database & can't write to the Oracle database. What I meant is that I have created links from MS Access to Oracle database before.

Could you advice why the users are not allowed to write to Oracle database via MS Access. I suspect it's some sort of access rights control.

How about another proposal, to try to use SQLs insert...statement, will it worked and how to populate in bulk.

Please advice.

 

[THoey]

If this is going to be an on going task, I would suggest option 1 above. Create a new Access DB. Link to the database and to the spreadsheets. Write a UPDATE query that changes a field in the Oracle DB based on a match in the spreadsheet.

Now, as to why your users can't change anything when they connect to Oracle from Access, there might be other causes, but I would look at the permissions on the account that they are connecting with first. Terry

 

[BenjaminLim]

THoey,

As I am not a DBA expert, please advice what is the command to check permission of user account.

And what will be the command to use to grant them access to update to MS Access database. With the in-house application, they have no difficulty performing all access priviledges.

Kindly advice. Thanks.