In SQL Developer if you use the Import Data feature from the main tree node, it will create a new table based on a delimited text or Excel file.
We’ll look at the data and create some columns for you.
In version 4.1, we’ll recognize numbers as numbers, dates as dates, and try to get a best guess for you in terms of the proper date formats, precision, etc.
In all version of the tool, we scan the preview size of the input stream to figure out how big to make the text fields.
Instead of letting the import fail b/c row 101 is 30 chars long, we’re going to have some ranges in play – so that if we see the largest string is 29 characters, we’ll default the field to 50 and so on.
Now, this is where I start to worry.
Are you just putting the data into the database for some quick-n-dirty processing? I really hope so. And it makes sense, you’re creating a new table based off a spreadsheet.
But, if you’re doing some serious application work, please do a proper data model first. Talk to your business users. Gather requirements. Analyze the data structures, performance requirements, etc.
You might want this to be a partitioned table after all. Or you might want CLOBs instead of VARCHAR2s. Or your indexing scheme might not work out well with some of your choices on this wizard.
Before you do ‘quick and dirty,’ consider the guy or gal that has to come in after a few months and make it work. It will cost a lot more then than the few minutes here while you’re quickly clicking through wizard dialog pages.
Thanks for letting me preach a bit. Excel kinda makes me want to deliver sermons for some reason.