Sitemap

Amicuk Programming Answers

Excel import problems (collected)

2015-10-11   Views:0

Advertisement

There are a few problems with Excel Import. Some have been reported elsewhere. Test Data Table Definition.   CREATE TABLE "JIM"."EXCEL_IMPORT"    (     "COLUMN1" VARCHAR2(100 BYTE),      "COLUMN2" VARCHAR2(100 BYTE)

There are a few problems with Excel Import. Some have been reported elsewhere.
Test Data
Table Definition.
  CREATE TABLE "JIM"."EXCEL_IMPORT"
   (     "COLUMN1" VARCHAR2(100 BYTE),
     "COLUMN2" VARCHAR2(100 BYTE),
     "COLUMN3" VARCHAR2(100 BYTE),
     "COLUMN4" NUMBER(10,2),
     "COLUMN5" NUMBER(10,0)
  TABLESPACE "USERS" ;
Spreadsheet contents
Label1,Name 1,Desc 1,2.95,2.45
Label2,Name 2,Desc 2,1,1.23456
1 The file chooser doesn't remember the last directory used, but defaults to the sqldeveloper install directory.
2 Numbers in the preview are truncated/rounded.
2.95->3
2.45->2.4
1->1
1.23456->1.2
3 If you make a mistake in assigning table columns to spreadsheet columns, you can't undo it because the columns already used are removed from the list of available columns. This needs an 'De-assign' option
4 When you assign columns, number columns don't get their precision or scale picked up.
This shouldn't matter because the DML is simple doing insert...values(1.2), but the end result is wrong (see 5).
The DML looks OK (relative to the already rounded numbers, not to the original spreadsheet.
insert into EXCEL_IMPORT (COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5) VALUES('Label1','Name 1','Desc 1',3,2.4);
insert into EXCEL_IMPORT (COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5) VALUES('Label2','Name 2','Desc 2',1,1.2);
5 A problem with assigning colums if you switch off header row after assigning columns. There are a number of slightly different problems.
a. Select your table and select all the columns in the 'Columns' tab
b. Assign table columns to your spreadsheet columns in the 'Data Types' tab
c Go back to the columns tab
d Uncheck 'Header Row?' - the columns are all deselected
e Reselect all columns
Any of several things may happen now
e.1 In the Data types tab, columns may be missing
e.2 Column assignments may be wrong
e.3 There are no table columns in the drop down when trying to assign columns
It looks as if the handling of the list of table columns is not right.
6 The 'Choose data type' prompt is misleading. It should be 'Choose Table Column'
7 The help is very sparse, especially on the datatypes tab.
I've also posted a couple of enhancement suggestions in the exchange
- an option to save the DML
- an option to save the metadata for a given import so that the next import to the same table is just a matter of select file and go.

The replay answer
Advertisement
Are there any solutions to the data truncation/rounding problem?
I am trying to import Excel data into tables through SQL Developer and notice that the preview screen displays truncated/rounded numbers even after I have deliberately formatted the data in Excel as numbers in 3.2 format. Is there a workaround?
Thanks for any advice you can give.
Frank Eichel
Forest Inventory Analyst
Natural Resources Canada
Victoria, Canada

Go to See the other 5 answers

Excel import problems (collected)

Category:DefaultRelease time:2015-10-11Views:130

There are a few problems with Excel Import. Some have been reported elsewhere. Test Data Table Definition.   CREATE TABLE "JIM"."EXCEL_IMPORT"    (     "COLUMN1" VARCHAR2(100 BYTE),      "COLUMN2" VARCHAR2(100 BYTE)[More]

Excel Import problem

Category:DefaultRelease time:2015-10-11Views:130

I'm having trouble with importing data from excel. I'm using HiQ version 4.5 and during import spurious data seems to appear after import. doesn't inspire confidence in software qualityWhen the Excel Import dialog is invoked, you need to give the inf[More]

Excel import problems

Category:DefaultRelease time:2015-10-11Views:130

Hi - i recently imported information of a football team from excel into indesign and the birth date information magically all changed by 4 years and 1 day. Not very helpful! The Excel application is Microsoft 2008 and Indesign is CS3. Has anyone else[More]

1.5.1: Excel import - Problem with NULL values in date fileds

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I'm trying to import Excel Data via CSV with the import into table feature of SQLDeveloper. Everything works fine, but if I select some of my date colums I get an error telling me that I have null or invalid values in a date column: Verifying if[More]

Probleme bei Excel-Import mit SBO 2007

Category:DefaultRelease time:-0001-11-30Views:130

Hallo zusammen! Wir haben bei einem gewöhnlichen Stammdatenimport zu den Geschäftspartnern mit Excel folgendes Problem: die Fehlermeldung lautet: interner Fehler --> Zahlwege für Zahlungsassistenten (OPYM) (-2007) aufgetreten. Wenn wir die Zahlwege f[More]

Excel import: importing too many rows

Category:DefaultRelease time:2015-10-11Views:130

I am working with SQL Developer 1.2.0 Build MAIN-29.98 and have a problem with Excel import: I would like to import some data from an XLS file. I click on Actions -> Import Data -> Excel, complete the dialog and check the DML: there are 3 insert int[More]

Excel-Import with SBO 2007

Category:DefaultRelease time:2015-10-11Views:130

Hello to all! I have problems concerning the Excel-Import-function within SAP 2007. I want to import some business partners (what should not be such a huge problem), but an internal error occures which states: "internal error: Payment Methods for Pay[More]

CS5 Windows import problems (ppj, avi)

Category:DefaultRelease time:-0001-11-30Views:130

I have the CS5 Master Collection installed on a Windows 7 Ultimate 64-bit laptop. I have some old Premiere 6.5 projects that I wanted to open/import. When I first tried this, I could open the ppj files and make prproj files from them, either by doubl[More]

HD Import Problems

Category:DefaultRelease time:-0001-11-30Views:130

I recently purchased a Canon HV 20 HD Camcorder. Picture quality and playback from the camera is excellent. However I have had a few problems following import of HD footage to my computer with IMovie 06 HD. Most of the footage is fine, however a few[More]

Excel Importer for Visual Studio LightSwitch 2013

Category:DefaultRelease time:2015-10-11Views:130

Has anyone managed to get the 'Excel Importer for Visual Studio LightSwitch' extension working with Visual Studio 2013?Hi Kevin, I assume you mean this extension here - http://code.msdn.microsoft.com/silverlight/Excel-Importer-for-Visual-61dd4a90 Fro[More]

LR3.2 import problem on computer with Windows OS

Category:DefaultRelease time:2015-10-11Views:130

LR3.2 import problem on Windows OS This is a continuation of a question that I posed on August 22, 2010 in the discussion string "Cannot import CR2".  I have not been able to resolve the issue of importing an image into an existing folder locati[More]

Hot
When I connect my MacBook Pro to my iMac with the firewire to use as a monitor it makes me enter the Key producy code for Office:Mac 2011 every time. Then when I disconnect my MacBook I have to enter the product key again to use office on it??? Any i [More]
I recently setup Mail to use with a new POP email account. I find that I can send messages using Mail with no problem, but I cannot receive any email messages other than the ones sent by my ISP. There were three of these waiting for me when I opened [More]
I have film that is 1920x1080 and is very good quality. I don't want people to only be able to play this in a hd player, but I want to preserve the quality. I have 8.5gb discs (enough to accommodate the film 7.5gb). When I set the settings to sd howe [More]
I'm sorry, if I'm asking about something that's obvious in SAP. I need to develop a perl application in a remote server that will receive a requisition from an abap application. I'm using the SAP Module sapnwrfc. I connect to SAP Gateway with no erro [More]
I apologize if this is the wrong forum, but I am stuck. I have a DVD of my son-in-law's memorial service that I need to copy onto my computer in a playable form. The DVD itself works great on both the TV, and on the computer (playing with iDVD on the [More]
Hello again everyone, all I seem to do is complain about BT, my goodness its so annoying & you will have to listen to me again..Sorry guys. Here is my problem I finally decided to go for BT Vision had it about 2 weeks, I forgot to record a programme [More]
Im setting up a website for a medium sized business and havnt implemented the Coldfusion Verity search into the site yet... but I was looking over the Google Mini and seems like an nice/intuitive nice little box. Should I go with the $2000 mini price [More]
My new Ipod nano keeps shutting off after about a minute when playing music. It is fully charged.Hi, I had the same problem  googled the problem & it said to make sure ear piece  is in as far as possible , make sure no silver is showing on the jack, [More]
I have two clients and a friend using IPhone 3G. None of them are technically patient. Is their a way to backup the phone to their computer with the bluetooth connnection and not using itunes. I know if they have to plug and use itunes they will not [More]
Hi All, I've updated iPhoto to 9.4.1 At first launch iPhoto asks to update the photo library. I click OK, iPhoto shows a progress bar and hangs... Help! Thank youOption 1 Back Up and try rebuild the library: hold down the command and option (or alt) [More]