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!
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!
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.
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"
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.
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
Create
a table(yourtable) with structure needed to import from xl.Then create a cursor:SELECT
* FROM yourtable INTO CURSOR yourcursor READWRITEAPPEND FROM
....XLSRe: 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.
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.
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.