1

I have an excel file where the column rule_Number is a 4 digit number and have leading zeroes for few rows.

In the excel file I have formatted the cell as "0000" format. Now the values are displayed properly in excel file

But when I import the excel file into SQLDeveloper, then the value "0117" is changed into "117.0"

Could someone please let me know the correct format I should use in excel so that the SQLDeveloper will work correctly?

Thanks.

firstpostcommenter
  • 2,328
  • 4
  • 30
  • 59
  • Importing 'into SQL Developer'? Don't you mean 'using SQL Developer to import into a database table'? What data type is the target field? If it's a numeric type then I don't think it will ever display in SQL Developer with leading zeros. If it's a string then you may need to have a dummy row at the top of your Excel data with something like 'xxxx' in the rule_Number column to force SQL Developer to see the column as string data. This is purely guessing, based on a similar experience importing into SQL Server some years ago. – Skippy Mar 11 '17 at 13:04
  • The datatype is VARCHAR2. I have tried by inserting a dummy row in the excel file with 'xxxx' but that did not work. The only temporary option that works is to copy the data from excel and paste it in SQL Developer directly (after adding empty rows in SQL developer GUI) – firstpostcommenter Mar 11 '17 at 13:16
  • Hmmm, you may have formatted your Excel cell so that 117 displays as "0117", but as far as Excel is concerned it's still a numeric value. I suspect that the only way to get it to import as a varchar2 would be to tell Excel that it's string data by entering `'0117` into the cell (note the leading apostrophe). – Skippy Mar 11 '17 at 16:52
  • @Skippy: Thanks. When I add an apostrophe manually in excel then it is working fine. Instead of doing for each cell in the column manually I used http://stackoverflow.com/questions/8181749/adding-appostrophe-in-every-field-in-particular-column-for-excel – firstpostcommenter Mar 11 '17 at 20:03
  • @Skippy: I am not able to convert 117 to '0117 in excel for whole column automatically. Not sure if this is possible? – firstpostcommenter Mar 11 '17 at 20:16
  • 1
    Say your data is in column A. Insert a blank column at B. Formula in B2 (I am assuming row 1 is header) should be `="'" & Right("0000" & A2, 4)` Copy this down for the rest of your data rows. Then copy column B and paste values into column A. Now you can delete column B and try your import again. – Skippy Mar 11 '17 at 22:49

2 Answers2

1

Answer: convert your Excel numeric data to string data with the leading zeros.

Skippy
  • 1,595
  • 1
  • 9
  • 13
1

Personally would prefer using this Excel formula before copy/pasting as values back to the target column:

=TEXT(A2,"0000")

dreamwork
  • 96
  • 4