SQL Server: How to Avoid Data Loss During Migration From MS Excel

While importing data from excel sheet, keep in mind that, SQL Server export/import utility look into first  row of your data in excel sheet to guess data types of different columns.  Problem occurs when we want to import columns, which contains both numeric and character data.

In above screen shot of ms excel data sheet, we have three columns, first one contains only numeric data, but second and third columns contains mix (numeric and character data), which in future we like to import as varchar or nvarchar.  If data type of cells in Excel is GENERAL (that is default). During data migration on column mapping page we can see that, utility made guess for data types according to data in first row. For example first cell of second column contains numeric data, that’s why utility guessed float as data type for whole column.

Check out the results, after import is complete. We lost the data for some rows in second and third columns. Because utility imported data that fits into data type criteria and left other. Problem still exists even if we change the data type during mapping.

Best way to resolve this problem is that we should change the data type of each cells, explicitly in Excel Sheet i.e. for columns which are supposed to contain numeric , data type should be number and for character it should be text. After changing data type explicitly in Excel sheet, mapping page will look like as follow.

Now check the results.