How to Get Excel Sheet data to Cursor 1

[Bhashi]

I have an excel sheet as I drafted below.

I want to get the above Excel Sheet data to Cursor. How can I do this in FoxPro?
Please help..
Thank you.
bashi.

 

[Chris Miller]

Excel drivers make a spreadsheet acccesible as a table, so you can query it.

Then you can automate excel via oExcel=CREATEOBJECT("Excel.Application"), which opens doors to many solutions, saving as CSV you can easily read in VFP with APPEND or IMPORT, reading the individual cells via OLE Object model of excel. Or use a select and copy feature to get at the data through the _clipboard, even.

If it's xlsx you can unzip and find XML files that contain the data and can use XML functionalities to get at it.

Is there a best way? No. So to just sketch one of the easier ones, if Excel already is open and has this sheet

oExcel=GETOBJECT(,"Excel.Application")
oExcel.ActiveWorkBook.SaveAs("C:\CSVData\data.csv",23)
SELECT import
APPEND FROM ("C:\CSVData\data.csv") TYPE CSV

Where 23 is the xlFileFormat constant for Windows CSV. you could also try others like UTF8 or DOS formats, see

https://learn.microsoft.com/de-de/office/vba/api/excel.xlfileformat

But the topic is so wide and rich, that you find drivers and libraries like Vilhelm-Ion Praisach's libraries, described in thread184-1759033: ImportFromXlsx 1.3.

Chriss

 

[Chris Miller]

The above code has a prerequisite: The import workarea. That has to be a workarea of an empty DBF or cursor with the fields able to import the data.

If you want to cover the general case of important any size sheet and the sheet only has the property to have headers in row 1, then the best approach will be using an Excel driver that allows you to act on it as a remote table and so simply query it into a cursor in VFP, as per example of brigmar in thread184-1633357 (last post)

oCA = CREATEOBJECT("CursorAdapter")
WITH oCA
    .Alias          = "tranfile"
    .DataSourceType = "ADO"
    oConn = NEWOBJECT("adodb.connection")
    WITH oConn
      .ConnectionString = [Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\path\to\your\sheet.xlsx';Extended Properties="Excel 12.0;HDR=YES;"]
      .Open( )
      oRS = .Execute( "SELECT * FROM [Sheet1$]" )
      lnNumFields = oRS.Fields.Count
    ENDWITH
    IF .CursorFill(,,,oRS)
      GO BOTTOM && This speeds up the translation
    ELSE
      AERROR(laError)
      m.oktocontinue = .F.
      **** ERROR PROCESSING HERE
    ENDIF 
    .CursorDetach
ENDWITH

Instead of using the native CursorAdapter you could - and in my opinion it's the richer and better way - first create a class based on Cursoradapter, name it ExcelAdapter, for example, and interactively set it's properties by using the builder. When you're in the class designer right click on the cursoradapter square and pick builder. Then go through the dialog tabs.

Using an OLEDB provider you want the data source type to be ADO. Then can apply the connection string and in one of the next steps you can interactively pick the sheet instead of needing to use the Execute method.

When the cursoradapter runs and is set up to automatically query its data source you don't even need the cursorFill call, just create the CA as abject and you have the cursor you specified in the alias property of the cursoradapter.

Chriss

 

[Chris Miller]

Of course you have to have the ACE olebd provider for Excel installed for the second sample to work. It comes with Office but isn't in the standard installation.

Chriss

 

[Steve Meyerson]

Bhashi,

If you need to automate the process (in other words have your routine access the spreadsheet directly without having to open Excel itself), Chris offers you an excellent array of methods, so ignore below hints.

One thing I found is the APPEND process sometimes has a problem with putting certain types of Excel data into the precise format desired in your program.

So if I can open the xls file in Excel (which is usually my case), I save it (in Excel) as a Tab-delimited text file. Then use FILETOSTR() to "open" it in VFP and use GETWORDNUM() (STREXTRACT also works) to extract each row. To parse the row, I may have to remove quote marks, dollar signs, commas, etc. to form each item exactly as needed before INSERTing the record into a VFP table (or cursor).

Caution: Even though tab is one of two GETWORDNUM's default delimiters, you may need to specify it anyway in GETWORDNUM's parameters if your data contains spaces (the other delimiter). Otherwise the record gets jumbled. See Help.

Another "gotcha": You may need to insert a space between two consecutive tabs (a blank or null). Otherwise, GETWORDNUM will skip over one of them, treating them as a single tab, thereby jumbling the record.

Of course there are different approaches others may prefer. I offer them here because they have worked for me. HTH.

Steve

 

[Bhashi]

Chris Miller ,
Thank you for your code; it worked for me, but the some data fields in cursor shows as MEMO FIELDS. How can I fix this?
please help!
thank you.

 

[Chris Miller]

I don't see a problem with Memos, they are also character fields, just for any length.

And before I can help: What code did you use? The one going through a CSV file or the one using the Microsoft.ACE.OLEDB.12.0 oledb provider and cursoradapter?
I guess the second. If you stay with SELECT * you get what the OleDB provider (in conjunction with the VFP runtime) thinks you need. To take more control, take my advice to go through the builder process. One tab also allows you to set the field types.

If you look for a general way here, there's no general way to eliminate Memos and have char(254) or similar instead automatically, so you'll have to program something in the CA class to adjust the cursor schema.

Chriss

 

[Bhashi]

Chris Miller ,
Thank you for immediate response . I used the 2nd method(Microsoft.ACE.OLEDB.12.0 oledb provider and cursoradapter). I want to directly show my data in a cursor, not a memo field. How can I do this? Please help

Thank you.

 

[Chris Miller]

You have to use the CA CursorSchema property and set the UseCursorSchema property to .T.

Chriss

 

[Bhashi]

I tried the below code also from the Microsoft website.

LOCAL lcXLBook AS STRING, lnSQLHand AS INTEGER, ;
    lcSQLCmd AS STRING, lnSuccess AS INTEGER, ;
    lcConnstr AS STRING
CLEAR

lcXLBook = [C:\GENERAL\AB.xlsx]

lcConnstr = [Driver=] + ;
    [{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};] + ;
    [DBQ=] + lcXLBook

IF !FILE( lcXLBook )
    ? [Excel file not found]
    RETURN .F.
ENDIF

lnSQLHand = SQLSTRINGCONNECT( lcConnstr )

SQLExec(lnSQLHand , "Select * FROM 'Sheet1$'" ,'xlResults')

? [SQL Cmd Success:], IIF( xlResults > 0, 'Good!', 'Failed' )
IF xlResults < 0
    LOCAL ARRAY laErr[1]
    AERROR( laErr )
    ? laErr(3)
    SQLDISCONNECT( lnSQLHand )
    RETURN .F.
ENDIF

SELECT xlResults
BROWSE NOWAIT
SQLDISCONNECT( lnSQLHand )

But an error comes when I debug this part as below,

SQLExec(lnSQLHand , "Select * FROM 'Sheet1$'" ,'xlResults')

.
please help me fix this?
Thank you

 

[Mike Lewis]

You should always check the value returned from SQLSTRINCONNECT(). If it returns -1, it indicates an error. You can then use AERROR() to determine what caused the error.

Something like this:

lnSQLHand = SQLSTRINGCONNECT( lcConnstr )
IF lnSQLHand < 0
  AERROR(laError)
  * laError(1) now contains the error number;
  * laError(2) contains the text of the error message
ELSE
 * Connection handle OK
 * Rest or your code goes here
ENDIF

In the same way, you should also check the value returned from SQLEXEC().

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Bhashi]

The return value is -l, But I can't find any issue with the above code 
Thank you

 

[Chris Miller]

Have you followed Mikes advice? What error is reported by AERROR when SQLSTRINGCONNECT returns -1? You have to look into that error, the -1 alone only tells you that you didn't get a valid connection handle, but not why.

Issues are manifold: You could miss the driver, the file couldn't be opened by the driver as it's open in Excel, etc. etc. The error message will tell something.

To put Mike's advice into your code at two stages:

lnSQLHand = SQLSTRINGCONNECT( lcConnstr )

IF lnSQLHand < 0
  AERROR(laError)
  * laError(1) now contains the error number;
  * laError(2) contains the text of the error message
  Suspend 
  * Now open up the debugger and inspect laError in the locals window, for example.
ELSE
 * Connection handle OK
 * Rest or your code goes here
  [highlight #FCE94F]lnResult =[/highlight] SQLExec(lnSQLHand , "Select * FROM 'Sheet1$'" ,'xlResults')

 ? [SQL Cmd Success:], ICASE( [highlight #FCE94F]lnResult [/highlight] < 0, 'Failed', lnResult=0,'Still Processing','Good!' )
 IF lnResult  < 0
    LOCAL ARRAY laErr[1]
    AERROR( laErr )
    ? laErr(1),laErr(2),laErr(3)
    SQLDISCONNECT( lnSQLHand )
    RETURN .F.
 ENDIF

 SELECT xlResults
 BROWSE NOWAIT
 SQLDISCONNECT( lnSQLHand ) 
ENDIF

And Bhashi, along which lines are you thinking here about xlResult?

SQLExec(lnSQLHand , "Select * FROM 'Sheet1$'" ,'xlResults')

? [SQL Cmd Success:], IIF( xlResults > 0, 'Good!', 'Failed' )

xlResults in SQLEXECs third parameter is in a string, you're specifying the alias name of a workarea that will have the result with this string. There is no numeric variable xlResult you can check to be >0, you have to set lnResult = SQLEXEC... and then check that, as I highlighted it. What you were doing makes no sense at all.

Last not least, do you expect a different result with no Memo when using another driver? With SQLEXEC you even have no possibility to define a cursor schema and apply it. And Excel SQL has no CAST or CONVERT to turn a memo/text into a char field.

Chriss

 

[Mike Lewis]

You say you got the code from the Microsoft site. Did you just copy and paste it without any changes?

This might sound obvious, but did you change the following line to show your actual workbook path and name?
[tt]
lcXLBook = [C:\GENERAL\AB.xlsx][/tt]

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Bhashi]

Thank you Mike and Chris
When I used the AERROR () function, it returned error No 1526.

 

[Chris Miller]

That's generally ODBC error, you have to look into the details, not just the error number.

It's in general the nature of SQLEXEC and other SQL Passthrough functions, also of SQLSTRINGCONNECT or SQLCONNECT to not throw an error and trigger the general ON ERROR handling, but only return something <0 and then it's up to you to use AERROR. This is also very generally documented, please read a bit more of the documentation. Also documentation about AERROR tells you the meaning of the AERROR array elements in case of error number 1526. Other numbers also have special meaning array elements, for example in the case of DBC trigger errors.

It's all documented and on top of that you already have been pointed to using AERROR and looking at more than just the error number.

Chriss

 

[Mike Lewis]

As Chris says, if you would take the trouble to read the documentation, you would see that, if the error number is 1526, then the third element of the error array would give you the actual message as returned from the data source.

Did you also check that the actual path and filename of the XLSX file are correct - as per my previous post?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads