Solved

export excel to table in sql

Posted on 2014-04-29
3
803 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
  • 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

803 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