JonathanVickers


Hi,
I'm struggling to import from an excel spreadsheet into foxpro. The problem is there's a "remarks" column in the xls which may contain more than 255 characters, so I want to import that column as a memo field.
I've tried using APPEND FROM, but that's just putting nulls in the field.
I've also tried IMPORT, but that just gives me a character field.
I've tried setting up an ODBC connection - that gives me the memo field, but never with more than 255 charcters in it.
And I've tried going through access, all to no avail.

Any advice gratefully received!



Re: importing from excel

JonathanVickers


Thanks Naomi,

I've not managed to get VFP OleDb to work properly either.
I had thought of opening up a low-level file and populating a table as it runs through character strings. I think that would do the job - it just seemed like a lot of work. And I'm lazy! Smile






Re: importing from excel

dni

You may try to use a cursor(accept more then 255 in memo field) to import xls file and then save it in a table.





Re: importing from excel

JonathanVickers

Sorry Dni, I don't follow. How would I get the data off the xls into a cursor
If I try to append to a cursor I get "Cannot update the cursor"




Re: importing from excel

CetinBasoz

ODBC works for memo. ie:

lcXLS = GETFILE('XLS')

lcConn = "Driver={Microsoft Excel Driver (*.xls)};"+;

"DriverId=790;Dbq="+m.lcXLS+;

";DefaultDir="+JustPath(m.lcXLS)+";"

lnHandle = SQLStringConnect(m.lcConn)

SQLTables(m.lnHandle,"", "SheetNames")

Scan

lcTableName = Trim(SheetNames.Table_Name)

lcOutput = Chrtran(m.lcTableName,' $','_')

SQLExec(lnHandle,'select * from ['+m.lcTableName+']',m.lcOutput )

endscan

SQLDisconnect(m.lnHandle)

PS: You can use ADO with a cursoradapter (makes it easier to convert to a cursor).





Re: importing from excel

JonathanVickers

I've tried pretty much the same thing and found it truncates the contents of the memo field to 255 characters.

I should have mentioned I'm using VFP 5, maybe it works differently in subsequent versions.

I think I've got it to work by saving the xls as a tab delimited text files, then FOPEN, then chopping up the character string with ATC(chr(9)) and populating a table from that.
It's a shame because this only works if I know what the required table structure is, but it's only a one-off job so I'm not too concerned that it's not very elegant.

Thanks for your help.




Re: importing from excel

dni

You need to have it with option READWRITE.




Re: importing from excel

dni

Create a table(yourtable) with structure needed to import from xl.Then create a cursor:

SELECT * FROM yourtable INTO CURSOR yourcursor READWRITE

APPEND FROM ....XLS






Re: importing from excel

JonathanVickers

Oh right, I don't think I can do that in VFP 5. Editable cursors came in in VFP7.
Shame, cos that would have been useful.




Re: importing from excel

Cindy Winegarden

Naomi Nosonovsky wrote:

You may try VFP OleDB instead.

How would you use VFP OLE DB when importing into VFP from an Excel spreadsheet






Re: importing from excel

Naomi Nosonovsky

Good question. I'm wrong here, it can be used the other way around - from VFP to Excel to export memo fields.



Re: importing from excel

Naomi Nosonovsky

I've created a cursor with a memo field and tried to append from xls file, but the memo field comes empty. Could you please elaborate more on your idea

Thanks.