If I have a spreadsheet with "Column Headers" (which become field
names in Oracle) such as "Wtd Avg Variable O & M Costs $/MWh" I'd like
it to convert that to "Wtd_Avg_Variable_O_M_Costs_MWh". Any after
determining the field type for a given field, why can't the tool go
through the data and determine the length of each string in that
field. Then determine the max string length for that field. Use that
max as the length of the field. Use similar methods for numeric
fields. Once these things have been done, all I need is the create
table SQL statement... the rest is easy.

On 4 Ott, 21:04, Joel <johow... (AT) gmail (DOT) com> wrote:
Ah Ok. Not a good idea to use those chars in header (field) names.
Anyway they can be easily renamed.

If you want to determine the **exact** maximum string lenghts for
every
"field", there is also a data scanner which will tell you such
values.

The following procedure is **only** to determine such maxima. Then,
you can use
these value to substitute the default values in "Table setup" for the
export to DBMS:

QuickExport > ExportToTextFile> ExportOptions > SpacedColumns >
ScanDataToDetermineSizes

Keep note of the field sizes and may, if you think it's meaningful,
use those values to compute the appropriate field sizes
in the CREATE TABLE statement (keep into account possible encoding
settings to determine the
appropriate value).

-P

Easily?!? I'm the one that faces hundreds of tables (data collection
projects) that commonly have >20 columns! You do the math... Not only
that, but there is a limitation on the maximum field name length.
Correct me if i'm wrong, but I believe the name can't be longer than
30 chars. I really don't like having to go back to determine which
field name is causing my create table statement to error out.

I want a tool that will do that for me.


Seriously, what century are we living in? Maybe I should be asking if
anyone has the appropriate punch cards...

On 4 Ott, 23:51, Joel <johow... (AT) gmail (DOT) com> wrote:
:-)))

It wouldn' really cost me much to add the functionality you are
suggesting (automatically passing the maxima to the create create
statement), but I am a little bit lacking conviction currently, and I
feel I need some more arguments to get persuaded about the usefulness
of such an automatism.

Usually the field sizes should be based on some design planning and
not just because you found string of some lenght in an Excel sheet.
Not to mention the encoding issues.

And what about someone add a char to string. Are you going to recreate
your table ?

Sometimes (it may not be your case), when one feel there is a lack of
tools, it may also be worth considering to take this as an hint to
think about the approach being used.

Cheers,


-P

The approach consists of loading data of unknown quality as rapidly as
possible. The data is evaluated and then stored. A report is written
and no updates or inserts are made. The approach works just fine for
me. If the data is incorporated into a system, someone else takes it
from there.

I think I'll just build my own tool

Just an FYI - I decided to take a closer at the SQL Server SSIS today
after I was given a spreadsheet with over 250 columns and over 20,000
rows. There is a "Suggest Types" button in the import and export
wizard. It determines the appropriate field type and the appropriate
size for each field based on the data being imported. One limitation
I found is that it will only scan 10,000 rows when determining this
information. This causes errors when the string data in a column is
at the bottom of the spreadsheet and the tool never scans it, so it
believes it's a numeric field; at least it's a start though. Finding
this feature in SQL Server saved me hours of manual work. Why does
SQL Server have this feature but Oracle specific tools dont?