1

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.

SampleData

First Try

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.

enter image description here

Second Try

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.

enter image description here

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.

Note

  1. I should not open the excel file manually and change formatting. This step is not allowed.
  2. 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.
  • HDR=YES indicates the first row is a header. Please add the current table definition to your question. – Max Vernon Jun 5 at 13:05
  • 2
    Export your excel data to a different format. I personally prefer tab-delimited .csv files because people don't often include tabs within their free-form data fields, but you can do whatever you want. Excel, in my opinion, is one of the worst "raw formats" to import data from as it does all manner of custom formatting under the covers which often throws off SSIS packages or, as you're seeing, direct TSQL queries using OPENROWSET. – John Eisbrener Jun 6 at 14:18
  • After changing TypeGuessRows, did you restart the SQL Server instance? I tested this and the setting change had no effect until I restarted the SQL Server instance. – HandyD Jun 7 at 1:13
  • @HandyD - I restarted my system, but still not working – Shiva Jun 7 at 4:12
  • @HandyD - I restarted my system and SQL Server instance, but still not working. Did you try with the file which I have shared? – Shiva Jun 7 at 4:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.