Import to Memo Fields 2

[Scott24x7]

Hi All,
Ran into a small problem today, as I was trying to create an import routine for data from Excel files. Excel has advanced a lot since VFP 9 came out, and even though it was around 2007 that VFP9 came out, it seems they only really support exchange between VFP and Excel up to around the Office '97 edition. Of course Office 2007 was a major break in how everything Office worked.
So, I tried first to save existing files to '97 format and then tried both APPEND FROM [XL8] and IMPORT [XL8] but it just doesn't work. Took a hard dive and crashed VFP on several attempts, not bringing a single character into the table.
So my next attempt was to save the Excel file as tab delimited (avoided CSV because many many of the fields in the file are rife with commas). Then I discovered the next problem... some of the rows have comments that are quite extensive, and exceed the 254 maximum character size for a "Char" field. No problem, I changed them to Memo but then, remembered the "Gotcha" from few years back, when after my next import, the memo fields are empty.

So, I explain all the above so you can see how I got here.
The question then becomes, what is the best way to import text into a table where character fields exceed 254 (i.e. how can I get that data imported into a memo field?)

Each spreadsheet has about 200 rows in it, so it's not practical to copy and paste the records by hand. Solutions?
Thanks!

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[vgulielmus]

You may try these functions :
Import from xlsx
and
Append from xlsx

The first one creates the table, based on the first imported row of the spreadsheet (tries to mimic VFP's import command).
The second one append the spreadsheet content to and existing table (tries to mimic VFP's append from command)

Both functions handles memo fields

You can specify the sheet (either by name or position) and the starting row (useful to skip the first row(s) of the sheet, which usually contains the table header)

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Scott24x7]

Vilhelm,
This is nothing short of brilliant. I really appreciate you sharing this with me. Have you considered making an FAQ on this? This is incredibly powerful stuff.
Dare I impose, do you have an export back to XLSX? That would streamline the full cycle in a really useful way.
Many many thanks.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[vgulielmus]

I have a FAQ with export to pptx.
When I sent that fact, I also proposed export to xlsx and export to docx, but these wasn't approved.

But the export functions and classes can be found here :

1) to xlsx
DBF2XLSX
or
DBF2XLSX

2) to docx
DBF2DOCX
DBF2DOCX
or
DBF2DOCX
or
DBF2DOCX

3) to pptx
DBF2PPTX
or
DBF2PPTX
or
DBF2PPTX
or
DBF2PPTX

There is also a import / append from docx
DOCX2DBF
DOCX2DBF
or
DOCX2DBF
or
DOCX2DBF



Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Scott24x7]

Vilheilm,
This is really incredible. Great work, and I thank you very much for your generosity in sharing! Brilliant that it's all native VFP code as well. Too bad MS has killed their MVP program for VFP... you would surely have earned it with this. I thank you once again, and I'm looking forward to studying this code you've written as a means of better understanding the interaction between office/xml/vfp.
Cheers!


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[vgulielmus]

Thank you for your kind words
The thanks are the true MVP.

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Scott24x7]

Hi Vilhelm,
I was testing the ImportFromXLSX but ran into a snag. When I ran the TEST and TEST2 prgs in the directory (so I could watch how it works), it seems to get stuck in this loop in the extract Function:

		FOR lni = 0 TO m.oShell.NameSpace(ADDBS(m.lcZip)+'xl\worksheets').items.count - 1
			ofile = m.oShell.NameSpace(ADDBS(m.lcZip)+'xl\worksheets').items.item(m.lni)
			IF LOWER(LEFT(m.ofile.name,5)) == 'sheet'
				oShell.NameSpace( m.lcDir).copyhere( m.ofile)
				lnFF = FOPEN(ADDBS(lcDir) + m.ofile.name)
				DO WHILE m.lnFF < 0
					sleep(50)
					lnFF = FOPEN(m.ofile.name)
				ENDDO
				FCLOSE(m.lnFF)
			ENDIF
		ENDFOR

The "DO WHILE m.lnFF" is at constant -1, so it just remains caught in the loop.
Any idea what might be wrong?


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[vgulielmus]

This was a trick found, because of the asynchronous nature of shell.copyhere()

Can you, please, give more details, like OS ?
Because for shell.movehere() I was forced to find two different kind of tricks, one for WIN 7 or higher, and another for Vista or lower.

Also I didn't understood what do you mean by "I ran the TEST and TEST2 prgs in the directory".
English is not my strongest point :-(

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Scott24x7]

Hi Vilhelm,
Thanks. I'm using Windows 10 (it's build 10532 if that matters) it's the most up to date Win10 with all patches applied.
What I meant about "Ran from the directory" was, I issued:

SET DEFAULT TO T:\TESTBED\VFP - XLSX

Which is where I put the files from the zip package.

I had "SET STEP ON" in the 3rd line of IMPORTFROMXLSX.PRG (since it appeared to "freeze" the first time, I thought it was caught in a loop).

So I tried from VFP command window then:
DO TEST.PRG

and

DO TEST2.PRG

Both of which get stuck at that same point in the code.
Sorry for the hassle, but it is kind of fascinating what you are doing. I noticed the SLEEP(50) is a Shell command, I assume that causes some delay for some purpose, but I wasn't quite sure what.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Scott24x7]

Oh, and if it matters the Office version I have currently installed is 2013, though that seemed to be independent of your method. I mention here just in case it is important.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[vgulielmus]

At the top of the same procedure (Function extract) are these commands

	lcDir = ADDBS(SYS(2023)) + SYS(2015)
	lcZip = FORCEEXT(m.lcDir,'.zip')
	COPY FILE (m.lcFileName) TO (m.lcZip)
	MD (m.lcDir)

I suppose something happened here.
Can you check the creation of the file ADDBS(SYS(2023)) + SYS(2015).zip and the folder ADDBS(SYS(2023)) + SYS(2015) ?

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

Vilhelm-Ion,

I think if the XLSX is open during extracting from its file that would hinder FOPEN to get at the embedded files, so you never get a positive handle, you should give up after a timeout has expired. You might first test whether you can FOPEN the overall XLSX file exclusively, but even then I'd not do any endless retry.

Less probable, but the m.oShell.NameSpace.items might also contain folders, which you never can FOPEN. So you might not check for file extension xml on top of filenames beginning with "sheet".

Overall, I think you could parse the [Content_Types].xml in the root directory, pick out all xml nodes with attribute ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" and you have your list of sheet xml files. There might be differences in the ContentType in the different Office versions and even within the same Office version, since there are at least two XML standards. For example you can also save Strict Open XML Standard files with xlsx extension, not only MS Office XML.

Bye, Olaf.

 

[Olaf Doschke]

Should MD go before COPY FILE?

Bye, Olaf.

 

[vgulielmus]

The procedure runs correctly, even now on my PC, even after I opened the xlsx in Excel.
Unfortunately, right now I am at a PC with Win 7 and MSOffice 2010.

I agree that instead of
lcDir = ADDBS(SYS(2023)) + SYS(2015)
can be used any other directory.

MD and should not have problems with COPY FILE.
COPY FILE (m.lcFileName) TO (m.lcZip) copy the xlsx source file, into a file like C:\Users\MyUser\AppData\Local\Temp\_4gd0tieyl.zip,
while MD (m.lcDir) creates a directory like C:\Users\MyUser\AppData\Local\Temp\_4gd0tieyl

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Scott24x7]

Hi Vilhelm,
Yes, in the directory associated with SYS(2023) (in this case my user directory on my Windows Account), the _<uniqueprocedure> name created with SYS(2015) is there. In that directory, there is a XML file called "Sheet", and opening it I can see data (it's size is around 1,100 bytes).

So that is working ok.

Doesn't seem that that MD is required before the copy...


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

>Doesn't seem that that MD is required before the copy.

I can confirm that, lcZip = FORCEEXT(m.lcDir,'.zip') is generating a file name for the zip file, which is not within lcDir, but parallel to it. The COPY doesn't need the lcDir, the MD is not needed at all, or do you get problems with m.oShell.NameSpace(ADDBS(m.lcZip)+'xl\worksheets') if the directory does not exist? On Vista and lower?

There has to be a reason you sometimes don't get a file handle even trying many times. If it's not the currently use of the file, then how about testing my suspicion you might have folders in the items collection?

Bye, Olaf.

 

[Scott24x7]

Maybe it's just me but I've had problems when the directory that gets created is more than one layer deep of "non-existence". For instance if C:\TEMP exists, if I try to create in a file copy T:\TEMP\CLIENT\CONTACT\File.txt then the creation fails... but T:\TEMP\CLIENT\File.txt is fine... That's on Win 10.
But in any case, the MD or existence of the file doesn't seem to be the issue here, as it's stuck in the DO WHILE and never leaves it, with -1 (At least during "step through" is on. Executing, it seems to be caught there too.

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[vgulielmus]

Can you try another folder instead of
lcDir = ADDBS(SYS(2023)) + SYS(2015)

In the end, they will be removed anyway

I agree with Olaf about improving the creation of folders and files, by adding some TRY-CATCH, and to replace that infinity loop.
But it will take longer than changing ADDBS(SYS(2023)) + SYS(2015) with another directory

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Scott24x7]

Sure, I'll give it a try. I'll move them into a TEMP dir on the local drive.
Will let you know how it turns out.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Scott24x7]

Hi Vilhelm,
Well, in the overall scheme of things, changing the DIR from SYS(2023) to a forced location didn't change anything... still stuck in the loop at the same place in the "DO WHILE".


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."