Solved

Excel vs SQL data types

Posted on 2011-09-09
5
178 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
5 Comments
 
LVL 21

Assisted Solution

by:JestersGrind
JestersGrind earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you for taking the time out to offer such an extensive answer
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
>>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
Comment Utility
My pleasure, murbro.  good luck!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now