Solved

Excel vs SQL data types

Posted on 2011-09-09
5
180 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:murbro
[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 100 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 400 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:murbro
ID: 36510880
Thank you for taking the time out to offer such an extensive answer
0
 
LVL 92

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

726 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