Importing from Excel 2007 2

[Jose201]

Has anyone come up with a solution? There was a thread dealing with this problem (thread1251-1338029), but it was closed in August 2007. At this time, the only solution that I can come up with is to open the offending file in Excel 2003, create a new spreadsheet and copy & paste contents of the offending spreadsheet into the new spreadsheet.

Saving the offending spreadsheet as an Excel 2003 type from either 2007 or even 2003 with the compatibility pack does not create a spreadsheet that FoxPro can import. Apparently 2007 (and the compatibility pack) add something to the spreadsheet that FoxPro's IMPORT command cannot handle.

 

[bborissov]

Do not use IMPORT or APPEND FROM.
Check brilliant solution from Cetin Basoz <b>VISTA EXCEL COMPATIBILITY W/ WORKSHEETS</b> at

http://www.foxite.com/archives/0000153776.htm

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[baltman]

Using ODBC. This is work in progress I have on the machince I'm on now...

CLOSE ALL

XL2007Import("c:\U.S. Extreme Temperatures.xlsx",.f.,.t.)

PROCEDURE XL2007Import
LPARAMETERS tcFullPathFile, tlHeaders, tlNull
LOCAL ARRAY aSheets[1]
SET DECIMALS TO 9
SET NULL OFF 
&& [URL unfurl="true"]http://www.microsoft.com/downloads/details.aspx?FamilyID=7554f536-8c28-4598-9b72-ef94e038c891&DisplayLang=en[/URL]

IF VARTYPE(XL2007Import)="L"
  tcFullPathFile = GETFILE('xlsx')
ENDIF 

IF UPPER(RIGHT(tcFullPathFile,4))<>"XLSX"
  RETURN -1
ENDIF 
*DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=<<tcFullPathFile>>;Extended Properties="Excel 12.0";HDR=<<ICASE(tlHeaders,"YES","NO")>>;IMEX=1;MAXSCANROWS=65000;

TEXT TO m.cConnStr TEXTMERGE NOSHOW    
  DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=<<tcFullPathFile>>;HDR=<<ICASE(tlHeaders,"YES","NO")>>;
ENDTEXT

nHandle = SQLSTRINGCONNECT(m.cConnStr)

IF nHandle < 0
  aerror(aa) 
ENDIF

SQLTABLES(1,"TABLE","curXLSTbls")
SELECT DISTINCT table_name FROM curXLSTbls INTO ARRAY aSheets
USE IN SELECT("curXLSTbls")

ALEN(aSheets)

FOR nSheets = 1 TO 1&&ALEN(aSheets)
  ?aSheets[nSheets]
  
  SQLCOLUMNS(nHandle,"["+ALLTRIM(aSheets[nSheets])+"]","native","curXLSCols")
  
  SELECT DISTINCT column_name,ICASE(ISDIGIT(LEFT(column_name,1)),"_","")+column_name as column_alias,;
    ordinal_position FROM curXLSCols ;
    ORDER BY 2 INTO ARRAY aCols

  nCols = ALEN(aCols,1)
  
  IF nCols <= 254 &&continue
    IF NOT tlNull
      m.cSQL = "select "
      FOR nCnt = 1 TO nCols
        GO nCnt 
        m.cSQL = m.cSQL + '"' + ALLTRIM(aCols[nCnt,1]) + '"' + ;
          ICASE(aCols[nCnt,1]=aCols[nCnt,2],""," as " + ALLTRIM(aCols[nCnt,2])) + ","
      ENDFOR
      
      m.cSql = LEFT(m.cSQL, LEN(m.cSQL)-1) + " from [" + ALLTRIM(aSheets[nSheets]) + "]"
      
    ELSE 
      m.cSQL = "select * from [" + ALLTRIM(aSheets[nSheets]) + "]"
    ENDIF 

      nResult = SQLEXEC(nHandle, m.cSQL, "temp")
      
      IF nResult<1
        AERROR(aWhatError)
        _cliptext = m.cSQL
        SET STEP ON 
      ENDIF 
  ELSE
    MESSAGEBOX("Work to be done for >254 cols")
  ENDIF 

  SELECT * FROM temp INTO TABLE (aSheets[nSheets])
  USE 
  USE IN SELECT("curXLSTbls")    

ENDFOR 
  USE  (aSheets[1])
  BROWSE NOWAIT 
  MESSAGEBOX("your data clean-up rountines etc here")
ENDPROC

 

[Jose201]

Many thanks to both of you for your suggestions. Both use ODBC (which confirms the direction I was planning to take), and Cetin Basoz's solution includes a code sample for writing to Excel 2007 spreadsheets.

 

[white605]

From Foxite.com article

> If you know how to use automation to save in 95 format, that could be used as well.

This approach seems very easy to me, save the file and append from it. There must be a reason to go in the other directions - please enlighten me with it before I program myself into a box taking the easy way out with files like this.
wjwjr


This old world keeps spinning round - It's a wonder tall trees ain't layin' down

 

[Mike Lewis]

White605,

please enlighten me with it before I program myself into a box

As I understand it, the point about the ODBC approach is that you don't need Excel 2007 to be installed on your computer, and you don't need the person who originates the file to do anything special.

In order to save an Excel 2007 file in Excel 95 format, the person sending the file must do that interactively, and you might not have any control over that. Alternatively, you can do it via automation, but only if you have Excel 2007 installed.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Jose201]

The problem that I encountered is that Excel 2007 adds something to the spreadsheet even when it is saved as an Excel 2003 spreadsheet. When I compared the original Excel 2007 spreadsheet with an Excel 2003 spreadsheet with the same content, the version created in Excel 2007 was 9 Kb larger.

The 2007 compatibility pack is installed on my PC. So I don't know if that affects the outcome. However, saving the original file as an Excel 95 spreadsheet made no difference. It was still not possible to append or import the spreadsheet into FoxPro. And it can be seen that any file derived from the original 2007 spreadsheet is larger than the equivalent spreadsheet created in Excel 2003.

In researching this problem, I discovered that Microsoft is aware of the problem, but is not planning on releasing a patch to any version of VFP to fix it. Maybe they will patch Excel 2007, but Microsoft appears to view it as a feature, not a bug. Since Service Pack 1 for Office 2007 disables access to some of the older spreadsheet and document formats, it would appear unlikely that Microsoft will put any effort into dealing with this.

 

[Mike Lewis]

Jose,

Your observations are similar to my own. I've also researched this issue, and I've written about it both here on Tek Tips and elsewhere (see, for example,

http://my.advisor.com/articles.nsf/aid/19289)

- as have several other developers.

By the way, another problem with saving the file in 95 format is that you are limited to 16,384 rows.

I don't think installing the compabitily pack makes any difference. I haven't tried to programmatically open a 2007 file in an earlier version with the compatibility pack installed, but I've no reason to suppose it will work.

Several people have suggested saving the workbook in CSV format, which will obviously avoid the problem. But, again, that implies that you have some control over the originating application. (You can convert from Excel 2007 to CSV by automation, but only if you have 2007 installed.)

Along with many other developers, I have reported this problem to Microsoft. But I understand that it won't be fixed within VFP because it is an Excel issue, not a VFP one.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[white605]

But, again, that implies that you have some control over the originating application
Not only the application but also the people running it
Thanks, Point well taken
wjwjr

This old world keeps spinning round - It's a wonder tall trees ain't layin' down

 

[baltman]

Excel 2007 automation will allow you to import more than 65,000 records (it allows 1MM) and if you handle it with code, all the columns (it allows 16,000 columns). You'll have to make multiple tables to do so of course, but this effort is worth it for me because I'm sure to get >65,000 records if not more than 255 columns.