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
I am now trying Creative Cloud and I came with this bug. When I use a simple action to find and replace a text string in my file it will crash the Illustrator. My actions is pretty simple: Find > "aa" (I do have to check all the options to wo [More]
I have inadvertently infringe copyright on facebook and they have subsequently barred me from uploading any more videos. The question I need answered is ( and not able to find the answer on any of you help pages)..... Do the following have free copyr [More]
Hi, My requirement is I need to write an routine at the infopackage level for the fiscal period which should have the low value as the current period and the high value as current period +12 months. For example. As per the system date the low value s [More]
Hi guys, for the automatic determination of the route for MM module is it mandatory to fill the storage location? Thanks in advance, Dom.Dominique, Route determination uses the country code and transportation zone from the address records as some of [More]
Hello, When I try to enter time I'm getting thefollowing error after I press enter "counting rule 08/2/00/.. doesnot exist". In Data Entry Area I'm entering the details for Activity type, Rec. order, Attendance/Absence Type and the hours for the [More]
I just recently purchased an iphone 5s and today my speaker located at the rear end of my device is not sounding clearly, it sound muffed and blurry. Is there any way I could get a new one or get this fix for free?Hi Jayliin, Take it to the Apple Sto [More]
I cannot access a site I use often as a 403 error occurs. How do I resolve?You need to contact the website and tell them to upgrade their security protocols. Especially the following: * The site uses SSLv3, meaning it's vulnerable to the well known P [More]
Hi, Im trying to put the apex page(http://apex.oracle.com/pls/apex/f?p=44015:3:2304706657174469:::::) inside a iframe. but i cant achive it. when i run the below code the page cant able to display inside the iframe. <html>      <head> <titl [More]
Hey Guys, I created some packages in SCCM 2012 R2 (really the old packages no applications). Because this package is just a command line that runs I created the program directly without content. The package was deployed and works fine :) But in "Moni [More]
Hi, I have a scenario where we need to create an exception when a mapping error occurs. This is a 2 step process. The first step is to log the data into audit tables and the second step is to raise an exception. I have the following questions. How ca [More]