Solved

export excel to table in sql

Posted on 2014-04-29
3
829 Views
Last Modified: 2014-04-29
I have an excel sheet that I would like to import into a table in SQL.  Is there a way to do this?
0
Comment
Question by:al4629740
[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
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 40031028
Yep!

Open SSMS, choose the database you want in the object explorer.

Right click, choose Tasks ... Import Data.

Choose Excel as your input.

Choose database / table as output.

Map the columns to the table (or if the first row has column names you can have it auto create and you have an excel sheet imported into SQL Server.
0
 

Author Comment

by:al4629740
ID: 40031072
when running it, I get the following error

- Executing (Error)
Messages
Error 0xc020901c: Data Flow Task 1: There was an error with Source - Sheet1$.Outputs[Excel Source Output].Columns[Description] on Source - Sheet1$.Outputs[Excel Source Output]. The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
 (SQL Server Import and Export Wizard)
 
Error 0xc020902a: Data Flow Task 1: The "Source - Sheet1$.Outputs[Excel Source Output].Columns[Description]" failed because truncation occurred, and the truncation row disposition on "Source - Sheet1$.Outputs[Excel Source Output].Columns[Description]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Source - Sheet1$ returned error code 0xC020902A.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40031085
It seems to set up columns with a max width of 255 - I don't know if this is an SSIS limitation though  (that's all this wizard is, a front end to an SSIS process, I believe).

There's an article here that talked about the specific SSIS equivalent: http://www.bidn.com/blogs/BradSchacht/ssis/2623/excel-source-the-output-column-failed-because-truncation-occurred

You could also set up the table beforehand, I would think in order to have columns set with the correct width.

I've never used this to completion, though - I just know it's out there.
0

Featured Post

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

719 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