Re: [U2] Simple Export to Excel
The ! was just part of the data The standard is here http://tools.ietf.org/html/rfc4180 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of charles_shaf...@ntn-bower.com Sent: 07 July 2009 20:03 To: U2 Users List Subject: Re: [U2] Simple Export to Excel >> The csv 'standard' for fields containing a quote is double quote >> So for the data >> Col1 = col1data >> Col2 = col 2 "is here" !, and it is good >> Col3 = end >> You have a csv of >> "col1","col 2 ""is here"" !, and it is good","end" Does the ! in front of the comma in Col2 make it "escape" the comma. In other words, does it prevent an extra column from being created? Is the 'standard' a documented standard? Charles Shaffer Senior Analyst NTN-Bower Corporation ___ 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] Simple Export to Excel
Let's not forget what the "RS" stands for. In addition, there were many applications that went far beyond anything contemplated by it, kind of like off-label uses for prescription drugs. Your mileage will vary. Best regards, Henry Henry P. Unger Hitech Systems, Inc. http://www.hitech.com -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of charles_shaf...@ntn-bower.com Sent: Tuesday, July 07, 2009 1:51 PM To: U2 Users List Subject: Re: [U2] Simple Export to Excel >>>> Got it. Thanks everyone. I didn't know that. I still think I'll stick >>>> with tab delimiters though. >>>> >>>> >>lol, standards are made to be broken :) We used to joke about the RS-232 'standard.' It was such a great standard because there were so many different ways to implement it. Charles Shaffer Senior Analyst NTN-Bower Corporation ___ 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] Simple Export to Excel
Got it. Thanks everyone. I didn't know that. I still think I'll stick with tab delimiters though. >>lol, standards are made to be broken :) We used to joke about the RS-232 'standard.' It was such a great standard because there were so many different ways to implement it. Charles Shaffer Senior Analyst NTN-Bower Corporation ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
charles_shaf...@ntn-bower.com wrote: Got it. Thanks everyone. I didn't know that. I still think I'll stick with tab delimiters though. lol, standards are made to be broken :) ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
There is a "standard" RFC4180 with iana. The "standard" is to have any commas enclosed within the double quotes. Only the Double quotes are escaped with double quotes. Got it. Thanks everyone. I didn't know that. I still think I'll stick with tab delimiters though. Charles Shaffer Senior Analyst NTN-Bower Corporation ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
There is a "standard" RFC4180 with iana. The "standard" is to have any commas enclosed within the double quotes. Only the Double quotes are escaped with double quotes. hth Colin Alfke Calgary, Canada > From: Charles_Shaffer > > >> The csv 'standard' for fields containing a quote is double quote > > >> So for the data > >> Col1 = col1data > >> Col2 = col 2 "is here" !, and it is good > >> Col3 = end > >> You have a csv of > >> "col1","col 2 ""is here"" !, and it is good","end" > > Does the ! in front of the comma in Col2 make it "escape" the comma. In > other words, does it prevent an extra column from being created? > > Is the 'standard' a documented standard? > > Charles Shaffer _ Internet explorer 8 lets you browse the web faster. http://go.microsoft.com/?linkid=9655582 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
>> The csv 'standard' for fields containing a quote is double quote >> So for the data >> Col1 = col1data >> Col2 = col 2 "is here" !, and it is good >> Col3 = end >> You have a csv of >> "col1","col 2 ""is here"" !, and it is good","end" Does the ! in front of the comma in Col2 make it "escape" the comma. In other words, does it prevent an extra column from being created? Is the 'standard' a documented standard? Charles Shaffer Senior Analyst NTN-Bower Corporation ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
The csv 'standard' for fields containing a quote is double quote So for the data Col1 = col1data Col2 = col 2 "is here" !, and it is good Col3 = end You have a csv of "col1","col 2 ""is here"" !, and it is good","end" -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of charles_shaf...@ntn-bower.com Sent: 06 July 2009 16:21 To: U2 Users List Subject: Re: [U2] Simple Export to Excel >>Excel has the nasty habit of doing things to the data. For example, leading zeros are removed from numeric data. This is a bad thing if >>you have valid data like 123, 0123 & 00123. Excel will make then all 123. I agree. You can quote text values, but this is problematic if the data itself contained quotes. It helps some if you define the fields as text instead of general in the import screen. But there are still problems. I have tried to convince users not to overuse Excel, but they usually act like I just ran over their favorite puppy. Charles Shaffer Senior Analyst NTN-Bower Corporation ___ 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] Simple Export to Excel
Group (I didn't see who the OP was), I have a perl script/unidata program which will do XLS (Excel 2003 and before). It requires that perl be installed and a few *excel.ppms* freely available. The black box unidata driver program handles column heading, formatting, justification, sorting, subtotaling and grouping. I'm working through the OpenXML formats for Office, I've conquered Word 2007 and moving onto Excel 2007 (openxml) next. I will update the group when I finish. If anyone wants the perl/excel2003 scripts and unidata programs let me know. (email: u2ex...@remove-thissoutheast-florida.com) south east (dash) florida dot com Don Verhagen (Plugging along in SQL and .Net where a simple generic empty validation ain't so simple!) > -Original Message- > From: u2-users-boun...@listserver.u2ug.org > [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Kevin King > Sent: Monday, July 06, 2009 12:55 PM > To: U2 Users List > Subject: Re: [U2] Simple Export to Excel > > >On Mon, Jul 6, 2009 at 9:59 AM, Larry Hiscock > wrote: > >Or, you can create your export in the Office XML format, and > tell excel > >explicitly what to do with each column of data. > > Absolutely. But what a time-consuming chore that can be... :-) > > -K > ___ > 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] Simple Export to Excel
I have a little ODBC project that I just started. Getting the IBM-U2 ODBC Driver to work with Excel was almost painless. --Bill ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
The solutions provided here should help: nospamNebula-RnD.com/blog/tech/2009/07/excel-csv1.html > From: John Israel > ...Excel has the nasty habit of doing things to the > data. For example, leading zeros are removed from > numeric data. This is a bad thing if you have valid > data like 123, 0123 & 00123. Excel will make then all > 123. Sure, I can put quotes around them, but when the > folks that use macros and pivot tables, or tie into > other Excel files, these will not work ("00123" does > not equal 0123 in Excel). Big numbers, like GL#s get > converted to scientific notation. Some numbers or > text get converted to dates. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
Which is why Tony G's NebulaXlite exists. On Mon, Jul 6, 2009 at 9:54 AM, Kevin King wrote: > >On Mon, Jul 6, 2009 at 9:59 AM, Larry Hiscock > wrote: > >Or, you can create your export in the Office XML format, and tell excel > >explicitly what to do with each column of data. > > Absolutely. But what a time-consuming chore that can be... :-) > > -K > ___ > 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] Simple Export to Excel
>On Mon, Jul 6, 2009 at 9:59 AM, Larry Hiscock wrote: >Or, you can create your export in the Office XML format, and tell excel >explicitly what to do with each column of data. Absolutely. But what a time-consuming chore that can be... :-) -K ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
Or, you can create your export in the Office XML format, and tell excel explicitly what to do with each column of data. Larry Hiscock Western Computer Services -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Kevin King Sent: Monday, July 06, 2009 8:34 AM To: U2 Users List Subject: Re: [U2] Simple Export to Excel The problem with Excel stripping leading zeroes and converting stuff to dates can be completely mitigated if you download and open the sheet manually. If you open the sheet automatically, Excel does its "magic". However, if you open a .txt file with tabs (or even commas for that matter) you can then decide what each column should be. -K ___ 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] Simple Export to Excel
The problem with Excel stripping leading zeroes and converting stuff to dates can be completely mitigated if you download and open the sheet manually. If you open the sheet automatically, Excel does its "magic". However, if you open a .txt file with tabs (or even commas for that matter) you can then decide what each column should be. -K ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
>>Excel has the nasty habit of doing things to the data. For example, leading zeros are removed from numeric data. This is a bad thing if >>you have valid data like 123, 0123 & 00123. Excel will make then all 123. I agree. You can quote text values, but this is problematic if the data itself contained quotes. It helps some if you define the fields as text instead of general in the import screen. But there are still problems. I have tried to convince users not to overuse Excel, but they usually act like I just ran over their favorite puppy. Charles Shaffer Senior Analyst NTN-Bower Corporation ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
The problem is, Excel actually stores it with the equals and quotes. At a glance, it looks good, but again, it can cause some grief with pivot tables and when bounced against other Excel worksheets that have "true" data. John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Monday, July 06, 2009 10:35 AM To: U2 Users List Subject: Re: [U2] Simple Export to Excel I like the = method, ="1234567890",="001234",="01234",="1234" Will cause the import to treat as literalswhich might be a downside. > -Original Message- > From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- > boun...@listserver.u2ug.org] On Behalf Of Israel, John R. > Sent: Monday, July 06, 2009 10:15 AM > To: 'U2 Users List' > Subject: Re: [U2] Simple Export to Excel > > I agree completely. However, Excel has the nasty habit of doing things > to the data. For example, leading zeros are removed from numeric data. > This is a bad thing if you have valid data like 123, 0123 & 00123. > Excel will make then all 123. Sure, I can put quotes around them, but > when the folks that use macros and pivot tables, or tie into other > Excel files, these will not work ("00123" does not equal 0123 in > Excel). Big numbers, like GL#s get converted to scientific notation. > Some numbers or text get converted to dates. > > Argh!!! is it talk like pirate day again already? > > John Israel > Sr. Programmer/Analyst > Dayton Superior Corporation > 721 Richard St. > Dayton, OH 45342 > 937-866-0711 x44380 George ___ 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] Simple Export to Excel
I like the = method, ="1234567890",="001234",="01234",="1234" Will cause the import to treat as literalswhich might be a downside. > -Original Message- > From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- > boun...@listserver.u2ug.org] On Behalf Of Israel, John R. > Sent: Monday, July 06, 2009 10:15 AM > To: 'U2 Users List' > Subject: Re: [U2] Simple Export to Excel > > I agree completely. However, Excel has the nasty habit of doing things > to the data. For example, leading zeros are removed from numeric data. > This is a bad thing if you have valid data like 123, 0123 & 00123. > Excel will make then all 123. Sure, I can put quotes around them, but > when the folks that use macros and pivot tables, or tie into other > Excel files, these will not work ("00123" does not equal 0123 in > Excel). Big numbers, like GL#s get converted to scientific notation. > Some numbers or text get converted to dates. > > Argh!!! is it talk like pirate day again already? > > John Israel > Sr. Programmer/Analyst > Dayton Superior Corporation > 721 Richard St. > Dayton, OH 45342 > 937-866-0711 x44380 George ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
I agree completely. However, Excel has the nasty habit of doing things to the data. For example, leading zeros are removed from numeric data. This is a bad thing if you have valid data like 123, 0123 & 00123. Excel will make then all 123. Sure, I can put quotes around them, but when the folks that use macros and pivot tables, or tie into other Excel files, these will not work ("00123" does not equal 0123 in Excel). Big numbers, like GL#s get converted to scientific notation. Some numbers or text get converted to dates. Argh!!! John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Kevin King Sent: Monday, July 06, 2009 9:58 AM To: U2 Users List Subject: Re: [U2] Simple Export to Excel I second what Charles is saying. When moving data into Excel, tab-sep format is a lot cleaner because you don't have to care whether a field has an embedded comma, squote, or dquote. -K ___ 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] Simple Export to Excel
I second what Charles is saying. When moving data into Excel, tab-sep format is a lot cleaner because you don't have to care whether a field has an embedded comma, squote, or dquote. -K ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
>>Instead of Tab-delimited, look at CSV format. It supports embedded single- and double-quotes. And CSV imports into Excel. Embedded >>double quotes are two consecutive chars: "" Tab delimit4ed fiels work the same as comma delimited files as far as how they handle quotes. And Excel will import them. I tend to use tabs to avoid problems with users entering commas in the data which causes problems with columns. Charles Shaffer Senior Analyst NTN-Bower Corporation ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
Careful Tony, the police will be after you :-) Ross Ferris Stamina Software Visage > Better by Design! >-Original Message- >From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- >boun...@listserver.u2ug.org] On Behalf Of Tony G >Sent: Tuesday, 23 June 2009 7:22 AM >To: u2-users@listserver.u2ug.org >Subject: Re: [U2] Simple Export to Excel > >As Robert describes, double quotes are used in freeware I've >published called ExcelExport, which exports full workbooks and >individual worksheets to CSV. You can get the software from our >freeware page and see the TXT Readme for details. > >no.spam.pleaseNebula-rnd.com/freeware/ > >Rather than making another post, I'll mention here that people >are using our NebulaXLite from SB+, and it even displays progress >info through SBClient as it works through large reports. And for >people who say "use XML", well, that's exactly what NebulaXLite >does. > >Tony Gravagno >Nebula Research and Development >TG@ remove.pleaseNebula-RnD.com >Visit PickWiki.com! Contribute! > >> From: Robert Porter >> Double-quotes can be embedded within the quoted field >> by escaping them. Just double up the double-quote >> character. So the text: This is my "description" >> field. Would go be exported as: "This is my >> ""description"" field." > >___ >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] Simple Export to Excel
As Robert describes, double quotes are used in freeware I've published called ExcelExport, which exports full workbooks and individual worksheets to CSV. You can get the software from our freeware page and see the TXT Readme for details. no.spam.pleaseNebula-rnd.com/freeware/ Rather than making another post, I'll mention here that people are using our NebulaXLite from SB+, and it even displays progress info through SBClient as it works through large reports. And for people who say "use XML", well, that's exactly what NebulaXLite does. Tony Gravagno Nebula Research and Development TG@ remove.pleaseNebula-RnD.com Visit PickWiki.com! Contribute! > From: Robert Porter > Double-quotes can be embedded within the quoted field > by escaping them. Just double up the double-quote > character. So the text: This is my "description" > field. Would go be exported as: "This is my > ""description"" field." ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
Colin, I would like to to tie in to SB+ (as in an option available in OUTPUT.REDIRECT). I think OUTPUT.REDIRECT is a prelude specific routine, I may be wrong. Where I make the distinction is I do not want to involve any DDE or COM to the client, I would just liek the xls dropped to a dir on the filesystem or possibly emailed (based on size limitations.) I am thinking of a tool that is client agnostic. Doesnt depend on anything running client side for the creation of the xls. Colin Alfke wrote: Wasn't your question about tying it into SB+? If you exclude the SBClient portion of SB+ then how is it different from anything else? Whether you use something like NebulaXLite to generate the document at the Basic level or something server side like perl or python there shouldn't be anything unique about SB+ that would prevent any solution from anything else from working. Colin -Original Message- From: Steve Romanow It requires sbclient and windows. I use neither. ___ 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] Simple Export to Excel
Wasn't your question about tying it into SB+? If you exclude the SBClient portion of SB+ then how is it different from anything else? Whether you use something like NebulaXLite to generate the document at the Basic level or something server side like perl or python there shouldn't be anything unique about SB+ that would prevent any solution from anything else from working. Colin -Original Message- From: Steve Romanow It requires sbclient and windows. I use neither. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel [AD]
Please also consider NebulaXLite to create nicely formatted spreadsheets for Excel, OpenOffice, and Google Docs. When you deliver CSV in response to a request for Excel, it's like introducing someone to their new business software that looks like DOS. It just makes the business software seem antiquated and people start looking elsewhere for more modern applications. NebulaXLite is entirely free and fully functional for developers and VARs that demo their software on end-user systems. You can download it, use it as long as you want, and we won't ever ask you for a penny. For production use, there is a one-time purchase price of $200 for any server that goes live with business reports. There are no additional fees. Support and limited enhancements are always free. There are no connectivity components or any other hidden costs. As a "lite" product, this product model is simply to make it easy for a lot of people to do something that makes MV look good. Get more info, software, and full documentation here: remove.pleaseNebula-RnD.com/products/xlite.htm Thanks for your time, Tony Gravagno Nebula Research and Development TG@ remove.pleaseNebula-RnD.com Nebula R&D sells mv.NET and other Pick/MultiValue products worldwide, and provides related development services remove.pleaseNebula-RnD.com/blog Visit PickWiki.com! Contribute! > From: Edward Brown > Yes, if you quote-delimit all your fields then it's possible to pass > @VMs as line feeds, and it will do what you want. > > From: Israel, John R. > > We have lots of programs that export our data from > > UniData to tab-delimited text files. Some of these > > are opened by Excel (which has its faults), some are > > imported into other databases. These files will be 1) > > E-mailed as attachments or written to specific > > location on the network. > > > > If I know that the users will always use Excel to open > > the attachment, I usually make the file extension > > "xls" so that it simply fires up Excel when > > double-clicked and parses everything out reasonably > > cleanly (but still drops leading zeros, converts big > > numbers to scientific notation, etc). > > > We are using UniData on HP-Unix. > > > > Is there a good way to convert @VMs to something that > > Excel will correctly convert to what would be > > equivalent to the Alt+Enter in Excel? I do not want > > the data on multiple rows - I want multiple lines with > > breaks within a single row/cell. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
Tony Gravagno would put an around it, but I've had great luck with his NebulaXLite product. You populate a dynamic array, call Tony's routine, and you get an Excel 2003 XML document with whatever formatting you want. He even has a program to generate Basic code from any existing spreadsheet to get you started. On Mon, Jun 22, 2009 at 7:29 AM, Israel, John R. < johnisr...@daytonsuperior.com> wrote: > That would not work. We use double and single quotes in descriptions for > describing lengths. For example, we might sell something whose dimensions > are: > 2"x3"x10' > > Even so, your info was helpful if I know my data will not have double > quotes. > > Anyone else have an idea? > > > John Israel > Sr. Programmer/Analyst > Dayton Superior Corporation > 721 Richard St. > Dayton, OH 45342 > 937-866-0711 x44380 > > -Original Message- > From: u2-users-boun...@listserver.u2ug.org [mailto: > u2-users-boun...@listserver.u2ug.org] On Behalf Of Edward Brown > Sent: Monday, June 22, 2009 10:25 AM > To: U2 Users List > Subject: Re: [U2] Simple Export to Excel > > Dunno, how about converting double quotes in the data to single quotes? > Or stripping them out entirely? > > > > Ed > > -Original Message- > From: u2-users-boun...@listserver.u2ug.org > [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John > R. > Sent: 22 June 2009 15:22 > To: 'U2 Users List' > Subject: Re: [U2] Simple Export to Excel > > Cool. > > > > Here is the next wrinkle. What if my UniData data has (or may have) > quotes in it? Am I out of luck or is there some alternate way to get > the same result? Is there something like in other languages that you > could convert double quotes in the raw data to "/q" or whatever, and > Excel would know how to handle this? > > > > > > John Israel > > Sr. Programmer/Analyst > > Dayton Superior Corporation > > 721 Richard St. > > Dayton, OH 45342 > > 937-866-0711 x44380 > > > > -Original Message- > From: u2-users-boun...@listserver.u2ug.org > [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Edward Brown > Sent: Monday, June 22, 2009 9:58 AM > To: U2 Users List > Subject: Re: [U2] Simple Export to Excel > > > > Yes, if you quote-delimit all your fields then it's possible to pass > > @VMs as line feeds, and it will do what you want. > > > > Ed > > > > -Original Message- > > From: u2-users-boun...@listserver.u2ug.org > > [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John > > R. > > Sent: 22 June 2009 14:56 > > To: 'U2 Users List' > > Subject: [U2] Simple Export to Excel > > > > We have lots of programs that export our data from UniData to > > tab-delimited text files. Some of these are opened by Excel (which has > > its faults), some are imported into other databases. These files will > > be 1) E-mailed as attachments or written to specific location on the > > network. > > > > > > > > If I know that the users will always use Excel to open the attachment, I > > usually make the file extension "xls" so that it simply fires up Excel > > when double-clicked and parses everything out reasonably cleanly (but > > still drops leading zeros, converts big numbers to scientific notation, > > etc). > > > > > > > > We are using UniData on HP-Unix. > > > > > > > > Is there a good way to convert @VMs to something that Excel will > > correctly convert to what would be equivalent to the Alt+Enter in Excel? > > I do not want the data on multiple rows - I want multiple lines with > > breaks within a single row/cell. > > > > > > > > > > > > John Israel > > > > Sr. Programmer/Analyst > > > > Dayton Superior Corporation > > > > 721 Richard St. > > > > Dayton, OH 45342 > > > > 937-866-0711 x44380 > > ___ > > U2-Users mailing list > > U2-Users@listserver.u2ug.org > > http://listserver.u2ug.org/mailman/listinfo/u2-users > > > > > --- > > Please remember to recycle wherever possible. > > Reduce, reuse, recycle, think do you need to print this e-mail? > > > --- > > This e-mail and any attachment(s), is confidential and may be legally > privileged. It
Re: [U2] Simple Export to Excel
It requires sbclient and windows. I use neither. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
Steve; SB+ downloads quite nicely to Excel. Query reports handle it natively (even to 12 different graph types). Plus there is the TU.TO.EXCEL routine you can look at if you want more explicit control. Primarily, it dumps it to a local file, opens it with Excel and runs macros on it. Hth Colin Alfke Calgary, Canada -Original Message- From: Steve Romanow Do you have yours tied into SB+ at all? That is the holy grail. Jeff Butera wrote: > > >> I am using pyExcelerator to make native xls files on aix. I think this >> package is depracated and has been replaced by xlrd and xlwt. My use is >> not mature at all, but I envision a hook in Output.Redirect that will >> allow direct to xls using python. >> > > Similarly, we have used the Perl module Spreadsheet::WriteExcel to extract Unidata data and write Excel spreadsheets on the fly. > > ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
Since I have no idea how other folks may be using the exported data, I don't dare change the description. Visually, it is no big deal (and might not even be noticed), but if they are trying to match something up between systems, and I have changed the text of one of these, then what used to line up will not. I do not want to explain to senior management why some external process that worked flawlessly for years suddenly stopped working. John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Steve Romanow Sent: Monday, June 22, 2009 11:45 AM To: U2 Users List Subject: Re: [U2] Simple Export to Excel John, Somehting I do for some extracts is replace " with IN and ' with FT in description data. Israel, John R. wrote: > This took some tweaking of my test program, but I think with all the needed > tests and data scrubbing, this is the way to go. It took some playing around > with the commas and quotes (the raw data could have either or both of these > conditions). It also deals with putting multiple lines in a single cell by > enclosing that data in quotes, and putting the data on multiple lines. This > looks goofy at first glance (putting 1 row of data on multiple lines), but it > does work. Study the last example on the link below. > > > > Thanks Bob. > > > > John Israel > > Sr. Programmer/Analyst > > Dayton Superior Corporation > > 721 Richard St. > > Dayton, OH 45342 > > 937-866-0711 x44380 > > > > -Original Message- > From: u2-users-boun...@listserver.u2ug.org > [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Bobby Worley > Sent: Monday, June 22, 2009 10:44 AM > To: 'U2 Users List' > Subject: Re: [U2] Simple Export to Excel > > > > Instead of Tab-delimited, look at CSV format. It supports embedded single- > and double-quotes. And CSV imports into Excel. Embedded double quotes are > two consecutive chars: "" > > > > > > http://en.wikipedia.org/wiki/Comma-separated_values > > > > > > Bob > ___ > 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] Simple Export to Excel
> Do you have yours tied into SB+ at all? That is the holy grail. No. -- Jeff Butera, Ph.D. Administrative Systems Hampshire College jbut...@hampshire.edu 413-559-5556 "Dad, you're talking in grown-up and need to stop." Catherine Butera ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
John, Somehting I do for some extracts is replace " with IN and ' with FT in description data. Israel, John R. wrote: This took some tweaking of my test program, but I think with all the needed tests and data scrubbing, this is the way to go. It took some playing around with the commas and quotes (the raw data could have either or both of these conditions). It also deals with putting multiple lines in a single cell by enclosing that data in quotes, and putting the data on multiple lines. This looks goofy at first glance (putting 1 row of data on multiple lines), but it does work. Study the last example on the link below. Thanks Bob. John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Bobby Worley Sent: Monday, June 22, 2009 10:44 AM To: 'U2 Users List' Subject: Re: [U2] Simple Export to Excel Instead of Tab-delimited, look at CSV format. It supports embedded single- and double-quotes. And CSV imports into Excel. Embedded double quotes are two consecutive chars: "" http://en.wikipedia.org/wiki/Comma-separated_values Bob ___ 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] Simple Export to Excel
Double-quotes can be embedded within the quoted field by escaping them. Just double up the double-quote character. So the text: This is my "description" field. Would go be exported as: "This is my ""description"" field." Robert F. Porter, MCSE, CCNA, ZCE Sr. Programmer / Analyst Laboratory Information Services Ochsner Health System (504) 842 - 5185 This transmission (including any attachments) may contain confidential information, privileged material (including material protected by the solicitor-client or other applicable privileges), or constitute non-public information. Any use of this information by anyone other than the intended recipient is prohibited. If you have received this transmission in error, please immediately reply to the sender and delete this information from your system. Use, dissemination, distribution, or reproduction of this transmission by unintended recipients is not authorized and may be unlawful. >>> "Israel, John R." 6/22/2009 9:29 AM >>> That would not work. We use double and single quotes in descriptions for describing lengths. For example, we might sell something whose dimensions are: 2"x3"x10' Even so, your info was helpful if I know my data will not have double quotes. Anyone else have an idea? John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Edward Brown Sent: Monday, June 22, 2009 10:25 AM To: U2 Users List Subject: Re: [U2] Simple Export to Excel Dunno, how about converting double quotes in the data to single quotes? Or stripping them out entirely? Ed -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: 22 June 2009 15:22 To: 'U2 Users List' Subject: Re: [U2] Simple Export to Excel Cool. Here is the next wrinkle. What if my UniData data has (or may have) quotes in it? Am I out of luck or is there some alternate way to get the same result? Is there something like in other languages that you could convert double quotes in the raw data to "/q" or whatever, and Excel would know how to handle this? John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Edward Brown Sent: Monday, June 22, 2009 9:58 AM To: U2 Users List Subject: Re: [U2] Simple Export to Excel Yes, if you quote-delimit all your fields then it's possible to pass @VMs as line feeds, and it will do what you want. Ed -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: 22 June 2009 14:56 To: 'U2 Users List' Subject: [U2] Simple Export to Excel We have lots of programs that export our data from UniData to tab-delimited text files. Some of these are opened by Excel (which has its faults), some are imported into other databases. These files will be 1) E-mailed as attachments or written to specific location on the network. If I know that the users will always use Excel to open the attachment, I usually make the file extension "xls" so that it simply fires up Excel when double-clicked and parses everything out reasonably cleanly (but still drops leading zeros, converts big numbers to scientific notation, etc). We are using UniData on HP-Unix. Is there a good way to convert @VMs to something that Excel will correctly convert to what would be equivalent to the Alt+Enter in Excel? I do not want the data on multiple rows - I want multiple lines with breaks within a single row/cell. John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users --- Please remember to recycle wherever possible. Reduce, reuse, recycle, think do you need to print this e-mail? --- This e-mail and any attachment(s), is confidential and may be legally privileged. It is intended solely for the addressee. If you are not the addressee, dissemination, copying or use of this e-mail or any of its content is prohibited and may be unlawful. If you are not the intended recipient please inform the sender immediately and destro
Re: [U2] Simple Export to Excel
This took some tweaking of my test program, but I think with all the needed tests and data scrubbing, this is the way to go. It took some playing around with the commas and quotes (the raw data could have either or both of these conditions). It also deals with putting multiple lines in a single cell by enclosing that data in quotes, and putting the data on multiple lines. This looks goofy at first glance (putting 1 row of data on multiple lines), but it does work. Study the last example on the link below. Thanks Bob. John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Bobby Worley Sent: Monday, June 22, 2009 10:44 AM To: 'U2 Users List' Subject: Re: [U2] Simple Export to Excel Instead of Tab-delimited, look at CSV format. It supports embedded single- and double-quotes. And CSV imports into Excel. Embedded double quotes are two consecutive chars: "" http://en.wikipedia.org/wiki/Comma-separated_values Bob ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
Do you have yours tied into SB+ at all? That is the holy grail. Jeff Butera wrote: I am using pyExcelerator to make native xls files on aix. I think this package is depracated and has been replaced by xlrd and xlwt. My use is not mature at all, but I envision a hook in Output.Redirect that will allow direct to xls using python. Similarly, we have used the Perl module Spreadsheet::WriteExcel to extract Unidata data and write Excel spreadsheets on the fly. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
> I am using pyExcelerator to make native xls files on aix. I think this > package is depracated and has been replaced by xlrd and xlwt. My use is > not mature at all, but I envision a hook in Output.Redirect that will > allow direct to xls using python. Similarly, we have used the Perl module Spreadsheet::WriteExcel to extract Unidata data and write Excel spreadsheets on the fly. -- Jeff Butera, Ph.D. Administrative Systems Hampshire College jbut...@hampshire.edu 413-559-5556 "Dad, you're talking in grown-up and need to stop." Catherine Butera ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Simple Export to Excel
I am using pyExcelerator to make native xls files on aix. I think this package is depracated and has been replaced by xlrd and xlwt. My use is not mature at all, but I envision a hook in Output.Redirect that will allow direct to xls using python. Anyone want to team up to bring this idea to realization? George Gallen wrote: For that matter, why not go the XML route where you can define the column properties as well (at least I understand you can...). Makes the files a bit more swollen, but most excel's will load it up and parse it out properly. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- boun...@listserver.u2ug.org] On Behalf Of Jeff Schasny Sent: Monday, June 22, 2009 11:05 AM To: U2 Users List Subject: Re: [U2] Simple Export to Excel Make your data into an HTML table. Israel, John R. wrote: That would not work. We use double and single quotes in descriptions for describing lengths. For example, we might sell something whose dimensions are: 2"x3"x10' Even so, your info was helpful if I know my data will not have double quotes. Anyone else have an idea? John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- boun...@listserver.u2ug.org] On Behalf Of Edward Brown Sent: Monday, June 22, 2009 10:25 AM To: U2 Users List Subject: Re: [U2] Simple Export to Excel Dunno, how about converting double quotes in the data to single quotes? Or stripping them out entirely? Ed -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: 22 June 2009 15:22 To: 'U2 Users List' Subject: Re: [U2] Simple Export to Excel Cool. Here is the next wrinkle. What if my UniData data has (or may have) quotes in it? Am I out of luck or is there some alternate way to get the same result? Is there something like in other languages that you could convert double quotes in the raw data to "/q" or whatever, and Excel would know how to handle this? John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Edward Brown Sent: Monday, June 22, 2009 9:58 AM To: U2 Users List Subject: Re: [U2] Simple Export to Excel Yes, if you quote-delimit all your fields then it's possible to pass @VMs as line feeds, and it will do what you want. Ed -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: 22 June 2009 14:56 To: 'U2 Users List' Subject: [U2] Simple Export to Excel We have lots of programs that export our data from UniData to tab-delimited text files. Some of these are opened by Excel (which has its faults), some are imported into other databases. These files will be 1) E-mailed as attachments or written to specific location on the network. If I know that the users will always use Excel to open the attachment, I usually make the file extension "xls" so that it simply fires up Excel when double-clicked and parses everything out reasonably cleanly (but still drops leading zeros, converts big numbers to scientific notation, etc). We are using UniData on HP-Unix. Is there a good way to convert @VMs to something that Excel will correctly convert to what would be equivalent to the Alt+Enter in Excel? I do not want the data on multiple rows - I want multiple lines with breaks within a single row/cell. John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users - --- --- Please remember to recycle wherever possible. Reduce, reuse, recycle, think do you need to print this e-mail? - --- --- This e-mail and any attachment(s), is confidential and may be legally privileged. It is intended solely for the addressee. If you are not the addressee, dissemination, copying or use of this e-mail or any of its content is prohibited and may be unlawful. If you are not the intended recipient please inform
Re: [U2] Simple Export to Excel
For that matter, why not go the XML route where you can define the column properties as well (at least I understand you can...). Makes the files a bit more swollen, but most excel's will load it up and parse it out properly. George > -Original Message- > From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- > boun...@listserver.u2ug.org] On Behalf Of Jeff Schasny > Sent: Monday, June 22, 2009 11:05 AM > To: U2 Users List > Subject: Re: [U2] Simple Export to Excel > > Make your data into an HTML table. > > Israel, John R. wrote: > > That would not work. We use double and single quotes in descriptions > for describing lengths. For example, we might sell something whose > dimensions are: > > 2"x3"x10' > > > > Even so, your info was helpful if I know my data will not have double > quotes. > > > > Anyone else have an idea? > > > > > > John Israel > > Sr. Programmer/Analyst > > Dayton Superior Corporation > > 721 Richard St. > > Dayton, OH 45342 > > 937-866-0711 x44380 > > > > -Original Message- > > From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- > boun...@listserver.u2ug.org] On Behalf Of Edward Brown > > Sent: Monday, June 22, 2009 10:25 AM > > To: U2 Users List > > Subject: Re: [U2] Simple Export to Excel > > > > Dunno, how about converting double quotes in the data to single > quotes? > > Or stripping them out entirely? > > > > > > > > Ed > > > > -----Original Message- > > From: u2-users-boun...@listserver.u2ug.org > > [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, > John > > R. > > Sent: 22 June 2009 15:22 > > To: 'U2 Users List' > > Subject: Re: [U2] Simple Export to Excel > > > > Cool. > > > > > > > > Here is the next wrinkle. What if my UniData data has (or may have) > > quotes in it? Am I out of luck or is there some alternate way to get > > the same result? Is there something like in other languages that you > > could convert double quotes in the raw data to "/q" or whatever, and > > Excel would know how to handle this? > > > > > > > > > > > > John Israel > > > > Sr. Programmer/Analyst > > > > Dayton Superior Corporation > > > > 721 Richard St. > > > > Dayton, OH 45342 > > > > 937-866-0711 x44380 > > > > > > > > -Original Message- > > From: u2-users-boun...@listserver.u2ug.org > > [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Edward > Brown > > Sent: Monday, June 22, 2009 9:58 AM > > To: U2 Users List > > Subject: Re: [U2] Simple Export to Excel > > > > > > > > Yes, if you quote-delimit all your fields then it's possible to pass > > > > @VMs as line feeds, and it will do what you want. > > > > > > > > Ed > > > > > > > > -Original Message- > > > > From: u2-users-boun...@listserver.u2ug.org > > > > [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, > John > > > > R. > > > > Sent: 22 June 2009 14:56 > > > > To: 'U2 Users List' > > > > Subject: [U2] Simple Export to Excel > > > > > > > > We have lots of programs that export our data from UniData to > > > > tab-delimited text files. Some of these are opened by Excel (which > has > > > > its faults), some are imported into other databases. These files > will > > > > be 1) E-mailed as attachments or written to specific location on the > > > > network. > > > > > > > > > > > > > > > > If I know that the users will always use Excel to open the > attachment, I > > > > usually make the file extension "xls" so that it simply fires up > Excel > > > > when double-clicked and parses everything out reasonably cleanly (but > > > > still drops leading zeros, converts big numbers to scientific > notation, > > > > etc). > > > > > > > > > > > > > > > > We are using UniData on HP-Unix. > > > > > > > > > > > > > > > > Is there a good way to convert @VMs to something that Excel will > > > > correctly convert to what would be equivalent to the Alt+Enter in > Excel? > > > > I do not want the data on multiple rows - I want multiple lines with > > > > breaks within a singl
Re: [U2] Simple Export to Excel
Make your data into an HTML table. Israel, John R. wrote: That would not work. We use double and single quotes in descriptions for describing lengths. For example, we might sell something whose dimensions are: 2"x3"x10' Even so, your info was helpful if I know my data will not have double quotes. Anyone else have an idea? John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Edward Brown Sent: Monday, June 22, 2009 10:25 AM To: U2 Users List Subject: Re: [U2] Simple Export to Excel Dunno, how about converting double quotes in the data to single quotes? Or stripping them out entirely? Ed -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: 22 June 2009 15:22 To: 'U2 Users List' Subject: Re: [U2] Simple Export to Excel Cool. Here is the next wrinkle. What if my UniData data has (or may have) quotes in it? Am I out of luck or is there some alternate way to get the same result? Is there something like in other languages that you could convert double quotes in the raw data to "/q" or whatever, and Excel would know how to handle this? John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Edward Brown Sent: Monday, June 22, 2009 9:58 AM To: U2 Users List Subject: Re: [U2] Simple Export to Excel Yes, if you quote-delimit all your fields then it's possible to pass @VMs as line feeds, and it will do what you want. Ed -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: 22 June 2009 14:56 To: 'U2 Users List' Subject: [U2] Simple Export to Excel We have lots of programs that export our data from UniData to tab-delimited text files. Some of these are opened by Excel (which has its faults), some are imported into other databases. These files will be 1) E-mailed as attachments or written to specific location on the network. If I know that the users will always use Excel to open the attachment, I usually make the file extension "xls" so that it simply fires up Excel when double-clicked and parses everything out reasonably cleanly (but still drops leading zeros, converts big numbers to scientific notation, etc). We are using UniData on HP-Unix. Is there a good way to convert @VMs to something that Excel will correctly convert to what would be equivalent to the Alt+Enter in Excel? I do not want the data on multiple rows - I want multiple lines with breaks within a single row/cell. John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users --- Please remember to recycle wherever possible. Reduce, reuse, recycle, think do you need to print this e-mail? --- This e-mail and any attachment(s), is confidential and may be legally privileged. It is intended solely for the addressee. If you are not the addressee, dissemination, copying or use of this e-mail or any of its content is prohibited and may be unlawful. If you are not the intended recipient please inform the sender immediately and destroy the e-mail, any attachment(s) and any copies. All liability for viruses is excluded to the fullest extent permitted by law. It is your responsibility to scan or otherwise check this email and any attachment(s). Unless otherwise stated (i) views expressed in this message are those of the individual sender (ii) no contract may be construed by this e-mail. Emails may be monitored and you are taken to consent to this monitoring. Civica Services Limited, Company No. 02374268; Civica UK Limited, Company No. 01628868 Both companies are registered in England and Wales and each has its registered office at 2 Burston Road, Putney, London, SW15 6AR. --- ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u
Re: [U2] Simple Export to Excel
Instead of Tab-delimited, look at CSV format. It supports embedded single- and double-quotes. And CSV imports into Excel. Embedded double quotes are two consecutive chars: "" http://en.wikipedia.org/wiki/Comma-separated_values Bob -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: 22 June 2009 15:30 To: 'U2 Users List' Subject: Re: [U2] Simple Export to Excel That would not work. We use double and single quotes in descriptions for describing lengths. For example, we might sell something whose dimensions are: 2"x3"x10' Even so, your info was helpful if I know my data will not have double quotes. Anyone else have an idea? John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Edward Brown Sent: Monday, June 22, 2009 10:25 AM To: U2 Users List Subject: Re: [U2] Simple Export to Excel Dunno, how about converting double quotes in the data to single quotes? Or stripping them out entirely? Ed -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: 22 June 2009 15:22 To: 'U2 Users List' Subject: Re: [U2] Simple Export to Excel Cool. Here is the next wrinkle. What if my UniData data has (or may have) quotes in it? Am I out of luck or is there some alternate way to get the same result? Is there something like in other languages that you could convert double quotes in the raw data to "/q" or whatever, and Excel would know how to handle this? John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Edward Brown Sent: Monday, June 22, 2009 9:58 AM To: U2 Users List Subject: Re: [U2] Simple Export to Excel Yes, if you quote-delimit all your fields then it's possible to pass @VMs as line feeds, and it will do what you want. Ed -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: 22 June 2009 14:56 To: 'U2 Users List' Subject: [U2] Simple Export to Excel We have lots of programs that export our data from UniData to tab-delimited text files. Some of these are opened by Excel (which has its faults), some are imported into other databases. These files will be 1) E-mailed as attachments or written to specific location on the network. If I know that the users will always use Excel to open the attachment, I usually make the file extension "xls" so that it simply fires up Excel when double-clicked and parses everything out reasonably cleanly (but still drops leading zeros, converts big numbers to scientific notation, etc). We are using UniData on HP-Unix. Is there a good way to convert @VMs to something that Excel will correctly convert to what would be equivalent to the Alt+Enter in Excel? I do not want the data on multiple rows - I want multiple lines with breaks within a single row/cell. John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users --- Please remember to recycle wherever possible. Reduce, reuse, recycle, think do you need to print this e-mail? --- This e-mail and any attachment(s), is confidential and may be legally privileged. It is intended solely for the addressee. If you are not the addressee, dissemination, copying or use of this e-mail or any of its content is prohibited and may be unlawful. If you are not the intended recipient please inform the sender immediately and destroy the e-mail, any attachment(s) and any copies. All liability for viruses is excluded to the fullest extent permitted by law. It is your responsibility to scan or otherwise check this email and any attachment(s). Unless otherwise stated (i) views expressed in this message are those of the individual sender (ii) no contract may be construed by this e-mail. Emails may be monitored and you are taken to consent to this monitoring. Civica Services Limited, Company No. 02374268; Civica UK Limited, Company No. 01628868 Both companies are registered in England and Wale
Re: [U2] Simple Export to Excel
Well, you could convert double quotes to two single quotes - in variable width fonts the two forms are virtually indistinguishable. (view the line below in something like Arial) " vs '' Ed -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: 22 June 2009 15:30 To: 'U2 Users List' Subject: Re: [U2] Simple Export to Excel That would not work. We use double and single quotes in descriptions for describing lengths. For example, we might sell something whose dimensions are: 2"x3"x10' Even so, your info was helpful if I know my data will not have double quotes. Anyone else have an idea? John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Edward Brown Sent: Monday, June 22, 2009 10:25 AM To: U2 Users List Subject: Re: [U2] Simple Export to Excel Dunno, how about converting double quotes in the data to single quotes? Or stripping them out entirely? Ed -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: 22 June 2009 15:22 To: 'U2 Users List' Subject: Re: [U2] Simple Export to Excel Cool. Here is the next wrinkle. What if my UniData data has (or may have) quotes in it? Am I out of luck or is there some alternate way to get the same result? Is there something like in other languages that you could convert double quotes in the raw data to "/q" or whatever, and Excel would know how to handle this? John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Edward Brown Sent: Monday, June 22, 2009 9:58 AM To: U2 Users List Subject: Re: [U2] Simple Export to Excel Yes, if you quote-delimit all your fields then it's possible to pass @VMs as line feeds, and it will do what you want. Ed -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: 22 June 2009 14:56 To: 'U2 Users List' Subject: [U2] Simple Export to Excel We have lots of programs that export our data from UniData to tab-delimited text files. Some of these are opened by Excel (which has its faults), some are imported into other databases. These files will be 1) E-mailed as attachments or written to specific location on the network. If I know that the users will always use Excel to open the attachment, I usually make the file extension "xls" so that it simply fires up Excel when double-clicked and parses everything out reasonably cleanly (but still drops leading zeros, converts big numbers to scientific notation, etc). We are using UniData on HP-Unix. Is there a good way to convert @VMs to something that Excel will correctly convert to what would be equivalent to the Alt+Enter in Excel? I do not want the data on multiple rows - I want multiple lines with breaks within a single row/cell. John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users --- Please remember to recycle wherever possible. Reduce, reuse, recycle, think do you need to print this e-mail? --- This e-mail and any attachment(s), is confidential and may be legally privileged. It is intended solely for the addressee. If you are not the addressee, dissemination, copying or use of this e-mail or any of its content is prohibited and may be unlawful. If you are not the intended recipient please inform the sender immediately and destroy the e-mail, any attachment(s) and any copies. All liability for viruses is excluded to the fullest extent permitted by law. It is your responsibility to scan or otherwise check this email and any attachment(s). Unless otherwise stated (i) views expressed in this message are those of the individual sender (ii) no contract may be construed by this e-mail. Emails may be monitored and you are taken to consent to this monitoring. Civica Services Limited, Company No. 02374268; Civica UK Limited, Company No. 01628868 Both companies are registered in England and
Re: [U2] Simple Export to Excel
That would not work. We use double and single quotes in descriptions for describing lengths. For example, we might sell something whose dimensions are: 2"x3"x10' Even so, your info was helpful if I know my data will not have double quotes. Anyone else have an idea? John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Edward Brown Sent: Monday, June 22, 2009 10:25 AM To: U2 Users List Subject: Re: [U2] Simple Export to Excel Dunno, how about converting double quotes in the data to single quotes? Or stripping them out entirely? Ed -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: 22 June 2009 15:22 To: 'U2 Users List' Subject: Re: [U2] Simple Export to Excel Cool. Here is the next wrinkle. What if my UniData data has (or may have) quotes in it? Am I out of luck or is there some alternate way to get the same result? Is there something like in other languages that you could convert double quotes in the raw data to "/q" or whatever, and Excel would know how to handle this? John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Edward Brown Sent: Monday, June 22, 2009 9:58 AM To: U2 Users List Subject: Re: [U2] Simple Export to Excel Yes, if you quote-delimit all your fields then it's possible to pass @VMs as line feeds, and it will do what you want. Ed -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: 22 June 2009 14:56 To: 'U2 Users List' Subject: [U2] Simple Export to Excel We have lots of programs that export our data from UniData to tab-delimited text files. Some of these are opened by Excel (which has its faults), some are imported into other databases. These files will be 1) E-mailed as attachments or written to specific location on the network. If I know that the users will always use Excel to open the attachment, I usually make the file extension "xls" so that it simply fires up Excel when double-clicked and parses everything out reasonably cleanly (but still drops leading zeros, converts big numbers to scientific notation, etc). We are using UniData on HP-Unix. Is there a good way to convert @VMs to something that Excel will correctly convert to what would be equivalent to the Alt+Enter in Excel? I do not want the data on multiple rows - I want multiple lines with breaks within a single row/cell. John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users --- Please remember to recycle wherever possible. Reduce, reuse, recycle, think do you need to print this e-mail? --- This e-mail and any attachment(s), is confidential and may be legally privileged. It is intended solely for the addressee. If you are not the addressee, dissemination, copying or use of this e-mail or any of its content is prohibited and may be unlawful. If you are not the intended recipient please inform the sender immediately and destroy the e-mail, any attachment(s) and any copies. All liability for viruses is excluded to the fullest extent permitted by law. It is your responsibility to scan or otherwise check this email and any attachment(s). Unless otherwise stated (i) views expressed in this message are those of the individual sender (ii) no contract may be construed by this e-mail. Emails may be monitored and you are taken to consent to this monitoring. Civica Services Limited, Company No. 02374268; Civica UK Limited, Company No. 01628868 Both companies are registered in England and Wales and each has its registered office at 2 Burston Road, Putney, London, SW15 6AR. --- ___ 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] Simple Export to Excel
Dunno, how about converting double quotes in the data to single quotes? Or stripping them out entirely? Ed -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: 22 June 2009 15:22 To: 'U2 Users List' Subject: Re: [U2] Simple Export to Excel Cool. Here is the next wrinkle. What if my UniData data has (or may have) quotes in it? Am I out of luck or is there some alternate way to get the same result? Is there something like in other languages that you could convert double quotes in the raw data to "/q" or whatever, and Excel would know how to handle this? John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Edward Brown Sent: Monday, June 22, 2009 9:58 AM To: U2 Users List Subject: Re: [U2] Simple Export to Excel Yes, if you quote-delimit all your fields then it's possible to pass @VMs as line feeds, and it will do what you want. Ed -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: 22 June 2009 14:56 To: 'U2 Users List' Subject: [U2] Simple Export to Excel We have lots of programs that export our data from UniData to tab-delimited text files. Some of these are opened by Excel (which has its faults), some are imported into other databases. These files will be 1) E-mailed as attachments or written to specific location on the network. If I know that the users will always use Excel to open the attachment, I usually make the file extension "xls" so that it simply fires up Excel when double-clicked and parses everything out reasonably cleanly (but still drops leading zeros, converts big numbers to scientific notation, etc). We are using UniData on HP-Unix. Is there a good way to convert @VMs to something that Excel will correctly convert to what would be equivalent to the Alt+Enter in Excel? I do not want the data on multiple rows - I want multiple lines with breaks within a single row/cell. John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users --- Please remember to recycle wherever possible. Reduce, reuse, recycle, think do you need to print this e-mail? --- This e-mail and any attachment(s), is confidential and may be legally privileged. It is intended solely for the addressee. If you are not the addressee, dissemination, copying or use of this e-mail or any of its content is prohibited and may be unlawful. If you are not the intended recipient please inform the sender immediately and destroy the e-mail, any attachment(s) and any copies. All liability for viruses is excluded to the fullest extent permitted by law. It is your responsibility to scan or otherwise check this email and any attachment(s). Unless otherwise stated (i) views expressed in this message are those of the individual sender (ii) no contract may be construed by this e-mail. Emails may be monitored and you are taken to consent to this monitoring. Civica Services Limited, Company No. 02374268; Civica UK Limited, Company No. 01628868 Both companies are registered in England and Wales and each has its registered office at 2 Burston Road, Putney, London, SW15 6AR. --- ___ 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] Simple Export to Excel
Cool. Here is the next wrinkle. What if my UniData data has (or may have) quotes in it? Am I out of luck or is there some alternate way to get the same result? Is there something like in other languages that you could convert double quotes in the raw data to "/q" or whatever, and Excel would know how to handle this? John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Edward Brown Sent: Monday, June 22, 2009 9:58 AM To: U2 Users List Subject: Re: [U2] Simple Export to Excel Yes, if you quote-delimit all your fields then it's possible to pass @VMs as line feeds, and it will do what you want. Ed -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: 22 June 2009 14:56 To: 'U2 Users List' Subject: [U2] Simple Export to Excel We have lots of programs that export our data from UniData to tab-delimited text files. Some of these are opened by Excel (which has its faults), some are imported into other databases. These files will be 1) E-mailed as attachments or written to specific location on the network. If I know that the users will always use Excel to open the attachment, I usually make the file extension "xls" so that it simply fires up Excel when double-clicked and parses everything out reasonably cleanly (but still drops leading zeros, converts big numbers to scientific notation, etc). We are using UniData on HP-Unix. Is there a good way to convert @VMs to something that Excel will correctly convert to what would be equivalent to the Alt+Enter in Excel? I do not want the data on multiple rows - I want multiple lines with breaks within a single row/cell. John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users --- Please remember to recycle wherever possible. Reduce, reuse, recycle, think do you need to print this e-mail? --- This e-mail and any attachment(s), is confidential and may be legally privileged. It is intended solely for the addressee. If you are not the addressee, dissemination, copying or use of this e-mail or any of its content is prohibited and may be unlawful. If you are not the intended recipient please inform the sender immediately and destroy the e-mail, any attachment(s) and any copies. All liability for viruses is excluded to the fullest extent permitted by law. It is your responsibility to scan or otherwise check this email and any attachment(s). Unless otherwise stated (i) views expressed in this message are those of the individual sender (ii) no contract may be construed by this e-mail. Emails may be monitored and you are taken to consent to this monitoring. Civica Services Limited, Company No. 02374268; Civica UK Limited, Company No. 01628868 Both companies are registered in England and Wales and each has its registered office at 2 Burston Road, Putney, London, SW15 6AR. --- ___ 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] Simple Export to Excel
Yes, if you quote-delimit all your fields then it's possible to pass @VMs as line feeds, and it will do what you want. Ed -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: 22 June 2009 14:56 To: 'U2 Users List' Subject: [U2] Simple Export to Excel We have lots of programs that export our data from UniData to tab-delimited text files. Some of these are opened by Excel (which has its faults), some are imported into other databases. These files will be 1) E-mailed as attachments or written to specific location on the network. If I know that the users will always use Excel to open the attachment, I usually make the file extension "xls" so that it simply fires up Excel when double-clicked and parses everything out reasonably cleanly (but still drops leading zeros, converts big numbers to scientific notation, etc). We are using UniData on HP-Unix. Is there a good way to convert @VMs to something that Excel will correctly convert to what would be equivalent to the Alt+Enter in Excel? I do not want the data on multiple rows - I want multiple lines with breaks within a single row/cell. John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users --- Please remember to recycle wherever possible. Reduce, reuse, recycle, think do you need to print this e-mail? --- This e-mail and any attachment(s), is confidential and may be legally privileged. It is intended solely for the addressee. If you are not the addressee, dissemination, copying or use of this e-mail or any of its content is prohibited and may be unlawful. If you are not the intended recipient please inform the sender immediately and destroy the e-mail, any attachment(s) and any copies. All liability for viruses is excluded to the fullest extent permitted by law. It is your responsibility to scan or otherwise check this email and any attachment(s). Unless otherwise stated (i) views expressed in this message are those of the individual sender (ii) no contract may be construed by this e-mail. Emails may be monitored and you are taken to consent to this monitoring. Civica Services Limited, Company No. 02374268; Civica UK Limited, Company No. 01628868 Both companies are registered in England and Wales and each has its registered office at 2 Burston Road, Putney, London, SW15 6AR. --- ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
[U2] Simple Export to Excel
We have lots of programs that export our data from UniData to tab-delimited text files. Some of these are opened by Excel (which has its faults), some are imported into other databases. These files will be 1) E-mailed as attachments or written to specific location on the network. If I know that the users will always use Excel to open the attachment, I usually make the file extension "xls" so that it simply fires up Excel when double-clicked and parses everything out reasonably cleanly (but still drops leading zeros, converts big numbers to scientific notation, etc). We are using UniData on HP-Unix. Is there a good way to convert @VMs to something that Excel will correctly convert to what would be equivalent to the Alt+Enter in Excel? I do not want the data on multiple rows - I want multiple lines with breaks within a single row/cell. John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users