0

I am exporting data from oracle table into a csv file. I have a column of varchar2 datatype and it has values like 1.1 and 1.10. When I export these to a csv file the value 1.10 becomes 1.1 and thus creating duplicate records. Is there a way to get both the values 1.10, 1.1 into csv file without loosing the last zero in "1.10".

I am using sqldeveloper and pl/sql developer and using excel to open the csv file. Ideally it should display the number as it is since its a varchar. But this is not happening. When I export it in xls/text format I am getting the correct result. But not with csv. But I want the file to be in csv format.

sandywho
  • 33
  • 3
  • Oracle probably stores your (version?) data as text, but this information is lost when you export to csv and excel considers it to be number. I don't know how is possible to change settings at open, best way I think is to open your file in notepad or similar and copy the content to excel. There you'll have all the data in one column, separated by comma, go to data - "text to columns" and there you'll be able to specify your problematic column to be text – Máté Juhász Aug 10 '15 at 13:12
  • @Mate I tried exporting data to text then opened it in excel and specified the required column as text. But as soon as I save it as .csv it removes the last '0''s – sandywho Aug 10 '15 at 13:18
  • @sandywho [Please read this answer](http://superuser.com/a/527894/50173) – nixda Aug 10 '15 at 13:22
  • @nixda Thanks for the help. Itried everything but its not working. I think this is a bug in excel – sandywho Aug 10 '15 at 13:52
  • 1
    @sandywho I doubt that. Import your CSV with data import wizard as described in my link.This way, all values are stoed as text. Then, save it as CSV without changing anything in Excel. Open the CSV again, but with an editor like notepad. You will see that your zeros are still there. I just re-re-retested it – nixda Aug 10 '15 at 13:58
  • CSV does not store type information. Therefore, at the least provocation Excel will try to guess datatypes which often leads to this sort of mangling. – Scarlet Manuka Aug 11 '15 at 09:50
  • To prevent this, try exporting your data enclosed in quote marks: "1.10" rather than just 1.10. Then when Excel reads the CSV it knows to treat that value as text and will not alter it. – Scarlet Manuka Aug 11 '15 at 09:51

0 Answers0