Import excel into Oracle

Toad Data Point
1

I was able to get beta 2.6 and import my excel file into Oracle where the wizard creates the table. But I’m not able to change the default data-type from Nvarchar2 to just a simple varchar2. Why can’t I be allowed to change this during the import?

SP32-20100203-131859.jpeg

2

In TDA version 3.0 we plan to offer a full table editor so you can define each column separately. Since we support Unicode, Nvarchar2 was the best default choice.

I do realize having this as the only data type is limiting. The only work around is to create the import table prior to import and truncate before inserting. (If you need to truncate)

If you have an existing table and want to use the DDL from that table you can use the Create Like Table editor or you can use the Script Table Tab and use the DDL that is retrieved from the database. In the latter you will need to make changes.

Debbie

3

How long before version 3.0 is going to release?

We would use TDA a ton when importing new client data we always seem to get from Excel and from cvs if this works.

I would like the tool to know the data types we could pick for each column for the target dB as well as to be able to change the column names before the table is created and the data imported. As is, I have to first change the source file’s column names before even starting the TDA import wizard.

I think this will be a great tool when these features are available.

4

TDA 3.0 will be a major release and there is no set date at this point. However, this enhancement is something we can work on early in the project and include in the early Beta’s. Would you be able to use a Beta for this enhancement?

I would like to know a bit more about your use case. The easiest enhancement we could do is just pop up our table editor for the database you want to import to and pre populate columns with the header names. You could then change the column names and data types. Is this sufficient?

Can I get a description of your workflow? IE: Client sends you excel data, you import into table, create report (what type?) or …

Debbie

5

I would be able to beta test.

You noted process would work just fine for me.
But it would have to work the same way for both Oracle and SQL Server.

My work flow is:

  1. A new client wants to start using our work management and lease management website application.

  2. They send us their current RAW work tracking data and lease data in Excel, Access, cvs or such. (but normal Excel).

  3. I first need to get all the data loaded into Oracle or SQL Server so I can clean it and normalize it a bit.

    a) it would be nice to do some of this from TDA before importing to Oracle but that would mean I would have to be able to target a developed SQL statement that sourced the Excel or the csv file. (maybe another enhancement).

  4. Once I know the data is clean and well mapped to our current production schema I run custom append scripts into production target tables.

I need TDA to help cleanly, quickly and simply get the data into Oracle or SQL Server.
I know there are other tools, which I do use now, but it would be nice if I could use just one tool like TDA regardless of the source and target.
Your noted solution would help a ton to this end.