Sql-server – Importing from Excel

excelsql serverssms

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:

Import a sheet with only headers and sample data (to "seed" the data type) into a new table
Use Design view to correct data types as necessary
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:

How can I get the data imported with the correct type?
Why does the Importer get it wrong sometimes? Other columns without data are recognized as "float".

Best Answer

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.

MANUAL ALTERNATIVE

If you have the patience, you could do the following

STEP 01) Create a table using the CSV Storage Engine

When you create the table like this:

CREATE TABLE mydb.mytable
(
    i INT NOT NULL,
    c CHAR(10) NOT NULL,
    ...
    column29 INT NOT NULL,
    column30 CHAR(10) NOT NULL,
    column31 CHAR(30) NOT NULL
) ENGINE=CSV;

This will create two files under the mydb subfolder

mytable.frm
mytable.CSV

CAVEAT : I do not think NULL columns are allowed for MySQL CSV tables

STEP 02)

Using Excel, export the spreadsheet with the same number of columns as the MySQL CSV table. Empty Cells should come out as "".

STEP 03) If using MySQL in Linux, you could do this

cd /var/lib/mysql/mydb
mv mytable.CSV mytable_old.CSV

STEP 04) Copy the Excel output into /var/lib/mysql/mydb/mytable.CSV

STEP 05) In the mysql client, run

FLUSH TABLES;
SELECT * FROM mydb.mytable;

This will close and reopen all tables. If you see your data, CONGRATULATIONS !!!

STEP 06) If you get an Error

If you get an error on SELECT * FROM mydb.mytable;, then one of the cells is not field enclosed with double quotes. Just re-export the data and try Step 04 again until you get it right

For Those using MySQL for Windows

If you have MySQL for Windows, you can drop in mydb\mytable.CSV becauswe of the way Windows locks files. You will have to run net stop mysql, copy the file in, and net start mysql, skip the FLUSH TABLES; and just run SELECT * FROM mydb.mytable;

Give it a Try !!!