How about using the string functions to convert the file to Comma Separated Values and just having Excel open the .CSV?

Joe

On Thu, Aug 18, 2016 at 12:32 PM, Fred Taylor fbtaylor@gmail.com wrote:

Use 2 queries to break up the number of columns. You can then set the active cell and do 2 separate copy/paste calls.

Fred

On Thu, Aug 18, 2016 at 9:28 AM, < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:

...

VFP9SP2/Win7Pro/SDF fixed width file

I've got a flat file that needs to be translated to an Excel file. Traditional program put it into a cursor and then via automation did copy/paste into Excel. Problem is now that there are more than 255

columns

...

(410 to be exact) so using a VFP cursor/table won't work. I was just

doing

...

direct string manipulations and insertting right to the Excel cells, but it's a SLOW process. It works, but I'm always looking for a better way.

Suggestions for approach?

tia!

[excessive quoting removed by server]

mbsoftwaresolutions@mbsoftwaresolutions.com wrote on 2016-08-18:

Thanks, Joe, Stephen, and Kevin for these ideas. Yep...I've used Christof's ExcelXML class years ago here so that would work for sure. I'm going to try Joe's CSV approach first though, as there's no pretty formatting to be done with this.

On 2016-08-18 12:58, Kevin J Cully wrote: Thinking outside of the box...

Don't use the Excel automation. Convert the flat file into an XML string, and spit that out into a file named "MyFile.xls" and then have Excel open that. It'll be wicked fast. Excel will load the XML as if it was an Excel spreadsheet.

I think Christof had a posting a couple of years ago with a VFP class that would allow for some formatting (Bolds, colors, etc.) of the XML so it looks "nicer" when opened. I think you can even put formulas in the XML and have Excel honor it.

I've used this in VFP and Xojo over the years. It's a great way to do "Excel" without having Excel installed and without worrying about M$ borking up the automation version to version.

-Kevin

-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of mbsoftwaresolutions@mbsoftwaresolutions.com Sent: Thursday, August 18, 2016 12:29 PM To: profox@leafe.com Subject: Flat file to Excel....but it's got more than 255 columns!

VFP9SP2/Win7Pro/SDF fixed width file

I've got a flat file that needs to be translated to an Excel file. Traditional program put it into a cursor and then via automation did copy/paste into Excel. Problem is now that there are more than 255 columns (410 to be exact) so using a VFP cursor/table won't work. I was just doing direct string manipulations and insertting right to the Excel cells, but it's a SLOW process. It works, but I'm always looking for a better way.

Suggestions for approach?

tia!

Mike,

Since you have an automation routine, try it with ExcelObject.Application.ScreenUpdating = .F.

https://msdn.microsoft.com/en-us/library/office/ff193498.aspx

It might speed it up.

Tracy Pearson PowerChurch Software

Thank you for the help with that class Christof. I've converted it to be used in Xojo as well.

Is there a way to do calculations into a cell in the export? Example: To put the formula "=SUM(D2:D10)" into a cell so Excel evaluates the expression?

-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Wollenhaupt, Christof Sent: Thursday, August 18, 2016 2:00 PM To: profox@leafe.com Subject: Re: Flat file to Excel....but it's got more than 255 columns!

I think Christof had a posting a couple of years ago with a VFP class that would allow for some formatting (Bolds, colors, etc.) of the XML so it looks "nicer" when opened. I think you can even put formulas in the XML and have Excel honor it.

Indeed. The white paper is still on our web site:

http://foxpert.com/docs/excel.en.htm

A sample program is at the bottom

-- Christof

--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---

[excessive quoting removed by server]

Kevin J Cully wrote on 2016-08-18:

Thank you for the help with that class Christof. I've converted it to be

used in Xojo as well.

Is there a way to do calculations into a cell in the export? Example: To

put the formula "=SUM(D2:D10)" into a cell so Excel evaluates the expression?

-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Wollenhaupt,

Christof

Sent: Thursday, August 18, 2016 2:00 PM To: profox@leafe.com Subject: Re: Flat file to Excel....but it's got more than 255 columns!

...

I think Christof had a posting a couple of years ago with a VFP class that would allow for some formatting (Bolds, colors, etc.) of the XML so it looks "nicer" when opened. I think you can even put formulas in the XML and have Excel honor it.

Indeed. The white paper is still on our web site:

http://foxpert.com/docs/excel.en.htm

A sample program is at the bottom

-- Christof

Kevin,

I liked the idea of creating an xml file that Excel could open. So I tested and found a way to use a formula. (I saved a worksheet with a formula to the XML format.)

<Cell ss:Formula="=COUNT(RC[-1]:R[9]C[-1])"> <Data ss:Type="Number">0</Data> </Cell>

You can also use a formula in a single-mapped cell, if the cell is mapped to an XML element with an XML Schema Definition (XSD) data type that Excel interprets as a number, date, or time. Pulled from here: https://support.office.com/en-us/article/Overview-of-XML-in-Excel-f11faa7e-6 3ae-4166-b3ac-c9e9752a7d80

Tracy Pearson PowerChurch Software

On 2016-08-19 04:19, Alan Bourke wrote:

I've used the XLSXWorkbook classes on VFPx before (https://vfpx.codeplex.com/wikipage?title=XLSXWorkbook&referringTitle=Hom...) which can read from\write to .XLSX format using native VFP code without Excel being installed.

Sounds like Christof's ExcelXML class. Recall that I can't use VFP data storage due to > 255 columns. I'll check that out. Thx!

Nothing to do with relations. Just too wide a dataset for conventional Fox stuff.

The CSV approach worked out great, btw...thanks, Joe!!!

On 2016-08-19 14:56, Stephen Russell wrote:

What was the command Set relation to? You might be able to get all your tables synced this way. Wow is that a blowback to foxBase days.

On Fri, Aug 19, 2016 at 1:49 PM, < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:

...

On 2016-08-19 04:19, Alan Bourke wrote:

...

I've used the XLSXWorkbook classes on VFPx before (https://vfpx.codeplex.com/wikipage?title=XLSXWorkbook&refer ringTitle=Home) which can read from\write to .XLSX format using native VFP code without Excel being installed.

Sounds like Christof's ExcelXML class. Recall that I can't use VFP data storage due to > 255 columns. I'll check that out. Thx!

[excessive quoting removed by server]

On 2016-08-20 06:09, Wollenhaupt, Christof wrote:

...

Sounds like Christof's ExcelXML class. Recall that I can't use VFP data storage due to > 255 columns.

My class uses the first Excel XML format; the VFPX class uses the current XML format. The old format has the advantage of easily being creatable with text merge, since it's a plain text file. The XLSX Format is a ZIP file with a more complex XML format that requires a library to create files.

Personally, I'm mostly using a commercial product named GemBox these days with Rick Strahl's wwDotNetBridge to call it from VFP.

The CSV tip worked great...now I've got a DIFFERENT type of input source that is again a SDF flat (position-dependent) file, but the trick this time is that the record type flag is a single byte in position X. Depending on X, you have to choose the appropriate SDF layout definition. (Seriously, it's 2016...why the hell are they still outputting text files like this from what's probably a mainframe?!?!???) Same record type is a lot easier, but now that there are multiple layouts and I have to conditionally branch, it's not as easy. The output should be an Excel file with a separate tab for each record type. My current plan is to create the 3 CSVs (as there are 3 layouts per file, record types 1, 2, and 3) and then use Excel automation or Christof's ExcelXML to create the file. I'm leaning towards the former, as it's a given that Excel is on the machine here, and I'm thinking it'll be an easy macro to record and replicate in Excel automation code.

Files are by Provider Number, which is the first 6 characters in each record. I keep a cursor of Provider numbers, the Low Level File Function handle value for each, and then set the FWRITE handle accordingly based on the lookup. So with this changing record type (up to 3), I'll keep a file handle for each Provider and Record Type, output accordingly, and then Excel automation to marry them into one file for that ProviderNumber.

Any thoughts on this twist---would you do it a different way?