Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Jet to read excel file returns blank for last cell - sometim
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Jet to read excel file returns blank for last cell - sometim
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Jet to read excel file returns blank for last cell - sometim
Have you discounted a 'mixed data types' situation? e.g. see:
http://www.dicks-blog.com/archives/2...ed-data-types/ Jamie. -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Jet to read excel file returns blank for last cell - som
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. -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Jet to read excel file returns blank for last cell - som
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. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Jet to read excel file returns blank for last cell - som
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. -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Jet to read excel file returns blank for last cell - som
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Jet to read excel file returns blank for last cell - som
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Jet to read excel file returns blank for last cell - som
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. -- |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Jet to read excel file returns blank for last cell - som
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Jet to read excel file returns blank for last cell - som
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Jet to read excel file returns blank for last cell - som
Let's use your data as an example of how a 'mixed types' situation can
easily arise. If you do as I have just done i.e. import your data from the text of your post into a new clean workbook, you'll see that column 'Beg' is seen as numeric i.e. Double (FLOAT) when queried. Change the last value in the column from 168 to '168 (the leading single quote designates it as text). Without IMEX=1 in the connection string, the column will still be seen as numeric because the majority is numeric and the '168 will now be null because it is text i.e. not the majority type. Now format the whole column using the 'Text' number format and re-query: it has made no difference, the majority type is still seen as numeric. Now restore the General format and remove the quote from the 168 value. Again, no difference, the value is still seen as text and being in the minority will query as null. So you now have a column where all the formats *and* values appear to be numeric yet one value is seen by Jet as text. Jamie. -- |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Jet to read excel file returns blank for last cell - som
try to
post the excel file and your vb6 project files as a zip file and then post in the newsgroup as attachment. This posting is provided "AS IS" For some better advice, see: http://www.cpearson.com/excel/Attachments.htm Jamie. -- |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Jet to read excel file returns blank for last cell - som
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 ""Peter Huang" [MSFT]" wrote: 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. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Jet to read excel file returns blank for last cell - som
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? "onedaywhen" wrote: Let's use your data as an example of how a 'mixed types' situation can easily arise. If you do as I have just done i.e. import your data from the text of your post into a new clean workbook, you'll see that column 'Beg' is seen as numeric i.e. Double (FLOAT) when queried. Change the last value in the column from 168 to '168 (the leading single quote designates it as text). Without IMEX=1 in the connection string, the column will still be seen as numeric because the majority is numeric and the '168 will now be null because it is text i.e. not the majority type. Now format the whole column using the 'Text' number format and re-query: it has made no difference, the majority type is still seen as numeric. Now restore the General format and remove the quote from the 168 value. Again, no difference, the value is still seen as text and being in the minority will query as null. So you now have a column where all the formats *and* values appear to be numeric yet one value is seen by Jet as text. Jamie. -- |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Jet to read excel file returns blank for last cell - som
Ron wrote:
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. -- |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Jet to read excel file returns blank for last cell - som
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. "onedaywhen" wrote: Ron wrote: 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. -- |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Jet to read excel file returns blank for last cell - som
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! |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Jet to read excel file returns blank for last cell - som
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP returns 0 (zero) when lookup cell is blank | Excel Discussion (Misc queries) | |||
VLOOKUP returns 0 (zero) when lookup cell is blank | Excel Discussion (Misc queries) | |||
Cell returns blank result... | Excel Worksheet Functions | |||
Opening an excel file returns a blank worksheet | Excel Discussion (Misc queries) | |||
Using Jet to read excel file returns blank for last cell - sometim | Excel Discussion (Misc queries) |