Re: [U2] Building an Excel File
On 09/02/12 17:03, Israel, John R. wrote: I always read in the file and convert known problem characters to null then process the file row by row. Except if you do that *here* you won't be able to convert it row by row ... you'll have all the data in just one row !!! Cheers, Wol -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Joshua Gallant Sent: Thursday, February 09, 2012 11:44 AM To: U2 Users List Subject: Re: [U2] Building an Excel File I was going to ask the same question about embedded line feeds. I've been dealing with a lot of outside companies the last few months transferring data and found a lot have embedded line feeds all over. - Josh On 2/9/12 11:11 AM, George Gallen ggal...@wyanokegroup.com wrote: Is it possible that the URL has an embedded lf at the end from entry on the source side? I've created csv files comma/quoted that contained URLs and were quite long and didn't have any issues importing Them into excel as a .csv , at least not as breaking into two lines. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:06 AM To: U2 Users List Subject: Re: [U2] Building an Excel File Thanks George. I changed the tabs to pipes just to display them here. Normally I save as tab delimited with no surrounding character. Since the cell contents copies and pastes as 2 lines, I'm not sure whether these suggestions would solve my problem. Thanks again, Charlie On 02-09-2012 8:54 AM, George Gallen wrote: Not sure if this helps... I noticed that you were showing pipe delimited lines... If you rename your file from .csv to .txt Then from excel, you use the Open option, and then you can set the delimiter to | And also change the column types before it imports it, or have it skip a column as well. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 9:46 AM To: U2 Users List Subject: Re: [U2] Building an Excel File While everyone is thinking about CSV and Excel, maybe I can get a solution to a problem that's been plaguing me for a long time. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
On 09/02/12 16:55, Charlie Noah wrote: Hi Josh, I'd be happy to share my routines with you, if they would be of any use. I have a program I use to load a csv file, and a subroutine which converts back and forth between csv, dynamic and fixed width, either a line at a time or an entire flat file. Charlie Aren't there programs to do this on Pickwiki? Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
On 09/02/12 16:47, Charlie Noah wrote: I'm exporting from Excel and importing into jBASE. DCOUNTing on the header line is an excellent idea. I'll give that a try. Since I'm using a convert routine, if that fixes the problem, it will be fixed for any spreadsheet I import. It may very well be someone hitting Enter when typing, because some links in the descriptions don't cause this problem. If I could just detect it in the spreadsheet itself, I could let my distributor know what's causing it and which rows need fixing on their end. They are usually pretty good about fixing things, if they know what to do. I'm sure I've hit this problem ... ime, these fields are quoted. So if you parse the file properly line-by-line you will run out of data in the middle of a quoted field. You then just have to recover by getting the next line. A pain, I know. It makes parsing a pain in the neck. Then add the problem that the csv format doesn't actually have a proper definition, just because Excel does it one way, doesn't mean other programs will do the same. csv is a de-facto, not a formal, standard, and you need to be careful... Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Hi Wol, Yes, many people have written their own routines, and the ones on Pickwiki do most of the things mine do. Different strokes... Regards, Charlie On 02-11-2012 5:22 AM, Wols Lists wrote: On 09/02/12 16:55, Charlie Noah wrote: Hi Josh, I'd be happy to share my routines with you, if they would be of any use. I have a program I use to load a csv file, and a subroutine which converts back and forth between csv, dynamic and fixed width, either a line at a time or an entire flat file. Charlie Aren't there programs to do this on Pickwiki? Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Yes, parsing csv is a pain. So far it may just be easier to default format the column, at least until I can get my distributor to fix the spreadsheet. I save as tab delimited, and as long as I remember to format first, everything is OK. Thanks, Charlie On 02-11-2012 5:27 AM, Wols Lists wrote: On 09/02/12 16:47, Charlie Noah wrote: I'm exporting from Excel and importing into jBASE. DCOUNTing on the header line is an excellent idea. I'll give that a try. Since I'm using a convert routine, if that fixes the problem, it will be fixed for any spreadsheet I import. It may very well be someone hitting Enter when typing, because some links in the descriptions don't cause this problem. If I could just detect it in the spreadsheet itself, I could let my distributor know what's causing it and which rows need fixing on their end. They are usually pretty good about fixing things, if they know what to do. I'm sure I've hit this problem ... ime, these fields are quoted. So if you parse the file properly line-by-line you will run out of data in the middle of a quoted field. You then just have to recover by getting the next line. A pain, I know. It makes parsing a pain in the neck. Then add the problem that the csv format doesn't actually have a proper definition, just because Excel does it one way, doesn't mean other programs will do the same. csv is a de-facto, not a formal, standard, and you need to be careful... Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Windows uses a CR LF where UNIX uses just one (I forget which as I sit at home). The other is converted to a FM. Stripping off the offending character from Windows will still leave the file in a traditional FM delimited structure that is easy to manipulate. We do this all the time with imports. As always, there are exceptions to any thing. John Israel Sent from my iPhone On Feb 11, 2012, at 6:22 AM, Wols Lists antli...@youngman.org.uk wrote: On 09/02/12 17:03, Israel, John R. wrote: I always read in the file and convert known problem characters to null then process the file row by row. Except if you do that *here* you won't be able to convert it row by row ... you'll have all the data in just one row !!! Cheers, Wol -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Joshua Gallant Sent: Thursday, February 09, 2012 11:44 AM To: U2 Users List Subject: Re: [U2] Building an Excel File I was going to ask the same question about embedded line feeds. I've been dealing with a lot of outside companies the last few months transferring data and found a lot have embedded line feeds all over. - Josh On 2/9/12 11:11 AM, George Gallen ggal...@wyanokegroup.com wrote: Is it possible that the URL has an embedded lf at the end from entry on the source side? I've created csv files comma/quoted that contained URLs and were quite long and didn't have any issues importing Them into excel as a .csv , at least not as breaking into two lines. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:06 AM To: U2 Users List Subject: Re: [U2] Building an Excel File Thanks George. I changed the tabs to pipes just to display them here. Normally I save as tab delimited with no surrounding character. Since the cell contents copies and pastes as 2 lines, I'm not sure whether these suggestions would solve my problem. Thanks again, Charlie On 02-09-2012 8:54 AM, George Gallen wrote: Not sure if this helps... I noticed that you were showing pipe delimited lines... If you rename your file from .csv to .txt Then from excel, you use the Open option, and then you can set the delimiter to | And also change the column types before it imports it, or have it skip a column as well. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 9:46 AM To: U2 Users List Subject: Re: [U2] Building an Excel File While everyone is thinking about CSV and Excel, maybe I can get a solution to a problem that's been plaguing me for a long time. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
On 11/02/12 15:38, Israel, John R. wrote: Windows uses a CR LF where UNIX uses just one (I forget which as I sit at home). The other is converted to a FM. Stripping off the offending character from Windows will still leave the file in a traditional FM delimited structure that is easy to manipulate. We do this all the time with imports. But that's the point - the problem character is NOT the CR, it's the LF or FM, whichever one UV cares to present to you. Strip out the FMs, and you won't be able to process it row by row because you won't have row*s* to process. As always, there are exceptions to any thing. Yes. In this case it's the FM that is the problem ... John Israel Cheers, Wol Sent from my iPhone On Feb 11, 2012, at 6:22 AM, Wols Lists antli...@youngman.org.uk wrote: On 09/02/12 17:03, Israel, John R. wrote: I always read in the file and convert known problem characters to null then process the file row by row. Except if you do that *here* you won't be able to convert it row by row ... you'll have all the data in just one row !!! Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
While everyone is thinking about CSV and Excel, maybe I can get a solution to a problem that's been plaguing me for a long time. First, let me say that it's been my experience that Excel (and Open Office, which emulates Excel /so /well) has a problem with saving spreadsheets with double quotes as CSV. The typical result is 2 double quotes where there was one in the original cell. Saving as tab delimited seems to solve the problem, and appears to be more reliable. That is what I do pretty much all the time. This is my problem: I get a weekly product spreadsheet for my online store, which contains a detailed description. I have found that if the description cell contains a URL link, usually to a YouTube video about the product, that row gets split into 2 CSV lines. I have changed tabs to bars and broken all the lines displayed below into 80 column lines for clarity. Here is an example: Copy and paste of Excel cell: Handi Stand|HANDI1|Handi Stand Bird Feeder Stand (Black)|A new innovat ive way to feed the birds. This adjustable Handi-Stand will bring your bird feeder to waist height with the lift of a handle. No need for la dders, stools, buckets, etc to fill your feeder. Adjustable from 5 fee t to 8 feet. Three adjustable heights. Total height of item is 92 inch es and the shortest setting is 59 inches with a middle setting of 70 i nches. The 54 inch measurement is the length of the item when it is bo xed for shipping. Wheel chair accessible. Powder coated steel frame. Although it was all in one cell, when I copied and pasted, it split into 2 lines. This is the second line: Click here to view product video.|689466277531|23.50|2.50|4.00|54.00|2 .50|4.00|92.00|0.00|65.80|131.60||0||HANDI1|HANDI1.jpg|1|1|287|2010-11 -18 00:00:00|171|171|196|194USA URL link referenced in the Excel cell: http://www.youtube.com/watch?v=Sn2JFhAfiPU Tab delimited Save As from Excel file shown in a line editor: 3041: Handi Stand|HANDI1|Handi Stand Bird Feeder Stand (Black)|A new i nnovative way to feed the birds. This adjustable Handi-Stand will brin g your bird feeder to waist height with the lift of a handle. No need for ladders, stools, buckets, etc to fill your feeder. Adjustable from 5 feet to 8 feet. Three adjustable heights. Total height of item is 9 2 inches and the shortest setting is 59 inches with a middle setting o f 70 inches. The 54 inch measurement is the length of the item when i t is boxed for shipping. Wheel chair accessible. Powder coated steel f rame. 3042: Click here to view product video.|689466277531|23.50|2.50|4.00|5 4.00|2.50|4.00|92.00|0.00|65.80|131.60||0||HANDI1|HANDI1.jpg|1|1|287|2 010-11-18 00:00:00|171|171|196|194USA Comma delimited Save As from Excel file shown in a line editor: 3041: Handi Stand,HANDI1,Handi Stand Bird Feeder Stand (Black), A new innovative way to feed the birds. This adjustable Handi-Stand wi ll bring your bird feeder to waist height with the lift of a handle. N o need for ladders, stools, buckets, etc to fill your feeder. Adjustab le from 5 feet to 8 feet. Three adjustable heights. Total height of it em is 92 inches and the shortest setting is 59 inches with a middle se tting of 70 inches. The 54 inch measurement is the length of the item when it is boxed for shipping. Wheel chair accessible. Powder coated s teel frame. 3042: Click here to view product video.,689466277531,23.50,2.50 ,4.00,54.00,2.50,4.00,92.00,0.00,65.80,131.60,,0,,H ANDI1,HANDI1.jpg,1,1,287,2010-11-18 00:00:00,171,171,1 96,194USA I've been working around it by highlighting all the cells in the description column and applying default formatting. That seems to remove the URL link and then everything is fine. Does anyone know of a better way to deal with this? I've asked my distributor to remove the links, but so far they have not. Thanks, Charlie Noah Tiny Bear's Wild Bird Store Everything For The Backyard Bird Enthusiast, Except For The Birds Info, Forum: http://www.TinyBearMarketing.com Store:http://Stores.TinyBearMarketing.com Toll Free: 1-855-TinyBear (855-846-9232) On 02-06-2012 6:06 PM, Holt, Jake wrote: From what I have seen Excel2010 (I think 2007 did it as well) will often strip off the first occurrence of the double quote in the field and leave the ending quote, but still get the columns right. For instance: test,he said, Watch out below.,test2 Will show up as Col1 test Col2 he said, Watch out below. Col3 test2 You have to escape them with an additional double quote to get it right This: test,he said, Watch out below.,test2 Works as expected. So the moral is, replace your double quotes with 2 double quotes before you write surround it with double quotes and write it. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of John Hester Sent: Monday, February 06, 2012 5:45 PM To: U2 Users List Subject: Re: [U2] Building an Excel File It's been my
Re: [U2] Building an Excel File [AD-FREE]
Thanks Don! This is quite nice -- and well documented! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Don Sent: Wednesday, February 08, 2012 9:15 PM To: 'U2 Users List' Subject: Re: [U2] Building an Excel File [AD-FREE] [AD] Completely free http://www.southeast-florida.com/ud/UD2EXCEL_CREATE_V2.zip Contains: - Perl Script to create XLS binary (97-2003) - Unidata Programs including a demo program - Limited Documentation Some features - Headers - Columns Headings - Sorting - SubTotaling and Outlining - Justification (Left / Right / Center) - Footers [/AD] This was written by me about 7 years. It's free, so please keep your comments about the horrible code to yourself. =) Additional Software need: PERL 5.8+ - PPM Module Spreadsheet::Write 2.20+ - PPM Module GetOpt::Long 2.3.7+ I will gladly answer questions offline at u2-usersrem...@thissoutheast-florida.com. Regards, Don Verhagen -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Tony Gravagno Sent: Tuesday, February 07, 2012 10:15 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Building an Excel File Wil, while the OP asked about reading Excel on an iPhone, the title of the thread is Building an Excel File. That prompted suggestions for how to create Excel, including use CSV. Yes, all spreadsheet clients including Excel will open a CSV file. But that led to my assertion that CSV is not Excel and that CSV is not acceptable when people want Excel, though that is a common belief amongst MV developers because most end-users will just take what you give them ... and then they'll quietly start looking for people who will give them what they really want, Excel. This is a digression that you are welcome not to follow. About your statements - I'm intimately familiar with internals of Excel (Office in general), XML, CSV, and related document standards, and I have no idea what kind of middleware you're talking about below. CSV has no information about formatting, it's pure data. It's therefore impossible for any utility to do anything but autosize the columns, and that's trivial. As I said above, if all you're doing is opening the file, sure, that should work, but the result is hardly Excel outside of being text in columns and rows. You can't just simply 'change the Excel files into csv files'. Your statement doesn't survive the first attempt. Excel will warn you that it's going to lose detail if you do that. Again, CSV is not Excel. It doesn't provide borders, colors, images, cell references, merged cells, fonts, type styles, multiple sheets, named sheets, named ranges, file properties, page formatting, print pagination, fixed col/row settings, custom col/row sizes, or data typing. And while you can provide formulas in CSV, you'll lose them if you save Excel as CSV. [AD] And _that_ is the reason I created NebulaXLite, to allow programmers to do all of that from BASIC, on any OS, any DBMS, and with no underlying libraries. It's real workbooks for Excel, Open Office, and Google docs - when everyone else is ready to provide a delimited text file. T From: Wjhonson I think somewhere this jumped the track. No one is saying that CSV is Excel Rather the OP wanted a way to read an Excel file on the Iphone. Can the Iphone natively read Excel files? I know that it can natively read csv files, because we're doing it. It puts the csv file into a neat table with a border, with columns and rows all autosized and pretty with no effort whatsoever. So the solution could be simply to change the Excel files into csv files and voila you have no problem anymore for the Iphone. This doesn't address the issue of whether existing Excel files, or Excel files you receive from others can be handled. It only addresses the issue of reading Tables (of some kind) on the Iphone. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
This is standard and documented behaviour of a csv - if you have a cell with a in it, it is 'escaped' as Anything that then reads a csv must unescape such characters. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: 09 February 2012 14:46 To: U2 Users List Subject: Re: [U2] Building an Excel File While everyone is thinking about CSV and Excel, maybe I can get a solution to a problem that's been plaguing me for a long time. First, let me say that it's been my experience that Excel (and Open Office, which emulates Excel /so /well) has a problem with saving spreadsheets with double quotes as CSV. The typical result is 2 double quotes where there was one in the original cell. Saving as tab delimited seems to solve the problem, and appears to be more reliable. That is what I do pretty much all the time. This is my problem: I get a weekly product spreadsheet for my online store, which contains a detailed description. I have found that if the description cell contains a URL link, usually to a YouTube video about the product, that row gets split into 2 CSV lines. I have changed tabs to bars and broken all the lines displayed below into 80 column lines for clarity. Here is an example: Copy and paste of Excel cell: Handi Stand|HANDI1|Handi Stand Bird Feeder Stand (Black)|A new innovat ive way to feed the birds. This adjustable Handi-Stand will bring your bird feeder to waist height with the lift of a handle. No need for la dders, stools, buckets, etc to fill your feeder. Adjustable from 5 fee t to 8 feet. Three adjustable heights. Total height of item is 92 inch es and the shortest setting is 59 inches with a middle setting of 70 i nches. The 54 inch measurement is the length of the item when it is bo xed for shipping. Wheel chair accessible. Powder coated steel frame. Although it was all in one cell, when I copied and pasted, it split into 2 lines. This is the second line: Click here to view product video.|689466277531|23.50|2.50|4.00|54.00|2 .50|4.00|92.00|0.00|65.80|131.60||0||HANDI1|HANDI1.jpg|1|1|287|2010-11 -18 00:00:00|171|171|196|194USA URL link referenced in the Excel cell: http://www.youtube.com/watch?v=Sn2JFhAfiPU Tab delimited Save As from Excel file shown in a line editor: 3041: Handi Stand|HANDI1|Handi Stand Bird Feeder Stand (Black)|A new i nnovative way to feed the birds. This adjustable Handi-Stand will brin g your bird feeder to waist height with the lift of a handle. No need for ladders, stools, buckets, etc to fill your feeder. Adjustable from 5 feet to 8 feet. Three adjustable heights. Total height of item is 9 2 inches and the shortest setting is 59 inches with a middle setting o f 70 inches. The 54 inch measurement is the length of the item when i t is boxed for shipping. Wheel chair accessible. Powder coated steel f rame. 3042: Click here to view product video.|689466277531|23.50|2.50|4.00|5 4.00|2.50|4.00|92.00|0.00|65.80|131.60||0||HANDI1|HANDI1.jpg|1|1|287|2 010-11-18 00:00:00|171|171|196|194USA Comma delimited Save As from Excel file shown in a line editor: 3041: Handi Stand,HANDI1,Handi Stand Bird Feeder Stand (Black), A new innovative way to feed the birds. This adjustable Handi-Stand wi ll bring your bird feeder to waist height with the lift of a handle. N o need for ladders, stools, buckets, etc to fill your feeder. Adjustab le from 5 feet to 8 feet. Three adjustable heights. Total height of it em is 92 inches and the shortest setting is 59 inches with a middle se tting of 70 inches. The 54 inch measurement is the length of the item when it is boxed for shipping. Wheel chair accessible. Powder coated s teel frame. 3042: Click here to view product video.,689466277531,23.50,2.50 ,4.00,54.00,2.50,4.00,92.00,0.00,65.80,131.60,,0,,H ANDI1,HANDI1.jpg,1,1,287,2010-11-18 00:00:00,171,171,1 96,194USA I've been working around it by highlighting all the cells in the description column and applying default formatting. That seems to remove the URL link and then everything is fine. Does anyone know of a better way to deal with this? I've asked my distributor to remove the links, but so far they have not. Thanks, Charlie Noah Tiny Bear's Wild Bird Store Everything For The Backyard Bird Enthusiast, Except For The Birds Info, Forum: http://www.TinyBearMarketing.com Store:http://Stores.TinyBearMarketing.com Toll Free: 1-855-TinyBear (855-846-9232) On 02-06-2012 6:06 PM, Holt, Jake wrote: From what I have seen Excel2010 (I think 2007 did it as well) will often strip off the first occurrence of the double quote in the field and leave the ending quote, but still get the columns right. For instance: test,he said, Watch out below.,test2 Will show up as Col1 test Col2 he said, Watch out below. Col3 test2 You have to escape them with an additional double quote to get it right This: test,he said, Watch out below.,test2 Works
Re: [U2] Building an Excel File
Funny how Word's mailmerge doesn't respect the quoted quotes that Excel produces! Even though they are cousins...I don't know about Word2010 however, that may have Been fixed, but I doubt it. Although I suspect that the issue with the URL is unrelated to the quote issue. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Symeon Breen Sent: Thursday, February 09, 2012 10:54 AM To: 'U2 Users List' Subject: Re: [U2] Building an Excel File This is standard and documented behaviour of a csv - if you have a cell with a in it, it is 'escaped' as Anything that then reads a csv must unescape such characters. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Thanks George. I changed the tabs to pipes just to display them here. Normally I save as tab delimited with no surrounding character. Since the cell contents copies and pastes as 2 lines, I'm not sure whether these suggestions would solve my problem. Thanks again, Charlie On 02-09-2012 8:54 AM, George Gallen wrote: Not sure if this helps... I noticed that you were showing pipe delimited lines... If you rename your file from .csv to .txt Then from excel, you use the Open option, and then you can set the delimiter to | And also change the column types before it imports it, or have it skip a column as well. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 9:46 AM To: U2 Users List Subject: Re: [U2] Building an Excel File While everyone is thinking about CSV and Excel, maybe I can get a solution to a problem that's been plaguing me for a long time. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Is it possible that the URL has an embedded lf at the end from entry on the source side? I've created csv files comma/quoted that contained URLs and were quite long and didn't have any issues importing Them into excel as a .csv , at least not as breaking into two lines. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:06 AM To: U2 Users List Subject: Re: [U2] Building an Excel File Thanks George. I changed the tabs to pipes just to display them here. Normally I save as tab delimited with no surrounding character. Since the cell contents copies and pastes as 2 lines, I'm not sure whether these suggestions would solve my problem. Thanks again, Charlie On 02-09-2012 8:54 AM, George Gallen wrote: Not sure if this helps... I noticed that you were showing pipe delimited lines... If you rename your file from .csv to .txt Then from excel, you use the Open option, and then you can set the delimiter to | And also change the column types before it imports it, or have it skip a column as well. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 9:46 AM To: U2 Users List Subject: Re: [U2] Building an Excel File While everyone is thinking about CSV and Excel, maybe I can get a solution to a problem that's been plaguing me for a long time. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Hi Symeon, I had forgotten that. Fortunately my convert routine hadn't. ;^) My real problem, though, is URLs splitting lines, which George correctly surmised was a separate situation. Thanks, Charlie On 02-09-2012 9:54 AM, Symeon Breen wrote: This is standard and documented behaviour of a csv - if you have a cell with a in it, it is 'escaped' as Anything that then reads a csv must unescape such characters. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: 09 February 2012 14:46 To: U2 Users List Subject: Re: [U2] Building an Excel File While everyone is thinking about CSV and Excel, maybe I can get a solution to a problem that's been plaguing me for a long time. First, let me say that it's been my experience that Excel (and Open Office, which emulates Excel /so /well) has a problem with saving spreadsheets with double quotes as CSV. The typical result is 2 double quotes where there was one in the original cell. Saving as tab delimited seems to solve the problem, and appears to be more reliable. That is what I do pretty much all the time. This is my problem: I get a weekly product spreadsheet for my online store, which contains a detailed description. I have found that if the description cell contains a URL link, usually to a YouTube video about the product, that row gets split into 2 CSV lines. I have changed tabs to bars and broken all the lines displayed below into 80 column lines for clarity. Here is an example: Copy and paste of Excel cell: Handi Stand|HANDI1|Handi Stand Bird Feeder Stand (Black)|A new innovat ive way to feed the birds. This adjustable Handi-Stand will bring your bird feeder to waist height with the lift of a handle. No need for la dders, stools, buckets, etc to fill your feeder. Adjustable from 5 fee t to 8 feet. Three adjustable heights. Total height of item is 92 inch es and the shortest setting is 59 inches with a middle setting of 70 i nches. The 54 inch measurement is the length of the item when it is bo xed for shipping. Wheel chair accessible. Powder coated steel frame. Although it was all in one cell, when I copied and pasted, it split into 2 lines. This is the second line: Click here to view product video.|689466277531|23.50|2.50|4.00|54.00|2 .50|4.00|92.00|0.00|65.80|131.60||0||HANDI1|HANDI1.jpg|1|1|287|2010-11 -18 00:00:00|171|171|196|194USA URL link referenced in the Excel cell: http://www.youtube.com/watch?v=Sn2JFhAfiPU Tab delimited Save As from Excel file shown in a line editor: 3041: Handi Stand|HANDI1|Handi Stand Bird Feeder Stand (Black)|A new i nnovative way to feed the birds. This adjustable Handi-Stand will brin g your bird feeder to waist height with the lift of a handle. No need for ladders, stools, buckets, etc to fill your feeder. Adjustable from 5 feet to 8 feet. Three adjustable heights. Total height of item is 9 2 inches and the shortest setting is 59 inches with a middle setting o f 70 inches. The 54 inch measurement is the length of the item when i t is boxed for shipping. Wheel chair accessible. Powder coated steel f rame. 3042: Click here to view product video.|689466277531|23.50|2.50|4.00|5 4.00|2.50|4.00|92.00|0.00|65.80|131.60||0||HANDI1|HANDI1.jpg|1|1|287|2 010-11-18 00:00:00|171|171|196|194USA Comma delimited Save As from Excel file shown in a line editor: 3041: Handi Stand,HANDI1,Handi Stand Bird Feeder Stand (Black), A new innovative way to feed the birds. This adjustable Handi-Stand wi ll bring your bird feeder to waist height with the lift of a handle. N o need for ladders, stools, buckets, etc to fill your feeder. Adjustab le from 5 feet to 8 feet. Three adjustable heights. Total height of it em is 92 inches and the shortest setting is 59 inches with a middle se tting of 70 inches. The 54 inch measurement is the length of the item when it is boxed for shipping. Wheel chair accessible. Powder coated s teel frame. 3042: Click here to view product video.,689466277531,23.50,2.50 ,4.00,54.00,2.50,4.00,92.00,0.00,65.80,131.60,,0,,H ANDI1,HANDI1.jpg,1,1,287,2010-11-18 00:00:00,171,171,1 96,194USA I've been working around it by highlighting all the cells in the description column and applying default formatting. That seems to remove the URL link and then everything is fine. Does anyone know of a better way to deal with this? I've asked my distributor to remove the links, but so far they have not. Thanks, Charlie Noah Tiny Bear's Wild Bird Store Everything For The Backyard Bird Enthusiast, Except For The Birds Info, Forum: http://www.TinyBearMarketing.com Store:http://Stores.TinyBearMarketing.com Toll Free: 1-855-TinyBear (855-846-9232) On 02-06-2012 6:06 PM, Holt, Jake wrote: From what I have seen Excel2010 (I think 2007 did it as well) will often strip off the first occurrence of the double quote in the field and leave the ending quote, but still get the columns right
Re: [U2] Building an Excel File
It's certainly possible, although I can't detect it. Do you know how I would see it in the spreadsheet itself? Charlie On 02-09-2012 10:11 AM, George Gallen wrote: Is it possible that the URL has an embedded lf at the end from entry on the source side? I've created csv files comma/quoted that contained URLs and were quite long and didn't have any issues importing Them into excel as a .csv , at least not as breaking into two lines. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:06 AM To: U2 Users List Subject: Re: [U2] Building an Excel File Thanks George. I changed the tabs to pipes just to display them here. Normally I save as tab delimited with no surrounding character. Since the cell contents copies and pastes as 2 lines, I'm not sure whether these suggestions would solve my problem. Thanks again, Charlie On 02-09-2012 8:54 AM, George Gallen wrote: Not sure if this helps... I noticed that you were showing pipe delimited lines... If you rename your file from .csv to .txt Then from excel, you use the Open option, and then you can set the delimiter to | And also change the column types before it imports it, or have it skip a column as well. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 9:46 AM To: U2 Users List Subject: Re: [U2] Building an Excel File While everyone is thinking about CSV and Excel, maybe I can get a solution to a problem that's been plaguing me for a long time. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Are you have trouble importing into UV, or importing it into Excel? If UV, what I do is while looping through the data to be imported, do a dcount on the fields If the dcount is not the same as the dcount on the header line, then concatenate the following to it And skip the following line. You could go the route of keep checking the dcount until it's equal just In case you find a cell or cells that have more than one embedded cr on that row. The problem is in UV, there is no way to distinguish the embedded cr (sorry not lf) from an @AM, however, Usually, excel sees it usually fine, if it's still in the .xls format, but when it exports it to .csv That embedded cr causes it to break it to new line, so you wind up getting the beginning on one line And the end of the row on the next line. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:22 AM To: U2 Users List Subject: Re: [U2] Building an Excel File It's certainly possible, although I can't detect it. Do you know how I would see it in the spreadsheet itself? Charlie On 02-09-2012 10:11 AM, George Gallen wrote: Is it possible that the URL has an embedded lf at the end from entry on the source side? I've created csv files comma/quoted that contained URLs and were quite long and didn't have any issues importing Them into excel as a .csv , at least not as breaking into two lines. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:06 AM To: U2 Users List Subject: Re: [U2] Building an Excel File Thanks George. I changed the tabs to pipes just to display them here. Normally I save as tab delimited with no surrounding character. Since the cell contents copies and pastes as 2 lines, I'm not sure whether these suggestions would solve my problem. Thanks again, Charlie On 02-09-2012 8:54 AM, George Gallen wrote: Not sure if this helps... I noticed that you were showing pipe delimited lines... If you rename your file from .csv to .txt Then from excel, you use the Open option, and then you can set the delimiter to | And also change the column types before it imports it, or have it skip a column as well. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 9:46 AM To: U2 Users List Subject: Re: [U2] Building an Excel File While everyone is thinking about CSV and Excel, maybe I can get a solution to a problem that's been plaguing me for a long time. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
BTW, this occurs usually, when a textbox is used for data entry, someone pastes a URL, then hits the ENTER key And then types some more data. To the original database/excel file it's one cell (.xls file), but when that Source file is exported to the .csv (you know, that warning that some formatting features will be lost?!?) What would solve the problem would be if the .csv standard also included escaping of cr and lf characters so That the export didn't get broken I get those frequently from clients, when they have address fields where the client types the first line, Then hits enter and enters the second line, instead of having two address line fields. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:22 AM To: U2 Users List Subject: Re: [U2] Building an Excel File It's certainly possible, although I can't detect it. Do you know how I would see it in the spreadsheet itself? Charlie On 02-09-2012 10:11 AM, George Gallen wrote: Is it possible that the URL has an embedded lf at the end from entry on the source side? I've created csv files comma/quoted that contained URLs and were quite long and didn't have any issues importing Them into excel as a .csv , at least not as breaking into two lines. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:06 AM To: U2 Users List Subject: Re: [U2] Building an Excel File Thanks George. I changed the tabs to pipes just to display them here. Normally I save as tab delimited with no surrounding character. Since the cell contents copies and pastes as 2 lines, I'm not sure whether these suggestions would solve my problem. Thanks again, Charlie On 02-09-2012 8:54 AM, George Gallen wrote: Not sure if this helps... I noticed that you were showing pipe delimited lines... If you rename your file from .csv to .txt Then from excel, you use the Open option, and then you can set the delimiter to | And also change the column types before it imports it, or have it skip a column as well. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 9:46 AM To: U2 Users List Subject: Re: [U2] Building an Excel File While everyone is thinking about CSV and Excel, maybe I can get a solution to a problem that's been plaguing me for a long time. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
I was going to ask the same question about embedded line feeds. I've been dealing with a lot of outside companies the last few months transferring data and found a lot have embedded line feeds all over. - Josh On 2/9/12 11:11 AM, George Gallen ggal...@wyanokegroup.com wrote: Is it possible that the URL has an embedded lf at the end from entry on the source side? I've created csv files comma/quoted that contained URLs and were quite long and didn't have any issues importing Them into excel as a .csv , at least not as breaking into two lines. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:06 AM To: U2 Users List Subject: Re: [U2] Building an Excel File Thanks George. I changed the tabs to pipes just to display them here. Normally I save as tab delimited with no surrounding character. Since the cell contents copies and pastes as 2 lines, I'm not sure whether these suggestions would solve my problem. Thanks again, Charlie On 02-09-2012 8:54 AM, George Gallen wrote: Not sure if this helps... I noticed that you were showing pipe delimited lines... If you rename your file from .csv to .txt Then from excel, you use the Open option, and then you can set the delimiter to | And also change the column types before it imports it, or have it skip a column as well. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 9:46 AM To: U2 Users List Subject: Re: [U2] Building an Excel File While everyone is thinking about CSV and Excel, maybe I can get a solution to a problem that's been plaguing me for a long time. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
I'm exporting from Excel and importing into jBASE. DCOUNTing on the header line is an excellent idea. I'll give that a try. Since I'm using a convert routine, if that fixes the problem, it will be fixed for any spreadsheet I import. It may very well be someone hitting Enter when typing, because some links in the descriptions don't cause this problem. If I could just detect it in the spreadsheet itself, I could let my distributor know what's causing it and which rows need fixing on their end. They are usually pretty good about fixing things, if they know what to do. Charlie On 02-09-2012 10:26 AM, George Gallen wrote: Are you have trouble importing into UV, or importing it into Excel? If UV, what I do is while looping through the data to be imported, do a dcount on the fields If the dcount is not the same as the dcount on the header line, then concatenate the following to it And skip the following line. You could go the route of keep checking the dcount until it's equal just In case you find a cell or cells that have more than one embedded cr on that row. The problem is in UV, there is no way to distinguish the embedded cr (sorry not lf) from an @AM, however, Usually, excel sees it usually fine, if it's still in the .xls format, but when it exports it to .csv That embedded cr causes it to break it to new line, so you wind up getting the beginning on one line And the end of the row on the next line. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:22 AM To: U2 Users List Subject: Re: [U2] Building an Excel File It's certainly possible, although I can't detect it. Do you know how I would see it in the spreadsheet itself? Charlie On 02-09-2012 10:11 AM, George Gallen wrote: Is it possible that the URL has an embedded lf at the end from entry on the source side? I've created csv files comma/quoted that contained URLs and were quite long and didn't have any issues importing Them into excel as a .csv , at least not as breaking into two lines. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:06 AM To: U2 Users List Subject: Re: [U2] Building an Excel File Thanks George. I changed the tabs to pipes just to display them here. Normally I save as tab delimited with no surrounding character. Since the cell contents copies and pastes as 2 lines, I'm not sure whether these suggestions would solve my problem. Thanks again, Charlie On 02-09-2012 8:54 AM, George Gallen wrote: Not sure if this helps... I noticed that you were showing pipe delimited lines... If you rename your file from .csv to .txt Then from excel, you use the Open option, and then you can set the delimiter to | And also change the column types before it imports it, or have it skip a column as well. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 9:46 AM To: U2 Users List Subject: Re: [U2] Building an Excel File While everyone is thinking about CSV and Excel, maybe I can get a solution to a problem that's been plaguing me for a long time. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
It is entitrely possible and ok to have new lines inside a cell in excel and inside a cell in a csv The following col1,2,col3 and New line,col4 Is ok because the new line is inside the quotes of col3 The trick when parsing in u2 is to not do it line by line, but byte by byte -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: 09 February 2012 16:31 To: U2 Users List Subject: Re: [U2] Building an Excel File BTW, this occurs usually, when a textbox is used for data entry, someone pastes a URL, then hits the ENTER key And then types some more data. To the original database/excel file it's one cell (.xls file), but when that Source file is exported to the .csv (you know, that warning that some formatting features will be lost?!?) What would solve the problem would be if the .csv standard also included escaping of cr and lf characters so That the export didn't get broken I get those frequently from clients, when they have address fields where the client types the first line, Then hits enter and enters the second line, instead of having two address line fields. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:22 AM To: U2 Users List Subject: Re: [U2] Building an Excel File It's certainly possible, although I can't detect it. Do you know how I would see it in the spreadsheet itself? Charlie On 02-09-2012 10:11 AM, George Gallen wrote: Is it possible that the URL has an embedded lf at the end from entry on the source side? I've created csv files comma/quoted that contained URLs and were quite long and didn't have any issues importing Them into excel as a .csv , at least not as breaking into two lines. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:06 AM To: U2 Users List Subject: Re: [U2] Building an Excel File Thanks George. I changed the tabs to pipes just to display them here. Normally I save as tab delimited with no surrounding character. Since the cell contents copies and pastes as 2 lines, I'm not sure whether these suggestions would solve my problem. Thanks again, Charlie On 02-09-2012 8:54 AM, George Gallen wrote: Not sure if this helps... I noticed that you were showing pipe delimited lines... If you rename your file from .csv to .txt Then from excel, you use the Open option, and then you can set the delimiter to | And also change the column types before it imports it, or have it skip a column as well. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 9:46 AM To: U2 Users List Subject: Re: [U2] Building an Excel File While everyone is thinking about CSV and Excel, maybe I can get a solution to a problem that's been plaguing me for a long time. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users - No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1424 / Virus Database: 2112/4797 - Release Date: 02/08/12 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Hi Josh, I'd be happy to share my routines with you, if they would be of any use. I have a program I use to load a csv file, and a subroutine which converts back and forth between csv, dynamic and fixed width, either a line at a time or an entire flat file. Charlie On 02-09-2012 10:44 AM, Joshua Gallant wrote: I was going to ask the same question about embedded line feeds. I've been dealing with a lot of outside companies the last few months transferring data and found a lot have embedded line feeds all over. - Josh On 2/9/12 11:11 AM, George Gallenggal...@wyanokegroup.com wrote: Is it possible that the URL has an embedded lf at the end from entry on the source side? I've created csv files comma/quoted that contained URLs and were quite long and didn't have any issues importing Them into excel as a .csv , at least not as breaking into two lines. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:06 AM To: U2 Users List Subject: Re: [U2] Building an Excel File Thanks George. I changed the tabs to pipes just to display them here. Normally I save as tab delimited with no surrounding character. Since the cell contents copies and pastes as 2 lines, I'm not sure whether these suggestions would solve my problem. Thanks again, Charlie On 02-09-2012 8:54 AM, George Gallen wrote: Not sure if this helps... I noticed that you were showing pipe delimited lines... If you rename your file from .csv to .txt Then from excel, you use the Open option, and then you can set the delimiter to | And also change the column types before it imports it, or have it skip a column as well. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 9:46 AM To: U2 Users List Subject: Re: [U2] Building an Excel File While everyone is thinking about CSV and Excel, maybe I can get a solution to a problem that's been plaguing me for a long time. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
The problem occurs before it ever gets to UV (or in my case jBASE). The csv flat file already has the cell split into 2 lines. On 02-09-2012 10:53 AM, Symeon Breen wrote: It is entitrely possible and ok to have new lines inside a cell in excel and inside a cell in a csv The following col1,2,col3 and New line,col4 Is ok because the new line is inside the quotes of col3 The trick when parsing in u2 is to not do it line by line, but byte by byte -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: 09 February 2012 16:31 To: U2 Users List Subject: Re: [U2] Building an Excel File BTW, this occurs usually, when a textbox is used for data entry, someone pastes a URL, then hits the ENTER key And then types some more data. To the original database/excel file it's one cell (.xls file), but when that Source file is exported to the .csv (you know, that warning that some formatting features will be lost?!?) What would solve the problem would be if the .csv standard also included escaping of cr and lf characters so That the export didn't get broken I get those frequently from clients, when they have address fields where the client types the first line, Then hits enter and enters the second line, instead of having two address line fields. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:22 AM To: U2 Users List Subject: Re: [U2] Building an Excel File It's certainly possible, although I can't detect it. Do you know how I would see it in the spreadsheet itself? Charlie On 02-09-2012 10:11 AM, George Gallen wrote: Is it possible that the URL has an embedded lf at the end from entry on the source side? I've created csv files comma/quoted that contained URLs and were quite long and didn't have any issues importing Them into excel as a .csv , at least not as breaking into two lines. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:06 AM To: U2 Users List Subject: Re: [U2] Building an Excel File Thanks George. I changed the tabs to pipes just to display them here. Normally I save as tab delimited with no surrounding character. Since the cell contents copies and pastes as 2 lines, I'm not sure whether these suggestions would solve my problem. Thanks again, Charlie On 02-09-2012 8:54 AM, George Gallen wrote: Not sure if this helps... I noticed that you were showing pipe delimited lines... If you rename your file from .csv to .txt Then from excel, you use the Open option, and then you can set the delimiter to | And also change the column types before it imports it, or have it skip a column as well. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 9:46 AM To: U2 Users List Subject: Re: [U2] Building an Excel File While everyone is thinking about CSV and Excel, maybe I can get a solution to a problem that's been plaguing me for a long time. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users - No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1424 / Virus Database: 2112/4797 - Release Date: 02/08/12 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Hmm. That might make for an interesting pre-processor to escape those pesky embedded lf's first Then run it through your normal parsing program (line by line parsing). -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Symeon Breen Sent: Thursday, February 09, 2012 11:53 AM To: 'U2 Users List' Subject: Re: [U2] Building an Excel File It is entitrely possible and ok to have new lines inside a cell in excel and inside a cell in a csv The following col1,2,col3 and New line,col4 Is ok because the new line is inside the quotes of col3 The trick when parsing in u2 is to not do it line by line, but byte by byte ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
I always read in the file and convert known problem characters to null then process the file row by row. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Joshua Gallant Sent: Thursday, February 09, 2012 11:44 AM To: U2 Users List Subject: Re: [U2] Building an Excel File I was going to ask the same question about embedded line feeds. I've been dealing with a lot of outside companies the last few months transferring data and found a lot have embedded line feeds all over. - Josh On 2/9/12 11:11 AM, George Gallen ggal...@wyanokegroup.com wrote: Is it possible that the URL has an embedded lf at the end from entry on the source side? I've created csv files comma/quoted that contained URLs and were quite long and didn't have any issues importing Them into excel as a .csv , at least not as breaking into two lines. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:06 AM To: U2 Users List Subject: Re: [U2] Building an Excel File Thanks George. I changed the tabs to pipes just to display them here. Normally I save as tab delimited with no surrounding character. Since the cell contents copies and pastes as 2 lines, I'm not sure whether these suggestions would solve my problem. Thanks again, Charlie On 02-09-2012 8:54 AM, George Gallen wrote: Not sure if this helps... I noticed that you were showing pipe delimited lines... If you rename your file from .csv to .txt Then from excel, you use the Open option, and then you can set the delimiter to | And also change the column types before it imports it, or have it skip a column as well. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 9:46 AM To: U2 Users List Subject: Re: [U2] Building an Excel File While everyone is thinking about CSV and Excel, maybe I can get a solution to a problem that's been plaguing me for a long time. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
What Symeon suggests should still work, since the break is inside the quotes (assuming your using Quoted csv and not tabs), so if you go byte by byte, if you encounter a cr that is inside quotes Escape it and keep reading, if you encounter a cr that is between quotes, consider that the end of the row. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:57 AM To: U2 Users List Subject: Re: [U2] Building an Excel File The problem occurs before it ever gets to UV (or in my case jBASE). The csv flat file already has the cell split into 2 lines. On 02-09-2012 10:53 AM, Symeon Breen wrote: It is entitrely possible and ok to have new lines inside a cell in excel and inside a cell in a csv The following col1,2,col3 and New line,col4 Is ok because the new line is inside the quotes of col3 The trick when parsing in u2 is to not do it line by line, but byte by byte ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
The trick is to NOT read up to a CR or LF, first, and process that as a line. You have to read the file as a stream, literally byte-by-byte, and recognize when you are processing quotes and handle that with different logic. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: February-09-12 9:04 AM To: U2 Users List Subject: Re: [U2] Building an Excel File What Symeon suggests should still work, since the break is inside the quotes (assuming your using Quoted csv and not tabs), so if you go byte by byte, if you encounter a cr that is inside quotes Escape it and keep reading, if you encounter a cr that is between quotes, consider that the end of the row. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:57 AM To: U2 Users List Subject: Re: [U2] Building an Excel File The problem occurs before it ever gets to UV (or in my case jBASE). The csv flat file already has the cell split into 2 lines. On 02-09-2012 10:53 AM, Symeon Breen wrote: It is entitrely possible and ok to have new lines inside a cell in excel and inside a cell in a csv The following col1,2,col3 and New line,col4 Is ok because the new line is inside the quotes of col3 The trick when parsing in u2 is to not do it line by line, but byte by byte ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Hmmm... getting a bit complicated now. I think George's suggestion to DCOUNT the header line and concat if the number of columns are less might be simpler. I really appreciate all the ideas, though. They give me something to think about. Charlie Make things as simple as possible, but not simpler. - Albert Einstein On 02-09-2012 11:16 AM, Robert Houben wrote: The trick is to NOT read up to a CR or LF, first, and process that as a line. You have to read the file as a stream, literally byte-by-byte, and recognize when you are processing quotes and handle that with different logic. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: February-09-12 9:04 AM To: U2 Users List Subject: Re: [U2] Building an Excel File What Symeon suggests should still work, since the break is inside the quotes (assuming your using Quoted csv and not tabs), so if you go byte by byte, if you encounter a cr that is inside quotes Escape it and keep reading, if you encounter a cr that is between quotes, consider that the end of the row. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:57 AM To: U2 Users List Subject: Re: [U2] Building an Excel File The problem occurs before it ever gets to UV (or in my case jBASE). The csv flat file already has the cell split into 2 lines. On 02-09-2012 10:53 AM, Symeon Breen wrote: It is entitrely possible and ok to have new lines inside a cell in excel and inside a cell in a csv The following col1,2,col3 and New line,col4 Is ok because the new line is inside the quotes of col3 The trick when parsing in u2 is to not do it line by line, but byte by byte ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Until someone decides to skip trailing, empty columns... :o YMMV -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: February-09-12 9:25 AM To: U2 Users List Subject: Re: [U2] Building an Excel File Hmmm... getting a bit complicated now. I think George's suggestion to DCOUNT the header line and concat if the number of columns are less might be simpler. I really appreciate all the ideas, though. They give me something to think about. Charlie Make things as simple as possible, but not simpler. - Albert Einstein On 02-09-2012 11:16 AM, Robert Houben wrote: The trick is to NOT read up to a CR or LF, first, and process that as a line. You have to read the file as a stream, literally byte-by-byte, and recognize when you are processing quotes and handle that with different logic. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: February-09-12 9:04 AM To: U2 Users List Subject: Re: [U2] Building an Excel File What Symeon suggests should still work, since the break is inside the quotes (assuming your using Quoted csv and not tabs), so if you go byte by byte, if you encounter a cr that is inside quotes Escape it and keep reading, if you encounter a cr that is between quotes, consider that the end of the row. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:57 AM To: U2 Users List Subject: Re: [U2] Building an Excel File The problem occurs before it ever gets to UV (or in my case jBASE). The csv flat file already has the cell split into 2 lines. On 02-09-2012 10:53 AM, Symeon Breen wrote: It is entitrely possible and ok to have new lines inside a cell in excel and inside a cell in a csv The following col1,2,col3 and New line,col4 Is ok because the new line is inside the quotes of col3 The trick when parsing in u2 is to not do it line by line, but byte by byte ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Yikes! I've seen that, too. On 02-09-2012 11:29 AM, Robert Houben wrote: Until someone decides to skip trailing, empty columns... :o YMMV -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: February-09-12 9:25 AM To: U2 Users List Subject: Re: [U2] Building an Excel File Hmmm... getting a bit complicated now. I think George's suggestion to DCOUNT the header line and concat if the number of columns are less might be simpler. I really appreciate all the ideas, though. They give me something to think about. Charlie Make things as simple as possible, but not simpler. - Albert Einstein On 02-09-2012 11:16 AM, Robert Houben wrote: The trick is to NOT read up to a CR or LF, first, and process that as a line. You have to read the file as a stream, literally byte-by-byte, and recognize when you are processing quotes and handle that with different logic. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: February-09-12 9:04 AM To: U2 Users List Subject: Re: [U2] Building an Excel File What Symeon suggests should still work, since the break is inside the quotes (assuming your using Quoted csv and not tabs), so if you go byte by byte, if you encounter a cr that is inside quotes Escape it and keep reading, if you encounter a cr that is between quotes, consider that the end of the row. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:57 AM To: U2 Users List Subject: Re: [U2] Building an Excel File The problem occurs before it ever gets to UV (or in my case jBASE). The csv flat file already has the cell split into 2 lines. On 02-09-2012 10:53 AM, Symeon Breen wrote: It is entitrely possible and ok to have new lines inside a cell in excel and inside a cell in a csv The following col1,2,col3 and New line,col4 Is ok because the new line is inside the quotes of col3 The trick when parsing in u2 is to not do it line by line, but byte by byte ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
I actually worked through it fine. If 10 programmers attempted this the code would be written 10 different ways but below is what I use. This works fine if the csv file is a windows version where lines end in a LF:CR. I need to adapt this to work with files generated on other systems. In my case I'm working with descriptions that end up as HTML so I convert the embedded characters to a line break br sequence. I use this to get a full line and them dump that line through a parser that processes the line. - Josh LINE.NUM = 0 LINE = '' LAST.VAL = '' IN.QUOTES = @FALSE LOOP WHILE READBLK VAL FROM D.PATH,1 DO BEGIN CASE CASE VAL = CHAR(13) AND NOT(IN.QUOTES) GOSUB PROCESS.LINE LINE = '' CASE VAL = '' AND IN.QUOTES IN.QUOTES = @FALSE LINE := VAL CASE VAL = '' IN.QUOTES = @TRUE LINE := VAL CASE VAL = CHAR(13) AND IN.QUOTES LINE := 'br' CASE VAL = CHAR(10) AND LAST.VAL = CHAR(13) CASE VAL = CHAR(10) LINE := 'br' CASE 1 LINE := VAL END CASE LAST.VAL = VAL REPEAT IF LINE # '' THEN GOSUB PROCESS.LINE LINE = '' END On 2/9/12 11:55 AM, Charlie Noah cwn...@comcast.net wrote: Hi Josh, I'd be happy to share my routines with you, if they would be of any use. I have a program I use to load a csv file, and a subroutine which converts back and forth between csv, dynamic and fixed width, either a line at a time or an entire flat file. Charlie On 02-09-2012 10:44 AM, Joshua Gallant wrote: I was going to ask the same question about embedded line feeds. I've been dealing with a lot of outside companies the last few months transferring data and found a lot have embedded line feeds all over. - Josh On 2/9/12 11:11 AM, George Gallenggal...@wyanokegroup.com wrote: Is it possible that the URL has an embedded lf at the end from entry on the source side? I've created csv files comma/quoted that contained URLs and were quite long and didn't have any issues importing Them into excel as a .csv , at least not as breaking into two lines. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 11:06 AM To: U2 Users List Subject: Re: [U2] Building an Excel File Thanks George. I changed the tabs to pipes just to display them here. Normally I save as tab delimited with no surrounding character. Since the cell contents copies and pastes as 2 lines, I'm not sure whether these suggestions would solve my problem. Thanks again, Charlie On 02-09-2012 8:54 AM, George Gallen wrote: Not sure if this helps... I noticed that you were showing pipe delimited lines... If you rename your file from .csv to .txt Then from excel, you use the Open option, and then you can set the delimiter to | And also change the column types before it imports it, or have it skip a column as well. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Thursday, February 09, 2012 9:46 AM To: U2 Users List Subject: Re: [U2] Building an Excel File While everyone is thinking about CSV and Excel, maybe I can get a solution to a problem that's been plaguing me for a long time. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Since the discussion has shifted from building Excel to consuming CSV, here's a reminder that I have a little utility on my freeware page called ExcelExport, which exports multiple sheets from a workbook into quoted CSV. http://nebula-rnd.com/freeware/ There is also a page dedicated to CSV at PickWiki.com (use the Search box there for csv) and several code modules. I think some of the questions here are already answered there. Contributions to those pages based on this thread would be welcome. If someone emails me content for a specific page I'll be happy to post/format. If nothing else, at some point I'll link from PickWiki back to this thread, though it's nice if people don't need to wade through long threads to find solid content. Tony Gravagno Nebula Research and Development TG@ remove.pleaseNebula-RnD.com remove.pleaseNebula-RnD.com/blog Visit http://PickWiki.com! Contribute! http://Twitter.com/TonyGravagno NEW! http://groups.google.com/group/mvdbms/about ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File [AD]
Sorry to come in a bit late here. I have worked through a couple of versions of NebulaXLite and must say I am very impressed, with both the astounding results and the thoughtful improvements Tony has made to this breakthrough tool. If you have a requirement to clone an existing spreadsheet, but with data from U2, you need to get this tool. It will pay for itself day one and spare you a lot of grunt work. On Mon, Feb 6, 2012 at 2:56 PM, Tony Gravagno 3xk547...@sneakemail.comwrote: [AD] NebulaXLite creates rich XML workbooks (multiple sheets) which can be opened by Google Docs and Open Office, in addition of course to Excel. The XML isn't Excel-specific, it conforms to open standards, and Excel happens to open those files as well. remove.pleaseNebula-RnD.com/products/xlite.htm When I got my Android, I didn't look for Excel for Android, I just viewed docs as Google spreadsheets. Your BlackBerry and iPhone users would probably do well to do the same. NebulaXLite is free for all developers and only a one-time $200 purchase price for end-users with completely free support. Many people here use NebulaXLite over Universe and Unidata, and I hope a few of our clients will comment on their experience. Some months ago I also announced NebulaXPlus which exports XLite documents to a local Windows box to convert to a real Excel ..xls file. This allows even more complex documents to be created - yes, without user intervention. NebulaXPlus is now in beta. Thanks for you time. Tony Gravagno Nebula Research and Development TG@ remove.pleaseNebula-RnD.com remove.pleaseNebula-RnD.com/blog Visit http://PickWiki.com! Contribute! http://Twitter.com/TonyGravagno NEW! http://groups.google.com/group/mvdbms/about From: Israel, John R. We are running UniData on a UNIX platform. We create tab-delimited txt files (and tab delimited txt files with xls extensions) all the time that users open with Excel. Many of these jobs are run in the middle of the night or in a trigger program. In all cases, there is no direct user interaction building these files. We also have some XML files that are coded so that Excel opens them with formatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a meaningful way. Is there a way to build a TRUE Excel file without any user intervention from UniData or UNIX? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File [AD]
Honestly -- back to my prior statement -- why is Rocket just not licensing the logic and shipping it like DataVu?? I suspect it would be used WAY more!! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Ross Morrissey Sent: Thursday, February 09, 2012 3:16 PM To: U2 Users List Subject: Re: [U2] Building an Excel File [AD] Sorry to come in a bit late here. I have worked through a couple of versions of NebulaXLite and must say I am very impressed, with both the astounding results and the thoughtful improvements Tony has made to this breakthrough tool. If you have a requirement to clone an existing spreadsheet, but with data from U2, you need to get this tool. It will pay for itself day one and spare you a lot of grunt work. On Mon, Feb 6, 2012 at 2:56 PM, Tony Gravagno 3xk547...@sneakemail.comwrote: [AD] NebulaXLite creates rich XML workbooks (multiple sheets) which can be opened by Google Docs and Open Office, in addition of course to Excel. The XML isn't Excel-specific, it conforms to open standards, and Excel happens to open those files as well. remove.pleaseNebula-RnD.com/products/xlite.htm When I got my Android, I didn't look for Excel for Android, I just viewed docs as Google spreadsheets. Your BlackBerry and iPhone users would probably do well to do the same. NebulaXLite is free for all developers and only a one-time $200 purchase price for end-users with completely free support. Many people here use NebulaXLite over Universe and Unidata, and I hope a few of our clients will comment on their experience. Some months ago I also announced NebulaXPlus which exports XLite documents to a local Windows box to convert to a real Excel ..xls file. This allows even more complex documents to be created - yes, without user intervention. NebulaXPlus is now in beta. Thanks for you time. Tony Gravagno Nebula Research and Development TG@ remove.pleaseNebula-RnD.com remove.pleaseNebula-RnD.com/blog Visit http://PickWiki.com! Contribute! http://Twitter.com/TonyGravagno NEW! http://groups.google.com/group/mvdbms/about From: Israel, John R. We are running UniData on a UNIX platform. We create tab-delimited txt files (and tab delimited txt files with xls extensions) all the time that users open with Excel. Many of these jobs are run in the middle of the night or in a trigger program. In all cases, there is no direct user interaction building these files. We also have some XML files that are coded so that Excel opens them with formatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a meaningful way. Is there a way to build a TRUE Excel file without any user intervention from UniData or UNIX? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File [AD-FREE]
David W. and others, I also have a perl script that will parse a Excel worksheet from a workbook (Excel 97-2003) if anyone is interested. It returns back a Variable in SheetDatarow,col automatically converts dates/times from the excel number to the pick date time number. I haven't look at in years, because it was solid as a rock. =). As I'm sure Tony G also has a product. I (have) could turn this into a (.NET) webservice that could run on any IIS server that handles both .XLS and XLSX format, but that will cost ya (not too much). David, I should have told you about TU.FORM.OPENDOS [SAVEDOS], we used it in Outsmart forever but I'm old and I forgot. Don Verhagen -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of David Wolverton Sent: Thursday, February 09, 2012 10:39 AM To: 'U2 Users List' Subject: Re: [U2] Building an Excel File [AD-FREE] Thanks Don! This is quite nice -- and well documented! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Don Sent: Wednesday, February 08, 2012 9:15 PM To: 'U2 Users List' Subject: Re: [U2] Building an Excel File [AD-FREE] [AD] Completely free http://www.southeast-florida.com/ud/UD2EXCEL_CREATE_V2.zip Contains: - Perl Script to create XLS binary (97-2003) - Unidata Programs including a demo program - Limited Documentation Some features - Headers - Columns Headings - Sorting - SubTotaling and Outlining - Justification (Left / Right / Center) - Footers [/AD] This was written by me about 7 years. It's free, so please keep your comments about the horrible code to yourself. =) Additional Software need: PERL 5.8+ - PPM Module Spreadsheet::Write 2.20+ - PPM Module GetOpt::Long 2.3.7+ I will gladly answer questions offline at u2-usersrem...@thissoutheast-florida.com. Regards, Don Verhagen -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Tony Gravagno Sent: Tuesday, February 07, 2012 10:15 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Building an Excel File Wil, while the OP asked about reading Excel on an iPhone, the title of the thread is Building an Excel File. That prompted suggestions for how to create Excel, including use CSV. Yes, all spreadsheet clients including Excel will open a CSV file. But that led to my assertion that CSV is not Excel and that CSV is not acceptable when people want Excel, though that is a common belief amongst MV developers because most end-users will just take what you give them ... and then they'll quietly start looking for people who will give them what they really want, Excel. This is a digression that you are welcome not to follow. About your statements - I'm intimately familiar with internals of Excel (Office in general), XML, CSV, and related document standards, and I have no idea what kind of middleware you're talking about below. CSV has no information about formatting, it's pure data. It's therefore impossible for any utility to do anything but autosize the columns, and that's trivial. As I said above, if all you're doing is opening the file, sure, that should work, but the result is hardly Excel outside of being text in columns and rows. You can't just simply 'change the Excel files into csv files'. Your statement doesn't survive the first attempt. Excel will warn you that it's going to lose detail if you do that. Again, CSV is not Excel. It doesn't provide borders, colors, images, cell references, merged cells, fonts, type styles, multiple sheets, named sheets, named ranges, file properties, page formatting, print pagination, fixed col/row settings, custom col/row sizes, or data typing. And while you can provide formulas in CSV, you'll lose them if you save Excel as CSV. [AD] And _that_ is the reason I created NebulaXLite, to allow programmers to do all of that from BASIC, on any OS, any DBMS, and with no underlying libraries. It's real workbooks for Excel, Open Office, and Google docs - when everyone else is ready to provide a delimited text file. T From: Wjhonson I think somewhere this jumped the track. No one is saying that CSV is Excel Rather the OP wanted a way to read an Excel file on the Iphone. Can the Iphone natively read Excel files? I know that it can natively read csv files, because we're doing it. It puts the csv file into a neat table with a border, with columns and rows all autosized and pretty with no effort whatsoever. So the solution could be simply to change the Excel files into csv files and voila you have no problem anymore for the Iphone. This doesn't address the issue of whether existing Excel files, or Excel files you receive from others can be handled. It only addresses the issue of reading Tables (of some kind) on the Iphone. ___ U2-Users mailing list U2
Re: [U2] Building an Excel File
Have encountered exactly this issue. You can convert the raw xml to xlsx (using DocumentFormat.OpenXml.Spreadsheet) and Excel will happily open this. Sadly, have found that iPhone still can't. Not sure why yet but it may be an Apple thing: https://discussions.apple.com/thread/2494221?start=0tstart=0 Brett Israel, John R. johnisr...@daytonsuperior.com wrote in message news:c60fce7b7dfbba4a82b3dfac17e23d0c0f4acdd...@mbexmbx03.daysup.com... No, the Smart Phones can not open the XML at all. The txt and xls files can be opened, but they are just text. Managers want to be able to read the files cleanly. I am guessing the issue is that when a smart phone opens a true Excel file, it is not running Excel, but some sort of emulator. The emulator does not have all the bells and whistles that a full blown version of Excel has, thus undesirable results. John -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Monday, February 06, 2012 4:45 PM To: U2 Users List Subject: Re: [U2] Building an Excel File Can they open an XML file coded for Excel, but without formatting and colors and such? If so, you may just have to have two files produced, one for the phones and one for desktops. I haven't checked, but are there any perl modules that can convert an XML file to an .xls file This would require unix, but not any user intervention. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: Monday, February 06, 2012 4:40 PM To: 'U2 Users List' Subject: [U2] Building an Excel File We are running UniData on a UNIX platform. We create tab-delimited txt files (and tab delimited txt files with xls extensions) all the time that users open with Excel. Many of these jobs are run in the middle of the night or in a trigger program. In all cases, there is no direct user interaction building these files. We also have some XML files that are coded so that Excel opens them with formatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a meaningful way. Is there a way to build a TRUE Excel file without any user intervention from UniData or UNIX? John John Israel Senior ERP Developer Dayton Superior Corporation 1125 Byers Rd Miamisburg, OH 45342 Office: 937-866-0711 x44380 Fax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the intended recipient(s) and may contain information that is privileged, confidential or proprietary. If you are not an intended recipient, please notify the sender, and then please delete and destroy all copies and attachments, and be advised that any review or dissemination of, or the taking of any action in reliance on, the information contained in or attached to this message is prohibited. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users This message contains information that may be privileged or confidential and is the property of GPM Development Ltd. It is intended only for the person to whom it is addressed. If you are not the intended recipient ,you are not authorized to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message. This e-mail was sent to you by GPM Development Ltd. We are incorporated under the laws of England and Wales (company no. 2292156 and VAT registration no. 523 5622 63). Our registered office is 6th Floor, AMP House, Croydon, Surrey CR0 2LX. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
I have never personally used JET-EDIT. For a replacement, we wrote our own code. Since we're an end-user site its not something released anywhere. - Josh On 2/7/12 3:57 PM, Wjhonson wjhon...@aol.com wrote: So what are they using in place of JET? Did you ever use JET-EDIT to edit your programs? -Original Message- From: Joshua Gallant jgall...@cbd.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Tue, Feb 7, 2012 12:41 pm Subject: Re: [U2] Building an Excel File We were running JET until our latest UniVerse upgrade earlier this year. e used it all over and the users actually resisted when we told them it ad to go. Go figure. After a few days testing with our own code they aw the light. - Josh On 2/7/12 12:46 PM, Wjhonson wjhon...@aol.com wrote: Dig it up and market it because Universe is no longer offering JET. -Original Message- rom: Brian Leach br...@brianleach.co.uk o: 'U2 Users List' u2-users@listserver.u2ug.org ent: Tue, Feb 7, 2012 9:43 am ubject: Re: [U2] Building an Excel File IRC didn't VMARK buy CompuSheet, at around the same time they bought JET? 's probably lying around on and old QIC somewhere.. 'd just written a JET and Ultiword emulator for UniVerse when they popped with JET. Grr.. but a long time ago. nd back to topic I second the use Tony's tools motion. Why waste time and ney reinventing the wheel? ou make think your working time is free, but someone's paying for it!) f you really need to call out from Excel (as opposed to building readsheets from the db) you can use VBA and UniObjects. rian Original Message- om: u2-users-boun...@listserver.u2ug.org ailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah nt: 07 February 2012 16:13 : U2 Users List bject: Re: [U2] Building an Excel File men! I don't think I would want to go back to 9600 baud serial mmunications, though. My first Reality job was contract programming r a local service bureau, using an ADDS Viewpoint terminal and 300 ud acoustical coupler. They handed me the terminal, modem and a set of nuals and said Have fun!. Dang, now I'm telling my age! asyCalc ran on Reality as well. Of course, it went the way of many of e other dinosaurs. Now EasyCalc Googles to a scientific calculator on urceForge. I acquired the source code and rights to redistribute, but at never went anywhere. f memory serves, Compusheet used a lot of abs frames for speed. EC was l Data/Basic. It was a bit slow recalculating a series of 600+ readsheets from the bottom up. The others in IT (then called Data ocessing) didn't think I would be able to get it to work at all, but did. The sheets drilled all the way down to class enrollment and perclip counts, and rolled it all up to a top level , which they used feed their GL. probably should have taken this [OT], but I didn't know what effect anging the subject would have. harlie n 02-07-2012 9:52 AM, Dianne Ackerman wrote: Hm, don't remember EasyCalc, but Compusheet was on a Reality system. It was so much easier when we only had to know what was native in our own systems and not be concerned with a million different things to connect to in the outside world! -Dianne On 2/7/2012 10:45 AM, Charlie Noah wrote: Hi Dianne, You're telling your age here! ;^) Actually, it was EasyCalc, which was probably very similar to Compusheet. Charlie On 02-07-2012 9:30 AM, Dianne Ackerman wrote: Compusheet? I remember working with that! -Dianne On 2/7/2012 6:50 AM, Charlie Noah wrote: Hi Bob, I don't suppose CrowFlite can be obtained these days, can it? It looks like Phil has retired. If it were available, would it work with the newer versions of Excel? What about Open Office? In the 80s I worked for a university in Florida, where we used a Pick-based spreadsheet modeled after Lotus 123. I built a series of spreadsheets which reached down into every aspect of the university's database, giving upper management an up to the minute view of the school's financial picture. I think it ended up being over 600 spreadsheets, but hey, fund accounting is complicated and we did still have the 32K limit. Fond memories. ;^) Regards, Charlie Noah Tiny Bear's Wild Bird Store Everything For The Backyard Bird Enthusiast, Except For The Birds Info, Forum: http://www.TinyBearMarketing.com Store:http://Stores.TinyBearMarketing.com Toll Free: 1-855-TinyBear (855-846-9232) On 02-06-2012 10:57 PM, Bob Rasmussen wrote: Makes one long for CrowFlite, doesn't it? For those that don't remember, CrowFlite was an add-in to Excel that allowed it, at a per-cell level, to reach back into a Pick database and grab individual pieces of data. In other words, it was a pull solution as well as a push. I'm cc'ing its creator, Phil Gerber, so he can reminisce about the past while reveling in the sights among the mountains of Oregon :-) On Mon, 6 Feb 2012, Tony Gravagno wrote: Not responding to any particular quote here, just the CSV
Re: [U2] Building an Excel File
I have no idea what you're saying now. I respond to you too much and I'm a troll. You respond to me too much and you're justified. One Troll to another Don't taze me man! -Original Message- From: Tony Gravagno 3xk547...@sneakemail.com To: u2-users u2-users@listserver.u2ug.org Sent: Tue, Feb 7, 2012 11:37 pm Subject: Re: [U2] Building an Excel File Wil, you didn't say 'I', so you're not saying it 'again'. responded to what you said. You're doing that troll thing again. o again, I say - Just stop it man. From: Wjhonson And again I never said csv was Excel. From: Wjhonson No one is saying that CSV is Excel ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
ding ding ding... gentlemen, to your corners... ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
I'm curious; was the university in Florida Barry University? Someone I worked with years ago went there as a Pick programmer. The following code might be helpful subroutine ucsv(result,source,status,is.oconv) * * Public Domain program for OpenQM by Keith Johnson 2012 * * OCONV Converts from attribute and value marks to .CSV format *Attribute marks are converted to CR:LF * ICONV Converts from .CSV to delimited form *If there are no CR:LF sequences, it uses LF *If there are no LF characters, it uses attribute marks * * Usage: To convert ABC to a .CSV format, use code like * *NEWABC = OCONV(ABC,'UUCSV') * * result = '' status = 0 crlf = char(13):char(10) test = index(source,crlf,1) * begin case * * this is OK case source = '' null * * OCONV not OK if there are already CR:LF sequences case is.oconv and test status = 1 * * The normal case when generating .CSV data case is.oconv atts = dcount(source,@am) for attr = 1 to atts line = sourceattr CRT 'line = ':line vals = dcount(line,@vm) part = '' for valu = 1 to vals bite = line1,valu if part ne '' then part := ',' good = @true if index(bite,'',1) then good = @false if index(bite,' ',1) then good = @false if index(bite,',',1) then good = @false if index(bite,char(10),1) then good = @false if index(bite,char(13),1) then good = @false if not(good) then bite = change(bite,'','') bite = '':bite:'' end part := bite next valu CRT 'part = ':part if result eq '' then result = part else result := crlf:part next attr * * Cannot have both of these in ICONV file - the rows will change case index(source,@am,1) and index(source,char(10),1) status = 2 * * converting from standard .CSV file case test flag = @false mine = change(source,crlf,@am) atts = dcount(mine,@am) for attr = 1 to atts line = mineattr span = len(line) part = '' for posn = 1 to span that = line[posn,2] this = that[1,1] begin case case that eq '' and flag part := '' posn += 1 case this eq '' flag = not(flag) case this eq ',' and not(flag) part := @vm case 1 part := this end case next posn result-1 = part next attr * * converting from LF only (or possibly @am) row delimited file case 1 mark = char(10) if not(index(source,mark,1)) then mark = @am if not(index(source,mark,1)) then status = 3 return end end * flag = @false atts = dcount(source,mark) part = '' for attr = 1 to atts line = field(source,mark,attr) span = len(line) for posn = 1 to span that = line[posn,2] this = that[1,1] begin case case that eq '' and flag part := '' posn += 1 case this eq '' and flag flag = @false case this eq ',' and not(flag) part := @vm case 1 part := this end case next posn * * It is possible to have a linefeed within a field if not(flag) then result-1 = part:char(10) part = '' end else * * But not an attribute mark when it is the delimiter * Assume they forgot the last double quote and be nice if mark eq @am then result-1 = part part = '' flag = @false end end next attr end case * return ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
No, it was Stetson University in Deland. On 02-08-2012 4:47 PM, Keith Johnson [DATACOM] wrote: I'm curious; was the university in Florida Barry University? Someone I worked with years ago went there as a Pick programmer. The following code might be helpful subroutine ucsv(result,source,status,is.oconv) * * Public Domain program for OpenQM by Keith Johnson 2012 * * OCONV Converts from attribute and value marks to .CSV format *Attribute marks are converted to CR:LF * ICONV Converts from .CSV to delimited form *If there are no CR:LF sequences, it uses LF *If there are no LF characters, it uses attribute marks * * Usage: To convert ABC to a .CSV format, use code like * *NEWABC = OCONV(ABC,'UUCSV') * * result = '' status = 0 crlf = char(13):char(10) test = index(source,crlf,1) * begin case * * this is OK case source = '' null * * OCONV not OK if there are already CR:LF sequences case is.oconv and test status = 1 * * The normal case when generating .CSV data case is.oconv atts = dcount(source,@am) for attr = 1 to atts line = sourceattr CRT 'line = ':line vals = dcount(line,@vm) part = '' for valu = 1 to vals bite = line1,valu if part ne '' then part := ',' good = @true if index(bite,'',1) then good = @false if index(bite,' ',1) then good = @false if index(bite,',',1) then good = @false if index(bite,char(10),1) then good = @false if index(bite,char(13),1) then good = @false if not(good) then bite = change(bite,'','') bite = '':bite:'' end part := bite next valu CRT 'part = ':part if result eq '' then result = part else result := crlf:part next attr * * Cannot have both of these in ICONV file - the rows will change case index(source,@am,1) and index(source,char(10),1) status = 2 * * converting from standard .CSV file case test flag = @false mine = change(source,crlf,@am) atts = dcount(mine,@am) for attr = 1 to atts line = mineattr span = len(line) part = '' for posn = 1 to span that = line[posn,2] this = that[1,1] begin case case that eq '' and flag part := '' posn += 1 case this eq '' flag = not(flag) case this eq ',' and not(flag) part := @vm case 1 part := this end case next posn result-1 = part next attr * * converting from LF only (or possibly @am) row delimited file case 1 mark = char(10) if not(index(source,mark,1)) then mark = @am if not(index(source,mark,1)) then status = 3 return end end * flag = @false atts = dcount(source,mark) part = '' for attr = 1 to atts line = field(source,mark,attr) span = len(line) for posn = 1 to span that = line[posn,2] this = that[1,1] begin case case that eq '' and flag part := '' posn += 1 case this eq '' and flag flag = @false case this eq ',' and not(flag) part := @vm case 1 part := this end case next posn * * It is possible to have a linefeed within a field if not(flag) then result-1 = part:char(10) part = '' end else * * But not an attribute mark when it is the delimiter * Assume they forgot the last double quote and be nice if mark eq @am then result-1 = part part = '' flag = @false end end next attr end case * return ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org
Re: [U2] Building an Excel File [AD-FREE]
[AD] Completely free http://www.southeast-florida.com/ud/UD2EXCEL_CREATE_V2.zip Contains: - Perl Script to create XLS binary (97-2003) - Unidata Programs including a demo program - Limited Documentation Some features - Headers - Columns Headings - Sorting - SubTotaling and Outlining - Justification (Left / Right / Center) - Footers [/AD] This was written by me about 7 years. It's free, so please keep your comments about the horrible code to yourself. =) Additional Software need: PERL 5.8+ - PPM Module Spreadsheet::Write 2.20+ - PPM Module GetOpt::Long 2.3.7+ I will gladly answer questions offline at u2-usersrem...@thissoutheast-florida.com. Regards, Don Verhagen -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Tony Gravagno Sent: Tuesday, February 07, 2012 10:15 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Building an Excel File Wil, while the OP asked about reading Excel on an iPhone, the title of the thread is Building an Excel File. That prompted suggestions for how to create Excel, including use CSV. Yes, all spreadsheet clients including Excel will open a CSV file. But that led to my assertion that CSV is not Excel and that CSV is not acceptable when people want Excel, though that is a common belief amongst MV developers because most end-users will just take what you give them ... and then they'll quietly start looking for people who will give them what they really want, Excel. This is a digression that you are welcome not to follow. About your statements - I'm intimately familiar with internals of Excel (Office in general), XML, CSV, and related document standards, and I have no idea what kind of middleware you're talking about below. CSV has no information about formatting, it's pure data. It's therefore impossible for any utility to do anything but autosize the columns, and that's trivial. As I said above, if all you're doing is opening the file, sure, that should work, but the result is hardly Excel outside of being text in columns and rows. You can't just simply 'change the Excel files into csv files'. Your statement doesn't survive the first attempt. Excel will warn you that it's going to lose detail if you do that. Again, CSV is not Excel. It doesn't provide borders, colors, images, cell references, merged cells, fonts, type styles, multiple sheets, named sheets, named ranges, file properties, page formatting, print pagination, fixed col/row settings, custom col/row sizes, or data typing. And while you can provide formulas in CSV, you'll lose them if you save Excel as CSV. [AD] And _that_ is the reason I created NebulaXLite, to allow programmers to do all of that from BASIC, on any OS, any DBMS, and with no underlying libraries. It's real workbooks for Excel, Open Office, and Google docs - when everyone else is ready to provide a delimited text file. T From: Wjhonson I think somewhere this jumped the track. No one is saying that CSV is Excel Rather the OP wanted a way to read an Excel file on the Iphone. Can the Iphone natively read Excel files? I know that it can natively read csv files, because we're doing it. It puts the csv file into a neat table with a border, with columns and rows all autosized and pretty with no effort whatsoever. So the solution could be simply to change the Excel files into csv files and voila you have no problem anymore for the Iphone. This doesn't address the issue of whether existing Excel files, or Excel files you receive from others can be handled. It only addresses the issue of reading Tables (of some kind) on the Iphone. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Hi Bob, I don't suppose CrowFlite can be obtained these days, can it? It looks like Phil has retired. If it were available, would it work with the newer versions of Excel? What about Open Office? In the 80s I worked for a university in Florida, where we used a Pick-based spreadsheet modeled after Lotus 123. I built a series of spreadsheets which reached down into every aspect of the university's database, giving upper management an up to the minute view of the school's financial picture. I think it ended up being over 600 spreadsheets, but hey, fund accounting is complicated and we did still have the 32K limit. Fond memories. ;^) Regards, Charlie Noah Tiny Bear's Wild Bird Store Everything For The Backyard Bird Enthusiast, Except For The Birds Info, Forum: http://www.TinyBearMarketing.com Store:http://Stores.TinyBearMarketing.com Toll Free: 1-855-TinyBear (855-846-9232) On 02-06-2012 10:57 PM, Bob Rasmussen wrote: Makes one long for CrowFlite, doesn't it? For those that don't remember, CrowFlite was an add-in to Excel that allowed it, at a per-cell level, to reach back into a Pick database and grab individual pieces of data. In other words, it was a pull solution as well as a push. I'm cc'ing its creator, Phil Gerber, so he can reminisce about the past while reveling in the sights among the mountains of Oregon :-) On Mon, 6 Feb 2012, Tony Gravagno wrote: Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks for Excel and you give them a CSV you're just perpetuating the myth that Pick is a dinosaur. They will gladly spend tens of thousands of dollars to replace your application with something that creates real Excel (and PDF) despite the fact that such things can be attained at low cost or no cost right now. Trust me, I've seen it happen. This dove-tails with the reasons why people get 20 people to support Oracle when they can have 3 working on Pick. The reason is that the Oracle people say yes, and give them pretty reports, when their Pick guys say no, and give them plain text in columns and rows and call it Excel. Please don't let that happen to you. Be sure you are properly responding to end-user requests. Just ask them what they do with the documents after you generate them. If they really just want raw data, OK. But if they go on to tell you how many days it takes to reformat the data, assemble the multiple CSVs into a single workbook, etc, then you have found a great deal of room for improvement. Yeah, I've been there too. Off the soapbox, thanks. T ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users Regards, Bob Rasmussen, President, Rasmussen Software, Inc. personal e-mail: r...@anzio.com company e-mail: r...@anzio.com voice: (US) 503-624-0360 (9:00-6:00 Pacific Time) fax: (US) 503-624-0760 web: http://www.anzio.com street address: Rasmussen Software, Inc. 10240 SW Nimbus, Suite L9 Portland, OR 97223 USA ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
You said this was written for Revelation. Isn't Revelation a Windows based version of PICK, and thus the .bat file it builds will be running Excel which must be loaded on the server? I have a UNIX box which will not have Excel, thus this solution does not appear like it will work for me. If I have missed something, let me know. John -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Richard A. Wilson Sent: Monday, February 06, 2012 4:50 PM To: U2 Users List Subject: Re: [U2] Building an Excel File try this subroutine. Note it is written for an old Revelation G2b application so change as needed Rich SUBROUTINE EXCEL_SCRIPTS_SUBR(RESULT,PROCESS,FROM.FILE,TO.FILE,EXTRA.INFORMATION) * * * Purpose: Create Excel Vbs Scripts * * Date:01-24-12 * * Author: Richard A. Wilson * Lakeside Systems * 4 Myers Street * Smithfield, RI 02917-1821 * Email: rwil...@lakeside-systems.com * * * * M o d i f i c a t i o n s * * Date By Description * 02-06-12 RAW Saving as XLS doesnt require file format * Infact it will cause save as vbs error * * * Note from and to files require complete path L:\RES\etc * RESULT = '' BEGIN CASE CASE PROCESS EQ 'XLSTOTAB' FILE.FORMAT = -4158 EXTENSION = 'TXT' CASE PROCESS EQ 'XLSTOCSV' FILE.FORMAT = 6 EXTENSION = 'CSV' CASE PROCESS EQ 'CSVTOXLS' FILE.FORMAT = 56 EXTENSION = 'XLS' END CASE * COMMA.CNT = COUNT(FROM.FILE,'.') PART1 = FIELD(FROM.FILE,'.',1,COMMA.CNT) * IF TO.FILE EQ THEN TO.FILE = PART1:'.':EXTENSION END * * Quote the file names just incase spaces might exist and cause issues * QUOTED.FROM.FILE = '':FROM.FILE:'' QUOTED.TO.FILE = '':TO.FILE:'' * SCRIPT = '' SCRIPT-1 = ' SCRIPT-1 = ' 01-23-12 Found this script at technet.microsoft.com SCRIPT-1 = ' SCRIPT-1 = if WScript.Arguments.Count 2 Then SCRIPT-1 = 'WScript.Echo Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv' SCRIPT-1 = Wscript.Quit SCRIPT-1 = End If SCRIPT-1 = Dim oExcel SCRIPT-1 = 'Set oExcel = CreateObject(Excel.Application)' SCRIPT-1 = Dim oBook SCRIPT-1 = Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0)) SCRIPT-1 = ' SCRIPT-1 = ' following disables overwrite popup SCRIPT-1 = ' SCRIPT-1 = oExcel.DisplayAlerts = False * * 02-06-12 following IF logic * IF EXTENSION EQ 'XLS' THEN SCRIPT-1 = oBook.SaveAs WScript.Arguments.Item(1) END ELSE SCRIPT-1 = oBook.SaveAs WScript.Arguments.Item(1), :FILE.FORMAT END * SCRIPT-1 = oBook.Close False SCRIPT-1 = oExcel.Quit SCRIPT-1 = ' SCRIPT-1 = ':' 01-23-12 comment out Done' SCRIPT-1 = ' SCRIPT-1 = ':' WScript.Echo Done' * CONVERT @FM TO CHAR(10) IN SCRIPT OSWRITE SCRIPT ON 'C:\BATFILES.WRK\':PROCESS:'.VBS' * * Execute script * CMD = 'C:\BATFILES.WRK\':PROCESS:'.VBS ' CMD := QUOTED.FROM.FILE:' ' CMD := QUOTED.TO.FILE BAT.NAME = 'REN':TIME() CONVERT '.' TO '' IN BAT.NAME BAT.NAME = '\BATFILES.WRK\':BAT.NAME[1,8]:'.BAT' OSWRITE CMD ON BAT.NAME OSCLOSE BAT.NAME PCPERFORM 'CMD.EXE /C ':BAT.NAME PCPERFORM 'ERASE ':BAT.NAME IF EXTRA.INFORMATION EQ 'DELETE_FROM.FILE' THEN PCPERFORM 'CMD.EXE /C ERASE ':FROM.FILE END * RETURN END Israel, John R. wrote: We are running UniData on a UNIX platform. We create tab-delimited txt files (and tab delimited txt files with xls extensions) all the time that users open with Excel. Many of these jobs are run in the middle of the night or in a trigger program. In all cases, there is no direct user interaction building these files. We also have some XML files that are coded so that Excel opens them with formatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a meaningful way. Is there a way to build a TRUE Excel file without any user intervention from UniData or UNIX? John John Israel Senior ERP Developer Dayton Superior Corporation 1125 Byers Rd Miamisburg, OH 45342 Office: 937-866-0711 x44380 Fax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the intended recipient(s) and may contain information that is privileged, confidential or proprietary. If you are not an intended recipient, please notify the sender, and then please
Re: [U2] Building an Excel File
Rev G2b was released in 1986 so it is truely Dos based once Unidata has written the .TXT or .CSV file I assume you'll start up some command shell that would be provided by accuterm or whatever I have used the same type of program for both of the U2's in a windows server environment Rich Israel, John R. wrote: You said this was written for Revelation. Isn't Revelation a Windows based version of PICK, and thus the .bat file it builds will be running Excel which must be loaded on the server? I have a UNIX box which will not have Excel, thus this solution does not appear like it will work for me. If I have missed something, let me know. John -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Richard A. Wilson Sent: Monday, February 06, 2012 4:50 PM To: U2 Users List Subject: Re: [U2] Building an Excel File try this subroutine. Note it is written for an old Revelation G2b application so change as needed Rich SUBROUTINE EXCEL_SCRIPTS_SUBR(RESULT,PROCESS,FROM.FILE,TO.FILE,EXTRA.INFORMATION) * * * Purpose: Create Excel Vbs Scripts * * Date:01-24-12 * * Author: Richard A. Wilson * Lakeside Systems * 4 Myers Street * Smithfield, RI 02917-1821 * Email: rwil...@lakeside-systems.com * * * * M o d i f i c a t i o n s * * Date By Description * 02-06-12 RAW Saving as XLS doesnt require file format * Infact it will cause save as vbs error * * * Note from and to files require complete path L:\RES\etc * RESULT = '' BEGIN CASE CASE PROCESS EQ 'XLSTOTAB' FILE.FORMAT = -4158 EXTENSION = 'TXT' CASE PROCESS EQ 'XLSTOCSV' FILE.FORMAT = 6 EXTENSION = 'CSV' CASE PROCESS EQ 'CSVTOXLS' FILE.FORMAT = 56 EXTENSION = 'XLS' END CASE * COMMA.CNT = COUNT(FROM.FILE,'.') PART1 = FIELD(FROM.FILE,'.',1,COMMA.CNT) * IF TO.FILE EQ THEN TO.FILE = PART1:'.':EXTENSION END * * Quote the file names just incase spaces might exist and cause issues * QUOTED.FROM.FILE = '':FROM.FILE:'' QUOTED.TO.FILE = '':TO.FILE:'' * SCRIPT = '' SCRIPT-1 = ' SCRIPT-1 = ' 01-23-12 Found this script at technet.microsoft.com SCRIPT-1 = ' SCRIPT-1 = if WScript.Arguments.Count 2 Then SCRIPT-1 = 'WScript.Echo Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv' SCRIPT-1 = Wscript.Quit SCRIPT-1 = End If SCRIPT-1 = Dim oExcel SCRIPT-1 = 'Set oExcel = CreateObject(Excel.Application)' SCRIPT-1 = Dim oBook SCRIPT-1 = Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0)) SCRIPT-1 = ' SCRIPT-1 = ' following disables overwrite popup SCRIPT-1 = ' SCRIPT-1 = oExcel.DisplayAlerts = False * * 02-06-12 following IF logic * IF EXTENSION EQ 'XLS' THEN SCRIPT-1 = oBook.SaveAs WScript.Arguments.Item(1) END ELSE SCRIPT-1 = oBook.SaveAs WScript.Arguments.Item(1), :FILE.FORMAT END * SCRIPT-1 = oBook.Close False SCRIPT-1 = oExcel.Quit SCRIPT-1 = ' SCRIPT-1 = ':' 01-23-12 comment out Done' SCRIPT-1 = ' SCRIPT-1 = ':' WScript.Echo Done' * CONVERT @FM TO CHAR(10) IN SCRIPT OSWRITE SCRIPT ON 'C:\BATFILES.WRK\':PROCESS:'.VBS' * * Execute script * CMD = 'C:\BATFILES.WRK\':PROCESS:'.VBS ' CMD := QUOTED.FROM.FILE:' ' CMD := QUOTED.TO.FILE BAT.NAME = 'REN':TIME() CONVERT '.' TO '' IN BAT.NAME BAT.NAME = '\BATFILES.WRK\':BAT.NAME[1,8]:'.BAT' OSWRITE CMD ON BAT.NAME OSCLOSE BAT.NAME PCPERFORM 'CMD.EXE /C ':BAT.NAME PCPERFORM 'ERASE ':BAT.NAME IF EXTRA.INFORMATION EQ 'DELETE_FROM.FILE' THEN PCPERFORM 'CMD.EXE /C ERASE ':FROM.FILE END * RETURN END Israel, John R. wrote: We are running UniData on a UNIX platform. We create tab-delimited txt files (and tab delimited txt files with xls extensions) all the time that users open with Excel. Many of these jobs are run in the middle of the night or in a trigger program. In all cases, there is no direct user interaction building these files. We also have some XML files that are coded so that Excel opens them with formatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a meaningful way. Is there a way to build a TRUE Excel file without any user intervention from UniData or UNIX? John John Israel Senior ERP Developer Dayton Superior Corporation 1125 Byers Rd Miamisburg, OH 45342 Office: 937-866-0711 x44380 Fax
Re: [U2] Building an Excel File
This looks VERY promising! Thanks John -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jeff Butera Sent: Monday, February 06, 2012 7:04 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Building an Excel File I dump csv files and convert to true excel with perl using Spreadsheet::WriteExcel module. Once scripted it's pretty painless and automated. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: Monday, February 06, 2012 4:40 PM To: 'U2 Users List' Subject: [U2] Building an Excel File We are running UniData on a UNIX platform. We create tab-delimited txt files (and tab delimited txt files with xls extensions) all the time that users open with Excel. Many of these jobs are run in the middle of the night or in a trigger program. In all cases, there is no direct user interaction building these files. We also have some XML files that are coded so that Excel opens them with formatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a meaningful way. Is there a way to build a TRUE Excel file without any user intervention from UniData or UNIX? John John Israel Senior ERP Developer Dayton Superior Corporation 1125 Byers Rd Miamisburg, OH 45342 Office: 937-866-0711 x44380 Fax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the intended recipient(s) and may contain information that is privileged, confidential or proprietary. If you are not an intended recipient, please notify the sender, and then please delete and destroy all copies and attachments, and be advised that any review or dissemination of, or the taking of any action in reliance on, the information contained in or attached to this message is prohibited. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- Jeff Butera, PhD Manager of ERP Systems Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
I have to say amen to Tony's comments. Why try and reinvent the wheel when there is a utility already written? Or throw out some kludgy solution when a native one can be found? David A. Green (480) 813-1725 DAG Consulting -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Tony Gravagno Sent: Monday, February 06, 2012 8:06 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Building an Excel File Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks for Excel and you give them a CSV you're just perpetuating the myth that Pick is a dinosaur. They will gladly spend tens of thousands of dollars to replace your application with something that creates real Excel (and PDF) despite the fact that such things can be attained at low cost or no cost right now. Trust me, I've seen it happen. This dove-tails with the reasons why people get 20 people to support Oracle when they can have 3 working on Pick. The reason is that the Oracle people say yes, and give them pretty reports, when their Pick guys say no, and give them plain text in columns and rows and call it Excel. Please don't let that happen to you. Be sure you are properly responding to end-user requests. Just ask them what they do with the documents after you generate them. If they really just want raw data, OK. But if they go on to tell you how many days it takes to reformat the data, assemble the multiple CSVs into a single workbook, etc, then you have found a great deal of room for improvement. Yeah, I've been there too. Off the soapbox, thanks. T ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Compusheet? I remember working with that! -Dianne On 2/7/2012 6:50 AM, Charlie Noah wrote: Hi Bob, I don't suppose CrowFlite can be obtained these days, can it? It looks like Phil has retired. If it were available, would it work with the newer versions of Excel? What about Open Office? In the 80s I worked for a university in Florida, where we used a Pick-based spreadsheet modeled after Lotus 123. I built a series of spreadsheets which reached down into every aspect of the university's database, giving upper management an up to the minute view of the school's financial picture. I think it ended up being over 600 spreadsheets, but hey, fund accounting is complicated and we did still have the 32K limit. Fond memories. ;^) Regards, Charlie Noah Tiny Bear's Wild Bird Store Everything For The Backyard Bird Enthusiast, Except For The Birds Info, Forum: http://www.TinyBearMarketing.com Store:http://Stores.TinyBearMarketing.com Toll Free: 1-855-TinyBear (855-846-9232) On 02-06-2012 10:57 PM, Bob Rasmussen wrote: Makes one long for CrowFlite, doesn't it? For those that don't remember, CrowFlite was an add-in to Excel that allowed it, at a per-cell level, to reach back into a Pick database and grab individual pieces of data. In other words, it was a pull solution as well as a push. I'm cc'ing its creator, Phil Gerber, so he can reminisce about the past while reveling in the sights among the mountains of Oregon :-) On Mon, 6 Feb 2012, Tony Gravagno wrote: Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks for Excel and you give them a CSV you're just perpetuating the myth that Pick is a dinosaur. They will gladly spend tens of thousands of dollars to replace your application with something that creates real Excel (and PDF) despite the fact that such things can be attained at low cost or no cost right now. Trust me, I've seen it happen. This dove-tails with the reasons why people get 20 people to support Oracle when they can have 3 working on Pick. The reason is that the Oracle people say yes, and give them pretty reports, when their Pick guys say no, and give them plain text in columns and rows and call it Excel. Please don't let that happen to you. Be sure you are properly responding to end-user requests. Just ask them what they do with the documents after you generate them. If they really just want raw data, OK. But if they go on to tell you how many days it takes to reformat the data, assemble the multiple CSVs into a single workbook, etc, then you have found a great deal of room for improvement. Yeah, I've been there too. Off the soapbox, thanks. T ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users Regards, Bob Rasmussen, President, Rasmussen Software, Inc. personal e-mail: r...@anzio.com company e-mail: r...@anzio.com voice: (US) 503-624-0360 (9:00-6:00 Pacific Time) fax: (US) 503-624-0760 web: http://www.anzio.com street address: Rasmussen Software, Inc. 10240 SW Nimbus, Suite L9 Portland, OR 97223 USA ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Hi Dianne, You're telling your age here! ;^) Actually, it was EasyCalc, which was probably very similar to Compusheet. Charlie On 02-07-2012 9:30 AM, Dianne Ackerman wrote: Compusheet? I remember working with that! -Dianne On 2/7/2012 6:50 AM, Charlie Noah wrote: Hi Bob, I don't suppose CrowFlite can be obtained these days, can it? It looks like Phil has retired. If it were available, would it work with the newer versions of Excel? What about Open Office? In the 80s I worked for a university in Florida, where we used a Pick-based spreadsheet modeled after Lotus 123. I built a series of spreadsheets which reached down into every aspect of the university's database, giving upper management an up to the minute view of the school's financial picture. I think it ended up being over 600 spreadsheets, but hey, fund accounting is complicated and we did still have the 32K limit. Fond memories. ;^) Regards, Charlie Noah Tiny Bear's Wild Bird Store Everything For The Backyard Bird Enthusiast, Except For The Birds Info, Forum: http://www.TinyBearMarketing.com Store:http://Stores.TinyBearMarketing.com Toll Free: 1-855-TinyBear (855-846-9232) On 02-06-2012 10:57 PM, Bob Rasmussen wrote: Makes one long for CrowFlite, doesn't it? For those that don't remember, CrowFlite was an add-in to Excel that allowed it, at a per-cell level, to reach back into a Pick database and grab individual pieces of data. In other words, it was a pull solution as well as a push. I'm cc'ing its creator, Phil Gerber, so he can reminisce about the past while reveling in the sights among the mountains of Oregon :-) On Mon, 6 Feb 2012, Tony Gravagno wrote: Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks for Excel and you give them a CSV you're just perpetuating the myth that Pick is a dinosaur. They will gladly spend tens of thousands of dollars to replace your application with something that creates real Excel (and PDF) despite the fact that such things can be attained at low cost or no cost right now. Trust me, I've seen it happen. This dove-tails with the reasons why people get 20 people to support Oracle when they can have 3 working on Pick. The reason is that the Oracle people say yes, and give them pretty reports, when their Pick guys say no, and give them plain text in columns and rows and call it Excel. Please don't let that happen to you. Be sure you are properly responding to end-user requests. Just ask them what they do with the documents after you generate them. If they really just want raw data, OK. But if they go on to tell you how many days it takes to reformat the data, assemble the multiple CSVs into a single workbook, etc, then you have found a great deal of room for improvement. Yeah, I've been there too. Off the soapbox, thanks. T ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users Regards, Bob Rasmussen, President, Rasmussen Software, Inc. personal e-mail: r...@anzio.com company e-mail: r...@anzio.com voice: (US) 503-624-0360 (9:00-6:00 Pacific Time) fax: (US) 503-624-0760 web: http://www.anzio.com street address: Rasmussen Software, Inc. 10240 SW Nimbus, Suite L9 Portland, OR 97223 USA ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
On 2/7/2012 6:50 AM, Charlie Noah wrote: Hi Bob, I don't suppose CrowFlite can be obtained these days, can it? It looks like Phil has retired. If it were available, would it work with the newer versions of Excel? What about Open Office? CrowFlite is long out of date, both in support of Excel versions and support of Pick versions (which was always a hassle). But the biggest difficulty was that it used the serial terminal interface to extract data from Pick. This went on through a usually hidden instance of Anzio Lite (on the Windows platform), which is where I was involved. It would be conceivable, though not necessarily profitable, to have an Excel add-in that would pull data out of Pick via a network-based object call. I don't know whether ODBC or SQL or something like that is available, in various implementations - that would be the first question. Regards, Bob Rasmussen, President, Rasmussen Software, Inc. personal e-mail: r...@anzio.com company e-mail: r...@anzio.com voice: (US) 503-624-0360 (9:00-6:00 Pacific Time) fax: (US) 503-624-0760 web: http://www.anzio.com street address: Rasmussen Software, Inc. 10240 SW Nimbus, Suite L9 Portland, OR 97223 USA ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Hm, don't remember EasyCalc, but Compusheet was on a Reality system. It was so much easier when we only had to know what was native in our own systems and not be concerned with a million different things to connect to in the outside world! -Dianne On 2/7/2012 10:45 AM, Charlie Noah wrote: Hi Dianne, You're telling your age here! ;^) Actually, it was EasyCalc, which was probably very similar to Compusheet. Charlie On 02-07-2012 9:30 AM, Dianne Ackerman wrote: Compusheet? I remember working with that! -Dianne On 2/7/2012 6:50 AM, Charlie Noah wrote: Hi Bob, I don't suppose CrowFlite can be obtained these days, can it? It looks like Phil has retired. If it were available, would it work with the newer versions of Excel? What about Open Office? In the 80s I worked for a university in Florida, where we used a Pick-based spreadsheet modeled after Lotus 123. I built a series of spreadsheets which reached down into every aspect of the university's database, giving upper management an up to the minute view of the school's financial picture. I think it ended up being over 600 spreadsheets, but hey, fund accounting is complicated and we did still have the 32K limit. Fond memories. ;^) Regards, Charlie Noah Tiny Bear's Wild Bird Store Everything For The Backyard Bird Enthusiast, Except For The Birds Info, Forum: http://www.TinyBearMarketing.com Store:http://Stores.TinyBearMarketing.com Toll Free: 1-855-TinyBear (855-846-9232) On 02-06-2012 10:57 PM, Bob Rasmussen wrote: Makes one long for CrowFlite, doesn't it? For those that don't remember, CrowFlite was an add-in to Excel that allowed it, at a per-cell level, to reach back into a Pick database and grab individual pieces of data. In other words, it was a pull solution as well as a push. I'm cc'ing its creator, Phil Gerber, so he can reminisce about the past while reveling in the sights among the mountains of Oregon :-) On Mon, 6 Feb 2012, Tony Gravagno wrote: Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks for Excel and you give them a CSV you're just perpetuating the myth that Pick is a dinosaur. They will gladly spend tens of thousands of dollars to replace your application with something that creates real Excel (and PDF) despite the fact that such things can be attained at low cost or no cost right now. Trust me, I've seen it happen. This dove-tails with the reasons why people get 20 people to support Oracle when they can have 3 working on Pick. The reason is that the Oracle people say yes, and give them pretty reports, when their Pick guys say no, and give them plain text in columns and rows and call it Excel. Please don't let that happen to you. Be sure you are properly responding to end-user requests. Just ask them what they do with the documents after you generate them. If they really just want raw data, OK. But if they go on to tell you how many days it takes to reformat the data, assemble the multiple CSVs into a single workbook, etc, then you have found a great deal of room for improvement. Yeah, I've been there too. Off the soapbox, thanks. T ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Amen! I don't think I would want to go back to 9600 baud serial communications, though. My first Reality job was contract programming for a local service bureau, using an ADDS Viewpoint terminal and 300 baud acoustical coupler. They handed me the terminal, modem and a set of manuals and said Have fun!. Dang, now I'm telling my age! EasyCalc ran on Reality as well. Of course, it went the way of many of the other dinosaurs. Now EasyCalc Googles to a scientific calculator on SourceForge. I acquired the source code and rights to redistribute, but that never went anywhere. If memory serves, Compusheet used a lot of abs frames for speed. EC was all Data/Basic. It was a bit slow recalculating a series of 600+ spreadsheets from the bottom up. The others in IT (then called Data Processing) didn't think I would be able to get it to work at all, but it did. The sheets drilled all the way down to class enrollment and paperclip counts, and rolled it all up to a top level , which they used to feed their GL. I probably should have taken this [OT], but I didn't know what effect changing the subject would have. Charlie On 02-07-2012 9:52 AM, Dianne Ackerman wrote: Hm, don't remember EasyCalc, but Compusheet was on a Reality system. It was so much easier when we only had to know what was native in our own systems and not be concerned with a million different things to connect to in the outside world! -Dianne On 2/7/2012 10:45 AM, Charlie Noah wrote: Hi Dianne, You're telling your age here! ;^) Actually, it was EasyCalc, which was probably very similar to Compusheet. Charlie On 02-07-2012 9:30 AM, Dianne Ackerman wrote: Compusheet? I remember working with that! -Dianne On 2/7/2012 6:50 AM, Charlie Noah wrote: Hi Bob, I don't suppose CrowFlite can be obtained these days, can it? It looks like Phil has retired. If it were available, would it work with the newer versions of Excel? What about Open Office? In the 80s I worked for a university in Florida, where we used a Pick-based spreadsheet modeled after Lotus 123. I built a series of spreadsheets which reached down into every aspect of the university's database, giving upper management an up to the minute view of the school's financial picture. I think it ended up being over 600 spreadsheets, but hey, fund accounting is complicated and we did still have the 32K limit. Fond memories. ;^) Regards, Charlie Noah Tiny Bear's Wild Bird Store Everything For The Backyard Bird Enthusiast, Except For The Birds Info, Forum: http://www.TinyBearMarketing.com Store:http://Stores.TinyBearMarketing.com Toll Free: 1-855-TinyBear (855-846-9232) On 02-06-2012 10:57 PM, Bob Rasmussen wrote: Makes one long for CrowFlite, doesn't it? For those that don't remember, CrowFlite was an add-in to Excel that allowed it, at a per-cell level, to reach back into a Pick database and grab individual pieces of data. In other words, it was a pull solution as well as a push. I'm cc'ing its creator, Phil Gerber, so he can reminisce about the past while reveling in the sights among the mountains of Oregon :-) On Mon, 6 Feb 2012, Tony Gravagno wrote: Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks for Excel and you give them a CSV you're just perpetuating the myth that Pick is a dinosaur. They will gladly spend tens of thousands of dollars to replace your application with something that creates real Excel (and PDF) despite the fact that such things can be attained at low cost or no cost right now. Trust me, I've seen it happen. This dove-tails with the reasons why people get 20 people to support Oracle when they can have 3 working on Pick. The reason is that the Oracle people say yes, and give them pretty reports, when their Pick guys say no, and give them plain text in columns and rows and call it Excel. Please don't let that happen to you. Be sure you are properly responding to end-user requests. Just ask them what they do with the documents after you generate them. If they really just want raw data, OK. But if they go on to tell you how many days it takes to reformat the data, assemble the multiple CSVs into a single workbook, etc, then you have found a great deal of room for improvement. Yeah, I've been there too. Off the soapbox, thanks. T ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
IIRC didn't VMARK buy CompuSheet, at around the same time they bought JET? It's probably lying around on and old QIC somewhere.. I'd just written a JET and Ultiword emulator for UniVerse when they popped up with JET. Grr.. but a long time ago. And back to topic I second the use Tony's tools motion. Why waste time and money reinventing the wheel? (You make think your working time is free, but someone's paying for it!) If you really need to call out from Excel (as opposed to building spreadsheets from the db) you can use VBA and UniObjects. Brian -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: 07 February 2012 16:13 To: U2 Users List Subject: Re: [U2] Building an Excel File Amen! I don't think I would want to go back to 9600 baud serial communications, though. My first Reality job was contract programming for a local service bureau, using an ADDS Viewpoint terminal and 300 baud acoustical coupler. They handed me the terminal, modem and a set of manuals and said Have fun!. Dang, now I'm telling my age! EasyCalc ran on Reality as well. Of course, it went the way of many of the other dinosaurs. Now EasyCalc Googles to a scientific calculator on SourceForge. I acquired the source code and rights to redistribute, but that never went anywhere. If memory serves, Compusheet used a lot of abs frames for speed. EC was all Data/Basic. It was a bit slow recalculating a series of 600+ spreadsheets from the bottom up. The others in IT (then called Data Processing) didn't think I would be able to get it to work at all, but it did. The sheets drilled all the way down to class enrollment and paperclip counts, and rolled it all up to a top level , which they used to feed their GL. I probably should have taken this [OT], but I didn't know what effect changing the subject would have. Charlie On 02-07-2012 9:52 AM, Dianne Ackerman wrote: Hm, don't remember EasyCalc, but Compusheet was on a Reality system. It was so much easier when we only had to know what was native in our own systems and not be concerned with a million different things to connect to in the outside world! -Dianne On 2/7/2012 10:45 AM, Charlie Noah wrote: Hi Dianne, You're telling your age here! ;^) Actually, it was EasyCalc, which was probably very similar to Compusheet. Charlie On 02-07-2012 9:30 AM, Dianne Ackerman wrote: Compusheet? I remember working with that! -Dianne On 2/7/2012 6:50 AM, Charlie Noah wrote: Hi Bob, I don't suppose CrowFlite can be obtained these days, can it? It looks like Phil has retired. If it were available, would it work with the newer versions of Excel? What about Open Office? In the 80s I worked for a university in Florida, where we used a Pick-based spreadsheet modeled after Lotus 123. I built a series of spreadsheets which reached down into every aspect of the university's database, giving upper management an up to the minute view of the school's financial picture. I think it ended up being over 600 spreadsheets, but hey, fund accounting is complicated and we did still have the 32K limit. Fond memories. ;^) Regards, Charlie Noah Tiny Bear's Wild Bird Store Everything For The Backyard Bird Enthusiast, Except For The Birds Info, Forum: http://www.TinyBearMarketing.com Store:http://Stores.TinyBearMarketing.com Toll Free: 1-855-TinyBear (855-846-9232) On 02-06-2012 10:57 PM, Bob Rasmussen wrote: Makes one long for CrowFlite, doesn't it? For those that don't remember, CrowFlite was an add-in to Excel that allowed it, at a per-cell level, to reach back into a Pick database and grab individual pieces of data. In other words, it was a pull solution as well as a push. I'm cc'ing its creator, Phil Gerber, so he can reminisce about the past while reveling in the sights among the mountains of Oregon :-) On Mon, 6 Feb 2012, Tony Gravagno wrote: Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks for Excel and you give them a CSV you're just perpetuating the myth that Pick is a dinosaur. They will gladly spend tens of thousands of dollars to replace your application with something that creates real Excel (and PDF) despite the fact that such things can be attained at low cost or no cost right now. Trust me, I've seen it happen. This dove-tails with the reasons why people get 20 people to support Oracle when they can have 3 working on Pick. The reason is that the Oracle people say yes, and give them pretty reports, when their Pick guys say no, and give them plain text in columns and rows and call it Excel. Please don't let that happen to you. Be sure you are properly responding to end-user requests. Just ask them what they do with the documents after you generate them. If they really just want raw data
Re: [U2] Building an Excel File
Dig it up and market it because Universe is no longer offering JET. -Original Message- From: Brian Leach br...@brianleach.co.uk To: 'U2 Users List' u2-users@listserver.u2ug.org Sent: Tue, Feb 7, 2012 9:43 am Subject: Re: [U2] Building an Excel File IIRC didn't VMARK buy CompuSheet, at around the same time they bought JET? t's probably lying around on and old QIC somewhere.. I'd just written a JET and Ultiword emulator for UniVerse when they popped p with JET. Grr.. but a long time ago. nd back to topic I second the use Tony's tools motion. Why waste time and oney reinventing the wheel? You make think your working time is free, but someone's paying for it!) If you really need to call out from Excel (as opposed to building preadsheets from the db) you can use VBA and UniObjects. Brian -Original Message- rom: u2-users-boun...@listserver.u2ug.org mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah ent: 07 February 2012 16:13 o: U2 Users List ubject: Re: [U2] Building an Excel File Amen! I don't think I would want to go back to 9600 baud serial ommunications, though. My first Reality job was contract programming or a local service bureau, using an ADDS Viewpoint terminal and 300 aud acoustical coupler. They handed me the terminal, modem and a set of anuals and said Have fun!. Dang, now I'm telling my age! EasyCalc ran on Reality as well. Of course, it went the way of many of he other dinosaurs. Now EasyCalc Googles to a scientific calculator on ourceForge. I acquired the source code and rights to redistribute, but hat never went anywhere. If memory serves, Compusheet used a lot of abs frames for speed. EC was ll Data/Basic. It was a bit slow recalculating a series of 600+ preadsheets from the bottom up. The others in IT (then called Data rocessing) didn't think I would be able to get it to work at all, but t did. The sheets drilled all the way down to class enrollment and aperclip counts, and rolled it all up to a top level , which they used o feed their GL. I probably should have taken this [OT], but I didn't know what effect hanging the subject would have. Charlie On 02-07-2012 9:52 AM, Dianne Ackerman wrote: Hm, don't remember EasyCalc, but Compusheet was on a Reality system. It was so much easier when we only had to know what was native in our own systems and not be concerned with a million different things to connect to in the outside world! -Dianne On 2/7/2012 10:45 AM, Charlie Noah wrote: Hi Dianne, You're telling your age here! ;^) Actually, it was EasyCalc, which was probably very similar to Compusheet. Charlie On 02-07-2012 9:30 AM, Dianne Ackerman wrote: Compusheet? I remember working with that! -Dianne On 2/7/2012 6:50 AM, Charlie Noah wrote: Hi Bob, I don't suppose CrowFlite can be obtained these days, can it? It looks like Phil has retired. If it were available, would it work with the newer versions of Excel? What about Open Office? In the 80s I worked for a university in Florida, where we used a Pick-based spreadsheet modeled after Lotus 123. I built a series of spreadsheets which reached down into every aspect of the university's database, giving upper management an up to the minute view of the school's financial picture. I think it ended up being over 600 spreadsheets, but hey, fund accounting is complicated and we did still have the 32K limit. Fond memories. ;^) Regards, Charlie Noah Tiny Bear's Wild Bird Store Everything For The Backyard Bird Enthusiast, Except For The Birds Info, Forum: http://www.TinyBearMarketing.com Store:http://Stores.TinyBearMarketing.com Toll Free: 1-855-TinyBear (855-846-9232) On 02-06-2012 10:57 PM, Bob Rasmussen wrote: Makes one long for CrowFlite, doesn't it? For those that don't remember, CrowFlite was an add-in to Excel that allowed it, at a per-cell level, to reach back into a Pick database and grab individual pieces of data. In other words, it was a pull solution as well as a push. I'm cc'ing its creator, Phil Gerber, so he can reminisce about the past while reveling in the sights among the mountains of Oregon :-) On Mon, 6 Feb 2012, Tony Gravagno wrote: Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks for Excel and you give them a CSV you're just perpetuating the myth that Pick is a dinosaur. They will gladly spend tens of thousands of dollars to replace your application with something that creates real Excel (and PDF) despite the fact that such things can be attained at low cost or no cost right now. Trust me, I've seen it happen. This dove-tails with the reasons why people get 20 people to support Oracle when they can have 3 working on Pick. The reason is that the Oracle people say yes, and give them pretty reports, when their Pick guys say no, and give them plain text in columns and rows
Re: [U2] Building an Excel File
what a horrible thought ... some things rightly belong in the trash can of history. Jet and Runoff are definitely in that category. Brian Sent from my ASUS Eee Pad Wjhonson wjhon...@aol.com wrote: Dig it up and market it because Universe is no longer offering JET. -Original Message- From: Brian Leach br...@brianleach.co.uk To: 'U2 Users List' u2-users@listserver.u2ug.org Sent: Tue, Feb 7, 2012 9:43 am Subject: Re: [U2] Building an Excel File IIRC didn't VMARK buy CompuSheet, at around the same time they bought JET? t's probably lying around on and old QIC somewhere.. I'd just written a JET and Ultiword emulator for UniVerse when they popped p with JET. Grr.. but a long time ago. nd back to topic I second the use Tony's tools motion. Why waste time and oney reinventing the wheel? You make think your working time is free, but someone's paying for it!) If you really need to call out from Excel (as opposed to building preadsheets from the db) you can use VBA and UniObjects. Brian -Original Message- rom: u2-users-boun...@listserver.u2ug.org mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah ent: 07 February 2012 16:13 o: U2 Users List ubject: Re: [U2] Building an Excel File Amen! I don't think I would want to go back to 9600 baud serial ommunications, though. My first Reality job was contract programming or a local service bureau, using an ADDS Viewpoint terminal and 300 aud acoustical coupler. They handed me the terminal, modem and a set of anuals and said Have fun!. Dang, now I'm telling my age! EasyCalc ran on Reality as well. Of course, it went the way of many of he other dinosaurs. Now EasyCalc Googles to a scientific calculator on ourceForge. I acquired the source code and rights to redistribute, but hat never went anywhere. If memory serves, Compusheet used a lot of abs frames for speed. EC was ll Data/Basic. It was a bit slow recalculating a series of 600+ preadsheets from the bottom up. The others in IT (then called Data rocessing) didn't think I would be able to get it to work at all, but t did. The sheets drilled all the way down to class enrollment and aperclip counts, and rolled it all up to a top level , which they used o feed their GL. I probably should have taken this [OT], but I didn't know what effect hanging the subject would have. Charlie On 02-07-2012 9:52 AM, Dianne Ackerman wrote: Hm, don't remember EasyCalc, but Compusheet was on a Reality system. It was so much easier when we only had to know what was native in our own systems and not be concerned with a million different things to connect to in the outside world! -Dianne On 2/7/2012 10:45 AM, Charlie Noah wrote: Hi Dianne, You're telling your age here! ;^) Actually, it was EasyCalc, which was probably very similar to Compusheet. Charlie On 02-07-2012 9:30 AM, Dianne Ackerman wrote: Compusheet? I remember working with that! -Dianne On 2/7/2012 6:50 AM, Charlie Noah wrote: Hi Bob, I don't suppose CrowFlite can be obtained these days, can it? It looks like Phil has retired. If it were available, would it work with the newer versions of Excel? What about Open Office? In the 80s I worked for a university in Florida, where we used a Pick-based spreadsheet modeled after Lotus 123. I built a series of spreadsheets which reached down into every aspect of the university's database, giving upper management an up to the minute view of the school's financial picture. I think it ended up being over 600 spreadsheets, but hey, fund accounting is complicated and we did still have the 32K limit. Fond memories. ;^) Regards, Charlie Noah Tiny Bear's Wild Bird Store Everything For The Backyard Bird Enthusiast, Except For The Birds Info, Forum: http://www.TinyBearMarketing.com Store:http://Stores.TinyBearMarketing.com Toll Free: 1-855-TinyBear (855-846-9232) On 02-06-2012 10:57 PM, Bob Rasmussen wrote: Makes one long for CrowFlite, doesn't it? For those that don't remember, CrowFlite was an add-in to Excel that allowed it, at a per-cell level, to reach back into a Pick database and grab individual pieces of data. In other words, it was a pull solution as well as a push. I'm cc'ing its creator, Phil Gerber, so he can reminisce about the past while reveling in the sights among the mountains of Oregon :-) On Mon, 6 Feb 2012, Tony Gravagno wrote: Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks for Excel and you give them a CSV you're just perpetuating the myth that Pick is a dinosaur. They will gladly spend tens of thousands of dollars to replace your application with something that creates real Excel (and PDF) despite the fact that such things can be attained at low cost or no cost right now. Trust me, I've seen it happen. This dove-tails with the reasons why people get 20 people
Re: [U2] Building an Excel File
why when i was writing interfaces we did it with rocks and sticks, AND we had to walk through the snow to do it. barefooted AND WE LOVED IT! ;-) actually i'm kinda surprised no one mentioned using TAB as a delimiter. since it's never in text fields, you always get the data across with no problems. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of David A. Green Sent: Tuesday, February 07, 2012 7:20 AM To: 'U2 Users List' Subject: Re: [U2] Building an Excel File I have to say amen to Tony's comments. Why try and reinvent the wheel when there is a utility already written? Or throw out some kludgy solution when a native one can be found? David A. Green (480) 813-1725 DAG Consulting -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Tony Gravagno Sent: Monday, February 06, 2012 8:06 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Building an Excel File Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks for Excel and you give them a CSV you're just perpetuating the myth that Pick is a dinosaur. They will gladly spend tens of thousands of dollars to replace your application with something that creates real Excel (and PDF) despite the fact that such things can be attained at low cost or no cost right now. Trust me, I've seen it happen. This dove-tails with the reasons why people get 20 people to support Oracle when they can have 3 working on Pick. The reason is that the Oracle people say yes, and give them pretty reports, when their Pick guys say no, and give them plain text in columns and rows and call it Excel. Please don't let that happen to you. Be sure you are properly responding to end-user requests. Just ask them what they do with the documents after you generate them. If they really just want raw data, OK. But if they go on to tell you how many days it takes to reformat the data, assemble the multiple CSVs into a single workbook, etc, then you have found a great deal of room for improvement. Yeah, I've been there too. Off the soapbox, thanks. T ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Why back in our day, the bits traveled at 110 baud, and simplex too. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Allen E. Elwood Sent: Tuesday, February 07, 2012 12:57 PM To: 'U2 Users List' Subject: Re: [U2] Building an Excel File why when i was writing interfaces we did it with rocks and sticks, AND we had to walk through the snow to do it. barefooted AND WE LOVED IT! ;-) actually i'm kinda surprised no one mentioned using TAB as a delimiter. since it's never in text fields, you always get the data across with no problems. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of David A. Green Sent: Tuesday, February 07, 2012 7:20 AM To: 'U2 Users List' Subject: Re: [U2] Building an Excel File I have to say amen to Tony's comments. Why try and reinvent the wheel when there is a utility already written? Or throw out some kludgy solution when a native one can be found? David A. Green (480) 813-1725 DAG Consulting -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Tony Gravagno Sent: Monday, February 06, 2012 8:06 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Building an Excel File Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks for Excel and you give them a CSV you're just perpetuating the myth that Pick is a dinosaur. They will gladly spend tens of thousands of dollars to replace your application with something that creates real Excel (and PDF) despite the fact that such things can be attained at low cost or no cost right now. Trust me, I've seen it happen. This dove-tails with the reasons why people get 20 people to support Oracle when they can have 3 working on Pick. The reason is that the Oracle people say yes, and give them pretty reports, when their Pick guys say no, and give them plain text in columns and rows and call it Excel. Please don't let that happen to you. Be sure you are properly responding to end-user requests. Just ask them what they do with the documents after you generate them. If they really just want raw data, OK. But if they go on to tell you how many days it takes to reformat the data, assemble the multiple CSVs into a single workbook, etc, then you have found a great deal of room for improvement. Yeah, I've been there too. Off the soapbox, thanks. T ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Bet you had ones AND zeros .. We couldn't afford the ones ... Brian Sent from my iPad On 7 Feb 2012, at 18:08, George Gallen ggal...@wyanokegroup.com wrote: Why back in our day, the bits traveled at 110 baud, and simplex too. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Allen E. Elwood Sent: Tuesday, February 07, 2012 12:57 PM To: 'U2 Users List' Subject: Re: [U2] Building an Excel File why when i was writing interfaces we did it with rocks and sticks, AND we had to walk through the snow to do it. barefooted AND WE LOVED IT! ;-) actually i'm kinda surprised no one mentioned using TAB as a delimiter. since it's never in text fields, you always get the data across with no problems. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of David A. Green Sent: Tuesday, February 07, 2012 7:20 AM To: 'U2 Users List' Subject: Re: [U2] Building an Excel File I have to say amen to Tony's comments. Why try and reinvent the wheel when there is a utility already written? Or throw out some kludgy solution when a native one can be found? David A. Green (480) 813-1725 DAG Consulting -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Tony Gravagno Sent: Monday, February 06, 2012 8:06 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Building an Excel File Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks for Excel and you give them a CSV you're just perpetuating the myth that Pick is a dinosaur. They will gladly spend tens of thousands of dollars to replace your application with something that creates real Excel (and PDF) despite the fact that such things can be attained at low cost or no cost right now. Trust me, I've seen it happen. This dove-tails with the reasons why people get 20 people to support Oracle when they can have 3 working on Pick. The reason is that the Oracle people say yes, and give them pretty reports, when their Pick guys say no, and give them plain text in columns and rows and call it Excel. Please don't let that happen to you. Be sure you are properly responding to end-user requests. Just ask them what they do with the documents after you generate them. If they really just want raw data, OK. But if they go on to tell you how many days it takes to reformat the data, assemble the multiple CSVs into a single workbook, etc, then you have found a great deal of room for improvement. Yeah, I've been there too. Off the soapbox, thanks. T ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
When I was a kid we had to carry the bits from one computer to the other by hand! In a tray! And if you dropped that tray you spend the next week putting all the bits back in order! -Original Message- From: George Gallen ggal...@wyanokegroup.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Tue, Feb 7, 2012 10:08 am Subject: Re: [U2] Building an Excel File Why back in our day, the bits traveled at 110 baud, and simplex too. -Original Message- rom: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] n Behalf Of Allen E. Elwood ent: Tuesday, February 07, 2012 12:57 PM o: 'U2 Users List' ubject: Re: [U2] Building an Excel File why when i was writing interfaces we did it with rocks and sticks, AND we ad to walk through the snow to do it. barefooted AND WE LOVED IT! ;-) actually i'm kinda surprised no one mentioned using TAB as a delimiter. ince it's never in text fields, you always get the data across with no roblems. -Original Message- rom: u2-users-boun...@listserver.u2ug.org mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of David A. Green ent: Tuesday, February 07, 2012 7:20 AM o: 'U2 Users List' ubject: Re: [U2] Building an Excel File I have to say amen to Tony's comments. Why try and reinvent the wheel when here is a utility already written? Or throw out some kludgy solution when native one can be found? David A. Green 480) 813-1725 AG Consulting Original Message- rom: u2-users-boun...@listserver.u2ug.org mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Tony Gravagno ent: Monday, February 06, 2012 8:06 PM o: u2-users@listserver.u2ug.org ubject: Re: [U2] Building an Excel File Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks or Excel and you give them a CSV you're just perpetuating the myth that ick is a dinosaur. They will gladly spend tens of thousands of dollars to eplace your application with something that creates real Excel (and PDF) espite the fact that such things can be attained at low cost or no cost ight now. Trust me, I've seen it happen. This dove-tails with the reasons why people get 20 people to support Oracle hen they can have 3 working on Pick. The reason is that the Oracle people ay yes, and give them pretty reports, when their Pick guys say no, and ive them plain text in columns and rows and call it Excel. Please don't let that happen to you. Be sure you are properly responding to nd-user requests. Just ask them what they do with the documents after you enerate them. If they really just want raw data, OK. But if they go on to ell you how many days it takes to reformat the data, assemble the multiple SVs into a single workbook, etc, then you have found a great deal of room or improvement. Yeah, I've been there too. Off the soapbox, thanks. ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users __ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Before we had monitors, WE used to have to write code with magnets! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wjhonson Sent: Tuesday, February 07, 2012 1:19 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Building an Excel File When I was a kid we had to carry the bits from one computer to the other by hand! In a tray! And if you dropped that tray you spend the next week putting all the bits back in order! -Original Message- From: George Gallen ggal...@wyanokegroup.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Tue, Feb 7, 2012 10:08 am Subject: Re: [U2] Building an Excel File Why back in our day, the bits traveled at 110 baud, and simplex too. -Original Message- rom: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] n Behalf Of Allen E. Elwood ent: Tuesday, February 07, 2012 12:57 PM o: 'U2 Users List' ubject: Re: [U2] Building an Excel File why when i was writing interfaces we did it with rocks and sticks, AND we ad to walk through the snow to do it. barefooted AND WE LOVED IT! ;-) actually i'm kinda surprised no one mentioned using TAB as a delimiter. ince it's never in text fields, you always get the data across with no roblems. -Original Message- rom: u2-users-boun...@listserver.u2ug.org mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of David A. Green ent: Tuesday, February 07, 2012 7:20 AM o: 'U2 Users List' ubject: Re: [U2] Building an Excel File I have to say amen to Tony's comments. Why try and reinvent the wheel when here is a utility already written? Or throw out some kludgy solution when native one can be found? David A. Green 480) 813-1725 AG Consulting Original Message- rom: u2-users-boun...@listserver.u2ug.org mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Tony Gravagno ent: Monday, February 06, 2012 8:06 PM o: u2-users@listserver.u2ug.org ubject: Re: [U2] Building an Excel File Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks or Excel and you give them a CSV you're just perpetuating the myth that ick is a dinosaur. They will gladly spend tens of thousands of dollars to eplace your application with something that creates real Excel (and PDF) espite the fact that such things can be attained at low cost or no cost ight now. Trust me, I've seen it happen. This dove-tails with the reasons why people get 20 people to support Oracle hen they can have 3 working on Pick. The reason is that the Oracle people ay yes, and give them pretty reports, when their Pick guys say no, and ive them plain text in columns and rows and call it Excel. Please don't let that happen to you. Be sure you are properly responding to nd-user requests. Just ask them what they do with the documents after you enerate them. If they really just want raw data, OK. But if they go on to ell you how many days it takes to reformat the data, assemble the multiple SVs into a single workbook, etc, then you have found a great deal of room or improvement. Yeah, I've been there too. Off the soapbox, thanks. ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users __ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Ick, that reminds me of a time I was carrying punch cards and dropped them all out of order : On 2/7/2012 1:19 PM, Wjhonson wrote: When I was a kid we had to carry the bits from one computer to the other by hand! In a tray! And if you dropped that tray you spend the next week putting all the bits back in order! -Original Message- From: George Gallenggal...@wyanokegroup.com To: U2 Users Listu2-users@listserver.u2ug.org Sent: Tue, Feb 7, 2012 10:08 am Subject: Re: [U2] Building an Excel File Why back in our day, the bits traveled at 110 baud, and simplex too. -Original Message- rom: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] n Behalf Of Allen E. Elwood ent: Tuesday, February 07, 2012 12:57 PM o: 'U2 Users List' ubject: Re: [U2] Building an Excel File why when i was writing interfaces we did it with rocks and sticks, AND we ad to walk through the snow to do it. barefooted AND WE LOVED IT! ;-) actually i'm kinda surprised no one mentioned using TAB as a delimiter. ince it's never in text fields, you always get the data across with no roblems. -Original Message- rom: u2-users-boun...@listserver.u2ug.org mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of David A. Green ent: Tuesday, February 07, 2012 7:20 AM o: 'U2 Users List' ubject: Re: [U2] Building an Excel File I have to say amen to Tony's comments. Why try and reinvent the wheel when here is a utility already written? Or throw out some kludgy solution when native one can be found? David A. Green 480) 813-1725 AG Consulting Original Message- rom: u2-users-boun...@listserver.u2ug.org mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Tony Gravagno ent: Monday, February 06, 2012 8:06 PM o: u2-users@listserver.u2ug.org ubject: Re: [U2] Building an Excel File Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks or Excel and you give them a CSV you're just perpetuating the myth that ick is a dinosaur. They will gladly spend tens of thousands of dollars to eplace your application with something that creates real Excel (and PDF) espite the fact that such things can be attained at low cost or no cost ight now. Trust me, I've seen it happen. This dove-tails with the reasons why people get 20 people to support Oracle hen they can have 3 working on Pick. The reason is that the Oracle people ay yes, and give them pretty reports, when their Pick guys say no, and ive them plain text in columns and rows and call it Excel. Please don't let that happen to you. Be sure you are properly responding to nd-user requests. Just ask them what they do with the documents after you enerate them. If they really just want raw data, OK. But if they go on to ell you how many days it takes to reformat the data, assemble the multiple SVs into a single workbook, etc, then you have found a great deal of room or improvement. Yeah, I've been there too. Off the soapbox, thanks. ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users __ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Just think if you had dropped them IN order you could now be in the Data Processing Hall of Fame ! -Original Message- From: Dianne Ackerman dia...@aptron.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Tue, Feb 7, 2012 10:32 am Subject: Re: [U2] Building an Excel File Ick, that reminds me of a time I was carrying punch cards and dropped hem all out of order : n 2/7/2012 1:19 PM, Wjhonson wrote: When I was a kid we had to carry the bits from one computer to the other by and! In a tray! And if you dropped that tray you spend the next week putting all he bits back in order! -Original Message- From: George Gallenggal...@wyanokegroup.com To: U2 Users Listu2-users@listserver.u2ug.org Sent: Tue, Feb 7, 2012 10:08 am Subject: Re: [U2] Building an Excel File Why back in our day, the bits traveled at 110 baud, and simplex too. -Original Message- rom: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] n Behalf Of Allen E. Elwood ent: Tuesday, February 07, 2012 12:57 PM o: 'U2 Users List' ubject: Re: [U2] Building an Excel File why when i was writing interfaces we did it with rocks and sticks, AND we ad to walk through the snow to do it. barefooted AND WE LOVED IT! ;-) actually i'm kinda surprised no one mentioned using TAB as a delimiter. ince it's never in text fields, you always get the data across with no roblems. -Original Message- rom: u2-users-boun...@listserver.u2ug.org mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of David A. Green ent: Tuesday, February 07, 2012 7:20 AM o: 'U2 Users List' ubject: Re: [U2] Building an Excel File I have to say amen to Tony's comments. Why try and reinvent the wheel when here is a utility already written? Or throw out some kludgy solution when native one can be found? David A. Green 480) 813-1725 AG Consulting Original Message- rom: u2-users-boun...@listserver.u2ug.org mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Tony Gravagno ent: Monday, February 06, 2012 8:06 PM o: u2-users@listserver.u2ug.org ubject: Re: [U2] Building an Excel File Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks or Excel and you give them a CSV you're just perpetuating the myth that ick is a dinosaur. They will gladly spend tens of thousands of dollars to eplace your application with something that creates real Excel (and PDF) espite the fact that such things can be attained at low cost or no cost ight now. Trust me, I've seen it happen. This dove-tails with the reasons why people get 20 people to support Oracle hen they can have 3 working on Pick. The reason is that the Oracle people ay yes, and give them pretty reports, when their Pick guys say no, and ive them plain text in columns and rows and call it Excel. Please don't let that happen to you. Be sure you are properly responding to nd-user requests. Just ask them what they do with the documents after you enerate them. If they really just want raw data, OK. But if they go on to ell you how many days it takes to reformat the data, assemble the multiple SVs into a single workbook, etc, then you have found a great deal of room or improvement. Yeah, I've been there too. Off the soapbox, thanks. ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users __ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
I just did something very similar last week. 'CSV' is a VM delimited dynarray to be converted: VCNT=DCOUNT(CSV,@VM) FOR VPTR=1 TO VCNT CSVCELL=CSV1,VPTR IF INDEX(CSVCELL,',',1) OR INDEX(CSVCELL,'',1) THEN CSVCELL=CHANGE(CSVCELL,'','') CSVCELL=QUOTE(CSVCELL) CSV1,VPTR=CSVCELL END NEXT VPTR CONVERT @VM TO ',' IN CSVDATA -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: Monday, February 06, 2012 5:26 PM To: U2 Users List Subject: Re: [U2] Building an Excel File I have always tried to avoid csv files because we have all kinds of characters in our descriptions (commas, double and single quotes, etc.) It is also very easy to build a row of data with 254 delimiters, then simply convert @FMs to tabs. That being said, do you have a good subroutine to build csv rows that will not get confused with commas or quotes in the data? I am willing to give this a try. John Israel ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
We were running JET until our latest UniVerse upgrade earlier this year. We used it all over and the users actually resisted when we told them it had to go. Go figure. After a few days testing with our own code they saw the light. - Josh On 2/7/12 12:46 PM, Wjhonson wjhon...@aol.com wrote: Dig it up and market it because Universe is no longer offering JET. -Original Message- From: Brian Leach br...@brianleach.co.uk To: 'U2 Users List' u2-users@listserver.u2ug.org Sent: Tue, Feb 7, 2012 9:43 am Subject: Re: [U2] Building an Excel File IIRC didn't VMARK buy CompuSheet, at around the same time they bought JET? t's probably lying around on and old QIC somewhere.. I'd just written a JET and Ultiword emulator for UniVerse when they popped p with JET. Grr.. but a long time ago. nd back to topic I second the use Tony's tools motion. Why waste time and oney reinventing the wheel? You make think your working time is free, but someone's paying for it!) If you really need to call out from Excel (as opposed to building preadsheets from the db) you can use VBA and UniObjects. Brian -Original Message- rom: u2-users-boun...@listserver.u2ug.org mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah ent: 07 February 2012 16:13 o: U2 Users List ubject: Re: [U2] Building an Excel File Amen! I don't think I would want to go back to 9600 baud serial ommunications, though. My first Reality job was contract programming or a local service bureau, using an ADDS Viewpoint terminal and 300 aud acoustical coupler. They handed me the terminal, modem and a set of anuals and said Have fun!. Dang, now I'm telling my age! EasyCalc ran on Reality as well. Of course, it went the way of many of he other dinosaurs. Now EasyCalc Googles to a scientific calculator on ourceForge. I acquired the source code and rights to redistribute, but hat never went anywhere. If memory serves, Compusheet used a lot of abs frames for speed. EC was ll Data/Basic. It was a bit slow recalculating a series of 600+ preadsheets from the bottom up. The others in IT (then called Data rocessing) didn't think I would be able to get it to work at all, but t did. The sheets drilled all the way down to class enrollment and aperclip counts, and rolled it all up to a top level , which they used o feed their GL. I probably should have taken this [OT], but I didn't know what effect hanging the subject would have. Charlie On 02-07-2012 9:52 AM, Dianne Ackerman wrote: Hm, don't remember EasyCalc, but Compusheet was on a Reality system. It was so much easier when we only had to know what was native in our own systems and not be concerned with a million different things to connect to in the outside world! -Dianne On 2/7/2012 10:45 AM, Charlie Noah wrote: Hi Dianne, You're telling your age here! ;^) Actually, it was EasyCalc, which was probably very similar to Compusheet. Charlie On 02-07-2012 9:30 AM, Dianne Ackerman wrote: Compusheet? I remember working with that! -Dianne On 2/7/2012 6:50 AM, Charlie Noah wrote: Hi Bob, I don't suppose CrowFlite can be obtained these days, can it? It looks like Phil has retired. If it were available, would it work with the newer versions of Excel? What about Open Office? In the 80s I worked for a university in Florida, where we used a Pick-based spreadsheet modeled after Lotus 123. I built a series of spreadsheets which reached down into every aspect of the university's database, giving upper management an up to the minute view of the school's financial picture. I think it ended up being over 600 spreadsheets, but hey, fund accounting is complicated and we did still have the 32K limit. Fond memories. ;^) Regards, Charlie Noah Tiny Bear's Wild Bird Store Everything For The Backyard Bird Enthusiast, Except For The Birds Info, Forum: http://www.TinyBearMarketing.com Store:http://Stores.TinyBearMarketing.com Toll Free: 1-855-TinyBear (855-846-9232) On 02-06-2012 10:57 PM, Bob Rasmussen wrote: Makes one long for CrowFlite, doesn't it? For those that don't remember, CrowFlite was an add-in to Excel that allowed it, at a per-cell level, to reach back into a Pick database and grab individual pieces of data. In other words, it was a pull solution as well as a push. I'm cc'ing its creator, Phil Gerber, so he can reminisce about the past while reveling in the sights among the mountains of Oregon :-) On Mon, 6 Feb 2012, Tony Gravagno wrote: Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks for Excel and you give them a CSV you're just perpetuating the myth that Pick is a dinosaur. They will gladly spend tens of thousands of dollars to replace your application with something that creates real Excel (and PDF) despite the fact that such things can be attained at low cost or no cost right now. Trust me, I've seen it happen
Re: [U2] Building an Excel File
R.I.P. On 07/02/2012 17:52, Brian Leach wrote: what a horrible thought ... some things rightly belong in the trash can of history. Jet and Runoff are definitely in that category. Brian Sent from my ASUS Eee Pad Wjhonsonwjhon...@aol.com wrote: Dig it up and market it because Universe is no longer offering JET. -Original Message- From: Brian Leachbr...@brianleach.co.uk To: 'U2 Users List'u2-users@listserver.u2ug.org Sent: Tue, Feb 7, 2012 9:43 am Subject: Re: [U2] Building an Excel File IIRC didn't VMARK buy CompuSheet, at around the same time they bought JET? t's probably lying around on and old QIC somewhere.. I'd just written a JET and Ultiword emulator for UniVerse when they popped p with JET. Grr.. but a long time ago. nd back to topic I second the use Tony's tools motion. Why waste time and oney reinventing the wheel? You make think your working time is free, but someone's paying for it!) If you really need to call out from Excel (as opposed to building preadsheets from the db) you can use VBA and UniObjects. Brian -Original Message- rom: u2-users-boun...@listserver.u2ug.org mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah ent: 07 February 2012 16:13 o: U2 Users List ubject: Re: [U2] Building an Excel File Amen! I don't think I would want to go back to 9600 baud serial ommunications, though. My first Reality job was contract programming or a local service bureau, using an ADDS Viewpoint terminal and 300 aud acoustical coupler. They handed me the terminal, modem and a set of anuals and said Have fun!. Dang, now I'm telling my age! EasyCalc ran on Reality as well. Of course, it went the way of many of he other dinosaurs. Now EasyCalc Googles to a scientific calculator on ourceForge. I acquired the source code and rights to redistribute, but hat never went anywhere. If memory serves, Compusheet used a lot of abs frames for speed. EC was ll Data/Basic. It was a bit slow recalculating a series of 600+ preadsheets from the bottom up. The others in IT (then called Data rocessing) didn't think I would be able to get it to work at all, but t did. The sheets drilled all the way down to class enrollment and aperclip counts, and rolled it all up to a top level , which they used o feed their GL. I probably should have taken this [OT], but I didn't know what effect hanging the subject would have. Charlie On 02-07-2012 9:52 AM, Dianne Ackerman wrote: Hm, don't remember EasyCalc, but Compusheet was on a Reality system. It was so much easier when we only had to know what was native in our own systems and not be concerned with a million different things to connect to in the outside world! -Dianne On 2/7/2012 10:45 AM, Charlie Noah wrote: Hi Dianne, You're telling your age here! ;^) Actually, it was EasyCalc, which was probably very similar to Compusheet. Charlie On 02-07-2012 9:30 AM, Dianne Ackerman wrote: Compusheet? I remember working with that! -Dianne On 2/7/2012 6:50 AM, Charlie Noah wrote: Hi Bob, I don't suppose CrowFlite can be obtained these days, can it? It looks like Phil has retired. If it were available, would it work with the newer versions of Excel? What about Open Office? In the 80s I worked for a university in Florida, where we used a Pick-based spreadsheet modeled after Lotus 123. I built a series of spreadsheets which reached down into every aspect of the university's database, giving upper management an up to the minute view of the school's financial picture. I think it ended up being over 600 spreadsheets, but hey, fund accounting is complicated and we did still have the 32K limit. Fond memories. ;^) Regards, Charlie Noah Tiny Bear's Wild Bird Store Everything For The Backyard Bird Enthusiast, Except For The Birds Info, Forum: http://www.TinyBearMarketing.com Store:http://Stores.TinyBearMarketing.com Toll Free: 1-855-TinyBear (855-846-9232) On 02-06-2012 10:57 PM, Bob Rasmussen wrote: Makes one long for CrowFlite, doesn't it? For those that don't remember, CrowFlite was an add-in to Excel that allowed it, at a per-cell level, to reach back into a Pick database and grab individual pieces of data. In other words, it was a pull solution as well as a push. I'm cc'ing its creator, Phil Gerber, so he can reminisce about the past while reveling in the sights among the mountains of Oregon :-) On Mon, 6 Feb 2012, Tony Gravagno wrote: Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks for Excel and you give them a CSV you're just perpetuating the myth that Pick is a dinosaur. They will gladly spend tens of thousands of dollars to replace your application with something that creates real Excel (and PDF) despite the fact that such things can be attained at low cost or no cost right now. Trust me, I've seen it happen. This dove-tails with the reasons why people get 20 people to support Oracle when they can have 3
Re: [U2] Building an Excel File
So what are they using in place of JET? Did you ever use JET-EDIT to edit your programs? -Original Message- From: Joshua Gallant jgall...@cbd.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Tue, Feb 7, 2012 12:41 pm Subject: Re: [U2] Building an Excel File We were running JET until our latest UniVerse upgrade earlier this year. e used it all over and the users actually resisted when we told them it ad to go. Go figure. After a few days testing with our own code they aw the light. - Josh On 2/7/12 12:46 PM, Wjhonson wjhon...@aol.com wrote: Dig it up and market it because Universe is no longer offering JET. -Original Message- rom: Brian Leach br...@brianleach.co.uk o: 'U2 Users List' u2-users@listserver.u2ug.org ent: Tue, Feb 7, 2012 9:43 am ubject: Re: [U2] Building an Excel File IRC didn't VMARK buy CompuSheet, at around the same time they bought JET? 's probably lying around on and old QIC somewhere.. 'd just written a JET and Ultiword emulator for UniVerse when they popped with JET. Grr.. but a long time ago. nd back to topic I second the use Tony's tools motion. Why waste time and ney reinventing the wheel? ou make think your working time is free, but someone's paying for it!) f you really need to call out from Excel (as opposed to building readsheets from the db) you can use VBA and UniObjects. rian Original Message- om: u2-users-boun...@listserver.u2ug.org ailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah nt: 07 February 2012 16:13 : U2 Users List bject: Re: [U2] Building an Excel File men! I don't think I would want to go back to 9600 baud serial mmunications, though. My first Reality job was contract programming r a local service bureau, using an ADDS Viewpoint terminal and 300 ud acoustical coupler. They handed me the terminal, modem and a set of nuals and said Have fun!. Dang, now I'm telling my age! asyCalc ran on Reality as well. Of course, it went the way of many of e other dinosaurs. Now EasyCalc Googles to a scientific calculator on urceForge. I acquired the source code and rights to redistribute, but at never went anywhere. f memory serves, Compusheet used a lot of abs frames for speed. EC was l Data/Basic. It was a bit slow recalculating a series of 600+ readsheets from the bottom up. The others in IT (then called Data ocessing) didn't think I would be able to get it to work at all, but did. The sheets drilled all the way down to class enrollment and perclip counts, and rolled it all up to a top level , which they used feed their GL. probably should have taken this [OT], but I didn't know what effect anging the subject would have. harlie n 02-07-2012 9:52 AM, Dianne Ackerman wrote: Hm, don't remember EasyCalc, but Compusheet was on a Reality system. It was so much easier when we only had to know what was native in our own systems and not be concerned with a million different things to connect to in the outside world! -Dianne On 2/7/2012 10:45 AM, Charlie Noah wrote: Hi Dianne, You're telling your age here! ;^) Actually, it was EasyCalc, which was probably very similar to Compusheet. Charlie On 02-07-2012 9:30 AM, Dianne Ackerman wrote: Compusheet? I remember working with that! -Dianne On 2/7/2012 6:50 AM, Charlie Noah wrote: Hi Bob, I don't suppose CrowFlite can be obtained these days, can it? It looks like Phil has retired. If it were available, would it work with the newer versions of Excel? What about Open Office? In the 80s I worked for a university in Florida, where we used a Pick-based spreadsheet modeled after Lotus 123. I built a series of spreadsheets which reached down into every aspect of the university's database, giving upper management an up to the minute view of the school's financial picture. I think it ended up being over 600 spreadsheets, but hey, fund accounting is complicated and we did still have the 32K limit. Fond memories. ;^) Regards, Charlie Noah Tiny Bear's Wild Bird Store Everything For The Backyard Bird Enthusiast, Except For The Birds Info, Forum: http://www.TinyBearMarketing.com Store:http://Stores.TinyBearMarketing.com Toll Free: 1-855-TinyBear (855-846-9232) On 02-06-2012 10:57 PM, Bob Rasmussen wrote: Makes one long for CrowFlite, doesn't it? For those that don't remember, CrowFlite was an add-in to Excel that allowed it, at a per-cell level, to reach back into a Pick database and grab individual pieces of data. In other words, it was a pull solution as well as a push. I'm cc'ing its creator, Phil Gerber, so he can reminisce about the past while reveling in the sights among the mountains of Oregon :-) On Mon, 6 Feb 2012, Tony Gravagno wrote: Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks for Excel and you give them a CSV you're just perpetuating the myth that Pick is a dinosaur. They will gladly spend
Re: [U2] Building an Excel File
From: Bob Rasmussen It would be conceivable, though not necessarily profitable, to have an Excel add-in that would pull data out of Pick via a network-based object call. Conceivable? How about Done. Phil Gerber has referred people to NebulaXChange as the replacement for CrowFlite. http://nebula-rnd.com/blog/tech/2009/03/xchange-intro1.html (Not bothering with nospam anymore...) If nothing else, just look at the screenshots. That blog links back to another 2007 blog which has code showing how to connect from Excel to any MV platform via a web service. The takeaway here is that anyone here can have a solution for Excel-to-U2 today, for free. (NebulaXChange for U2 can connect with UO.NET, mv.NET, telnet, or SSH.) Not necessarily profitable? Righteo! Not in this market. Once again, people will go for years longing for something that already exists, the underlying theme being it must be free. The mentality of free (not related to open source) or at least the insistence that for some reason Pick tools need to be cheap, has seriously done a lot to kill innovation. It has destroyed the motivation for developers like me to create solutions which address common needs. NebulaXChange never went production because of kickback on a couple hundred dollar price tag. I might rethink that. It might seem weird to say this, but XChange is software that just deserves to be used. Frustration with this market obvious? :) T ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Can you license this logic to Rocket? Seems like getting the 'check box' for the database would serve them well, and would be a drop in the bucket for them to throw dollars at you that would be FAR more than you would likely get licensing it one-off yourself. Just a thought. Rocket should be 'buying' this kind of stuff and putting it under the hood. Horizontal stuff it just seems that no one ever 'seeks out' - we all end up building it. Which is craziness and hurts Rocket more than anyone! Just my thought on a late afternoon. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Tony Gravagno Sent: Tuesday, February 07, 2012 3:18 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Building an Excel File From: Bob Rasmussen It would be conceivable, though not necessarily profitable, to have an Excel add-in that would pull data out of Pick via a network-based object call. Conceivable? How about Done. Phil Gerber has referred people to NebulaXChange as the replacement for CrowFlite. http://nebula-rnd.com/blog/tech/2009/03/xchange-intro1.html (Not bothering with nospam anymore...) If nothing else, just look at the screenshots. That blog links back to another 2007 blog which has code showing how to connect from Excel to any MV platform via a web service. The takeaway here is that anyone here can have a solution for Excel-to-U2 today, for free. (NebulaXChange for U2 can connect with UO.NET, mv.NET, telnet, or SSH.) Not necessarily profitable? Righteo! Not in this market. Once again, people will go for years longing for something that already exists, the underlying theme being it must be free. The mentality of free (not related to open source) or at least the insistence that for some reason Pick tools need to be cheap, has seriously done a lot to kill innovation. It has destroyed the motivation for developers like me to create solutions which address common needs. NebulaXChange never went production because of kickback on a couple hundred dollar price tag. I might rethink that. It might seem weird to say this, but XChange is software that just deserves to be used. Frustration with this market obvious? :) T ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Sorry, Tony, I should have been aware of your offerings. Maybe you need to charge A LOT for your product, and then people would be interested :-) On Tue, 7 Feb 2012, Tony Gravagno wrote: From: Bob Rasmussen It would be conceivable, though not necessarily profitable, to have an Excel add-in that would pull data out of Pick via a network-based object call. Conceivable? How about Done. Phil Gerber has referred people to NebulaXChange as the replacement for CrowFlite. http://nebula-rnd.com/blog/tech/2009/03/xchange-intro1.html (Not bothering with nospam anymore...) If nothing else, just look at the screenshots. That blog links back to another 2007 blog which has code showing how to connect from Excel to any MV platform via a web service. The takeaway here is that anyone here can have a solution for Excel-to-U2 today, for free. (NebulaXChange for U2 can connect with UO.NET, mv.NET, telnet, or SSH.) Not necessarily profitable? Righteo! Not in this market. Once again, people will go for years longing for something that already exists, the underlying theme being it must be free. The mentality of free (not related to open source) or at least the insistence that for some reason Pick tools need to be cheap, has seriously done a lot to kill innovation. It has destroyed the motivation for developers like me to create solutions which address common needs. NebulaXChange never went production because of kickback on a couple hundred dollar price tag. I might rethink that. It might seem weird to say this, but XChange is software that just deserves to be used. Frustration with this market obvious? :) T ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users Regards, Bob Rasmussen, President, Rasmussen Software, Inc. personal e-mail: r...@anzio.com company e-mail: r...@anzio.com voice: (US) 503-624-0360 (9:00-6:00 Pacific Time) fax: (US) 503-624-0760 web: http://www.anzio.com street address: Rasmussen Software, Inc. 10240 SW Nimbus, Suite L9 Portland, OR 97223 USA ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Amen Tony. I don't know how many times I get a call from a user saying for some reason my XLS/XLSX parser doesn't work with the Excel file they have. What the user really has is a CSV/TSV file with a XLS file extension that some programmer decided to name the file. This is because EXCEL doesn't bring up the CSV/TSV layout when the file has a .xls extension. However, my engine expects a true binary xls or x-zip xlsx envelope when the file has the xls/xlsx extension. Now the engine reads the first 20-50 bytes to determine if it's really an Excel file before attempting to parse it. It's NOT only U2/Pick programmers who do this little trick on the users. I have well known insurance company that does this, and it annoys the hell out of me. I've created excel files with PERL and the spreadsheet::write module when I worked on a HPUX/UD server and windows server for the past 12 years. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Tony Gravagno Sent: Monday, February 06, 2012 10:06 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Building an Excel File Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks for Excel and you give them a CSV you're just perpetuating the myth that Pick is a dinosaur. They will gladly spend tens of thousands of dollars to replace your application with something that creates real Excel (and PDF) despite the fact that such things can be attained at low cost or no cost right now. Trust me, I've seen it happen. This dove-tails with the reasons why people get 20 people to support Oracle when they can have 3 working on Pick. The reason is that the Oracle people say yes, and give them pretty reports, when their Pick guys say no, and give them plain text in columns and rows and call it Excel. Please don't let that happen to you. Be sure you are properly responding to end-user requests. Just ask them what they do with the documents after you generate them. If they really just want raw data, OK. But if they go on to tell you how many days it takes to reformat the data, assemble the multiple CSVs into a single workbook, etc, then you have found a great deal of room for improvement. Yeah, I've been there too. Off the soapbox, thanks. T ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
I think somewhere this jumped the track. No one is saying that CSV is Excel Rather the OP wanted a way to read an Excel file on the Iphone. Can the Iphone natively read Excel files? I know that it can natively read csv files, because we're doing it. It puts the csv file into a neat table with a border, with columns and rows all autosized and pretty with no effort whatsoever. So the solution could be simply to change the Excel files into csv files and voila you have no problem anymore for the Iphone. This doesn't address the issue of whether existing Excel files, or Excel files you receive from others can be handled. It only addresses the issue of reading Tables (of some kind) on the Iphone. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Wil, while the OP asked about reading Excel on an iPhone, the title of the thread is Building an Excel File. That prompted suggestions for how to create Excel, including use CSV. Yes, all spreadsheet clients including Excel will open a CSV file. But that led to my assertion that CSV is not Excel and that CSV is not acceptable when people want Excel, though that is a common belief amongst MV developers because most end-users will just take what you give them ... and then they'll quietly start looking for people who will give them what they really want, Excel. This is a digression that you are welcome not to follow. About your statements - I'm intimately familiar with internals of Excel (Office in general), XML, CSV, and related document standards, and I have no idea what kind of middleware you're talking about below. CSV has no information about formatting, it's pure data. It's therefore impossible for any utility to do anything but autosize the columns, and that's trivial. As I said above, if all you're doing is opening the file, sure, that should work, but the result is hardly Excel outside of being text in columns and rows. You can't just simply 'change the Excel files into csv files'. Your statement doesn't survive the first attempt. Excel will warn you that it's going to lose detail if you do that. Again, CSV is not Excel. It doesn't provide borders, colors, images, cell references, merged cells, fonts, type styles, multiple sheets, named sheets, named ranges, file properties, page formatting, print pagination, fixed col/row settings, custom col/row sizes, or data typing. And while you can provide formulas in CSV, you'll lose them if you save Excel as CSV. [AD] And _that_ is the reason I created NebulaXLite, to allow programmers to do all of that from BASIC, on any OS, any DBMS, and with no underlying libraries. It's real workbooks for Excel, Open Office, and Google docs - when everyone else is ready to provide a delimited text file. T From: Wjhonson I think somewhere this jumped the track. No one is saying that CSV is Excel Rather the OP wanted a way to read an Excel file on the Iphone. Can the Iphone natively read Excel files? I know that it can natively read csv files, because we're doing it. It puts the csv file into a neat table with a border, with columns and rows all autosized and pretty with no effort whatsoever. So the solution could be simply to change the Excel files into csv files and voila you have no problem anymore for the Iphone. This doesn't address the issue of whether existing Excel files, or Excel files you receive from others can be handled. It only addresses the issue of reading Tables (of some kind) on the Iphone. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
sounds awesome tony, really. rocket shouldn't launch without it :-) On 2/7/2012 7:14 PM, Tony Gravagno wrote: Wil, while the OP asked about reading Excel on an iPhone, the title of the thread is Building an Excel File. That prompted suggestions for how to create Excel, including use CSV. Yes, all spreadsheet clients including Excel will open a CSV file. But that led to my assertion that CSV is not Excel and that CSV is not acceptable when people want Excel, though that is a common belief amongst MV developers because most end-users will just take what you give them ... and then they'll quietly start looking for people who will give them what they really want, Excel. This is a digression that you are welcome not to follow. About your statements - I'm intimately familiar with internals of Excel (Office in general), XML, CSV, and related document standards, and I have no idea what kind of middleware you're talking about below. CSV has no information about formatting, it's pure data. It's therefore impossible for any utility to do anything but autosize the columns, and that's trivial. As I said above, if all you're doing is opening the file, sure, that should work, but the result is hardly Excel outside of being text in columns and rows. You can't just simply 'change the Excel files into csv files'. Your statement doesn't survive the first attempt. Excel will warn you that it's going to lose detail if you do that. Again, CSV is not Excel. It doesn't provide borders, colors, images, cell references, merged cells, fonts, type styles, multiple sheets, named sheets, named ranges, file properties, page formatting, print pagination, fixed col/row settings, custom col/row sizes, or data typing. And while you can provide formulas in CSV, you'll lose them if you save Excel as CSV. [AD] And _that_ is the reason I created NebulaXLite, to allow programmers to do all of that from BASIC, on any OS, any DBMS, and with no underlying libraries. It's real workbooks for Excel, Open Office, and Google docs - when everyone else is ready to provide a delimited text file. T From: Wjhonson I think somewhere this jumped the track. No one is saying that CSV is Excel Rather the OP wanted a way to read an Excel file on the Iphone. Can the Iphone natively read Excel files? I know that it can natively read csv files, because we're doing it. It puts the csv file into a neat table with a border, with columns and rows all autosized and pretty with no effort whatsoever. So the solution could be simply to change the Excel files into csv files and voila you have no problem anymore for the Iphone. This doesn't address the issue of whether existing Excel files, or Excel files you receive from others can be handled. It only addresses the issue of reading Tables (of some kind) on the Iphone. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
My comment that csv files can be read on the Iphone without any effort and they look nice only addresses the issue of reading tables on the iphone. Period. That's it. That's essentially what the OP wanted, unless of course some cells are actually calculated values. The information that we are pushing out to various persons for reading on their phones doesn't include formulas. It mostly consists of data being loaded, errors in the load, and valid loads and their effects. I don't know what the iphone is doing to allow these csvs to be seen as tables, but it was clever of them to include code to do that, because we didn't. It was an accidental discovery that it works, it surely wasn't intentional on my part. That Tony is the ONLY thing I was trying to address. Nothing else that you brought up. And again I never said csv was Excel. I said csv comes up, on the iphone, as a table. However it does it. -Original Message- From: Tony Gravagno 3xk547...@sneakemail.com To: u2-users u2-users@listserver.u2ug.org Sent: Tue, Feb 7, 2012 7:15 pm Subject: Re: [U2] Building an Excel File Wil, while the OP asked about reading Excel on an iPhone, the title of the thread is Building an Excel File. That prompted suggestions for how to create Excel, including use CSV. Yes, all spreadsheet clients including Excel will open a CSV file. But that led to my assertion that CSV is not Excel and that CSV is not acceptable when people want Excel, though that is a common belief amongst MV developers because most end-users will just take what you give them ... and then they'll quietly start looking for people who will give them what they really want, Excel. This is a digression that you are welcome not to follow. About your statements - I'm intimately familiar with internals of Excel (Office in general), XML, CSV, and related document standards, and I have no idea what kind of middleware you're talking about below. CSV has no information about formatting, it's pure data. It's therefore impossible for any utility to do anything but autosize the columns, and that's trivial. As I said above, if all you're doing is opening the file, sure, that should work, but the result is hardly Excel outside of being text in columns and rows. You can't just simply 'change the Excel files into csv files'. Your statement doesn't survive the first attempt. Excel will warn you that it's going to lose detail if you do that. Again, CSV is not Excel. It doesn't provide borders, colors, images, cell references, merged cells, fonts, type styles, multiple sheets, named sheets, named ranges, file properties, page formatting, print pagination, fixed col/row settings, custom col/row sizes, or data typing. And while you can provide formulas in CSV, you'll lose them if you save Excel as CSV. [AD] And _that_ is the reason I created NebulaXLite, to allow programmers to do all of that from BASIC, on any OS, any DBMS, and with no underlying libraries. It's real workbooks for Excel, Open Office, and Google docs - when everyone else is ready to provide a delimited text file. T From: Wjhonson I think somewhere this jumped the track. No one is saying that CSV is Excel Rather the OP wanted a way to read an Excel file on the Iphone. Can the Iphone natively read Excel files? I know that it can natively read csv files, because we're doing it. It puts the csv file into a neat table with a border, with columns and rows all autosized and pretty with no effort whatsoever. So the solution could be simply to change the Excel files into csv files and voila you have no problem anymore for the Iphone. This doesn't address the issue of whether existing Excel files, or Excel files you receive from others can be handled. It only addresses the issue of reading Tables (of some kind) on the Iphone. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Wil, you didn't say 'I', so you're not saying it 'again'. I responded to what you said. You're doing that troll thing again. So again, I say - Just stop it man. From: Wjhonson And again I never said csv was Excel. From: Wjhonson No one is saying that CSV is Excel ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Can they open an XML file coded for Excel, but without formatting and colors and such? If so, you may just have to have two files produced, one for the phones and one for desktops. I haven't checked, but are there any perl modules that can convert an XML file to an .xls file This would require unix, but not any user intervention. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: Monday, February 06, 2012 4:40 PM To: 'U2 Users List' Subject: [U2] Building an Excel File We are running UniData on a UNIX platform. We create tab-delimited txt files (and tab delimited txt files with xls extensions) all the time that users open with Excel. Many of these jobs are run in the middle of the night or in a trigger program. In all cases, there is no direct user interaction building these files. We also have some XML files that are coded so that Excel opens them with formatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a meaningful way. Is there a way to build a TRUE Excel file without any user intervention from UniData or UNIX? John John Israel Senior ERP Developer Dayton Superior Corporation 1125 Byers Rd Miamisburg, OH 45342 Office: 937-866-0711 x44380 Fax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the intended recipient(s) and may contain information that is privileged, confidential or proprietary. If you are not an intended recipient, please notify the sender, and then please delete and destroy all copies and attachments, and be advised that any review or dissemination of, or the taking of any action in reliance on, the information contained in or attached to this message is prohibited. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
try this subroutine. Note it is written for an old Revelation G2b application so change as needed Rich SUBROUTINE EXCEL_SCRIPTS_SUBR(RESULT,PROCESS,FROM.FILE,TO.FILE,EXTRA.INFORMATION) * * * Purpose: Create Excel Vbs Scripts * * Date:01-24-12 * * Author: Richard A. Wilson * Lakeside Systems * 4 Myers Street * Smithfield, RI 02917-1821 * Email: rwil...@lakeside-systems.com * * * * M o d i f i c a t i o n s * * Date By Description * 02-06-12 RAW Saving as XLS doesnt require file format * Infact it will cause save as vbs error * * * Note from and to files require complete path L:\RES\etc * RESULT = '' BEGIN CASE CASE PROCESS EQ 'XLSTOTAB' FILE.FORMAT = -4158 EXTENSION = 'TXT' CASE PROCESS EQ 'XLSTOCSV' FILE.FORMAT = 6 EXTENSION = 'CSV' CASE PROCESS EQ 'CSVTOXLS' FILE.FORMAT = 56 EXTENSION = 'XLS' END CASE * COMMA.CNT = COUNT(FROM.FILE,'.') PART1 = FIELD(FROM.FILE,'.',1,COMMA.CNT) * IF TO.FILE EQ THEN TO.FILE = PART1:'.':EXTENSION END * * Quote the file names just incase spaces might exist and cause issues * QUOTED.FROM.FILE = '':FROM.FILE:'' QUOTED.TO.FILE = '':TO.FILE:'' * SCRIPT = '' SCRIPT-1 = ' SCRIPT-1 = ' 01-23-12 Found this script at technet.microsoft.com SCRIPT-1 = ' SCRIPT-1 = if WScript.Arguments.Count 2 Then SCRIPT-1 = 'WScript.Echo Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv' SCRIPT-1 = Wscript.Quit SCRIPT-1 = End If SCRIPT-1 = Dim oExcel SCRIPT-1 = 'Set oExcel = CreateObject(Excel.Application)' SCRIPT-1 = Dim oBook SCRIPT-1 = Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0)) SCRIPT-1 = ' SCRIPT-1 = ' following disables overwrite popup SCRIPT-1 = ' SCRIPT-1 = oExcel.DisplayAlerts = False * * 02-06-12 following IF logic * IF EXTENSION EQ 'XLS' THEN SCRIPT-1 = oBook.SaveAs WScript.Arguments.Item(1) END ELSE SCRIPT-1 = oBook.SaveAs WScript.Arguments.Item(1), :FILE.FORMAT END * SCRIPT-1 = oBook.Close False SCRIPT-1 = oExcel.Quit SCRIPT-1 = ' SCRIPT-1 = ':' 01-23-12 comment out Done' SCRIPT-1 = ' SCRIPT-1 = ':' WScript.Echo Done' * CONVERT @FM TO CHAR(10) IN SCRIPT OSWRITE SCRIPT ON 'C:\BATFILES.WRK\':PROCESS:'.VBS' * * Execute script * CMD = 'C:\BATFILES.WRK\':PROCESS:'.VBS ' CMD := QUOTED.FROM.FILE:' ' CMD := QUOTED.TO.FILE BAT.NAME = 'REN':TIME() CONVERT '.' TO '' IN BAT.NAME BAT.NAME = '\BATFILES.WRK\':BAT.NAME[1,8]:'.BAT' OSWRITE CMD ON BAT.NAME OSCLOSE BAT.NAME PCPERFORM 'CMD.EXE /C ':BAT.NAME PCPERFORM 'ERASE ':BAT.NAME IF EXTRA.INFORMATION EQ 'DELETE_FROM.FILE' THEN PCPERFORM 'CMD.EXE /C ERASE ':FROM.FILE END * RETURN END Israel, John R. wrote: We are running UniData on a UNIX platform. We create tab-delimited txt files (and tab delimited txt files with xls extensions) all the time that users open with Excel. Many of these jobs are run in the middle of the night or in a trigger program. In all cases, there is no direct user interaction building these files. We also have some XML files that are coded so that Excel opens them with formatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a meaningful way. Is there a way to build a TRUE Excel file without any user intervention from UniData or UNIX? John John Israel Senior ERP Developer Dayton Superior Corporation 1125 Byers Rd Miamisburg, OH 45342 Office: 937-866-0711 x44380 Fax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the intended recipient(s) and may contain information that is privileged, confidential or proprietary. If you are not an intended recipient, please notify the sender, and then please delete and destroy all copies and attachments, and be advised that any review or dissemination of, or the taking of any action in reliance on, the information contained in or attached to this message is prohibited. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- Richard A Wilson Lakeside Systems Smithfield, RI, USA Voice 401-231-3959 Fax 206-202-2064 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
No, the Smart Phones can not open the XML at all. The txt and xls files can be opened, but they are just text. Managers want to be able to read the files cleanly. I am guessing the issue is that when a smart phone opens a true Excel file, it is not running Excel, but some sort of emulator. The emulator does not have all the bells and whistles that a full blown version of Excel has, thus undesirable results. John -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Monday, February 06, 2012 4:45 PM To: U2 Users List Subject: Re: [U2] Building an Excel File Can they open an XML file coded for Excel, but without formatting and colors and such? If so, you may just have to have two files produced, one for the phones and one for desktops. I haven't checked, but are there any perl modules that can convert an XML file to an .xls file This would require unix, but not any user intervention. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: Monday, February 06, 2012 4:40 PM To: 'U2 Users List' Subject: [U2] Building an Excel File We are running UniData on a UNIX platform. We create tab-delimited txt files (and tab delimited txt files with xls extensions) all the time that users open with Excel. Many of these jobs are run in the middle of the night or in a trigger program. In all cases, there is no direct user interaction building these files. We also have some XML files that are coded so that Excel opens them with formatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a meaningful way. Is there a way to build a TRUE Excel file without any user intervention from UniData or UNIX? John John Israel Senior ERP Developer Dayton Superior Corporation 1125 Byers Rd Miamisburg, OH 45342 Office: 937-866-0711 x44380 Fax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the intended recipient(s) and may contain information that is privileged, confidential or proprietary. If you are not an intended recipient, please notify the sender, and then please delete and destroy all copies and attachments, and be advised that any review or dissemination of, or the taking of any action in reliance on, the information contained in or attached to this message is prohibited. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
I will delve into this code tomorrow. Thanks. John -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Richard A. Wilson Sent: Monday, February 06, 2012 4:50 PM To: U2 Users List Subject: Re: [U2] Building an Excel File try this subroutine. Note it is written for an old Revelation G2b application so change as needed Rich SUBROUTINE EXCEL_SCRIPTS_SUBR(RESULT,PROCESS,FROM.FILE,TO.FILE,EXTRA.INFORMATION) * * * Purpose: Create Excel Vbs Scripts * * Date:01-24-12 * * Author: Richard A. Wilson * Lakeside Systems * 4 Myers Street * Smithfield, RI 02917-1821 * Email: rwil...@lakeside-systems.com * * * * M o d i f i c a t i o n s * * Date By Description * 02-06-12 RAW Saving as XLS doesnt require file format * Infact it will cause save as vbs error * * * Note from and to files require complete path L:\RES\etc * RESULT = '' BEGIN CASE CASE PROCESS EQ 'XLSTOTAB' FILE.FORMAT = -4158 EXTENSION = 'TXT' CASE PROCESS EQ 'XLSTOCSV' FILE.FORMAT = 6 EXTENSION = 'CSV' CASE PROCESS EQ 'CSVTOXLS' FILE.FORMAT = 56 EXTENSION = 'XLS' END CASE * COMMA.CNT = COUNT(FROM.FILE,'.') PART1 = FIELD(FROM.FILE,'.',1,COMMA.CNT) * IF TO.FILE EQ THEN TO.FILE = PART1:'.':EXTENSION END * * Quote the file names just incase spaces might exist and cause issues * QUOTED.FROM.FILE = '':FROM.FILE:'' QUOTED.TO.FILE = '':TO.FILE:'' * SCRIPT = '' SCRIPT-1 = ' SCRIPT-1 = ' 01-23-12 Found this script at technet.microsoft.com SCRIPT-1 = ' SCRIPT-1 = if WScript.Arguments.Count 2 Then SCRIPT-1 = 'WScript.Echo Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv' SCRIPT-1 = Wscript.Quit SCRIPT-1 = End If SCRIPT-1 = Dim oExcel SCRIPT-1 = 'Set oExcel = CreateObject(Excel.Application)' SCRIPT-1 = Dim oBook SCRIPT-1 = Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0)) SCRIPT-1 = ' SCRIPT-1 = ' following disables overwrite popup SCRIPT-1 = ' SCRIPT-1 = oExcel.DisplayAlerts = False * * 02-06-12 following IF logic * IF EXTENSION EQ 'XLS' THEN SCRIPT-1 = oBook.SaveAs WScript.Arguments.Item(1) END ELSE SCRIPT-1 = oBook.SaveAs WScript.Arguments.Item(1), :FILE.FORMAT END * SCRIPT-1 = oBook.Close False SCRIPT-1 = oExcel.Quit SCRIPT-1 = ' SCRIPT-1 = ':' 01-23-12 comment out Done' SCRIPT-1 = ' SCRIPT-1 = ':' WScript.Echo Done' * CONVERT @FM TO CHAR(10) IN SCRIPT OSWRITE SCRIPT ON 'C:\BATFILES.WRK\':PROCESS:'.VBS' * * Execute script * CMD = 'C:\BATFILES.WRK\':PROCESS:'.VBS ' CMD := QUOTED.FROM.FILE:' ' CMD := QUOTED.TO.FILE BAT.NAME = 'REN':TIME() CONVERT '.' TO '' IN BAT.NAME BAT.NAME = '\BATFILES.WRK\':BAT.NAME[1,8]:'.BAT' OSWRITE CMD ON BAT.NAME OSCLOSE BAT.NAME PCPERFORM 'CMD.EXE /C ':BAT.NAME PCPERFORM 'ERASE ':BAT.NAME IF EXTRA.INFORMATION EQ 'DELETE_FROM.FILE' THEN PCPERFORM 'CMD.EXE /C ERASE ':FROM.FILE END * RETURN END Israel, John R. wrote: We are running UniData on a UNIX platform. We create tab-delimited txt files (and tab delimited txt files with xls extensions) all the time that users open with Excel. Many of these jobs are run in the middle of the night or in a trigger program. In all cases, there is no direct user interaction building these files. We also have some XML files that are coded so that Excel opens them with formatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a meaningful way. Is there a way to build a TRUE Excel file without any user intervention from UniData or UNIX? John John Israel Senior ERP Developer Dayton Superior Corporation 1125 Byers Rd Miamisburg, OH 45342 Office: 937-866-0711 x44380 Fax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the intended recipient(s) and may contain information that is privileged, confidential or proprietary. If you are not an intended recipient, please notify the sender, and then please delete and destroy all copies and attachments, and be advised that any review or dissemination of, or the taking of any action in reliance on, the information contained in or attached to this message is prohibited. ___ U2-Users mailing
Re: [U2] Building an Excel File
On 06/02/12 21:44, George Gallen wrote: I haven't checked, but are there any perl modules that can convert an XML file to an .xls file This would require unix, but not any user intervention. You can get Perl for Windows. Done by ActiveState, I think MS has taken them over so it's even a supported Microsoft product ... And yes, there are Perl modules to produce excel files, but I'm not sure whether they take XML as input. Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Rebuild the files as csv files and they will open on the Iphone without any further intervention This is what we do. -Original Message- From: Israel, John R. johnisr...@daytonsuperior.com To: 'U2 Users List' u2-users@listserver.u2ug.org Sent: Mon, Feb 6, 2012 2:25 pm Subject: Re: [U2] Building an Excel File No, the Smart Phones can not open the XML at all. The txt and xls files can be pened, but they are just text. Managers want to be able to read the files leanly. I am guessing the issue is that when a smart phone opens a true Excel file, it s not running Excel, but some sort of emulator. The emulator does not have all he bells and whistles that a full blown version of Excel has, thus undesirable esults. John -Original Message- rom: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] n Behalf Of George Gallen ent: Monday, February 06, 2012 4:45 PM o: U2 Users List ubject: Re: [U2] Building an Excel File Can they open an XML file coded for Excel, but without formatting and colors and uch? f so, you may just have to have two files produced, one for the phones and one or desktops. I haven't checked, but are there any perl modules that can convert an XML file o an .xls file This would require unix, but not any user intervention. -Original Message- rom: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] n Behalf Of Israel, John R. ent: Monday, February 06, 2012 4:40 PM o: 'U2 Users List' ubject: [U2] Building an Excel File We are running UniData on a UNIX platform. We create tab-delimited txt files and tab delimited txt files with xls extensions) all the time that users open ith Excel. Many of these jobs are run in the middle of the night or in a rigger program. In all cases, there is no direct user interaction building hese files. We also have some XML files that are coded so that Excel opens them with ormatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a eaningful way. Is there a way to build a TRUE Excel file without any user intervention from niData or UNIX? John John Israel enior ERP Developer Dayton Superior Corporation 125 Byers Rd Miamisburg, OH 45342 ffice: 937-866-0711 x44380 ax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the ntended recipient(s) and may contain information that is privileged, onfidential or proprietary. If you are not an intended recipient, please notify he sender, and then please delete and destroy all copies and attachments, and e advised that any review or dissemination of, or the taking of any action in eliance on, the information contained in or attached to this message is rohibited. __ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users __ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
I forgot to mention that they will look beautiful, columns and rows all neatly lined up -Original Message- From: Wjhonson wjhon...@aol.com To: u2-users u2-users@listserver.u2ug.org Sent: Mon, Feb 6, 2012 2:28 pm Subject: Re: [U2] Building an Excel File ebuild the files as csv files and they will open on the Iphone without any urther intervention his is what we do. Original Message- rom: Israel, John R. johnisr...@daytonsuperior.com o: 'U2 Users List' u2-users@listserver.u2ug.org ent: Mon, Feb 6, 2012 2:25 pm ubject: Re: [U2] Building an Excel File o, the Smart Phones can not open the XML at all. The txt and xls files can be ened, but they are just text. Managers want to be able to read the files eanly. am guessing the issue is that when a smart phone opens a true Excel file, it not running Excel, but some sort of emulator. The emulator does not have all e bells and whistles that a full blown version of Excel has, thus undesirable sults. ohn -Original Message- om: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] n Behalf Of George Gallen nt: Monday, February 06, 2012 4:45 PM : U2 Users List bject: Re: [U2] Building an Excel File an they open an XML file coded for Excel, but without formatting and colors and uch? so, you may just have to have two files produced, one for the phones and one r desktops. haven't checked, but are there any perl modules that can convert an XML file an .xls file This would require unix, but not any user intervention. Original Message- om: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] n Behalf Of Israel, John R. nt: Monday, February 06, 2012 4:40 PM : 'U2 Users List' bject: [U2] Building an Excel File e are running UniData on a UNIX platform. We create tab-delimited txt files nd tab delimited txt files with xls extensions) all the time that users open th Excel. Many of these jobs are run in the middle of the night or in a igger program. In all cases, there is no direct user interaction building ese files. e also have some XML files that are coded so that Excel opens them with rmatting, colors, filters, etc. nfortunately, our BB users and iPhone users can not open these files up in a aningful way. s there a way to build a TRUE Excel file without any user intervention from iData or UNIX? ohn ohn Israel nior ERP Developer ayton Superior Corporation 25 Byers Rd Miamisburg, OH 45342 fice: 937-866-0711 x44380 x: 937-865-9182 ohnisr...@daytonsuperior.com his message w/attachments (message) is intended solely for the use of the tended recipient(s) and may contain information that is privileged, nfidential or proprietary. If you are not an intended recipient, please notify e sender, and then please delete and destroy all copies and attachments, and advised that any review or dissemination of, or the taking of any action in liance on, the information contained in or attached to this message is ohibited. _ -Users mailing list -us...@listserver.u2ug.org tp://listserver.u2ug.org/mailman/listinfo/u2-users _ -Users mailing list -us...@listserver.u2ug.org tp://listserver.u2ug.org/mailman/listinfo/u2-users __ -Users mailing list -us...@listserver.u2ug.org tp://listserver.u2ug.org/mailman/listinfo/u2-users ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File [AD]
[AD] NebulaXLite creates rich XML workbooks (multiple sheets) which can be opened by Google Docs and Open Office, in addition of course to Excel. The XML isn't Excel-specific, it conforms to open standards, and Excel happens to open those files as well. remove.pleaseNebula-RnD.com/products/xlite.htm When I got my Android, I didn't look for Excel for Android, I just viewed docs as Google spreadsheets. Your BlackBerry and iPhone users would probably do well to do the same. NebulaXLite is free for all developers and only a one-time $200 purchase price for end-users with completely free support. Many people here use NebulaXLite over Universe and Unidata, and I hope a few of our clients will comment on their experience. Some months ago I also announced NebulaXPlus which exports XLite documents to a local Windows box to convert to a real Excel ..xls file. This allows even more complex documents to be created - yes, without user intervention. NebulaXPlus is now in beta. Thanks for you time. Tony Gravagno Nebula Research and Development TG@ remove.pleaseNebula-RnD.com remove.pleaseNebula-RnD.com/blog Visit http://PickWiki.com! Contribute! http://Twitter.com/TonyGravagno NEW! http://groups.google.com/group/mvdbms/about From: Israel, John R. We are running UniData on a UNIX platform. We create tab-delimited txt files (and tab delimited txt files with xls extensions) all the time that users open with Excel. Many of these jobs are run in the middle of the night or in a trigger program. In all cases, there is no direct user interaction building these files. We also have some XML files that are coded so that Excel opens them with formatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a meaningful way. Is there a way to build a TRUE Excel file without any user intervention from UniData or UNIX? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
I have always tried to avoid csv files because we have all kinds of characters in our descriptions (commas, double and single quotes, etc.) It is also very easy to build a row of data with 254 delimiters, then simply convert @FMs to tabs. That being said, do you have a good subroutine to build csv rows that will not get confused with commas or quotes in the data? I am willing to give this a try. John Israel Sent from my iPhone On Feb 6, 2012, at 5:28 PM, Wjhonson wjhon...@aol.com wrote: Rebuild the files as csv files and they will open on the Iphone without any further intervention This is what we do. John Israel Senior ERP Developer Dayton Superior Corporation 1125 Byers Rd Miamisburg, OH 45342 Office: 937-866-0711 x44380 Fax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the intended recipient(s) and may contain information that is privileged, confidential or proprietary. If you are not an intended recipient, please notify the sender, and then please delete and destroy all copies and attachments, and be advised that any review or dissemination of, or the taking of any action in reliance on, the information contained in or attached to this message is prohibited. -Original Message- From: Israel, John R. johnisr...@daytonsuperior.com To: 'U2 Users List' u2-users@listserver.u2ug.org Sent: Mon, Feb 6, 2012 2:25 pm Subject: Re: [U2] Building an Excel File No, the Smart Phones can not open the XML at all. The txt and xls files can be pened, but they are just text. Managers want to be able to read the files leanly. I am guessing the issue is that when a smart phone opens a true Excel file, it s not running Excel, but some sort of emulator. The emulator does not have all he bells and whistles that a full blown version of Excel has, thus undesirable esults. John -Original Message- rom: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] n Behalf Of George Gallen ent: Monday, February 06, 2012 4:45 PM o: U2 Users List ubject: Re: [U2] Building an Excel File Can they open an XML file coded for Excel, but without formatting and colors and uch? f so, you may just have to have two files produced, one for the phones and one or desktops. I haven't checked, but are there any perl modules that can convert an XML file o an .xls file This would require unix, but not any user intervention. -Original Message- rom: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] n Behalf Of Israel, John R. ent: Monday, February 06, 2012 4:40 PM o: 'U2 Users List' ubject: [U2] Building an Excel File We are running UniData on a UNIX platform. We create tab-delimited txt files and tab delimited txt files with xls extensions) all the time that users open ith Excel. Many of these jobs are run in the middle of the night or in a rigger program. In all cases, there is no direct user interaction building hese files. We also have some XML files that are coded so that Excel opens them with ormatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a eaningful way. Is there a way to build a TRUE Excel file without any user intervention from niData or UNIX? John John Israel enior ERP Developer Dayton Superior Corporation 125 Byers Rd Miamisburg, OH 45342 ffice: 937-866-0711 x44380 ax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the ntended recipient(s) and may contain information that is privileged, onfidential or proprietary. If you are not an intended recipient, please notify he sender, and then please delete and destroy all copies and attachments, and e advised that any review or dissemination of, or the taking of any action in eliance on, the information contained in or attached to this message is rohibited. __ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users __ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
John: Perhaps the easiest way to do this is with Adobe's ColdFusion. To get started... the developer version of ColdFusion is free. --Bill -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: Monday, February 06, 2012 4:40 PM To: 'U2 Users List' Subject: [U2] Building an Excel File We are running UniData on a UNIX platform. We create tab-delimited txt files (and tab delimited txt files with xls extensions) all the time that users open with Excel. Many of these jobs are run in the middle of the night or in a trigger program. In all cases, there is no direct user interaction building these files. We also have some XML files that are coded so that Excel opens them with formatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a meaningful way. Is there a way to build a TRUE Excel file without any user intervention from UniData or UNIX? John John Israel Senior ERP Developer Dayton Superior Corporation 1125 Byers Rd Miamisburg, OH 45342 Office: 937-866-0711 x44380 Fax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the intended recipient(s) and may contain information that is privileged, confidential or proprietary. If you are not an intended recipient, please notify the sender, and then please delete and destroy all copies and attachments, and be advised that any review or dissemination of, or the taking of any action in reliance on, the information contained in or attached to this message is prohibited. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
It's been my experience in the past that you can avoid issues with the comma delimeter as long as you enclose your data in double-quotes. Supposedly this even applies to data that already contains double-quotes, although I've never attempted it personally. Wikipedia has that specific example documented, though: http://en.wikipedia.org/wiki/Comma-separated_values -John -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: Monday, February 06, 2012 3:26 PM To: U2 Users List Subject: Re: [U2] Building an Excel File I have always tried to avoid csv files because we have all kinds of characters in our descriptions (commas, double and single quotes, etc.) It is also very easy to build a row of data with 254 delimiters, then simply convert @FMs to tabs. That being said, do you have a good subroutine to build csv rows that will not get confused with commas or quotes in the data? I am willing to give this a try. John Israel Sent from my iPhone On Feb 6, 2012, at 5:28 PM, Wjhonson wjhon...@aol.com wrote: Rebuild the files as csv files and they will open on the Iphone without any further intervention This is what we do. John Israel Senior ERP Developer Dayton Superior Corporation 1125 Byers Rd Miamisburg, OH 45342 Office: 937-866-0711 x44380 Fax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the intended recipient(s) and may contain information that is privileged, confidential or proprietary. If you are not an intended recipient, please notify the sender, and then please delete and destroy all copies and attachments, and be advised that any review or dissemination of, or the taking of any action in reliance on, the information contained in or attached to this message is prohibited. -Original Message- From: Israel, John R. johnisr...@daytonsuperior.com To: 'U2 Users List' u2-users@listserver.u2ug.org Sent: Mon, Feb 6, 2012 2:25 pm Subject: Re: [U2] Building an Excel File No, the Smart Phones can not open the XML at all. The txt and xls files can be pened, but they are just text. Managers want to be able to read the files leanly. I am guessing the issue is that when a smart phone opens a true Excel file, it s not running Excel, but some sort of emulator. The emulator does not have all he bells and whistles that a full blown version of Excel has, thus undesirable esults. John -Original Message- rom: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] n Behalf Of George Gallen ent: Monday, February 06, 2012 4:45 PM o: U2 Users List ubject: Re: [U2] Building an Excel File Can they open an XML file coded for Excel, but without formatting and colors and uch? f so, you may just have to have two files produced, one for the phones and one or desktops. I haven't checked, but are there any perl modules that can convert an XML file o an .xls file This would require unix, but not any user intervention. -Original Message- rom: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] n Behalf Of Israel, John R. ent: Monday, February 06, 2012 4:40 PM o: 'U2 Users List' ubject: [U2] Building an Excel File We are running UniData on a UNIX platform. We create tab-delimited txt files and tab delimited txt files with xls extensions) all the time that users open ith Excel. Many of these jobs are run in the middle of the night or in a rigger program. In all cases, there is no direct user interaction building hese files. We also have some XML files that are coded so that Excel opens them with ormatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a eaningful way. Is there a way to build a TRUE Excel file without any user intervention from niData or UNIX? John John Israel enior ERP Developer Dayton Superior Corporation 125 Byers Rd Miamisburg, OH 45342 ffice: 937-866-0711 x44380 ax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the ntended recipient(s) and may contain information that is privileged, onfidential or proprietary. If you are not an intended recipient, please notify he sender, and then please delete and destroy all copies and attachments, and e advised that any review or dissemination of, or the taking of any action in eliance on, the information contained in or attached to this message is rohibited. __ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users __ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Commas embedded in text data should not be an issue for csv files. It's just an issue for those programmers who aren't using char-stepping routines to parse the data. You have to step char by char, not field by field as the state transition matrix is defined to parse only on strings quoted on both sides. Unfortunately programmers do things their own way and blame the format. The long and short is, the Iphone's built in viewer can handle embedded commas without any problem in the rendering, since we have them embedded in some fields as well and the rendering is not corrupted by that. -Original Message- From: Israel, John R. johnisr...@daytonsuperior.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Mon, Feb 6, 2012 3:27 pm Subject: Re: [U2] Building an Excel File I have always tried to avoid csv files because we have all kinds of characters n our descriptions (commas, double and single quotes, etc.) It is also very asy to build a row of data with 254 delimiters, then simply convert @FMs to abs. That being said, do you have a good subroutine to build csv rows that will not et confused with commas or quotes in the data? I am willing to give this a ry. John Israel Sent from my iPhone On Feb 6, 2012, at 5:28 PM, Wjhonson wjhon...@aol.com wrote: Rebuild the files as csv files and they will open on the Iphone without any urther intervention This is what we do. ohn Israel enior ERP Developer Dayton Superior Corporation 125 Byers Rd Miamisburg, OH 45342 ffice: 937-866-0711 x44380 ax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the ntended recipient(s) and may contain information that is privileged, onfidential or proprietary. If you are not an intended recipient, please notify he sender, and then please delete and destroy all copies and attachments, and e advised that any review or dissemination of, or the taking of any action in eliance on, the information contained in or attached to this message is rohibited. Original Message- From: Israel, John R. johnisr...@daytonsuperior.com To: 'U2 Users List' u2-users@listserver.u2ug.org Sent: Mon, Feb 6, 2012 2:25 pm Subject: Re: [U2] Building an Excel File No, the Smart Phones can not open the XML at all. The txt and xls files can e pened, but they are just text. Managers want to be able to read the files leanly. I am guessing the issue is that when a smart phone opens a true Excel file, it s not running Excel, but some sort of emulator. The emulator does not have ll he bells and whistles that a full blown version of Excel has, thus undesirable esults. John -Original Message- rom: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] n Behalf Of George Gallen ent: Monday, February 06, 2012 4:45 PM o: U2 Users List ubject: Re: [U2] Building an Excel File Can they open an XML file coded for Excel, but without formatting and colors nd uch? f so, you may just have to have two files produced, one for the phones and one or desktops. I haven't checked, but are there any perl modules that can convert an XML file o an .xls file This would require unix, but not any user intervention. -Original Message- rom: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] n Behalf Of Israel, John R. ent: Monday, February 06, 2012 4:40 PM o: 'U2 Users List' ubject: [U2] Building an Excel File We are running UniData on a UNIX platform. We create tab-delimited txt files and tab delimited txt files with xls extensions) all the time that users open ith Excel. Many of these jobs are run in the middle of the night or in a rigger program. In all cases, there is no direct user interaction building hese files. We also have some XML files that are coded so that Excel opens them with ormatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a eaningful way. Is there a way to build a TRUE Excel file without any user intervention from niData or UNIX? John John Israel enior ERP Developer Dayton Superior Corporation 125 Byers Rd Miamisburg, OH 45342 ffice: 937-866-0711 x44380 ax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the ntended recipient(s) and may contain information that is privileged, onfidential or proprietary. If you are not an intended recipient, please otify he sender, and then please delete and destroy all copies and attachments, and e advised that any review or dissemination of, or the taking of any action in eliance on, the information contained in or attached to this message is rohibited. __ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users __ 2-Users
Re: [U2] Building an Excel File
The CSV format is really pretty simple. Commas between fields. If a field contains a comma or quote it should be quoted. If a field contains a quote the quote should be doubled. Will is right that you have to use a character-stepping algorithm to parse it properly. But with these two rules in mind it's not hard to build. On Mon, Feb 6, 2012 at 4:46 PM, Wjhonson wjhon...@aol.com wrote: Commas embedded in text data should not be an issue for csv files. It's just an issue for those programmers who aren't using char-stepping routines to parse the data. You have to step char by char, not field by field as the state transition matrix is defined to parse only on strings quoted on both sides. Unfortunately programmers do things their own way and blame the format. The long and short is, the Iphone's built in viewer can handle embedded commas without any problem in the rendering, since we have them embedded in some fields as well and the rendering is not corrupted by that. -Original Message- From: Israel, John R. johnisr...@daytonsuperior.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Mon, Feb 6, 2012 3:27 pm Subject: Re: [U2] Building an Excel File I have always tried to avoid csv files because we have all kinds of characters n our descriptions (commas, double and single quotes, etc.) It is also very asy to build a row of data with 254 delimiters, then simply convert @FMs to abs. That being said, do you have a good subroutine to build csv rows that will not et confused with commas or quotes in the data? I am willing to give this a ry. John Israel Sent from my iPhone On Feb 6, 2012, at 5:28 PM, Wjhonson wjhon...@aol.com wrote: Rebuild the files as csv files and they will open on the Iphone without any urther intervention This is what we do. ohn Israel enior ERP Developer Dayton Superior Corporation 125 Byers Rd Miamisburg, OH 45342 ffice: 937-866-0711 x44380 ax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the ntended recipient(s) and may contain information that is privileged, onfidential or proprietary. If you are not an intended recipient, please notify he sender, and then please delete and destroy all copies and attachments, and e advised that any review or dissemination of, or the taking of any action in eliance on, the information contained in or attached to this message is rohibited. Original Message- From: Israel, John R. johnisr...@daytonsuperior.com To: 'U2 Users List' u2-users@listserver.u2ug.org Sent: Mon, Feb 6, 2012 2:25 pm Subject: Re: [U2] Building an Excel File No, the Smart Phones can not open the XML at all. The txt and xls files can e pened, but they are just text. Managers want to be able to read the files leanly. I am guessing the issue is that when a smart phone opens a true Excel file, it s not running Excel, but some sort of emulator. The emulator does not have ll he bells and whistles that a full blown version of Excel has, thus undesirable esults. John -Original Message- rom: u2-users-boun...@listserver.u2ug.org [mailto: u2-users-boun...@listserver.u2ug.org] n Behalf Of George Gallen ent: Monday, February 06, 2012 4:45 PM o: U2 Users List ubject: Re: [U2] Building an Excel File Can they open an XML file coded for Excel, but without formatting and colors nd uch? f so, you may just have to have two files produced, one for the phones and one or desktops. I haven't checked, but are there any perl modules that can convert an XML file o an .xls file This would require unix, but not any user intervention. -Original Message- rom: u2-users-boun...@listserver.u2ug.org [mailto: u2-users-boun...@listserver.u2ug.org] n Behalf Of Israel, John R. ent: Monday, February 06, 2012 4:40 PM o: 'U2 Users List' ubject: [U2] Building an Excel File We are running UniData on a UNIX platform. We create tab-delimited txt files and tab delimited txt files with xls extensions) all the time that users open ith Excel. Many of these jobs are run in the middle of the night or in a rigger program. In all cases, there is no direct user interaction building hese files. We also have some XML files that are coded so that Excel opens them with ormatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a eaningful way. Is there a way to build a TRUE Excel file without any user intervention from niData or UNIX? John John Israel enior ERP Developer Dayton Superior Corporation 125 Byers Rd Miamisburg, OH 45342 ffice: 937-866-0711 x44380 ax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the ntended recipient(s) and may contain information that is privileged, onfidential or proprietary. If you
Re: [U2] Building an Excel File
I dump csv files and convert to true excel with perl using Spreadsheet::WriteExcel module. Once scripted it's pretty painless and automated. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: Monday, February 06, 2012 4:40 PM To: 'U2 Users List' Subject: [U2] Building an Excel File We are running UniData on a UNIX platform. We create tab-delimited txt files (and tab delimited txt files with xls extensions) all the time that users open with Excel. Many of these jobs are run in the middle of the night or in a trigger program. In all cases, there is no direct user interaction building these files. We also have some XML files that are coded so that Excel opens them with formatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a meaningful way. Is there a way to build a TRUE Excel file without any user intervention from UniData or UNIX? John John Israel Senior ERP Developer Dayton Superior Corporation 1125 Byers Rd Miamisburg, OH 45342 Office: 937-866-0711 x44380 Fax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the intended recipient(s) and may contain information that is privileged, confidential or proprietary. If you are not an intended recipient, please notify the sender, and then please delete and destroy all copies and attachments, and be advised that any review or dissemination of, or the taking of any action in reliance on, the information contained in or attached to this message is prohibited. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- Jeff Butera, PhD Manager of ERP Systems Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
In my own routine, a simply enquote all fields, whether they need it or not. Then I don't have to check if the fields truly need it, and the nets get flooded with 8 percent more characters... The email and net gods however, seem to be keeping up with my wasteful ways. -Original Message- From: Kevin King ke...@precisonline.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Mon, Feb 6, 2012 4:02 pm Subject: Re: [U2] Building an Excel File The CSV format is really pretty simple. Commas between fields. If a field ontains a comma or quote it should be quoted. If a field contains a quote he quote should be doubled. Will is right that you have to use a haracter-stepping algorithm to parse it properly. But with these two ules in mind it's not hard to build. On Mon, Feb 6, 2012 at 4:46 PM, Wjhonson wjhon...@aol.com wrote: Commas embedded in text data should not be an issue for csv files. It's just an issue for those programmers who aren't using char-stepping routines to parse the data. You have to step char by char, not field by field as the state transition matrix is defined to parse only on strings quoted on both sides. Unfortunately programmers do things their own way and blame the format. The long and short is, the Iphone's built in viewer can handle embedded commas without any problem in the rendering, since we have them embedded in some fields as well and the rendering is not corrupted by that. -Original Message- From: Israel, John R. johnisr...@daytonsuperior.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Mon, Feb 6, 2012 3:27 pm Subject: Re: [U2] Building an Excel File I have always tried to avoid csv files because we have all kinds of characters n our descriptions (commas, double and single quotes, etc.) It is also very asy to build a row of data with 254 delimiters, then simply convert @FMs to abs. That being said, do you have a good subroutine to build csv rows that will not et confused with commas or quotes in the data? I am willing to give this a ry. John Israel Sent from my iPhone On Feb 6, 2012, at 5:28 PM, Wjhonson wjhon...@aol.com wrote: Rebuild the files as csv files and they will open on the Iphone without any urther intervention This is what we do. ohn Israel enior ERP Developer Dayton Superior Corporation 125 Byers Rd Miamisburg, OH 45342 ffice: 937-866-0711 x44380 ax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the ntended recipient(s) and may contain information that is privileged, onfidential or proprietary. If you are not an intended recipient, please notify he sender, and then please delete and destroy all copies and attachments, and e advised that any review or dissemination of, or the taking of any action in eliance on, the information contained in or attached to this message is rohibited. Original Message- From: Israel, John R. johnisr...@daytonsuperior.com To: 'U2 Users List' u2-users@listserver.u2ug.org Sent: Mon, Feb 6, 2012 2:25 pm Subject: Re: [U2] Building an Excel File No, the Smart Phones can not open the XML at all. The txt and xls files can e pened, but they are just text. Managers want to be able to read the files leanly. I am guessing the issue is that when a smart phone opens a true Excel file, it s not running Excel, but some sort of emulator. The emulator does not have ll he bells and whistles that a full blown version of Excel has, thus undesirable esults. John -Original Message- rom: u2-users-boun...@listserver.u2ug.org [mailto: u2-users-boun...@listserver.u2ug.org] n Behalf Of George Gallen ent: Monday, February 06, 2012 4:45 PM o: U2 Users List ubject: Re: [U2] Building an Excel File Can they open an XML file coded for Excel, but without formatting and colors nd uch? f so, you may just have to have two files produced, one for the phones and one or desktops. I haven't checked, but are there any perl modules that can convert an XML file o an .xls file This would require unix, but not any user intervention. -Original Message- rom: u2-users-boun...@listserver.u2ug.org [mailto: u2-users-boun...@listserver.u2ug.org] n Behalf Of Israel, John R. ent: Monday, February 06, 2012 4:40 PM o: 'U2 Users List' ubject: [U2] Building an Excel File We are running UniData on a UNIX platform. We create tab-delimited txt files and tab delimited txt files with xls extensions) all the time that users open ith Excel. Many of these jobs are run in the middle of the night or in a rigger program. In all cases, there is no direct user interaction building hese files. We also have some XML files that are coded so that Excel opens them with ormatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a eaningful way. Is there a way to build
Re: [U2] Building an Excel File
From what I have seen Excel2010 (I think 2007 did it as well) will often strip off the first occurrence of the double quote in the field and leave the ending quote, but still get the columns right. For instance: test,he said, Watch out below.,test2 Will show up as Col1 test Col2 he said, Watch out below. Col3 test2 You have to escape them with an additional double quote to get it right This: test,he said, Watch out below.,test2 Works as expected. So the moral is, replace your double quotes with 2 double quotes before you write surround it with double quotes and write it. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of John Hester Sent: Monday, February 06, 2012 5:45 PM To: U2 Users List Subject: Re: [U2] Building an Excel File It's been my experience in the past that you can avoid issues with the comma delimeter as long as you enclose your data in double-quotes. Supposedly this even applies to data that already contains double-quotes, although I've never attempted it personally. Wikipedia has that specific example documented, though: http://en.wikipedia.org/wiki/Comma-separated_values -John -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: Monday, February 06, 2012 3:26 PM To: U2 Users List Subject: Re: [U2] Building an Excel File I have always tried to avoid csv files because we have all kinds of characters in our descriptions (commas, double and single quotes, etc.) It is also very easy to build a row of data with 254 delimiters, then simply convert @FMs to tabs. That being said, do you have a good subroutine to build csv rows that will not get confused with commas or quotes in the data? I am willing to give this a try. John Israel Sent from my iPhone On Feb 6, 2012, at 5:28 PM, Wjhonson wjhon...@aol.com wrote: Rebuild the files as csv files and they will open on the Iphone without any further intervention This is what we do. John Israel Senior ERP Developer Dayton Superior Corporation 1125 Byers Rd Miamisburg, OH 45342 Office: 937-866-0711 x44380 Fax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the intended recipient(s) and may contain information that is privileged, confidential or proprietary. If you are not an intended recipient, please notify the sender, and then please delete and destroy all copies and attachments, and be advised that any review or dissemination of, or the taking of any action in reliance on, the information contained in or attached to this message is prohibited. -Original Message- From: Israel, John R. johnisr...@daytonsuperior.com To: 'U2 Users List' u2-users@listserver.u2ug.org Sent: Mon, Feb 6, 2012 2:25 pm Subject: Re: [U2] Building an Excel File No, the Smart Phones can not open the XML at all. The txt and xls files can be pened, but they are just text. Managers want to be able to read the files leanly. I am guessing the issue is that when a smart phone opens a true Excel file, it s not running Excel, but some sort of emulator. The emulator does not have all he bells and whistles that a full blown version of Excel has, thus undesirable esults. John -Original Message- rom: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] n Behalf Of George Gallen ent: Monday, February 06, 2012 4:45 PM o: U2 Users List ubject: Re: [U2] Building an Excel File Can they open an XML file coded for Excel, but without formatting and colors and uch? f so, you may just have to have two files produced, one for the phones and one or desktops. I haven't checked, but are there any perl modules that can convert an XML file o an .xls file This would require unix, but not any user intervention. -Original Message- rom: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] n Behalf Of Israel, John R. ent: Monday, February 06, 2012 4:40 PM o: 'U2 Users List' ubject: [U2] Building an Excel File We are running UniData on a UNIX platform. We create tab-delimited txt files and tab delimited txt files with xls extensions) all the time that users open ith Excel. Many of these jobs are run in the middle of the night or in a rigger program. In all cases, there is no direct user interaction building hese files. We also have some XML files that are coded so that Excel opens them with ormatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a eaningful way. Is there a way to build a TRUE Excel file without any user intervention from niData or UNIX? John John Israel enior ERP Developer Dayton Superior Corporation 125 Byers Rd Miamisburg, OH 45342 ffice: 937-866-0711 x44380 ax: 937-865-9182 johnisr
Re: [U2] Building an Excel File
Can the iphone open a true excel file though? -Original Message- From: Jeff Butera jbut...@hampshire.edu To: u2-users u2-users@listserver.u2ug.org Sent: Mon, Feb 6, 2012 4:04 pm Subject: Re: [U2] Building an Excel File I dump csv files and convert to true excel with perl using preadsheet::WriteExcel module. Once scripted it's pretty painless and utomated. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] n Behalf Of Israel, John R. Sent: Monday, February 06, 2012 4:40 PM To: 'U2 Users List' Subject: [U2] Building an Excel File We are running UniData on a UNIX platform. We create tab-delimited txt files and tab delimited txt files with xls extensions) all the time that users open ith Excel. Many of these jobs are run in the middle of the night or in a rigger program. In all cases, there is no direct user interaction building hese files. We also have some XML files that are coded so that Excel opens them with ormatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a eaningful way. Is there a way to build a TRUE Excel file without any user intervention from niData or UNIX? John John Israel Senior ERP Developer Dayton Superior Corporation 1125 Byers Rd Miamisburg, OH 45342 Office: 937-866-0711 x44380 Fax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the ntended recipient(s) and may contain information that is privileged, onfidential or proprietary. If you are not an intended recipient, please notify he sender, and then please delete and destroy all copies and attachments, and e advised that any review or dissemination of, or the taking of any action in eliance on, the information contained in or attached to this message is rohibited. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users - eff Butera, PhD anager of ERP Systems ampshire College 13-559-5556 ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
On 02/06/2012 07:07 PM, Wjhonson wrote: Can the iphone open a true excel file though? I know xls is no problem (I do it on my 4), not sure about xlsx. -Original Message- From: Jeff Buterajbut...@hampshire.edu To: u2-usersu2-users@listserver.u2ug.org Sent: Mon, Feb 6, 2012 4:04 pm Subject: Re: [U2] Building an Excel File I dump csv files and convert to true excel with perl using preadsheet::WriteExcel module. Once scripted it's pretty painless and utomated. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] n Behalf Of Israel, John R. Sent: Monday, February 06, 2012 4:40 PM To: 'U2 Users List' Subject: [U2] Building an Excel File We are running UniData on a UNIX platform. We create tab-delimited txt files and tab delimited txt files with xls extensions) all the time that users open ith Excel. Many of these jobs are run in the middle of the night or in a rigger program. In all cases, there is no direct user interaction building hese files. We also have some XML files that are coded so that Excel opens them with ormatting, colors, filters, etc. Unfortunately, our BB users and iPhone users can not open these files up in a eaningful way. Is there a way to build a TRUE Excel file without any user intervention from niData or UNIX? John John Israel Senior ERP Developer Dayton Superior Corporation 1125 Byers Rd Miamisburg, OH 45342 Office: 937-866-0711 x44380 Fax: 937-865-9182 johnisr...@daytonsuperior.com This message w/attachments (message) is intended solely for the use of the ntended recipient(s) and may contain information that is privileged, onfidential or proprietary. If you are not an intended recipient, please notify he sender, and then please delete and destroy all copies and attachments, and e advised that any review or dissemination of, or the taking of any action in eliance on, the information contained in or attached to this message is rohibited. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users - eff Butera, PhD anager of ERP Systems ampshire College 13-559-5556 ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- Jeff Butera, PhD Manager of ERP Systems Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Building an Excel File
Makes one long for CrowFlite, doesn't it? For those that don't remember, CrowFlite was an add-in to Excel that allowed it, at a per-cell level, to reach back into a Pick database and grab individual pieces of data. In other words, it was a pull solution as well as a push. I'm cc'ing its creator, Phil Gerber, so he can reminisce about the past while reveling in the sights among the mountains of Oregon :-) On Mon, 6 Feb 2012, Tony Gravagno wrote: Not responding to any particular quote here, just the CSV topic in general. Respected colleagues, CSV is not Excel. If you have an end-user that asks for Excel and you give them a CSV you're just perpetuating the myth that Pick is a dinosaur. They will gladly spend tens of thousands of dollars to replace your application with something that creates real Excel (and PDF) despite the fact that such things can be attained at low cost or no cost right now. Trust me, I've seen it happen. This dove-tails with the reasons why people get 20 people to support Oracle when they can have 3 working on Pick. The reason is that the Oracle people say yes, and give them pretty reports, when their Pick guys say no, and give them plain text in columns and rows and call it Excel. Please don't let that happen to you. Be sure you are properly responding to end-user requests. Just ask them what they do with the documents after you generate them. If they really just want raw data, OK. But if they go on to tell you how many days it takes to reformat the data, assemble the multiple CSVs into a single workbook, etc, then you have found a great deal of room for improvement. Yeah, I've been there too. Off the soapbox, thanks. T ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users Regards, Bob Rasmussen, President, Rasmussen Software, Inc. personal e-mail: r...@anzio.com company e-mail: r...@anzio.com voice: (US) 503-624-0360 (9:00-6:00 Pacific Time) fax: (US) 503-624-0760 web: http://www.anzio.com street address: Rasmussen Software, Inc. 10240 SW Nimbus, Suite L9 Portland, OR 97223 USA ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
