problem with import of excel sheet

[Nifrabar]

I try to import an excel sheet with command:
Import e:\filename type xls (also tried xl5).

This ends up with crash of VFP 9 sp1 as wel as VFP9 sp2.

Error message:
Microsoft Visual Foxpro 9.0 has stopped working:
A problem caused the program to stop working correctly. Pls close the program.

Any idea what went wrong?

-Bart

 

[Mike Lewis]

Difficult to know without more information. But the first thing to check is the version of Excel which created the workbook. If it was 2007 or above, then you need to save the file in Excel 95 format. Then again, that wouldn't explain the error you are seeing.

Check also that you can open the workbook in Excel. It might have got corrupted.

Then try the same import with different workbooks - perhaps starting with an extremely simple one, with just one page, one row and one columns.

Then try with the origianl workbook, but use APPEND FROM instead of IMPORT. And try the Import wizard rather than the command.

With luck, this will give you some clue as to where the problem lies. Once you have that information, report back.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Nifrabar]

Mike,

I don't know what the current version of Excel is in which the sheet was created.
I opened the sheet in Excel 2010 and saves in 95-format than I did import as described succesfully.
The sheet has extension xls so no real 2007 or 2010 sheet.
Will there be a way to investigate what version the excelsheet is? For I want the import to include in my app.
As there was no errornr. thrown I can't catch this error at runtime.

I feel only solution is to strictly describe the excel's version.

kr
-Bart

 

[Olaf Doschke]

Well, the extension for xlsx files can also be set as xls, that's not a clue.

You can open a xls file in excel of course, as you did manually, and then that info should be available, or you could save as Excel95 anyway and then Appned that new file.

The latest way I read in excel files is using Microsoft.ACE.OLEDB.12.0
Did we have that discussion recently? Or was that about the export to excel?

I already posted the following code in another thread:

lcConnString = [Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\your\excel\file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";]
loConn = CreateObject('ADODB.Connection')
loConn.Mode = 1  && adModeRead
loConn.Open( lcConnString  )
loSchema = loConn.OpenSchema(20) && adSchemaTables
loDOM = CreateObject('MSXML.DOMDocument')
loSchema.Save(loDOM, 1)
Xmltocursor(loDOM.XML, "curTables")
loSchema.Close()
lcTable = Alltrim(curTables.Table_Name)
loRs = loConn.Execute(RTRIM(Textmerge("Select * From [<<lcTable>>]")))
loDOM = CreateObject('MSXML.DOMDocument')
loRS.Save(loDOM, 1)
Xmltocursor(loDOM.XML, "curExceldata")
loRS.Close()

This should read in the first sheet of anything up to Excel 2010.

What you need to install is either Excel2010, Office 20120 or at least the Data Provider you get here:

http://www.microsoft.com/en-us/download/details.aspx?id=13255

You may need to make specific conversions after reading into the cursor and it may not be the fastest for large files.

Bye, Olaf.

 

[Mike Lewis]

The sheet has extension xls so no real 2007 or 2010 sheet.
Will there be a way to investigate what version the excelsheet is?

The fact that the extension is XLS doesn't tell you anything. If the workbook was created in 2007 - 2010, but saved as 97 - 2003, you will still have the same problem as if it was a native 2007 - 2010 (XLSX) file, regardless of the extension.

The real question is: Where does the file come from? Do you have any control over how it is created or saved?

If you do, then ensure it is saved as Excel 5.0/95, or better still as CSV.

However, I'm not yet convinced that your problem is related to the file type. That's why you need to try it with different files and different methods of importing in order to determine exactly what causes the error.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy