[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

export excel to table in sql

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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.

656 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