×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Importing from Excel 2007
2

Importing from Excel 2007

Importing from Excel 2007

(OP)
Has anyone come up with a solution? There was a thread dealing with this problem (thread1251-1338029: Error appending from new MS Excel 2007), 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.

RE: Importing from Excel 2007

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

RE: Importing from Excel 2007

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

CODE

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
&& http://www.microsoft.com/downloads/details.aspx?FamilyID=7554f536-8c28-4598-9b72-ef94e038c891&DisplayLang=en

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

RE: Importing from Excel 2007

(OP)
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. 2thumbsup

RE: Importing from Excel 2007

From Foxite.com article

Quote:

> 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

RE: Importing from Excel 2007


White605,

Quote:

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
 

RE: Importing from Excel 2007

(OP)
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.

RE: Importing from Excel 2007

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
 

RE: Importing from Excel 2007

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

RE: Importing from Excel 2007

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close