Excel to cursor SPT problems

Excel to cursor SPT problems

• Rocky79

  Here is one sample that I posted to UT earlier today:

  use customer
  Export to xxCustomer.xls type xl5
  use
  lcXLS = Sys(5)+Curdir()+'xxCustomer.xls'
  lcConn = "Driver={Microsoft Excel Driver (*.xls)};"+;
   "DriverId=790;Dbq="+m.lcXLS+;
   ";DefaultDir="+JustPath(m.lcXLS)+";" 
  lnHandle = SQLStringConnect(m.lcConn) 
  SQLTables(m.lnHandle,"", "SheetNames")
  Scan
   lcTableName = Trim(SheetNames.Table_Name)
   lcOutput = Chrtran(m.lcTableName,' $','_')
   SQLExec(m.lnHandle,'select * from ['+m.lcTableName+']',m.lcOutput )
  endscan
  SQLDisconnect(m.lnHandle)

  
  

• VenkateshBabu

  Thanks, I am about to test this, but if you could 'amuse me' why do you use the prefix 'm.'
  
  EDIT: Infact, I am getting nothing but syntax errors when I try to implement your code. Hate to ask, but can you possibly explain it
  
  EDIT2: Infact I'll just include the code for you.
  
      MCONNSTR="Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" + alltrim(thisform.txtFile.Value) + ";DefaultDir=" + alltrim(justpath(thisform.txtFile.Value)) + ";"
      nConn = sqlstringconnect(MCONNSTR)
      SQLTables(m.nconn, "", "SheetNames")
      scan
          lcTableName = trim(SheetNames.Table_Name)
          crsxls = chrtran(lcTableName,' $', '_')
          sqlexec(nconn, "select * from ['lcTableName']", crsxls)
      endscan
      sqldisconnect(nconn)
     
      select crsxls
         scan
         if sqlexec(hconn, "execute hub_massUpdate '" + alltrim(crsxls.hubID) + "'") < 0
                aerror(laerror)
                messagebox(laerror(2), 64, pcappname)
            endif
        endscan
        messagebox("Batch update complete.", 64, pcappname)
  
  
  
  

• CostasZ

  "m." prefix or also known as mdot is used to signify it's a memory variable. In other words "m" is the default alias for memory. It's optional in VFP but you should think as mandatory. It prevents hard to catch bugs like field variables taking precedence and increases performance in loops. There were long discussions about it on UT which you might check (if you can find, they're old).

  Your code is NOT an edited version of what I've sent to you. It'd naturally error. Copy paste what I've sent and just change the line where it assigns the .xls name.

  Here is one bug in your code that I could see at a glance:

  sqlexec(nconn, "select * from ['lcTableName']", crsxls)

  That should have been:

  sqlexec(nconn, "select * from ["+m.lcTableName+"]", m.crsxls)

  Another line:

       if sqlexec(hconn, "execute hub_massUpdate '" + alltrim(crsxls.hubID) + "'") < 0
  there is no cursor named "crsxls" there. The name of the cursor is m.crsxls and it's a variable. XLS might have more than one sheets with tables on it.If you know the name of the sheet beforehand use it. You could check if you got the cursor you want checking if HubID exists. ie:
  
  if fsize("hubID",m.crsxls) > 0 && found the sheet with hubID

  After sample code I sent check datasession. It'd have one or more cursors there populated from excel.

   

   

  
  

• JavierGT

  You'll be pleased to know that doing a SPT works just fine. My problem the ENTIRE time was a lack of " " around crsxls!
  
  So far this seems to have worked....BUT if something is wrong, you can be sure I'll be back about it!
  
  Thanks for everything!
  
  Josh
  
  
  

• laboremus

  I don't know why but this is all very confusing to me.
  
  This code:
  
  

  MCONNSTR="Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\FOX\TEST.xls;DefaultDir=c:\FOX;"

  nConn = sqlstringconnect(MCONNSTR)
  sqlexec(nconn, "select * from [sheet1$]", "CRSXLS")

  SELECT CRSXLS

  As supplied by Dave M. is fine, but it will sometimes produce an error (#1466), and I am wondering why, and if there is a simple solution to the problem that I am not seeing.
  
  Could it possibly be that I am not suppling a SQLDisconnect( )
  
  EDIT: It would seem that the error (#1466) comes during the sqlexec( ) function. Any ideas from that Dave M. might know best (as he generated the code), but you might also.
  
  
  

• Esqueleto

  I'll just reply to this one for both of them:
  
  I see, well the .xls file is user generated with the sheet names unchanged; they will not be created in VFP. What happens is that a user uses getFile() to select a .xls file, and then they click 'import.'
  
  I understad the fact that with generating a .xls file with VFP you aren't guaranteed the sheet name Sheet1$; however, if it is user generated (via Microsoft Excel), it would be.
  
  I've ONLY ran into this problem on other people's machines sometimes (never on mine, EVER), but I am not the end user for this program, so I am trying to figure out why it screws up at sqlexec( ).
  
  
  

• davidg12

  It's saying connection handle is invalid. IOW not getting a connection to Excel.

  If you don't supply an SQLDisconnect() then you're leaving behind open connections.

  Maybe it'd be better for you not to use SPT but automation instead. ie:

  lcXLS = "c:\fox\test.xls"
  lcTable = "c:\fox\test.dbf"
  oExcel = createobject("Excel.Application")
  with oExcel
  .DisplayAlerts = .F.
  .WorkBooks.Open(m.lcXLS)
  .ActiveWorkBook.SaveAs(m.lcTable,8)
  .ActiveWorkBook.Saved = .T.
  .Quit
  EndWith
  USE (m.lcTable)
  browse

  
  

• PhaedoHD

  Like I posted above: It would seem that the error (#1466) comes during the sqlexec( ) function. Any ideas from that Dave M. might know best (as he generated the code), but you might also.
  
  Ontop of this I am working with SQL Server 2000, not using FoxPro tables, so there is no .dbf file in existence. That's be a problem.
  
  
  

• Carlos Sanchez

  No it wouldn't be guaranteed to have a sheet name as Sheet1. Even if it would you're not guaranteed to have a table on excel (to excel every sheet doesn't contain a valid table that it could export via SPT. There are rules for it to accept a region as a valid "data table").

  End users behaviors are always different from developers and they do it differently from you or me. I don't know really. From my POV every excel file is not importable to any database (including SQL server). Tables have rules which Excel is not bound to.

  
  

• BBesser

  you can use 'm.' qualifier to reference a memory variable and is useful when you have an object with the same name, for example, of a used table.

  An example from the on-line help:

  USE customer
  SCATTER NAME customer
  
  customer.company && Returns the table value
  M.customer.company && Returns the object property value

  Bye,

  Giulio
  

  
  
  

• Zhao_Gil

  Dave's code is a subset of what I've posted and I think I have posted it before (not sure). If it's coming during SQLExec() makes more sense, because as I said before you shouldn't trust all tables in Excel are named "Sheet1$". For example:

  use customer

  copy to mycustomer.xls type xl5

  Open the xls in Excel and check what sheet name is (myCustomer not sheet1 - and hence SPT table name is myCustomer$). That's why I was querying all sheets in a loop.

  
  

• Danny Tuppeny

  ie: If excel is a Turkish version then Sheet1 is named Sayfa1 and that's only a change with language settings.
  

• Liran Russo

  You're editing the message and I'm trying t catch up:)

  If you're usign SQL 2000 and no need for DBFs why don't you directly query from SQL 2000 with OpenDataSource or OpenRowSet functions (you'd use Jet.4.0 driver - I believe there are samples with Excel in books online ffor excel in topics related with those functions). Also I see in your signature .Net. You might use .Net as well for this.

  
  
• Excel to cursor SPT problems