[RESOLVED] Excel Data Type
Hi all,
I'm trying to import an excel file to MS SQL Express 2005 by using VS 2003 VB.Net in ASP.Net.
The problem I'm facing now is when I import the data by using IMEX=1, its running OK in my laptop the one i used to develop this program.
However, when I run the application in Windows Server 2003 R2, it didn't run as smooth as my laptop.
The excel file has total 2156 rows. It can be imported all in my laptop but in Server OS, it only import 248 rows.
If I remove this IMEX = 1 from my OLEDB Connection, it (server) will import all rows that is 2156.
The reason I put this IMEX = 1 is among the columns, there is one column which values are NULL mix with Number. If I use without IMEX, it will import all rows as NULL for this column. So, I searched the net and found that this IMEX = 1 will treat as Text so I used it in my program.
Strange is it works on my PC but not in server.
Anybody any suggestions???
Thanks.
scsfdev
Re: [RESOLVED] Excel Data Type
i found
Quote:
5. In the Edit DWORD Value dialog box, click Decimal under Base. Type a value between 0 and 16, inclusive, for Value data. Click OK and quit the Registry Editor.
NOTE: For performance reasons, setting the TypeGuessRows value to zero (0) is not recommended if your Excel table is very large. When this value is set to zero, Microsoft Excel will scan all records in your table to determine the type of data in each column.
from http://support.microsoft.com/kb/189897, but may be different in later versions
Re: [RESOLVED] Excel Data Type
What scsfdev and westconn1 found is good to know, but if you are allowed you can only change registry entries on your PC but not on other users' PC.
In this particular case, I think it is OK if [Total Sum] is NULL but perhaps you hit the problem because [Total Sum] = "".
Why not change your SQL to convert the suspected fields to text such as below (not sure if it is correct):
Code:
" CAST([Total Sum] AS TEXT) as [Total Sum] , " & _
or
Code:
" CASE [Total Sum] WHEN [Total Sum] = "" THEN NULL ELSE [Total Sum] END as [Total Sum] , " & _
Re: [RESOLVED] Excel Data Type
Hi anhn,
What you have suggested in previous post didn't work for me.
It gave me the below error for both two ways.
Code:
System.Data.OleDb.OleDbException: IErrorInfo.GetDescription failed with E_FAIL(0x80004005). at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at ......
Not sure what went wrong. I google it and reserved word.
but as you can see from my query, there is no reserved word and beside i wrap all field with [..].
Thanks.
Re: [RESOLVED] Excel Data Type
With Jet, try this:
Code:
strSQL = " SELECT LEFT(A.[Product Model], 255) as [Product Model], " & _
" LEFT(A.[Person InCharge], 255) as [Person InCharge], " & _
" LEFT(A.[Customer Name], 255) as [Customer Name], " & _
" LEFT(A.[Invoice Number], 255) as [Invoice Number], " & _
" LEFT(A.[Item Number], 255) as [Item Number], " & _
" LEFT(A.[Item Description], 255) as [Item Description], " & _
" """" & A.[Total Sum] as [Total Sum] , " & _
" A.[Amount] as [Amount] " & _
" FROM [" & Me.txtSheetName.Text & "$] AS A"
Re: [RESOLVED] Excel Data Type
Quote:
Originally Posted by
anhn
With Jet, try this:
Code:
strSQL = " SELECT LEFT(A.[Product Model], 255) as [Product Model], " & _
" LEFT(A.[Person InCharge], 255) as [Person InCharge], " & _
" LEFT(A.[Customer Name], 255) as [Customer Name], " & _
" LEFT(A.[Invoice Number], 255) as [Invoice Number], " & _
" LEFT(A.[Item Number], 255) as [Item Number], " & _
" LEFT(A.[Item Description], 255) as [Item Description], " & _
" """" & A.[Total Sum] as [Total Sum] , " & _
" A.[Amount] as [Amount] " & _
" FROM [" & Me.txtSheetName.Text & "$] AS A"
Hi anhn,
Sorry for my late reply.
After using your above method, its also working.
but I wonder is there any way I can test the query like this in/for excel without using Visual Studio SDK?
Like Access DB, we open it write a query and retrieve the value.
Is there any way like this for Excel file?
Thanks and regards,
scsfdev