prashant_24patil
asked on
Problem when Importing data from excel sheet to SQL server 2005
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..............
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..............
daveamour
So what do you want Integer or Text?
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
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
ASKER
i want both data integer as well as text.
Then yes do as reb73 suggests
ASKER
Hi reb73,
can u please tell more specific on this issue.
can u please tell more specific on this issue.
ASKER
hi daveamour,
do you know steps for how to done which is suggested by reb73
do you know steps for how to done which is suggested by reb73
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..
-----------
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..
ASKER
I tried option 1 but it was not work. i am still facing same problem. is there any other way???
Is it possible for you to attach the MS-Excel file here?
ASKER
Ok i attached excel file please check it.
FileImport.xls
FileImport.xls
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
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.
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)
http://www.sqldts.com/254.
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
so please elaborated more on option 3 if nay one knows
ASKER
or also elaborated more on option 2 .
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.
Go and find your current Excel connection string and post it here.
In fact post your full OPENROWSET command.
ASKER
Problem is solved. Thanks to all of you for helping me..
thanks a lot.........
thanks a lot.........
ASKER
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?