Invalid memo file when reading data from Excel

[DPM]

Hi,

I use the following code to read data from MS Excel2007, using VFP9 SP1

lcConnectionToOpenedXls = "Driver={Microsoft Excel Driver (*.xls)};
DriverId=790; Dbq="+lcExcelFileName+";
DefaultDir="+JUSTPATH(lcExcelFileName)
lnHandleToOpenedXls = SQLSTRINGCONNECT(lcConnectionToOpenedXls)
SQLEXEC(lnHandleToOpenedXls, "SELECT * FROM ["+lcTableName+"]",
"fTmpExcelData")
SQLDISCONNECT(lnHandleToOpenedXls)

SELECT * FROM fTmpExcelData INTO TABLE (lcExcelDataTable)
USE IN fTmpExcelData
USE (lcExcelDataTable) EXCLUSIVE
BROW

When the created table is browsed and navigated through records noticed that
the following error prompts when a memo field is accessed.

"Memo file <lcExcelDataTable> missing or invalid."

Could someone please avice what may be the cause and a solution.

[Pavel Celba]

You should try BROWSE in fTmpExcelData workarea and look if data are
correctly retrieved from XLS. (Means BROWSE immediatelly after SQLEXEC)

What are column names in fTmpExcelData cursor?

Did you check if (lcExcelDataTable) + ".FPT" file exists on disk after
SELECT ... INTO TABLE (lcExcelDataTable) ?

You could also try FLUSH command before above SELECT.

P.C.

Post by DPM
Hi,
I use the following code to read data from MS Excel2007, using VFP9 SP1
lcConnectionToOpenedXls = "Driver={Microsoft Excel Driver (*.xls)};
DriverId=790; Dbq="+lcExcelFileName+";
DefaultDir="+JUSTPATH(lcExcelFileName)
lnHandleToOpenedXls = SQLSTRINGCONNECT(lcConnectionToOpenedXls)
SQLEXEC(lnHandleToOpenedXls, "SELECT * FROM ["+lcTableName+"]",
"fTmpExcelData")
SQLDISCONNECT(lnHandleToOpenedXls)
SELECT * FROM fTmpExcelData INTO TABLE (lcExcelDataTable)
USE IN fTmpExcelData
USE (lcExcelDataTable) EXCLUSIVE
BROW
When the created table is browsed and navigated through records noticed
that the following error prompts when a memo field is accessed.
"Memo file <lcExcelDataTable> missing or invalid."
Could someone please avice what may be the cause and a solution.

[DPM]

Hi Pavel,

Post by Pavel Celba
You should try BROWSE in fTmpExcelData workarea and look if data are
correctly retrieved from XLS. (Means BROWSE immediatelly after SQLEXEC)
What are column names in fTmpExcelData cursor?

Importing data from this excel works on other computers.
But fails for this perticular environment. Tried with another similar
environment and it also imports data from excel.
VFP9 exe is on 2008 server accessed as through a mapped drive.
Client computer has XP SP2, VFP9 SP1 runtimes and Excel 2007.

Post by Pavel Celba
Did you check if (lcExcelDataTable) + ".FPT" file exists on disk after
SELECT ... INTO TABLE (lcExcelDataTable) ?

The fpt is available.

Also noticed the following,
There are 2 ALTER TABLE commands after the table is opened exclusively,
before the browse.
It has created the tbk(backup of the fpt) but not the bak(backup of the
dbf). Rather the dbf is available with a different file name.

Can a ALTER TABLE fail in certain environments?

Post by Pavel Celba
You should try BROWSE in fTmpExcelData workarea and look if data are
correctly retrieved from XLS. (Means BROWSE immediatelly after SQLEXEC)
What are column names in fTmpExcelData cursor?
Did you check if (lcExcelDataTable) + ".FPT" file exists on disk after
SELECT ... INTO TABLE (lcExcelDataTable) ?
You could also try FLUSH command before above SELECT.
P.C.

Post by DPM
Hi,
I use the following code to read data from MS Excel2007, using VFP9 SP1
lcConnectionToOpenedXls = "Driver={Microsoft Excel Driver (*.xls)};
DriverId=790; Dbq="+lcExcelFileName+";
DefaultDir="+JUSTPATH(lcExcelFileName)
lnHandleToOpenedXls = SQLSTRINGCONNECT(lcConnectionToOpenedXls)
SQLEXEC(lnHandleToOpenedXls, "SELECT * FROM ["+lcTableName+"]",
"fTmpExcelData")
SQLDISCONNECT(lnHandleToOpenedXls)
SELECT * FROM fTmpExcelData INTO TABLE (lcExcelDataTable)
USE IN fTmpExcelData
USE (lcExcelDataTable) EXCLUSIVE
BROW
When the created table is browsed and navigated through records noticed
that the following error prompts when a memo field is accessed.
"Memo file <lcExcelDataTable> missing or invalid."
Could someone please avice what may be the cause and a solution.

[Villi Bernaroli]

Post by DPM

Post by Pavel Celba
You should try BROWSE in fTmpExcelData workarea and look if data
are correctly retrieved from XLS. (Means BROWSE immediatelly after
SQLEXEC) ...
Did you check if (lcExcelDataTable) + ".FPT" file exists on disk
after SELECT ... INTO TABLE (lcExcelDataTable) ?

The fpt is available.
Also noticed the following,
There are 2 ALTER TABLE commands after the table is opened
exclusively, before the browse.
It has created the tbk(backup of the fpt) but not the bak(backup of
the dbf). Rather the dbf is available with a different file name.
Can a ALTER TABLE fail in certain environments?

Can't you interrupt the code just after the SQLEXEC command? If you can, try
and see if the table is OK: in that case the issue lies in what gets done
after the SQLEXEC().
I don't know of ALTER TABLE failing, I used it many times just to "correct"
DBFs created by a SQLEXEC() against an Excel file: since all char() fields
get imported as memo, I use ALTER TABLE to convert all those memo fields to
char (only if the longest value stored is <= 254 bytes, otherwise I have to
leave the field as it is or I'd be losing data).

[DPM]

Hi Villi,

The VFP table created immediately from excel can be browsed and its data is
correct.

This problem happens when a memo column is changed to C(10) using,
ALTER TABLE <Alias name> ALTER COLUMN <ColumnName> C(10)

When this VFP table is browsed thereafter it gives the memo file missing or
invalid error and data of columns are shifted.
(Data of one column has shifted to another)
Seems to happen only with this client.

Post by Villi Bernaroli

Post by DPM

Post by Pavel Celba
You should try BROWSE in fTmpExcelData workarea and look if data
are correctly retrieved from XLS. (Means BROWSE immediatelly after
SQLEXEC) ...
Did you check if (lcExcelDataTable) + ".FPT" file exists on disk
after SELECT ... INTO TABLE (lcExcelDataTable) ?

The fpt is available.
Also noticed the following,
There are 2 ALTER TABLE commands after the table is opened
exclusively, before the browse.
It has created the tbk(backup of the fpt) but not the bak(backup of
the dbf). Rather the dbf is available with a different file name.
Can a ALTER TABLE fail in certain environments?

Can't you interrupt the code just after the SQLEXEC command? If you can,
try and see if the table is OK: in that case the issue lies in what gets
done after the SQLEXEC().
I don't know of ALTER TABLE failing, I used it many times just to
"correct" DBFs created by a SQLEXEC() against an Excel file: since all
char() fields get imported as memo, I use ALTER TABLE to convert all those
memo fields to char (only if the longest value stored is <= 254 bytes,
otherwise I have to leave the field as it is or I'd be losing data).
--
Villi Bernaroli, Italy
VFP, SOAP, MS SQLserver, MySQL, Oracle

[Villi Bernaroli]

Post by DPM
Hi Villi,
The VFP table created immediately from excel can be browsed and its
data is correct.
This problem happens when a memo column is changed to C(10) using,
ALTER TABLE <Alias name> ALTER COLUMN <ColumnName> C(10)
When this VFP table is browsed thereafter it gives the memo file
missing or invalid error and data of columns are shifted.
(Data of one column has shifted to another)
Seems to happen only with this client.

I don't remember why, but my utility to converto memo fields to char fields
uses a third column to store data, IIRC altering a memo into a char loses
the contained data (I created this in VFP6, maybe it's no more like that).
Here it is:

nFields = AFIELDS(arrFields)
ALTER TABLE alias ADD COLUMN tempchr
for i=1 to nFields
if allt(arrFields(i,2))="M"
nome_campo = allt(arrFields(i,1))
calc max(len(allt(&nome_campo))) to lung_campo
if lung_campo<=254
if lung_campo=0
lung_campo=1
endif
replace all tempchr with &nome_campo
cmd = "ALTER TABLE (alias) ALTER COLUMN "+nome_campo+"
C("+allt(str(lung_campo))+")"
&cmd
replace all &nome_campo with tempchr
endif
endif
endfor
ALTER TABLE alias DROP COLUMN tempchr

HTH

[Villi Bernaroli]

Post by Villi Bernaroli
nFields = AFIELDS(arrFields)
ALTER TABLE alias ADD COLUMN tempchr

I forgot to put the size of the temporary column, so:

ALTER TABLE alias ADD COLUMN tempchr C(254)

[DPM]

Hi Villi,

Thanks. The ALTER TABLE is simlar to what I use.
Further narrowing down the problem found that it does not allow to create
files with a extension bak in this folder, hence the ALTER TABLE fails.
If the table is copied to the windows temporary folder and ALTER TABLE is
issued it works.
Again if the bak file is copied to the previous folder from the windows
temporary folder it fails.

Can such a security be set not to create bak files on a folder?

Post by Villi Bernaroli

Post by Villi Bernaroli
nFields = AFIELDS(arrFields)
ALTER TABLE alias ADD COLUMN tempchr

ALTER TABLE alias ADD COLUMN tempchr C(254)
--
Villi Bernaroli, Italy
VFP, SOAP, MS SQLserver, MySQL, Oracle

[Villi Bernaroli]

Post by DPM
Thanks. The ALTER TABLE is simlar to what I use.
Further narrowing down the problem found that it does not allow to
create files with a extension bak in this folder, hence the ALTER
TABLE fails. If the table is copied to the windows temporary folder
and ALTER TABLE is issued it works.
Again if the bak file is copied to the previous folder from the
windows temporary folder it fails.
Can such a security be set not to create bak files on a folder?

This is something I have never seen, it can be possible but I fear it's
something else than a rights issue. Is there an antivirus program running on
that machine? If so, I'd try to add the dbf related extensions (dbf, cdx,
fpt, bak...) to the antivirus excluded extensions list.

[DPM]

Thanks Villi.

Post by Villi Bernaroli

Post by DPM
Thanks. The ALTER TABLE is simlar to what I use.
Further narrowing down the problem found that it does not allow to
create files with a extension bak in this folder, hence the ALTER
TABLE fails. If the table is copied to the windows temporary folder
and ALTER TABLE is issued it works.
Again if the bak file is copied to the previous folder from the
windows temporary folder it fails.
Can such a security be set not to create bak files on a folder?

This is something I have never seen, it can be possible but I fear it's
something else than a rights issue. Is there an antivirus program running
on that machine? If so, I'd try to add the dbf related extensions (dbf,
cdx, fpt, bak...) to the antivirus excluded extensions list.
--
Villi Bernaroli, Italy
VFP, SOAP, MS SQLserver, MySQL, Oracle