Author: mbsoftwaresolutions@mbsoftwaresolutions.com
Posted: 2016-08-18 11:28:58 Link
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!
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/904185dfa3688deba1fe2f1d225bdc1e@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: Brian Erickson
Posted: 2016-08-18 11:31:40 Link
Python is a great language to use.
Brian Erickson
--------------------------------------
"Great scott, 1.21 Giga Watts"
------ Original Message ------
From: mbsoftwaresolutions@mbsoftwaresolutions.com
To: profoxtech@leafe.com
Sent: 8/18/2016 10:28:58 AM
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!
>
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/emb6fb58a5-8398-40ce-9ba3-27de5451b999@brians-pc
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: Fred Taylor
Posted: 2016-08-18 11:32:28 Link
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]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJCBkspMPTS7R5EKbS0yLvpqbamZbZEJxkB+kaRH-jc9fxd93Q@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: Stephen Russell
Posted: 2016-08-18 11:36:25 Link
Consider making the data an xml file to consume in Excel?
On Thu, Aug 18, 2016 at 11: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]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMYKhfJCFp4qjHGT-JL+Xhvgqug7E6WnyanAQ2XvcoKuWqw@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: Joe Yoder
Posted: 2016-08-18 11:40:54 Link
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]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CABQeDnW3TtPfioJ84Ytfj-4HLPx=QCv7evSvbDGui+N=vQ=Egg@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: Kevin J Cully
Posted: 2016-08-18 11:58:37 Link
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!
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/F82DB4DE49440845A14B540F6C2DF6A91BEAF6@BLUFITSPMBS01.cherokeega.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: mbsoftwaresolutions@mbsoftwaresolutions.com
Posted: 2016-08-18 12:11:58 Link
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!
>
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/40b6aedc8957e61c8267e98e0668a90c@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: Tracy Pearson
Posted: 2016-08-18 12:19:35 Link
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
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/000901d1f974$b1ff0da0$15fd28e0$@powerchurch.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: Wollenhaupt, Christof
Posted: 2016-08-18 13:00:01 Link
>
> 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
---
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAL4QJhhxivvDYUNAkPdfuL4e6bKiZH6Od6YoXSwEmsTpmtyGCA@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: Kevin J Cully
Posted: 2016-08-18 13:17:30 Link
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]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/F82DB4DE49440845A14B540F6C2DF6A91BEB99@BLUFITSPMBS01.cherokeega.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.