[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Problem when Importing data from excel sheet to SQL server 2005

Posted on 2009-02-17
23
Medium Priority
?
297 Views
Last Modified: 2013-11-30
I try DTS packege and Openrowset for importing data from excel sheet into SQL server 2005.
Data is imported successfully. I have one table Importdata that contains three columns
EmpID(varchar(100), Ename(varchar(100), Salary(int). but problem is if Empid Column contains
text data as well as integer data in excel sheet  eg. test1,1520,test2,4562 like that.
when importing data first row of empid column put test1 properly but second value 1520 is not import instead of that column contains or put null value in table.
Please advise why this is happened? and it's solution.
is there any data setting in Excel sheet for above problem that i want to change or any other reason.
Please help me..............
0
Comment
Question by:prashant_24patil
  • 10
  • 6
  • 4
  • +2
23 Comments
 
LVL 19

Expert Comment

by:daveamour
ID: 23657132
So what do you want Integer or Text?
0
 
LVL 25

Expert Comment

by:reb73
ID: 23657149
Explicity format the source MS-Excel column as 'Text'..

Sometimes you may need to create a new column formatted as text in Excel and then do a paste special of values into this column to bring across the values correctly to SQL  Server
0
 

Author Comment

by:prashant_24patil
ID: 23657877
i want both data integer as well as text.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 19

Expert Comment

by:daveamour
ID: 23657890
Then yes do as reb73 suggests
0
 

Author Comment

by:prashant_24patil
ID: 23657899
Hi reb73,
       can u please tell more specific on this issue.
0
 

Author Comment

by:prashant_24patil
ID: 23657914
hi daveamour,
                     do you know steps for how to done which is suggested by reb73
0
 
LVL 25

Expert Comment

by:reb73
ID: 23659140
Option 1
-----------
1.Select entire source column (e.g. column G - click on the column header)
2.Right-click, select format cells-> Number Tab and choose Text in the available options
3.Save workbook and retry import..

 
0
 

Author Comment

by:prashant_24patil
ID: 23659401
I tried option 1  but it was not work. i am still facing same problem. is there any other way???
0
 
LVL 25

Expert Comment

by:reb73
ID: 23659433
Is it possible for you to attach the MS-Excel file here?
0
 

Author Comment

by:prashant_24patil
ID: 23659545
Ok i attached excel file please check it.
FileImport.xls
0
 
LVL 25

Expert Comment

by:reb73
ID: 23661078
Check attached file, I've inserted a new column B in your original worksheet with formulae converting values in column A to text values.

Here's what you do -

(If you select cell A2 now, the value will be shown in the address/edit bar as 5237 which means its a number value)

1. Goto Menu->Tools->Options, choose Transition tab and ensure 'Transition Navigation Keys' is checked
2. Review the new column B which contains values in A formatted as text
3. Select range B2:B7 and select Edit->Copy or press <Ctrl>+<C>
4. Select cell A2 and right-click->Paste Special->select option Values in Paste Frame and click OK
5. Delete column B

(If you select cell A2 now, the value will be shown in the address/edit bar as '5237 which means its a text value)

Now run your import script..


FileImport.xls
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23666491
This is what you're after:
http://www.sqldts.com/254.aspx
 
You need to explicitly tell the Excel driver not to do dumb stuff (like change numbers to NULL when it find them in a field which is text)
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23666501
PS you want option 2 in that link above, that way you don't need to format your file every time.
0
 

Author Comment

by:prashant_24patil
ID: 23667409
This link is useful. I am using SSIS packages for importing data and scheduled it for week. I am not able to format every time this excel sheet.Option 2 is good bu it is for SQL server 2000 i am using SQL server 2008. can i use option 3  so that i don't need to format my file every time.
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
ID: 23667447
option 2 (and 3) is for the Excel driver in general... be it for DTS, SSIS, whatever.

You basically need to change the connection string.

Here's a better example:

http://www.connectionstrings.com/excel

The very first connection string looks like this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

notice it has the IMEX=1 property in it.

So wherever you are defining your connection string, add the IMEX property to it.

This of course assumes you are using the OLEDB JET driver.
0
 

Author Comment

by:prashant_24patil
ID: 23667559
so please elaborated more on option 3 if nay one knows
0
 

Author Comment

by:prashant_24patil
ID: 23667946
or  also elaborated more on option 2 .
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 23673822
What is there to elaborate? nmcdermaid has given you the solution.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23675892
Option 3 is simply using script to update your connection string. You are better off directly editing your connection string rather than relying on a script to do it for you.
Go and find your current Excel connection string and post it here.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23675949
In fact post your full OPENROWSET command.
0
 

Author Comment

by:prashant_24patil
ID: 23677910
Problem is solved. Thanks to all of you for helping me..

thanks a lot.........
0
 

Author Closing Comment

by:prashant_24patil
ID: 31547674
Thanks a lot nmcdermaid............
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23678105
Glad you sorted it. Can you please expand on that statement... did the IMEX=1 help at all? Could you post the Openrowset syntax so that other people can reference in future?
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.
Suggested Courses

868 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