[RESOLVED] Excel Data Type

[scsfdev]

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

[anhn]

Not sure if it can help but try this to see what happens.

[scsfdev]

Not sure if it can help but try this to see what happens.

Hi anhn,

Thanks for you link.
According to your link, what I understand it, I may need to open the excel file and do those formatting.

I want to know is can I avoid this step? (without opening the excel file and edit Type)

Here is currently what I'm doing.

Dim fullPath As String = getFileName(txtFilePath.PostedFile.FileName)
Dim strSQL As String
strSQL = " SELECT LEFT([Product Model], 255) as [Product Model],  " & _
" LEFT([Person InCharge], 255) as [Person InCharge], " & _
" LEFT([Customer Name], 255) as [Customer Name], " & _
" LEFT([Invoice Number], 255) as [Invoice Number], " & _
" LEFT([Item Number], 255) as [Item Number], " & _
" LEFT([Item Description], 255) as [Item Description], " & _
" [Total Sum] as [Total Sum] , " & _
" [Amount] as [Amount] " & _
" FROM [" & Me.txtSheetName.Text & "$]"
 
Dim objDataset1 As New DataSet
'Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fullPath & ";Extended Properties=""Excel 8.0;"""
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fullPath & ";Extended Properties=""Excel 8.0;IMEX=1;"""
Dim objConn As New OleDbConnection(sConnectionString)

The problem is in [Total Sum] Column. In my SQL DB, its datatype is Float.
In Excel file, this column has null value(no vlaue in some particular rows.) + double datatype in it.
Worst case is its first around 20 rows is empty. So, if I didn't use IMEX=1 in my connection string, it will assume it as NULL and so all my rows become NULL in SQL Server.
If I use IMEX = 1, it will stop importing at a particular row which value is "Double DataType" like 10.22 or 0.321 in this [Total Sum] column.

If there is no choice, I need to follow your way (changing type) since this is the only one I found on net so far

[scsfdev]

Hi all,

After doing the searching and read carefully again, I found that there are some registry values we can change.

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes

Now I found the result of why my PC can read all rows while server 2003 can't this is because of TypeGuessRows count in Registry value. My Laptop has value of 30 while Server only has default 8 rows. After I change it to 30, it solved the problem now.

However, who knows the next time user will not import Excel file which has first 30 or 40 rows in blank???
So, I gonna check the method from anhn more detail.
So far, since my case is solved, I will close this thread.

Thanks for all.
And thanks again anhn for your method.

[westconn1]

i found

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

[anhn]

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

" CAST([Total Sum] AS TEXT) as [Total Sum] , " & _

or

" CASE [Total Sum] WHEN [Total Sum] = "" THEN NULL ELSE [Total Sum] END  as [Total Sum] , " & _

[scsfdev]

Hi anhn,

What you have suggested in previous post didn't work for me.
It gave me the below error for both two ways.

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.

[anhn]

With Jet, try this:

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"

[scsfdev]

With Jet, try this:

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