Excel column format errors during database migration lead to data loss-Oracle-Datapump

Excel column format errors during database migration lead to data loss-Oracle-Datapump
the
reason:
the
Export the data from the old SQLServer database to Excel, use Excel to save the data as a DBase 3 format file, and use Datapump to create a DBase III (*.dbf) format file into the Oracle database. www.2cto.com
Everything went smoothly, except that after a week of basic data migration, many records with a total material usage of 0 were found on the new API.
the
How can it be? If the total consumption is 0, it means that it is not used, and this kind of material will definitely not be put into storage at all.
process:
the
1. After getting to the bottom of it, I finally found that there are many records with a total usage of 0 in the data in the Oracle database. If it is 0 here, then the data migration is based on one time, and the processing result must be 0.
2. Continue to track and find that there is no record of 0 in the Excel data file exported from SQL Server.
3. However, it is found that the data with a usage of 0.017 has become the data in the format of 1.70E-02 (the same is true in dbf), so it is suspected that Datapump cannot recognize such a data format. www.2cto.com
the
4. Change the format of the data column from scientific notation to regular, and it will be normal after re-conversion (it is simple to say, but it takes a long time to do it again, almost a week)
the
in conclusion:
1. Data verification is very important, including the format (because after all, we want to use off-the-shelf tools, which do not guarantee the business of the data).
2. Organize and sort out the operation logs during the database migration process, so that you can quickly start all over again in the event of an accident.
3. Database migration is not as easy as imagined.
the
the
Author noodle123