Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

export excel to table in sql

Posted on 2014-04-29
3
Medium Priority
?
860 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 23

Accepted Solution

by:
Steve Wales earned 2000 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 23

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

580 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