This content has been marked as final. Show 1 reply
If this is just a one time thing I'd use sqlldr (part of the total client). You would make sure to add a trailing column to
the spreadsheet (past the last data column) and save as a tab delimited text file say mydata.txt
Then you have to create a control file that would be something like this:
(note that terminated by is a tab however you have to do that in your text editor)
that would load 3 columns some_date,some_chars and some_numbers in that order. The data items must appear
load data APPEND into table MYTABLE fields terminated by ' ' trailing nullcols (SOME_DATE "to_date(trim(:SOME_DATE),'MM/DD/YYYY')", SOME_CHARS "trim(:SOME_CHARS)", IGNORE_THIS FILLER, SOME_NUMBERS "trim(:SOME_NUMBERS)" ) Then it would be sqlldr control=mydata.ctl data=mydata.txt (it asks for id/password@instance )
in the order specified in the control file. You can specify the same data item more than once though if necessary.
PS: one thing that users manage to do that can really goof things is up is embed carriage returns (newlines) into the
spreadsheet. For that reason it is best to remove them in excel first. You do a search and replace, searching for
alt-010 (alt zero one zero where the numbers are on the numeric keypad). If this worked ok, excel represents the
search item as a dot, then you would replace with a space. Likewise if they embedded tabs that would be a good thing to remove before saving as a tab delimited text file.
Note you have to look at the log file and it may have not loaded things besides the headers which would go into the .bad file. Sqlldr is quite enigmatic and using it is an art to say the least. Good to know however as it's very important to get stuff into the database :-)
This would be a great feature for forms btw if it had a column name recognition load feature. I mean if it would match a column header line with a database table column NAME. Suppose that we have a way in forms to
tell it we want to load some data from excel into MYTABLE and then forms looks at ideally the excel file specified and
highlights what columns matched column names in MYTABLE. [step here for user to change things in excel, and or the
table.] [We need forms to optionally get rid of bad things that could be in cells such as returns, delimiters, and possibly escape some bad characters like colons, ampersands...] Somewhere in there it would have to be worked out how to convert dates, Case and various details. Then hopefully when the user pressed a button it would load the data and make it very clear what was not loaded as opposed to the "I don't like something" sort of message you can get. If it doesn't like something we want to know what exactly it did not like and why it did not like it. This would be a fabulous feature, smarter than sqlldr because it would be driven by column names and not solely column order.