help with excel import order please...

Results 1 to 2 of 2

Thread: help with excel import order please...

  1. #1
    Join Date
    Dec 1969

    Default help with excel import order please...

    I posted this question before but got no responses so I&#039;m posting again hopefully I can get some help today... <BR><BR>I&#039;m creating a form that lets customers upload their excel spreadsheet then I read the data of each row & insert them into my database as order lines.<BR>My question is, I wouldn&#039;t know in advance of the number of rows in the spreadsheet so how can I reference the From clause in this SQL statement: <BR>str="Select line,name,qty,code,font,item From [Sheet1$A1:F10] " <BR>Suppose this excel file has 10 order lines from A1 to F10. <BR><BR>Another way to query is based on the table name or range.. I can do this: <BR>str="Select line,name,qty,code,font,item From myorders " <BR>In this case, the table name is "myorders". But not all customers know or would name their tables. How can I deal with this situation? <BR><BR>My other question is, is it possible to validate fields imported from a spreadshet, like checking for required fields & validate the correct datatype? Please point me any resources you have on this validation.. <BR>Thanks for your help!

  2. #2
    Join Date
    Dec 1969

    Default Only one suggestion...

    Instead of using<BR> Sheet$A1:F10<BR>use<BR> Sheet$A1:F999<BR>and then, as you read the records, just stop when you get to a row that doesn&#039;t seem to have valid data.<BR><BR>Having said that...I have no idea whether or not the JET driver will let you use a number that is greater than the number of rows...but what can it hurt to try it?<BR><BR>You *can* also simply do<BR> [Sheet1$]<BR>to get all rows and columns, but of course if the user&#039;s "space" in the sheet is actually $C3:J20 you&#039;ll be getting a lot of inappropriate junk.<BR><BR>The other problem you have is people who put their column titles in (say) row 1 but don&#039;t start their data until row 4.<BR><BR>I&#039;m not sure there is a nice general solution unless you can get people to follow a *VERY* rigid template that you provide.<BR><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