We help IT Professionals succeed at work.

Problem when Importing data from excel sheet to SQL server 2005

Medium Priority
305 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..............
Comment
Watch Question

So what do you want Integer or Text?

Commented:
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

Author

Commented:
i want both data integer as well as text.
Then yes do as reb73 suggests

Author

Commented:
Hi reb73,
       can u please tell more specific on this issue.

Author

Commented:
hi daveamour,
                     do you know steps for how to done which is suggested by reb73

Commented:
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..

 

Author

Commented:
I tried option 1  but it was not work. i am still facing same problem. is there any other way???

Commented:
Is it possible for you to attach the MS-Excel file here?

Author

Commented:
Ok i attached excel file please check it.
FileImport.xls

Commented:
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
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)
PS you want option 2 in that link above, that way you don't need to format your file every time.

Author

Commented:
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.
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
so please elaborated more on option 3 if nay one knows

Author

Commented:
or  also elaborated more on option 2 .
CERTIFIED EXPERT
Top Expert 2012

Commented:
What is there to elaborate? nmcdermaid has given you the solution.
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.
In fact post your full OPENROWSET command.

Author

Commented:
Problem is solved. Thanks to all of you for helping me..

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

Author

Commented:
Thanks a lot nmcdermaid............
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?
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.