?
Solved

Excel vs SQL data types

Posted on 2011-09-09
5
Medium Priority
?
187 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

800 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