Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel vs SQL data types

Posted on 2011-09-09
5
Medium Priority
?
192 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 21

Assisted Solution

by:JestersGrind
JestersGrind earned 400 total points
ID: 36510466
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
 
LVL 17

Accepted Solution

by:
dbaSQL earned 1600 total points
ID: 36510534
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
 

Author Closing Comment

by:Murray Brown
ID: 36510880
Thank you for taking the time out to offer such an extensive answer
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36510910
>>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
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36511230
My pleasure, murbro.  good luck!
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question