1

I am attempting to import excel sheets into SSMS and I am having trouble with data types. I was running into problems with columns with both text and numbers in them. I want these columns to import as varchar, but some of the columns were importing as "float" and I was getting an "unknown type conversion" error that would crash the package. Changing the mapping seemed to cause more problems than it solved. The process that I have found that seems to work most of the time is as follows:

  1. Import a sheet with only headers and sample data (to "seed" the data type) into a new table
  2. Use Design view to correct data types as necessary
  3. Import the sheet with all the headers and data

This has worked for 9 tables. However, I am now running into the following problem: I followed steps (1) and (2) above. When executing step (3), I am getting "unknown column type conversion" errors for columns without any data in them. For some reason, the Import tool interprets a couple of empty columns as "DateTime" when the data type of the table column is "float". This is causing the import to fail. I changed the data type in Excel properties to "Number" but that does not seem to make a difference. I have two questions:

  1. How can I get the data imported with the correct type?
  2. Why does the Importer get it wrong sometimes? Other columns without data are recognized as "float".
  • 2) Why does the Importer get it wrong sometimes? The import/export wizard determines a data type by looking at the first handful of rows and choosing the most frequently occurring. There's a registry setting to change this. – SQL_Underworld Dec 29 '16 at 6:53
  • Info on the registry setting: google.com/amp/s/sqlserverrider.wordpress.com/2012/11/11/… – SQL_Underworld Dec 29 '16 at 7:00
  • Two other options - design an SSIS package or concatenate the data in Excel into INSERT statements for all of your records (do this for the first line and then drag the formula down). – SQL_Underworld Dec 29 '16 at 7:03
  • @James Alesi, How you try to import, i mean through TSQL or SSMS tools. – Md Haidar Ali Khan Dec 29 '16 at 7:39
  • @SQL_Underworld, can you elaborate on concatenating data in Excel into INSERT statements? – James Alesi Dec 29 '16 at 16:53
1

There are two ways to tackle this.

One is to use something similar to the following in a column somewhere after all of your data (and then wrap every 1,000 records (or less) in an INSERT statement):

=CONCATENATE("('",A2,"','",B2,"','",C2,"','",(IF(D2<>"",TEXT(D2,"dd-Mmm-yyyy"),""))

...you get the idea. You then drag the formula down to the end of your data set, copy the column, and paste as values.

The other method is to write the concatenation in such a way that each record is its own insert statement:

=CONCATENATE("INSERT INTO [database].[schema].[table] VALUES ('",A2,"','",B2,"','",C2,"');") 

This helps if you're lazy like me and don't want to find every 1,000 records to wrap in INSERT statements.

What you're left with in either case is TSQL you can execute in SSMS.

1

As you said

1) Import a sheet with only headers and sample data (to "seed" the data type) into a new table
2) Use Design view to correct data types as necessary
3) Import the sheet with all the headers and data

I would like to say that if you want to import excel sheet with header data then through TSQL is the best option to do that.

First you have to check in SSMS tools there is ODBC driver are there or not in Server Objects->Linked Servers->MICROSOFT.ACE.OLEDB.12.0 or other microsoft ACE OLEDB driver. it depends on your SQL Server version. In My case it is SQL Server 2012.

enter image description here

Suppose that if you are using SQL Server 2012 and there is MICROSOFT.ACE.OLEDB.12.0 then make sure 'Allow inprocess' is Enable.

How You shall do that

Right click over MICROSOFT.ACE.OLEDB.12.0 driver and in new window there will be 'Allow inprocess' option. check to that option like thatenter image description here

write down in your SSMS this TSQL statement like that, through this query you shall import your execl column header with your data.

Use DatabaseName;
        Go
        select * INTO [User]
        from openrowset('MICROSOFT.ACE.OLEDB.12.0',
        'Excel 12.0; Database=C:\TestFolder\User.xlsx; HDR=yes; IMEX=1',
        'select * from [User$]');
        Go

Here DatabaseName is the Name of Database.
User is the Table Name .
User.xlsx is the Excel sheet , which you will import.
Make sure that HDR='yes' which will contains your header column names.

I hope so that it will helpful to you . To import your Excel sheet data in SQL Server through SSMS tools & TSQL Script.

  • I tried the above and I get the following error: – James Alesi Dec 29 '16 at 16:25
  • OLE DB provider "MICROSOFT.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 10 Cannot initialize the data source object of OLE DB provider "MICROSOFT.ACE.OLEDB.12.0" for linked server "(null)". – James Alesi Dec 29 '16 at 16:25
  • I have Microsoft.ACE.OLEDB.12.0, 15.0 and 16.0 – James Alesi Dec 29 '16 at 16:26

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.