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

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.

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]" wrote:

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.

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.

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]" wrote:

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.

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.

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!

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!

Have you discounted a 'mixed data types' situation? e.g. see:

http://www.dicks-blog.com/archives/2...ed-data-types/
Jamie.

--

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.



"onedaywhen" wrote:

Have you discounted a 'mixed data types' situation? e.g. see:

http://www.dicks-blog.com/archives/2...ed-data-types/
Jamie.

--

Ron now says ...

the column contains all
'text' datatypes (so it isn't mixed)

But Ron originally said ...

there is
definately data in the cell - a number that I am
converting to a long format.
Sounds to me like something or someone is mixed :-)

Jamie.

--

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.

"onedaywhen" wrote:

Ron now says ...

the column contains all
'text' datatypes (so it isn't mixed)

But Ron originally said ...

there is
definately data in the cell - a number that I am
converting to a long format.
Sounds to me like something or someone is mixed :-)

Jamie.

--

Ron wrote:

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.


FWIW assuming your connection string contains HDR=YES (first row has
column names, the default value being TRUE) then the header row will
not count towards the column's data type.

Jamie.

--