Using Jet to read excel file returns blank for last cell - sometim

[Ron]

With VB6 I am opening an Excel file to query the contents using ADO. I am
then using the contents of the worksheet to create a format file that is used
for a bulk insert into SQL Server. We designed the program this way so that
users can use a spreadsheet to import data in any format they want and
process the data based on values set in the spreadsheet. This keeps us from
having to design new tables and format files for new record layouts which
come down pretty often. Everything works fine usually. There is just one
perculiarity that is happening with the Excel file. Sometimes, the first
cell in the last row of the worksheet returns a blank, even though there is
definately data in the cell - a number that I am converting to a long format.
The conversion fails because the program sees an empty string ("").

I have tried retyping the data in the cell when this happens. ALSO -
Changing the cell format (to general, text, numeric) and Copying and pasting
the entire worksheet.

Usually, to get it to work, I export the entire worksheet to a text file and
then re-import the file and then my program will read that last cell.

The program is also reading all of the other cells on the last row. It is
just the first cell of the last row that it has problems with and it happens
on maybe half the files we create for the program. The number of rows in the
worksheet is different depending on how many columns are in a particular
layout.

Can anyone tell me why this is happening and what I can do to prevent it?

Thanks,

Ron

 

[Peter Huang [MSFT]]

Hi

Based on my understanding, now you wants to import many kinds of data into
excelsheet and then use ADO to insert data from worksheet to SQL server.

So far it seems that the problem is not consistent. Can you help to build a
simple reproduce sample with the test excel file so that we can trouble
shooting the problem in our side?

What do you use the do the query?
Also since Excel is not designed for Database usage, if you do not have any
concern, you may try to use Access instead. You can establish a linked
table to Excel in the Access.

If you still have any concern, please feel free to post here.

Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Ron]

I discounted the mixed data types situation because the column contains all
'text' datatypes (so it isn't mixed) and only the last cell in the first
column is not being read properly. All other cells on that row are being
read properly. It is a very strange problem. It has occured a few times,
but does not happen every time one of the spreadsheets are put together for
use with the application.

 

[Ron]

It is a number, but a text data type, so "17" instead of 17.

So far this is the closest thing to a cause/solution I have found so I will
look into it further. Maybe I should have them make the column a number
column, but the top cell is the column name and has to be text anyway. I
will see how this works but I am still searching for a solution to this.

 

[Ron]

Because of the different elements, this may be difficult to reproduce. I
will include the code if I can not find any solutions today.

 

[Peter Huang [MSFT]]

Hi

Thank you for your reply.
I think it is better to include the test excel file together with the code
which will help us to reproduce the sample.
Thanks!

Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Ron]

Here is the code...

'create connection

Set cnn = New ADODB.Connection

'set connection properties and open

With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & FileName & ";" & _
"Extended Properties=Excel 8.0;"
.Open
End With

'create a recordset with the spreadsheet shema so i can get the sheet name
(table name), store table name in wsName, close recordset

Set objRS = cnn.OpenSchema(adSchemaTables)
wsName = objRS.Fields.Item("table_name")
objRS.Close
Set objRS = Nothing

'build SQL string with spreadsheet headers and wsName (table name)

strSQL = "select [Beg], [End], [Length], [Type], [InsightFieldName],
[FieldDescription], [FieldCount], [SortOrder] from [" & wsName & "]"

'open recordset with SQL string

Set objRS = cnn.Execute(strSQL)

'create cnn2 for SQL server connection, create command object, set
properties for cnn2 and command object, insert data from spreadsheet into sql
server with command oject, close cnn, close recordset

Set cnn2 = New ADODB.Connection
Set objComm = New ADODB.Command

strConnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=MDTOutputA;Data Source=mdtdpp01;Use Procedure for
Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for
Data=False;Tag with column collation when possible=False"

cnn2.Open strConnect

objComm.ActiveConnection = cnn2
objComm.CommandType = adCmdText

Do While Not objRS.EOF

strSQL = "INSERT INTO " & TblName & " ([Beg], [End], [Length], [Type],
[InsightFieldName], [FieldDescription], [FieldCount], [SortOrder]) VALUES ('"
& objRS("Beg") & "', '" & objRS("End") & "', '" & objRS("Length") & "', '" &
objRS("Type") & "', '" & objRS("InsightFieldName") & "', '" &
objRS("FieldDescription") & "', '" & objRS("FieldCount") & "', '" &
objRS("SortOrder") & "')"

objComm.CommandText = strSQL
objComm.Execute

objRS.MoveNext

Loop

objRS.Close
cnn.Close

'NOTE: just the 'end' field for the last row is not inserted correctly.
the end field turns out to be an empty string (""). here is a sample of the
spreadsheet (please let me know if there is a better way to insert this (the
fields are from Beg to SortOrder, it did not paste onto this box very well)...

Beg End Length Type InsightFieldName FieldDescription FieldCount SortOrder
1 12 12 A/N Keycode Keycode x
13 22 10 A/N MemAcctNum Membership Account Number
23 30 8 A/N NATitle Title
31 44 14 A/N NAFname First Name & Middle Initial
45 60 16 A/N NALname Last Name
61 64 4 A/N NASuffix Suffix
65 104 40 A/N NAAddr1 Address Line 1
105 132 28 A/N NAAddr2 Address Line 2
133 150 18 A/N NACity City
151 152 2 A/N State State x
153 157 5 A/N Zip Zip 1
158 161 4 A/N ZipPlus4 Zip Plus 4 2
162 163 2 A/N DPBarcode Delivery Point Barcode
164 167 4 A/N CRoute Carrier Route
168 200 33 X NA2 Filler

 

[Peter Huang [MSFT]]

Hi

The test sheet did not display correct in the web, I think you may try to
post the excel file and your vb6 project files as a zip file and then post
in the newsgroup as attachment.(You may also try in the Outlook express)

Also you may try to email to me directly by removing the "online" from my
email address.

Based on my test so far, your code seems ok.(I tested with other sample
data)
I guess you may try check if the datatype is compatible between the sql and
excel file.
What did you declare the according datatype in the SQL server?
You may try to declare all as varchar to see if it will help.

Also for ADO problem you may also try to post in the newsgroup below to
seek ADO expert for help.
microsoft.public.ado

Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Ron]

This is exactly what I did yesterday - make the SQL datatype varchar. The
code I posted is used to import the layout, the actual data is imported at a
later stage and I had to switch that to varchar a long time ago. I did not
switch the data type for the layout though. I had it set as text actually
and I know that is not good. Hopefully switching to varchar will fix the
problem.

thanks,

ron

 

[Ron]

That is weird, because I have been trying to store all of them as text. How
do i resolve this issue? Make all of them numeric except for the header?

 

[Ron]

the main problem with that solution is I am not the user. Anyone from a
pretty large team could be creating the workbooks. I am only the programmer
in this situation. We made the program so they would not need a coder
everytime a new layout came down the pipe. Hopefully the varchar data type
will take care of the problem.

How do i resolve this issue?
Make all of them numeric except for the header?

That may not be easy to achieve. The best way is to create and populate
the column using Jet e.g.

CREATE TABLE MyExcelTable (
Beg FLOAT NULL,
[End] FLOAT NULL,
Length FLOAT NULL,
Type VARCHAR(255) NULL,
InsightFieldName VARCHAR(255) NULL,
FieldDescription MEMO NULL,
FieldCount FLOAT NULL,
SortOrder FLOAT NULL
)
;
INSERT INTO MyExcelTable VALUES (
1,12,12,
'A/N','Keycode','Keycode x',
NULL, NULL
)
;

But you don't always get the opportunity to create your source
workbooks :-(

It may be better to work with the 'mixed types' situation if possible.
To do this, you must do two things.

First, change the following registry key:

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

Change the value to 0 (zero). This will force Jet to scan all rows
before deciding a mixed situation does not exist. While you are under
the hood, check ImportMixedTypes=Text (the default value).

Second, put your connection into import/export mode by adding IMEX=1 to
your connection string's extended properties e.g.

Extended Properties='Excel 8.0;IMEX=1'

This will force Jet to take account of the above registry settings i.e.
if a mixed situation exists then it will be picked up and all values
for the column will be converted to Text (maximum 255 characters).
Jamie.

 

[Peter Huang [MSFT]]

Hi

Thanks for your quickly reply!

If you still have any concern, please feel free to post here.

Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[narasimharao bandla]

Dear sir i am waiting for u r reply u r code is not working.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[narasimharao bandla]

Dear sir,

I tried u r code but its showing error in wsname =
objRS.Fields.Item("Sheet1$") line.i tried by giving table name and as
well as sheet name but both are not working it showing error u please
provide solution to my problem.
please.....

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!