I am trying to import huge data without any data loss from excel sheet to SQL Server. The excel sheet has few columns with mixed data types and few column with dates. I have prepared a sample file as seen in the first image below.
When I try to import through OPENROWSET query with
HDR=YES, data loss occurred under first column. Second column got imported fine. Please refer image below.
When I try to import through OPENROWSET with
HDR=NO, data imported fine under first column without any data loss, but second column got imported only with time whereas date was not imported. Please refer image below.
Edit 1:Third Try
After extensive internet search, I tried editing registry TypeGuessRows=1048576 according to this microsoft article. But this worked for the smaller data. But issue still persists with huge data. I have shared the data where you can test with it. Please download the excel file to test from this link and if it works please share the solution. In the excel file which I have shared in the link, please note that the row A1000011 has alphabetic chars which is not getting imported to SQL Server with
HDR=YES method. If I use
HDR=NO, date is not getting imported. Both column data is significant for me.
I need solution to import all data with any kind of formatting from excel to SQL Server without any data loss. Please advise.
- I should not open the excel file
manuallyand change formatting. This step is not allowed.
- Not all files are in same structure. I am getting many files with many columns with different structure. So I cannot create a standard table with data types fixed.