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]

Thank you. I will investigate.
Can you see the zip file, with the same name of the directory ?
Can you manually extract its content ?
Or even better, can you send me that xlsx, with less or changed content ?

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Scott24x7]

Vilhelm,
The .zip never gets created. The only output is an XML file called "Sheet".
Here's the results of the first few rows

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="[URL unfurl="true"]http://schemas.openxmlformats.org/spreadsheetml/2006/main"[/URL] xmlns:r="[URL unfurl="true"]http://schemas.openxmlformats.org/officeDocument/2006/relationships"[/URL] xmlns:mc="[URL unfurl="true"]http://schemas.openxmlformats.org/markup-compatibility/2006"[/URL] mc:Ignorable="x14ac" xmlns:x14ac="[URL unfurl="true"]http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"><dimension[/URL] ref="A1:G4000"/><sheetViews><sheetView tabSelected="1" workbookViewId="0"/></sheetViews><sheetFormatPr defaultRowHeight="12.75" x14ac:dyDescent="0.2"/><cols><col min="2" max="2" width="49.28515625" customWidth="1"/><col min="3" max="3" width="11.28515625" customWidth="1"/><col min="4" max="4" width="16.5703125" customWidth="1"/><col min="5" max="5" width="32" customWidth="1"/><col min="6" max="6" width="11.28515625" customWidth="1"/></cols><sheetData><row r="1" spans="1:7" x14ac:dyDescent="0.2"><c r="A1" s="1"/><c r="B1" s="2"/><c r="D1" s="10"/><c r="E1" s="10"/><c r="F1" s="15"/></row><row r="2" spans="1:7" ht="15.75" x14ac:dyDescent="0.25"><c r="A2" s="1"/><c r="B2" s="3" t="s"><v>8074</v></c><c r="D2" s="11"/><c r="E2" s="13"/><c r="F2" s="15"/></row><row r="3" spans="1:7" x14ac:dyDescent="0.2"><c r="A3" s="1"/><c r="B3" s="4" t="s"><v>8075</v></c><c r="D3" s="11"/><c r="E3" s="14"/><c r="F3" s="15"/></row><row r="4" spans="1:7" ht="13.5" thickBot="1" x14ac:dyDescent="0.25"><c r="A4" s="18"/><c r="B4" s="19"/><c r="C4" s="20"/><c r="D4" s="11"/><c r="E4" s="13"/><c r="F4" s="15"/></row><row r="5" spans="1:7" ht="13.5" thickBot="1" x14ac:dyDescent="0.25"><c r="A5" s="21" t="s"><v>8076</v></c><c r="B5" s="22" t="s"><v>8077</v></c><c r="C5" s="22" t="s"><v>8078</v></c><c r="D5" s="23" t="s"><v>8079</v></c><c r="E5" s="24" t="s"><v>8080</v></c><c r="F5" s="25" t="s"><v>8081</v></c></row><row r="6" spans="1:7" x14ac:dyDescent="0.2"><c r="A6" s="26"/><c r="B6" s="5"/><c r="C6" s="6"/><c r="D6" s="12"/><c r="E6" s="27"/><c r="F6" s="28"/><c r="G6" s="7"/></row><row r="7" spans="1:7" ht="14.25" x14ac:dyDescent="0.2"><c r="A7" s="29"><v>1</v></c><c r="B7" s="16" t="s"><v>4080</v></c><c r="C7" s="8" t="s"><v>4079</v></c><c r="D7" s="17"><v>73237</v></c><c r="E7" s="30" t="s"><v>1416</v></c><c r="F7" s="31"><v>7.7967381974248919</v></c></row><row r="8" spans="1:7" ht="14.25" x14ac:dyDescent="0.2"><c r="A8" s="29"><v>2</v></c><c r="B8" s="16" t="s"><v>4081</v></c><c r="C8" s="8" t="s"><v>4079</v></c><c r="D8" s="17"><v>35130</v></c><c r="E8" s="30" t="s"><v>1417</v></c><c r="F8" s="31"><v>0.86582081545064371</v></c><c r="G8" s="9"/></row><row r="9" spans="1:7" ht="14.25" x14ac:dyDescent="0.2"><c r="A9" s="29"><v>3</v></c><c r="B9" s="16" t="s"><v>4082</v></c><c r="C9" s="8" t="s"><v>4079</v></c><c r="D9" s="17"><v>73302</v></c><c r="E9" s="30" t="s"><v>1418</v></c><c r="F9" s="31"><v>13.866185622317595</v></c></row><row r="10" spans="1:7" ht="14.25" x14ac:dyDescent="0.2"><c r="A10" s="29"><v>4</v></c><c r="B10" s="16" t="s"><v>4083</v></c><c r="C10" s="8" t="s"><v>4079</v></c><c r="D10" s="17"><v>73309</v></c><c r="E10" s="30" t="s"><v>1419</v></c><c r="F10" s="31"><v>13.866185622317595</v></c></row><row r="11" spans="1:7" ht="14.25" x14ac:dyDescent="0.2"><c r="A11" s="29"><v>5</v></c><c r="B11" s="16" t="s"><v>4084</v></c><c r="C11" s="8" t="s"><v>4079</v></c><c r="D11" s="17"><v>237513</v></c><c r="E11" s="30" t="s"><v>1420</v></c><c r="F11" s="31"><v>11.560230686695279</v></c></row><row r="12" spans="1:7" ht="14.25" x14ac:dyDescent="0.2"><c r="A12" s="29"><v>6</v></c><c r="B12" s="16" t="s"><v>4085</v></c><c r="C12" s="8" t="s"><v>4079</v></c><c r="D12" s="17"><v>237514</v></c><c r="E12" s="30" t="s"><v>1421</v></c><c r="F12" s="31"><v>13.313626609442059</v></c></row><row r="13" spans="1:7" ht="14.25" x14ac:dyDescent="0.2"><c r="A13" s="29"><v>7</v></c><c r="B13" s="16" t="s"><v>4086</v></c><c r="C13" s="8" t="s"><v>4079</v></c><c r="D13" s="17"><v>237516</v></c><c r="E13" s="30" t="s"><v>1422</v></c><c r="F13" s="31"><v>16.920488197424891</v></c></row><row r="14" spans="1:7" ht="14.25" x14ac:dyDescent="0.2"><c r="A14" s="29"><v>8</v></c><c r="B14" s="16" t="s"><v>4087</v></c><c r="C14" s="8" t="s"><v>4079</v></c><c r="D14" s="17"><v>237517</v></c><c r="E14" s="30" t="s"><v>1423</v></c><c r="F14" s="31"><v>18.595568669527896</v></c></row><row r="15" spans="1:7" ht="14.25" x14ac:dyDescent="0.2"><c r="A15" s="29"><v>9</v></c><c r="B15" s="16" t="s"><v>4088</v></c><c r="C15" s="8" t="s"><v>4079</v></c><c r="D15" s="17"><v>87677</v></c><c r="E15" s="30" t="s"><v>1424</v></c><c r="F15" s="31"><v>24.264737124463519</v></c></row><row r="16" spans="1:7" ht="14.25" x14ac:dyDescent="0.2"><c r="A16" s="29"><v>10</v></c><c r="B16" s="16" t="s"><v>4089</v></c><c r="C16" s="8" t="s"><v>4079</v></c><c r="D16" s="17"><v>219658</v></c><c r="E16" s="30" t="s"><v>1425</v></c><c r="F16" s="31"><v>5.5995547210300414</v></c><c r="G16" s="9"/></row><row r="17" spans="1:7" ht="14.25" x14ac:dyDescent="0.2"><c r="A17" s="29"><v>11</v></c><c r="B17" s="16" t="s"><v>4090</v></c><c r="C17" s="8" t="s"><v>4079</v></c><c r="D17" s="17"><v>219659</v></c><c r="E17" s="30" t="s"><v>1426</v></c><c r="F17" s="31"><v>6.5915504291845481</v></c></row><row r="18" spans="1:7" ht="14.25" x14ac:dyDescent="0.2"><c r="A18" s="29"><v>12</v></c><c r="B18" s="16" t="s"><v>4091</v></c><c r="C18" s="8" t="s"><v>4079</v></c><c r="D18" s="17"><v>219660</v></c><c r="E18" s="30" t="s"><v>1427</v></c><c r="F18" s="31"><v>8.7278218884120147</v></c></row><row r="19" spans="1:7" ht="14.25" x14ac:dyDescent="0.2"><c r="A19" s="29"><v>13</v></c><c r="B19" s="16" t="s"><v>4092</v></c><c r="C19" s="8" t="s"><v>4079</v></c><c r="D19" s="17"><v>219661</v></c><c r="E19" s="30" t="s"><v>1428</v></c><c r="F19" s="31"><v>8.7278218884120147</v></c></row>

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

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

 

[Scott24x7]

Hi Vilhelm,
I made another try, changing the lcDir to

lcDIR = ADDBS(SYS(5))+ADDBS(SYS(2003))+SYS(2015)

I also closed Outlook which I had running, not sure if that made any difference.

I ran it with this change, and now I see the ZIP file get created, along with the DIR.
But the .xml file is not present, and I get the error message "Error opening Workbook.xml" Followed immediately by "Sheet not found", and then the cleanup deletes the zip and temp dir.

(This is when I run TEST2 from the directory that I have the files in).


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

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

 

[clipper01]

Vilhelm , this is a super little utility and actually "almost" solves a client issue that I have had. He has a file of about 27000+ price breaks that is created in Excel 2010 and needs to be updated from time to time to a price break file in his VFP app . Up til now, I've had to save the file as type excel 95 , in 2 parts ( excel 95 has max 16384 rows ) then do 2 imports.

I've tested your utility , works "almost" perfect , both on Vista and Windows 10. The excel file is august.xlsx , the dbf file is prices.dbf , starting at row 2 , the code is
=appendfromxslx("august","prices",,2) and it appends fully the 27000+ rows

So you ask , what is the "almost" , well there seems a bug in your append ( and possibly the import version also ) . If you look at the first field , when the Excel value in field #1 is numeric which it is mostly e.g. 123456 , then the utility imports the value of the previous field #2 . If the excel value is character eg 123456A , then it works correctly ( I'm not sure if this is just a feature of field#1 or also applies to any Excel column )

It may be that in this case the 'numeric' Excel values are actually character product codes that happen to be 6-digit most often, but can be 6-digit + suffix. So maybe the conversion between numeric excel and character dbf field is the problem , but either way it is a 'bug'

I've attached a zip file with the files in question, and can I also add my vote to you as MVP ++++

http://files.engineering.com/getfil...80a-447f-b6fa-235111c6ab36&file=appenderr.zip

Look forward to your comments

 

[vgulielmus]

clipper01, I apologize.
I made another small correction, but I forgot to update my blog.
You can download the most recent version from Append from xlsx 1.3.1
MVP stands for "Modest Vilhelm Praisach", isn't it ? )

Successfully tested under Windows 10.

Scott, I asked for the xlsx, because I have a hunch.
Maybe your Excel workbook contain some XML with different names, or even have names with Unicode characters.
If you have sensitive data, you can change them into gibberish and send me the file, or you can open yourself the xlsx or its copy (the zip file you saw being created)


Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Scott24x7]

Vilhelm,
One column has data validation on it, which is a drop-down list. Will this create a problem? All I care about is the data that is "visible".
I could do a Copy/Paste Values, wouldn't be the worst thing ever. I just tried it as well with your new AppendFrom 1.3.1 version, and that also gets stuck at the same point.
I can pass you the file, but how to do it?


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

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

 

[vgulielmus]

You can attach the file here (under this edit box where we type messages, is a textbox and a link "Click Here to upload...")

Or you can email to praisach@yahoo.com

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

Just in case you didn't noticed yet, the xlsx file is just a zip file and Vilhelm-Ion's coe is copying it to %TEMP% with zip extension, so the oShell object (Shell.Appication) can unzip it without touching the original file. The OS supports ZIP files as directories and you can extract files by copying them to the outside filesystem and that can also be done in code as in faq184-5113

So you can also unzip the xlsx yourself by changing the file extension of a copy to zip and see whether there's something unusual inside the xl\worksheets directory which FOPEN can't open.

Bye, Olaf.

 

[clipper01]

Vilhelm ,
downloaded & tested version 1.3.1 but still has the same issue. You can test with the sample zip files in the previous email. Does seem somehow related to the mismatch between the 6-digit dbf character field and the excel 6-digit field which excel naturally insists on seeing as a numeric.

 

[vgulielmus]

Very odd.

Scott sent me two xlsx files, which I imported without any problem.
On my personal laptop, using Windows 10 Home, and once again on a desktop, using Windows 10 Professional
On both cases, my user is administrator. Maybe this is the cause ?

clipper01, I had no problems with yours :-(

Attached is the appendfromxlsx function used by me... Who knows, maybe on my PC I have some last minute changes ?

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[clipper01]

Vilhelm
yes , seems that your file versions are mixed . The file on your download site on prior email, although it says version 1.3.1 is dated 04/09/2015 , the file you linked on this post is dated 25/09/2015 , still says version 1.3.1 but it works perfectly.

Many thanks for sharing this excellent utility , it fixes what could be a real show-stopper for apps that import Excel files

 

[vgulielmus]

Finally some good news
Thank you for the feedback, clipper01

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[vgulielmus]

Scott, please check your email and tell me if these versions solves your problem (to update the links).
Thank you.

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[vgulielmus]

With the generous support of Mr. Scott Payton, finally I can provide updated versions
I discovered a small but well hidden error
Added the possibility to extract the files using winrar.

To do this, uncomment the #DEFINE archiveWinRar .T. line from importfromxlsx / appendfromxlsx

Somewhere at the top of the two prgs, you'll find :
***************************************************************
* If you prefer to extract files with Winrar, uncomment this
***************************************************************
*#DEFINE archiveWinRar .T.


Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Scott24x7]

Yes, this is all working great now. And this is really quite genius work.
Capabilities like this extend VFP further into the future, supporting new file structures from common platforms like MS Office. Really well done. Wish I could give this 100 stars.

Also Vilhelm, thanks very much for tolerating my stupidity.
Cheers!

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

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