Importing from Excel

Question

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".

Answer 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.

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 that

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.

Answer 2

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.