How to create aTable by Loading Columns w/ Attributes From an Excel File

Toad for Oracle & DevOps Toolkit
1

Hello Y’All,

I have about 50 columns with attributes and comments saved in an excel file and I tried to use the “Create Table” Function/Util in Toad for Oracle to create a table with all 50 columns and their attributes and comments from this excel file. But it did not seem to work. The excel file contains the following information for all 50 columns and these headers are in the first row of the excel file:

ColumnName ColID PK Null DataType Default Comments Histogram NumDistinct EncryptionAlg Salt Trigger

Instead of loading the actual columns and attributes(starts from the second row of the excel file), the utility seems to load the first row which are the headers for each column information. When I clicked the ‘Load Cols From File’ PB in the Create Table screen, I received the following error at line 3:

CREATE TABLE JTOMASIC.JT_DOT_SGN_ASSEMBLY
(
COLUMNNAME VARCHAR2(30),
COLID VARCHAR2(30),
PK VARCHAR2(30),
NULL VARCHAR2(30),
DATATYPE VARCHAR2(30),
DEFAULT VARCHAR2(30),
COMMENTS VARCHAR2(30),
HISTOGRAM VARCHAR2(30),
NUMDISTINCT VARCHAR2(30),
ENCRYPTIONALG VARCHAR2(30),
SALT VARCHAR2(30),
TRIGGER VARCHAR2(30)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING
Error at line 3
ORA-00904: : invalid identifier

I could write a SQL script to create the table, but I then have to copy and paste all the column names, attributes and comments. It is tedious and time consuming. At this point, I am not sure if Toad util can help me to create a table from an excel file.

Any/all the help on this would be greatly appreciated.

Thanks.

2

It does not like one of your column names… I’ll look at the wizzard when
I get to work… can’t connect right now

On Mon, Mar 21, 2011 at 10:50 PM, linorchid wrote:

Hello Y'All,

I have about 50 columns with attributes and comments saved in an excel file
and I tried to use the "Create Table" Function/Util in Toad for Oracle to
create a table with all 50 columns and their attributes and comments from
this excel file. But it did not seem to work. The excel file contains the
following information for all 50 columns and these headers are in the first
row of the excel file:

ColumnName ColID PK Null DataType Default Comments Histogram NumDistinct
EncryptionAlg Salt Trigger

Instead of loading the actual columns and attributes(starts from the second
row of the excel file), the utility seems to load the first row which are
the headers for each column information. When I clicked the 'Load Cols
From File' PB in the Create Table screen, I received the following error
at line 3:

CREATE TABLE JTOMASIC.JT_DOT_SGN_ASSEMBLY
(
COLUMNNAME VARCHAR2(30),
COLID VARCHAR2(30),
PK VARCHAR2(30),
NULL VARCHAR2(30),
DATATYPE VARCHAR2(30),
DEFAULT VARCHAR2(30),
COMMENTS VARCHAR2(30),
HISTOGRAM VARCHAR2(30),
NUMDISTINCT VARCHAR2(30),
ENCRYPTIONALG VARCHAR2(30),
SALT VARCHAR2(30),
TRIGGER VARCHAR2(30)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING
Error at line 3
ORA-00904: : invalid identifier

I could write a SQL script to create the table, but I then have to copy and
paste all the column names, attributes and comments. It is tedious and time
consuming. At this point, I am not sure if Toad util can help me to create a
table from an excel file.

Any/all the help on this would be greatly appreciated.

Thanks.
3

Yeah you can NOT have a field named NULL . . .you have to assign it a name.

“gene.l.bradley”

“gene.l.bradley”
Sent by: toad@yahoogroups.com

03/22/2011 08:12 AM

Please respond to
toad@yahoogroups.com

To

toad@yahoogroups.com

cc

Subject

Re: [toad] How to create aTable by Loading Columns w/ Attributes From an Excel
File

It does not like one of your column names… I’ll look at the wizzard when I
get to work… can’t connect right now

On Mon, Mar 21, 2011 at 10:50 PM, linorchid wrote:

Hello Y’All,

I have about 50 columns with attributes and comments saved in an excel file and
I tried to use the “Create Table” Function/Util in Toad for Oracle to create a
table with all 50 columns and their attributes and comments from this excel
file. But it did not seem to work. The excel file contains the following
information for all 50 columns and these headers are in the first row of the
excel file:

ColumnName ColID PK Null DataType Default Comments Histogram NumDistinct
EncryptionAlg Salt Trigger

Instead of loading the actual columns and attributes(starts from the second row
of the excel file), the utility seems to load the first row which are the
headers for each column information. When I clicked the ‘Load Cols From File’ PB
in the Create Table screen, I received the following error at line 3:

CREATE TABLE JTOMASIC.JT_DOT_SGN_ASSEMBLY
(
COLUMNNAME VARCHAR2(30),
COLID VARCHAR2(30),
PK VARCHAR2(30),
NULL VARCHAR2(30),
DATATYPE VARCHAR2(30),
DEFAULT VARCHAR2(30),
COMMENTS VARCHAR2(30),
HISTOGRAM VARCHAR2(30),
NUMDISTINCT VARCHAR2(30),
ENCRYPTIONALG VARCHAR2(30),
SALT VARCHAR2(30),
TRIGGER VARCHAR2(30)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING
Error at line 3
ORA-00904: : invalid identifier

I could write a SQL script to create the table, but I then have to copy and
paste all the column names, attributes and comments. It is tedious and time
consuming. At this point, I am not sure if Toad util can help me to create a
table from an excel file.

Any/all the help on this would be greatly appreciated.

Thanks.
image004.gif

4

Yeah you can NOT have a field named NULL . . .you have to assign it a name.

“gene.l.bradley”

“gene.l.bradley”
Sent by: toad@yahoogroups.com

03/22/2011 08:12 AM

Please respond to
toad@yahoogroups.com

To

toad@yahoogroups.com

cc

Subject

Re: [toad] How to create aTable by Loading Columns w/ Attributes From an Excel
File

It does not like one of your column names… I’ll look at the wizzard when I
get to work… can’t connect right now

On Mon, Mar 21, 2011 at 10:50 PM, linorchid wrote:

Hello Y’All,

I have about 50 columns with attributes and comments saved in an excel file and
I tried to use the “Create Table” Function/Util in Toad for Oracle to create a
table with all 50 columns and their attributes and comments from this excel
file. But it did not seem to work. The excel file contains the following
information for all 50 columns and these headers are in the first row of the
excel file:

ColumnName ColID PK Null DataType Default Comments Histogram NumDistinct
EncryptionAlg Salt Trigger

Instead of loading the actual columns and attributes(starts from the second row
of the excel file), the utility seems to load the first row which are the
headers for each column information. When I clicked the ‘Load Cols From File’ PB
in the Create Table screen, I received the following error at line 3:

CREATE TABLE JTOMASIC.JT_DOT_SGN_ASSEMBLY
(
COLUMNNAME VARCHAR2(30),
COLID VARCHAR2(30),
PK VARCHAR2(30),
NULL VARCHAR2(30),
DATATYPE VARCHAR2(30),
DEFAULT VARCHAR2(30),
COMMENTS VARCHAR2(30),
HISTOGRAM VARCHAR2(30),
NUMDISTINCT VARCHAR2(30),
ENCRYPTIONALG VARCHAR2(30),
SALT VARCHAR2(30),
TRIGGER VARCHAR2(30)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING
Error at line 3
ORA-00904: : invalid identifier

I could write a SQL script to create the table, but I then have to copy and
paste all the column names, attributes and comments. It is tedious and time
consuming. At this point, I am not sure if Toad util can help me to create a
table from an excel file.

Any/all the help on this would be greatly appreciated.

Thanks.
16539948.gif (43 Bytes)

5

Yeah you can NOT have a field named NULL . . .you have to assign it a name.

“gene.l.bradley”

“gene.l.bradley”
Sent by: toad@yahoogroups.com

03/22/2011 08:12 AM

Please respond to
toad@yahoogroups.com

To

toad@yahoogroups.com

cc

Subject

Re: [toad] How to create aTable by Loading Columns w/ Attributes From an Excel
File

It does not like one of your column names… I’ll look at the wizzard when I
get to work… can’t connect right now

On Mon, Mar 21, 2011 at 10:50 PM, linorchid wrote:

Hello Y’All,

I have about 50 columns with attributes and comments saved in an excel file and
I tried to use the “Create Table” Function/Util in Toad for Oracle to create a
table with all 50 columns and their attributes and comments from this excel
file. But it did not seem to work. The excel file contains the following
information for all 50 columns and these headers are in the first row of the
excel file:

ColumnName ColID PK Null DataType Default Comments Histogram NumDistinct
EncryptionAlg Salt Trigger

Instead of loading the actual columns and attributes(starts from the second row
of the excel file), the utility seems to load the first row which are the
headers for each column information. When I clicked the ‘Load Cols From File’ PB
in the Create Table screen, I received the following error at line 3:

CREATE TABLE JTOMASIC.JT_DOT_SGN_ASSEMBLY
(
COLUMNNAME VARCHAR2(30),
COLID VARCHAR2(30),
PK VARCHAR2(30),
NULL VARCHAR2(30),
DATATYPE VARCHAR2(30),
DEFAULT VARCHAR2(30),
COMMENTS VARCHAR2(30),
HISTOGRAM VARCHAR2(30),
NUMDISTINCT VARCHAR2(30),
ENCRYPTIONALG VARCHAR2(30),
SALT VARCHAR2(30),
TRIGGER VARCHAR2(30)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING
Error at line 3
ORA-00904: : invalid identifier

I could write a SQL script to create the table, but I then have to copy and
paste all the column names, attributes and comments. It is tedious and time
consuming. At this point, I am not sure if Toad util can help me to create a
table from an excel file.

Any/all the help on this would be greatly appreciated.

Thanks.
graycol.gif

6

Yeah you can NOT have a field named NULL . . .you have to assign it a name.

“gene.l.bradley”

“gene.l.bradley”
Sent by: toad@yahoogroups.com

03/22/2011 08:12 AM

Please respond to
toad@yahoogroups.com

To

toad@yahoogroups.com

cc

Subject

Re: [toad] How to create aTable by Loading Columns w/ Attributes From an Excel
File

It does not like one of your column names… I’ll look at the wizzard when I
get to work… can’t connect right now

On Mon, Mar 21, 2011 at 10:50 PM, linorchid wrote:

Hello Y’All,

I have about 50 columns with attributes and comments saved in an excel file and
I tried to use the “Create Table” Function/Util in Toad for Oracle to create a
table with all 50 columns and their attributes and comments from this excel
file. But it did not seem to work. The excel file contains the following
information for all 50 columns and these headers are in the first row of the
excel file:

ColumnName ColID PK Null DataType Default Comments Histogram NumDistinct
EncryptionAlg Salt Trigger

Instead of loading the actual columns and attributes(starts from the second row
of the excel file), the utility seems to load the first row which are the
headers for each column information. When I clicked the ‘Load Cols From File’ PB
in the Create Table screen, I received the following error at line 3:

CREATE TABLE JTOMASIC.JT_DOT_SGN_ASSEMBLY
(
COLUMNNAME VARCHAR2(30),
COLID VARCHAR2(30),
PK VARCHAR2(30),
NULL VARCHAR2(30),
DATATYPE VARCHAR2(30),
DEFAULT VARCHAR2(30),
COMMENTS VARCHAR2(30),
HISTOGRAM VARCHAR2(30),
NUMDISTINCT VARCHAR2(30),
ENCRYPTIONALG VARCHAR2(30),
SALT VARCHAR2(30),
TRIGGER VARCHAR2(30)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING
Error at line 3
ORA-00904: : invalid identifier

I could write a SQL script to create the table, but I then have to copy and
paste all the column names, attributes and comments. It is tedious and time
consuming. At this point, I am not sure if Toad util can help me to create a
table from an excel file.

Any/all the help on this would be greatly appreciated.

Thanks.
ecblank.gif

7

Yeah you can NOT have a field named NULL . . .you have to assign it a name.

“gene.l.bradley”

“gene.l.bradley”
Sent by: toad@yahoogroups.com

03/22/2011 08:12 AM

Please respond to
toad@yahoogroups.com

To

toad@yahoogroups.com

cc

Subject

Re: [toad] How to create aTable by Loading Columns w/ Attributes From an Excel
File

It does not like one of your column names… I’ll look at the wizzard when I
get to work… can’t connect right now

On Mon, Mar 21, 2011 at 10:50 PM, linorchid wrote:

Hello Y’All,

I have about 50 columns with attributes and comments saved in an excel file and
I tried to use the “Create Table” Function/Util in Toad for Oracle to create a
table with all 50 columns and their attributes and comments from this excel
file. But it did not seem to work. The excel file contains the following
information for all 50 columns and these headers are in the first row of the
excel file:

ColumnName ColID PK Null DataType Default Comments Histogram NumDistinct
EncryptionAlg Salt Trigger

Instead of loading the actual columns and attributes(starts from the second row
of the excel file), the utility seems to load the first row which are the
headers for each column information. When I clicked the ‘Load Cols From File’ PB
in the Create Table screen, I received the following error at line 3:

CREATE TABLE JTOMASIC.JT_DOT_SGN_ASSEMBLY
(
COLUMNNAME VARCHAR2(30),
COLID VARCHAR2(30),
PK VARCHAR2(30),
NULL VARCHAR2(30),
DATATYPE VARCHAR2(30),
DEFAULT VARCHAR2(30),
COMMENTS VARCHAR2(30),
HISTOGRAM VARCHAR2(30),
NUMDISTINCT VARCHAR2(30),
ENCRYPTIONALG VARCHAR2(30),
SALT VARCHAR2(30),
TRIGGER VARCHAR2(30)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING
Error at line 3
ORA-00904: : invalid identifier

I could write a SQL script to create the table, but I then have to copy and
paste all the column names, attributes and comments. It is tedious and time
consuming. At this point, I am not sure if Toad util can help me to create a
table from an excel file.

Any/all the help on this would be greatly appreciated.

Thanks.
pic27941.gif

8

It looks like you saved the SB-Tables-Columns grid and would like to make a
table with that data. I recognized the column names.

A much easier way to do that would be to type this into the editor and then run
it:

create table as select * from

or, if you don’t want to copy the data:

create table as select * from where 0=1

OR, go to SB-Tables-SCRIPT tab for the table, copy/paste the script to the
editor, and change the table name, then run it.

OR, right-click on a table in the schema browser, and choose “create
like”.

9

Thanks John for your response. Let me try to rephrase what I am looking for:

I have a table of about 15,000 rows, and I exported the data dictionaries of the table to an excel file. Our user has added many new columns with attributes, modified the existing column attributes and added comments for each column in the excel file. I am trying to find an utility in Toad for oracle that will allow me to create a table from this excel file modified by our user.

I found and tried the ‘Load Cols From File’ function in following tool in Toad for Oracle, but it did not seem to work:

Toad for Oracle 10.6.1 -> Database -> Create -> Table

Just wonder if you and/or others have used this tool successfully. If it works for you and others, I think it is the format problem of the exported excel file. At this point, I guess I will use a SQL script to create a new table by copying and pasting the data attributes.

BTW, I do appreciate your information for the methods of creating a new table.

Thanks again.

10

I use the utillity a lot and it works fine.

On Tue, Mar 22, 2011 at 4:15 PM, linorchid wrote:

Thanks John for your response. Let me try to rephrase what I am looking for:

I have a table of about 15,000 rows, and I exported the data dictionaries of
the table to an excel file. Our user has added many new columns with
attributes, modified the existing column attributes and added comments for
each column in the excel file. I am trying to find an utility in Toad for
oracle that will allow me to create a table from this excel file modified by
our user.

I found and tried the 'Load Cols From File' function in following
tool in Toad for Oracle, but it did not seem to work:

Toad for Oracle 10.6.1 -> Database -> Create -> Table

Just wonder if you and/or others have used this tool successfully. If it
works for you and others, I think it is the format problem of the exported
excel file. At this point, I guess I will use a SQL script to create a new
table by copying and pasting the data attributes.

BTW, I do appreciate your information for the methods of creating a new
table.

Thanks again.
11

‘Load Cols from file’ works, but it expects table column names to be
in the first row of the Excel spreadsheet. It does its best to determine
datatype of each column based on the data in the subsequent rows. After that,
you can make changes in the ‘alter table’ dialog.

It sounds like your excel file is not of the expected format. I’ve
attached a sample that would work.

12

[ Attachment(s) from John Dorlon included below]

Oops, I wasn’t ready to hit send yet.

NOW I’ve attached it.

13

I agree with John, I know your spreadsheet is probably top secret but if we had
glimpse of it perhaps…

On Tue, Mar 22, 2011 at 4:24 PM, John Dorlon wrote:

‘Load Cols from file’ works, but it expects table column names to be in
the first row of the Excel spreadsheet.  It does its best to determine
datatype of each column based on the data in the subsequent rows.   
After that, you can make changes in the ‘alter table’ dialog. 

 

It sounds like your excel file is not of the expected format.    I’ve
attached a sample that would work.
14

Yep, I don’t know that Toad has a handy tool for that. But you can
always use Excel’s capabilities. If your spreadsheet has those columns
in columns A through L, then paste a formula like this in all rows for
column M
=","&A1&" “&E1&IF(D1=“Y”,” NOT NULL","")
And a formula like this in all rows for column N
=“comment on column .”&A1&" is
‘"&G1&"’;"
And then cut and paste column M between
CREATE TABLE whatever (
And
);
And paste column N after that, and you’re almost done with your Create
Table script.

Nate Schroeder
Enterprise Services - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167
314-694-2592

15

Hi John and All,

Mystery resolved and thanks for all your responses and especially John’s attached excel file. Yep, I misunderstood the use of the ‘Load Cols From File’ function in Toad for Oracle, thus the error message while trying to create a table by using my excel file.

All in all I guess Toad doesn’t seem to have a tool for what I am looking for.

BTW, I was going to attach a short version of my excel file, but I don’t know how to attach the file to this email posting.

Thanks again for all your help!