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
Dear All, I want that when i run my report with showing the output report builder also convert that file or ask me an option in parameter that in which view you want to see the output of the report in Report Builder, In Excel, or In Pdf. when i choos [More]
mail with multiple gmail accounts sending messages from wrong email, other than the one i select: i'm using mail on osx 10.7 with multiple gmail accounts. when i create an email, i check to be sure i'm sending/replying from the correct account. after [More]
I have an iPhone 5 IOS 8.1.2; for the past 13 weeks I get the message that "this phone has not been backed up on the cloud". However, every night it is connected to wifi, phone is connected to power, and the screen is locked. Any idea why back-u [More]
How do I configure drop down lists to allow for multiple selections?Hi, I am wondering how to best design the list box to allow for multiple selection? I have 4 items i need to have in the box but how do i best communicate it to the end user that the [More]
Hi Experts, I searched all many threads to understand the how Attribute change run is used for But a small question is going still. Is attribute change run used for getting consistent data from aggregates or from infocubes. Attribute change run is us [More]
Hi, In the reporting i want to apply variable and also fixed value restriction for one characteristic. Example: I want to apply variable(single and optional) for plant  and also restrict not assigned values(#) I created variable (given single and opt [More]
I have lots of videos on my iphone, however I do not know how to put these videos on a dvd, they are just in my itunes and i'm not sure what else I can do with them.  I tried using windows movie maker, but it does not support .mov files. I downloaded [More]
Hi, Does anybody have a certificate authenticator working in WebLogic 6.1 SP 2, in combination with the Apache HTTP Server plugin and SSL? We implemented a certificate authenticator that works correctly in WebLogic 6.1 SP 2 when we configure SSL with [More]
Hi there. I'm thinking of getting an iSight but I have a very noisy iMac G5. The fans are loud and it makes a whining noise too. (Long story but Apple say there's nothing wrong with it.) How good is the iSight at filtering out background noise like t [More]
I have created my header in Photoshop and pasted it in to my one-page site as a graphic.  In order to create anchors I've created text boxes over each word - no fill and no stroke - and lableled each one to the corresponding anchor.  In Preview only [More]