Solved

export excel to table in sql

Posted on 2014-04-29
3
789 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

728 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

10 Experts available now in Live!

Get 1:1 Help Now