Reading Excel

Results 1 to 2 of 2

Thread: Reading Excel

  1. #1
    Join Date
    Dec 1969

    Default Reading Excel

    Hi I need help desperately,<BR><BR>Im trying to read data from an excel file<BR>It has 3 columns, but somehow, from line 1 - 75, it will display the 3 columns which is correct, but from line 76 to the end, it jus displays columns 2 and 3 and not the Part Number column.<BR>Can anyone help me on this?<BR>My excel file can be downloaded here<BR><BR>my asp code is :<BR><BR>Set conn_xls = Server.CreateObject("ADODB.Connection") <BR> conn_xls.Open "DRIVER=Microsoft Excel Driver (*.xls);" _ <BR> & "DBQ=" & absolute_path <BR> <BR> Set RS = conn_xls.Execute("Select * From [Sheet1$]") <BR> <BR><BR><BR> Set RSadd = server.CreateObject("ADODB.recordset")<BR> <BR>%&#062;<BR> &#060;table border=1 align=center&#062;&#060;%<BR>For counter = 0 To rs.fields.count - 1 %&#062;<BR>&#060;th&#062;<BR>&#060;% &#039; Write out the field names <BR>response.write rs.fields.item(counter).name %&#062;<BR>&#060;/th&#062;&#060;%<BR>&#039; Move to the next field<BR>next <BR><BR>&#039; Move to the first record<BR>rs.movefirst<BR><BR>&#039; Write out the record set<BR>i=1<BR>do while not rs.eof %&#062;<BR>&#060;tr&#062;&#060;% <BR>&#039; Loop through all of the fileds<BR><BR>for counter = 0 to rs.fields.count - 1<BR><BR>%&#062;<BR>&#060;td align=right&#062;<BR>&#060;% &#039; Write out the field values <BR>response.write i&"."&rs.fields.item(counter).value %&#062;<BR>&#060;/td&#062;&#060;%<BR>&#039; Move to the next field<BR>next <BR>%&#062;<BR>&#060;/tr&#062;&#060;%<BR>&#039; Move to the next record<BR>i=i+1<BR>rs.movenext<BR>loop<BR>%&#062;< BR>&#060;/table&#062;<BR><BR>I want it to list from first row to last row, and all 3 columns, why is it that some rows are not showing the part number column?<BR>Please help me thank you.<BR><BR>regards,<BR>twinkle

  2. #2
    Join Date
    Dec 1969

    Default Easy answer...

    Starting with row 76, the values in column 1 are no longer numeric values.<BR><BR>The "Excel Driver" scans the first few rows (I think it is 15, by default) in an attempt to discover what the datatype of each column should be. Obviously, it decided that your first column is numeric (probably long integer, even). Then, when it hits data that doesn&#039;t fit the decided-upon type, it just returns NULL for those cells.<BR><BR>The easy way to fix this is to put a dummy row into the table (right after the heading row) that contains a string that is *NOT* numeric, thus forcing the driver to conclude that the column is textual.<BR><BR>YOu could, for example, just put in a separator (say "--------------"). And that would force it.<BR><BR>Another way is to specify that the driver must scan all rows, instead of just first 15 or so. The property to change is called something like "Rows to scan", but I forget the exact syntax for changing it.<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts