Excel vs SQL data types

Hi

In the coming months I have been assigned a task that involves adding large volumes
of data to SQL databases from Excel. I need to know as much as possible on which data types in SQL match up to the cell types in Excel. Any up to date  articles on this would be much appreciated. Thanks
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
dbaSQLConnect With a Mentor Commented:
Excel to SQL Server is quite doable, but there are many little problems with the formatting.  This is because Excel only recognizes a limited number of datatypes, and the string types are all unicode.  As a result, you will need to explicitly convert certain datatypes upon insertion into SQL.  In my book, SSIS is the best tool for this type of ETL... importing data from Excel (or any flat file) into SQL Server, and even the other direction, SQL to Excel.   Either way, you can use the derived columns, or data conversion transformations functions in SSIS, to get your data translated from Excel to SQL Server.
 
This is a general overview of Excel to SQL via SSIS:
http://dougbert.com/blogs/dougbert/archive/2008/06/18/excel-in-integration-services-part-2-of-3-tables-and-data-types.aspx

Theser are many very good references:
http://www.mssqltips.com/sqlservertip/1393/import-excel-unicode-data-with-sql-server-integration-services/
http://irfansworld.wordpress.com/2011/01/30/how-to-load-unicode-data-from-excel-to-sql-server-that-supports-non-unicode-formats/
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/12202c8c-aa5b-4c4d-a02e-65e4f63590f8/
http://dotnetslackers.com/articles/sql/Importing-MS-Excel-data-to-SQL-Server-2008.aspx#data-type-mismatch-and-the-fix


Lastly, these guys are one of the best SSIS/ETL resources I've seen:
http://sqlis.com/sqlis/
0
 
JestersGrindConnect With a Mentor Commented:
Generally there are only a handful of different data types in Excel.  Text or strings can be mapped to a varchar.  Numbers can be mapped to integer or decimal depending on if it's a whole number or not.  Dates can be mapped to datetime.  In SQL 2008 you can also map to just a date data type if you're not concerned with the time.  A boolean can be mapped to a bit.  Those are the most common ones that I've dealt with.

Greg

0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thank you for taking the time out to offer such an extensive answer
0
 
Patrick MatthewsCommented:
>>Text or strings can be mapped to a varchar.  

Or nvarchar, if you need any characters outside of the ANSI character set.

>>Numbers can be mapped to integer or decimal depending on if it's a whole number or not.

Side note about these:

1) Keep in mind that the SQL Server integer type is NOT the same as the VBA integer type.  SQL Server matches up to the VBA Long data type.

2) Excel and SQL Server are subtly different when it comes to specifying decimal precision.  So, an Excel (VBA) Single or Double value keeps a maximum number of significant digits, whereas SQL Server decimal/numeric types specify maximum precision for the entire value as well as how many significant digits are reserved for the decimal portion of the number.
0
 
dbaSQLCommented:
My pleasure, murbro.  good luck!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.