Last post Mar 06, 2019 03:22 AM by Ackerly Xu
Mar 05, 2019 05:07 PM|johnspiderwebb|LINK
Mar 05, 2019 05:35 PM|PatriceSc|LINK
I always found this to be a real mess ;-)
The problem is that Excel doesn't know about column types and db drivers are usually trying to guess from the first few rows which can cause issues. Try to make sure that all columns are using the same type and that you have some values in the first few
rows. Maybe also https://www.concentra.co.uk/blog/why-ssis-always-gets-excel-data-types-wrong-and-how-to-fix-it (and at worst you may need to change
values in the registry)
When doing that from a web app, my personal preference is to use an Excel library so that I can handle all values explicitely depending on what I expect them to be...
Mar 05, 2019 06:58 PM|mgebhard|LINK
When I use sql server import to import an excel file. Some of the date columns import correctly. But some import as varchar. . The actual value that ends up in the table is the number that excel uses as a date
I have put in a dummmy row with a proper date in it and it still doesn’t work.
Frankly, what I do any more, is require a specific file format. If the file fails validation the user must fix the file and resend. The problem is, if you do not enforce a standard interface and try to fix ever error (being nice), you'll add import fixes
forever. Eventually, one of the fixes will break other formats and you'll get bad data.
Mar 06, 2019 03:22 AM|Ackerly Xu|LINK
The way sqlserver decides what datatype to map is to select some sample data of your excel column.
But the count of the sample is limited, so if your first several rows is not recognized as datetime , for example the first several rows are all empty , sql server may recognize it as varchar.
So a workaround is to change the several rows of your column to date type and after imported remove them.
For more information , you could refer to