Import CSV to Excel and avoid text to number conversion
When you import comma separated values from a CSV file or export data with tools like Toad
to Microsoft Excel it tries to guess the type of each cell and apply some default formatting.
If you have e.g. Bank account numbers line 1.23456000 or telephone numbers like 0041761234567 which look like numbers
to Excel it will strip leading or trailing zeros or turns them into scientific notation and strips leading zeros.
For example you want to import this values
0041-79-1232-478
+417912324789
6.2.567000
00123456789123344
1.2345000
If you open that file in Microsoft Excel the first three rows are ok but 00123456789123344 will be turned into 123456789123344 and displayed as 1.234567E14 and 1.2345000 as 1.2345
0041-79-1232-478-9
+417912324789
6.2.567000
1.234567E14
1.2345
This is very annoying and unacceptable.
Simply rename the file with a .dif extension
move sample.csv sample.dif
and open it in Excel.
- Select Semicolon Separated Values
- You will then ask how to import each column.
- Select Text for the columns in question.
- Finish
- Mark and Copy the grid to the clipboard in Toad
- Open and empty Excel sheet
- Paste the Data
- Change Format of the columns in question to Text
- Paste the Data again
Status: Published Date: 2017/06/05 14:13:00 Revision: 1.1
Copyright bei Andreas Haack (C) 2014.
Diese Seite wird so wie sie ist zur Verfuegung gestellt, ohne irgenweche Garantien der Verwendbarkeit fuer bestimte Zwecke. Die auf dieser Seiten angebrachten Links liegen ausserhalb der redaktionellen Verantwortung von Andreas Haack und es wird keine Haftung oder Garantie uebernommen. Die Seiten sind Copyright (c) 2014 von Andreas Haack. Kein Teil darf ohne die schriftliche Einverstaendnis von Andreas Haack veroeffentlicht werden.
The page is provided 'as is' , without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fit- ness for a particular purpose and non-infringement. In no event shall Andreas Haack be liable for any claim, damages or other liability. This page is copyrighted property of Andreas Haack. Copyright by Andreas Haack (c) 2014 . No part of this page may be published without written permission for Andreas Haack. A hyper-link may created to this page but NOT to the embedded elements of this page. It may be freely downloaded for private purpose only as long as it is unaltered.