Import From Sheet Keyword Not Working 1

[SDTundraMan]

Hi,
I'm brand new at Foxpro, working to help a company get their legacy data under control.

I've been trying to get an import from Excel to work properly, using the following command:

IMPORT FROM (SpreadsheetPath) DATABASE Import NAME Loan_Accounts_Spreadsheet TYPE XL8 SHEET Results

The issue is that the spreadsheet file has multiple worksheets, and I want to pull in the data from the 2nd sheet (called "Results") The import command runs, but all I get is the data from the first worksheet in the file. Everything I've tried to get the SHEET keyword to work properly has failed.

Does anyone have any ideas? Thanks!
T

 

[Mike Lewis]

Hi SDTundraMan,

Welcome to FoxPro, and to the forum.

I've often seen the same problem myself with the IMPORT command. I don't know the solution. However, you'll probably get better results with APPEND FROM, which seems to work more consistently.

You'll need to create the table structure in advance, which you can do with CREATE TABLE. Then SELECT the table, and do:

APPEND FROM (SpreadsheetPath) TYPE XL8 SHEET "Results"

(Note that the sheet name is in double-quotes here, although I don't think it matters.)

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Nifrabar]

Hi SDTundraMan,
Just what it's worth:
Why don't you save the Excel-sheet to a DBF format?
Probably that's more easy to achieve your goal?
Simply select the required sheet and save as DBF.
But keep in mind while saving an Excel sheet into DBF-format to have the columns within Excel to be wide enough to prevent dataloss.
KR
-Bart

 

[cbasoz]

Check this (works with newer versions of excel too and you get all the 'tables' from excel - after running the code check the cursors created in your data session):

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

Cetin Basoz
MS Foxpro MVP, MCP

 

[SDTundraMan]

Mike,
I used the "Append From" method and that worked much better. I just created a static table to hold the data and used the "Zap" function in my method prior to doing the append.

Bart,
that would have been an easier method if this was a one-time import, but I'm automating a process that pulls data in from a spreadsheet that is downloaded from a 3rd party. I'm trying to make this as "hands off" as possible so that when I'm gone they don't need a Foxpro expert to use the application.

Cetin,
I'm going to bookmark that site in case the client ever updates the file format in which they're saving their data.

Thanks everyone for your responses!
T

 

[Nifrabar]

SDTundraMan,

I also have done some automation when I needed to extract data from an excel sheet.
In my case there was a range from the sheet I needed to further process.
My code for that (Thanks to Mike Lewis):

LOCAL loExcel, lnRow, lnCol, oSheet
loExcel  = .Null.

IF Isnull(loExcel)
    loExcel = Createobject("Excel.Application")
ENDIF

WITH loExcel
	.WorkBooks.Open(Sys(5)+Curdir()+"myExcelSheet.XLS")
	* COPY range from excel into array
		.Sheets("DesiredSheet").select
		oSheet = .activesheet
		PUBLIC ARRAY gaKop[2,100]
		FOR lnRow = 1 TO 2
			FOR lnCol = 1 TO 100
				gaKop[lnRow,lnCol] = oSheet.Cells(lnRow,lnCol).value
			ENDFOR 
		ENDFOR
ENDWITH 
loExcel  = .Null.

This leaves you with an public array holding all cell-data from the specific 'TAB'named DesiredSheet.
DesiredSheet may be sheet1 or sheet2 or sheetn.

Hopes this helps.

-Bart

 

[Mike Lewis]

SDTundraMan,

I used the "Append From" method and that worked much better. I just created a static table to hold the data and used the "Zap" function in my method prior to doing the append.

That's similar to what I do.

Glad to hear the solution worked. Be sure to come back if you have any more FoxPro problems.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business