Home » SQL & PL/SQL » Client Tools » Import Problem with SQL Developer v 2.1.1.64 and Excel Files
Import Problem with SQL Developer v 2.1.1.64 and Excel Files [message #510274] Fri, 03 June 2011 06:30 Go to next message
Mattes_01
Messages: 6
Registered: June 2011
Junior Member
Hi there,

I hope I posted this topic in the correct forum.

Anyway, I do not have much experience with databases (with MS Excel I do) but most of the problems I have been able to resolve by myself.

I am using a TNS database.


There is one problem occuring with which you guys might help me:
I want to import data from SAP to the database, but we are having some problems with the connection and so on.
Anyway, the only way to get the data into the database is to key them in manually or to import them.

I have been using the import function (I had a lot of trouble with date formats) and it worked:
I selected the "Import Data", the corresponsing file and then have been able to see an extract of the data. It worked and I have been able to import data from an Excel file to the database.

But an error occured: Some lines have not been copied.
So I wanted to reimport them.
But from this point of time I have not been able to open any Excel file anymore:

When I select the file no extract/preview is given anymore and the file seems to be empty.
I cannot select tabs of the Excel file or anything like this.

I restarted the computer, established a new connection to the database and even reinstalled my client.

It seems that one setting has changed.
I am still able to import the data from *.csv files, but this is not as comfortable that using Excel.

Does anyone of you have an idea how to solve this problem?


Best regards from germany.
Mattes
Re: Import Problem with SQL Developer v 2.1.1.64 and Excel Files [message #510277 is a reply to message #510274] Fri, 03 June 2011 06:47 Go to previous messageGo to next message
Roachcoach
Messages: 1125
Registered: May 2010
Location: UK
Senior Member
I'm not sure exactly what you mean, however having some experience with the vagaries of importing via that tool, always select the option to send the data to a SQL worksheet. That way when a line fails to insert, you have the prepped insert statements there to work on, rather than a "black box" style import where its all or nothing.
Re: Import Problem with SQL Developer v 2.1.1.64 and Excel Files [message #510281 is a reply to message #510277] Fri, 03 June 2011 07:09 Go to previous messageGo to next message
Mattes_01
Messages: 6
Registered: June 2011
Junior Member
Hi Roachcoach,

I did a screenshot:
h++p://uploadpic.org/v.php?img=TVVuBSjz4G (sorry can post links yet, change "+" to "t")
In the first screen you select the xls-file.

Regardless which xls file I selected (even the xls file which used to work earlier) the next screen is the same.

Usually a preview would be given over here.

The problem wasn't that "black box style". It was neither all nor nothing.
Only a few lines haven't been imported that way, but most of the lines have been inserted.

Maybe now you have an idea what I can do.

Does anybody know where to find the settings of the "xls to sql"-converter?
This seems to be the problem for me.
By the way: -files still can be imported....

Thanks and best regards

[Updated on: Fri, 03 June 2011 07:09]

Report message to a moderator

Re: Import Problem with SQL Developer v 2.1.1.64 and Excel Files [message #510282 is a reply to message #510281] Fri, 03 June 2011 07:17 Go to previous messageGo to next message
Roachcoach
Messages: 1125
Registered: May 2010
Location: UK
Senior Member
I do vaguely recall seeing it do stupid stuff like that, but I can't remember how I worked around it, sorry. Pablolee might be a help here.



I meant right at the end of the wizard, there's an option to either run it, or generate the code to do it off to a SQL sheet. If you know that already, ignore me Smile
Re: Import Problem with SQL Developer v 2.1.1.64 and Excel Files [message #510284 is a reply to message #510282] Fri, 03 June 2011 07:22 Go to previous messageGo to next message
Mattes_01
Messages: 6
Registered: June 2011
Junior Member
No I didn't know that, I used to check my xls-file again and again untill it worked and I have been able to do the upload Wink
This way it will work too.

Hopefully Pablolee will reckognize this thread Smile

Thanks anyway Roachcoach
Re: Import Problem with SQL Developer v 2.1.1.64 and Excel Files [message #510290 is a reply to message #510284] Fri, 03 June 2011 07:55 Go to previous messageGo to next message
Roachcoach
Messages: 1125
Registered: May 2010
Location: UK
Senior Member
When it drops rows and stuff its often because the sheet had bad data in a field, but if you post it to a SQL sheet you'll get an insert statement for each line.

Takes a wee bit longer to actually process because of the GUI, but it makes correcting issues easier.
Re: Import Problem with SQL Developer v 2.1.1.64 and Excel Files [message #513722 is a reply to message #510274] Wed, 29 June 2011 02:58 Go to previous messageGo to next message
Mattes_01
Messages: 6
Registered: June 2011
Junior Member
Hi guys,

the problem hasn't been solved yet, but the workaround, using a csv-file, works fine.

Some people told me that the converter, which converts the xls-file to csv (or any format the SQL-Developer understands) has hung.

As far as nobody knows where to find this Converter, I am using csv-files for the upload now.

Thanks & regards
Re: Import Problem with SQL Developer v 2.1.1.64 and Excel Files [message #513757 is a reply to message #513722] Wed, 29 June 2011 05:19 Go to previous messageGo to next message
Roachcoach
Messages: 1125
Registered: May 2010
Location: UK
Senior Member
There's a new version out now, have you tried that one? Might work better for you and since it's a straight up exe, no worries about overwriting the existing SQL developer program.

I've not tried it, not had to upload excel books in months.
Re: Import Problem with SQL Developer v 2.1.1.64 and Excel Files [message #515141 is a reply to message #510274] Fri, 08 July 2011 03:56 Go to previous messageGo to next message
Mattes_01
Messages: 6
Registered: June 2011
Junior Member
Hi there,

confirmed: The problem is that the SQL Developer connects twice on the Database and then blocks himself.

This might be a Bug or somebody implemented this function for a reason, but nobody was able to give me a workaround.

I am having access to another database tool now and I will be using this one as far as the SQL Developer doesn't work properly for this tasks.

Thanks anyhow for your help.

By the way, I am not sure when the SQL Developer will be updated internally.
When it is updated I will try it again and let you know.

Best regards
Mattes
Re: Import Problem with SQL Developer v 2.1.1.64 and Excel Files [message #515144 is a reply to message #515141] Fri, 08 July 2011 04:08 Go to previous message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
Hi, I was on holiday when this thread was going on. You seem to have got around your issue, but one or two quick pointers on using import:
1. No passwords on the file - this is a common cause of the 'no data showing in the preview' issue.
2. no filters on the worksheet. Again, this will cause no data to show in your preview.
3. as for dates, just always remember to put the right format in when defining the columns and you should be fine.

PL
Previous Topic: sqlplus
Next Topic: Explain plan Using toad and SQL Developer
Goto Forum:
  


Current Time: Sun Apr 20 09:56:03 CDT 2014

Total time taken to generate the page: 0.08173 seconds