Re: [U2] import data to excel sheet...
Hi Chris I would investigate Tony's product first - that may well save you a bunch of work. I'm generating OpenXML from a number of different places - so if your requirement is for UniVerse only I'd look to Nebula and see if it covers your needs. If you do find afterwards that you still want to look at OpenXML, it's reasonably simple to update and manage but you need to invest some time understanding the structure. Essentially, it's based on something called Open Packaging Convention which is a zipped archive under the hood - though not compatible will all ZIP programs :(. Within that archive, the content is divided into a number of separate 'parts' all of which link together to create a document - so a Word document will have separate parts for the main body, section headers and footers, media elements, embedded pictures etc. Once you have walked through that maze, the spreadsheet markup is simple as far as the data content is concerned: there are some gotchas when reading data (shared strings, for example) but you can generally avoid them all if you are the one generating it. All the content is separated from the styling, and held in a simple sheetData element for the chosen worksheet. Each worksheet is a separate XML doc within the archive. The difficulty is in adding new styles for formatting, simply because the format there is very verbose. Your best bet is to create a template, apply various styles within that template to dummy cells and save it. Then you have all the styling information ready to use and you can simply apply them to your cells by adding a style number attribute. It's worth noting that OpenXML was introduced as the standard format for Office 2007, but there is a free download from Microsoft called the Office Compatibility Pack that lets earlier versions of Office read and write in that format. I'd recommend the free eBook 'OpenXML Explained' by Wouter van Vugt. IIRC it's downloadable via www.openxmldeveloper.org. Brian ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] import data to excel sheet...
Brian, I really appreciate the reply. I'm going to spend some time over the weekend on this. I'll probably give Tony's product a try first and then go from there. Thanks! -Chris From: br...@brianleach.co.uk To: u2-users@listserver.u2ug.org Date: Wed, 14 Apr 2010 09:48:48 +0100 Subject: Re: [U2] import data to excel sheet... Hi Chris I would investigate Tony's product first - that may well save you a bunch of work. I'm generating OpenXML from a number of different places - so if your requirement is for UniVerse only I'd look to Nebula and see if it covers your needs. If you do find afterwards that you still want to look at OpenXML, it's reasonably simple to update and manage but you need to invest some time understanding the structure. Essentially, it's based on something called Open Packaging Convention which is a zipped archive under the hood - though not compatible will all ZIP programs :(. Within that archive, the content is divided into a number of separate 'parts' all of which link together to create a document - so a Word document will have separate parts for the main body, section headers and footers, media elements, embedded pictures etc. Once you have walked through that maze, the spreadsheet markup is simple as far as the data content is concerned: there are some gotchas when reading data (shared strings, for example) but you can generally avoid them all if you are the one generating it. All the content is separated from the styling, and held in a simple sheetData element for the chosen worksheet. Each worksheet is a separate XML doc within the archive. The difficulty is in adding new styles for formatting, simply because the format there is very verbose. Your best bet is to create a template, apply various styles within that template to dummy cells and save it. Then you have all the styling information ready to use and you can simply apply them to your cells by adding a style number attribute. It's worth noting that OpenXML was introduced as the standard format for Office 2007, but there is a free download from Microsoft called the Office Compatibility Pack that lets earlier versions of Office read and write in that format. I'd recommend the free eBook 'OpenXML Explained' by Wouter van Vugt. IIRC it's downloadable via www.openxmldeveloper.org. Brian ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users _ Hotmail is redefining busy with tools for the New Busy. Get more from your inbox. http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] import data to excel sheet...
It's worth mentioning that NebulaXLite does exactly what Brian describes, but without the developer needing to touch the XML. I manage all of that so that the developer just works with BASIC arrays containing data and style info. The process of creating a spreadsheet is similar to creating a report in a BASIC program, with header, detail, subtotals, and totals. And of course multiple spreadsheets are combined into a workbook (which you don't get with CSV exports). Also as Brian describes, the developer creates styles as normal dynamic arrays, with features like bold text, grey background, double borders on left and right - and then the style can easily be applied to any cells. The product also includes a utility that extracts styles from existing Excel documents. So you can design the visual aspects of your sheet visually, generate the styles, and then use these without having to manually create them (which is part of the learning process that David mentioned). I will note that this is a Lite product, and it does not (yet/ever?) allow BASIC to create documents with features that were not supported by the original SpreadsheetML schema. That includes charts, macros, and UI components. If there is enough demand I may create a new non-Lite product that has features supported by newer XML specs. HTH Tony Gravagno Nebula Research and Development TG@ remove.pleaseNebula-RnD.com remove.pleaseNebula-RnD.com/products/xlite.htm ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] import data to excel sheet...
Don't know if this helps, but UNIX generally provides a pair of utilities called dos2unix and unix2dos that handle the conversion. They can be used in scripts, etc. This is a function of the operating system. On Unix systems only a linefeed character is added to the end of a line whereas on Windows both carriage return and linefeed characters are added to the end of a line. If you open a file created on a Unix system with the notepad app it will have one line with strange characters in it. A quick fix, if you open the same file with word pad it will be converted and you can save it. If you created a file on a Unix system for use on a Windows system you have to add a carriage return character to the end of the line. Jerry Banker 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] import data to excel sheet...
one method I use is I create the file using on unix with just the lf, then I use zip to compress the file, converting the lf - lf/cr, then transfer the file to the windows system -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- boun...@listserver.u2ug.org] On Behalf Of jpb-u2ug Sent: Tuesday, April 13, 2010 1:49 AM To: 'U2 Users List' Subject: Re: [U2] import data to excel sheet... This is a function of the operating system. On Unix systems only a linefeed character is added to the end of a line whereas on Windows both carriage return and linefeed characters are added to the end of a line. If you open a file created on a Unix system with the notepad app it will have one line with strange characters in it. A quick fix, if you open the same file with word pad it will be converted and you can save it. If you created a file on a Unix system for use on a Windows system you have to add a carriage return character to the end of the line. Jerry Banker ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] import data to excel sheet...
I was curious if there is an easy way to add font formatting to the .CSV files I have been creating from UniVerse. Right now we just write to a sequential file and call it name.csv and just use comma's to separate the data. We then use a carriage return CHAR(13) to return to the next line in the spreadsheet. If I wanted to make a word appear as blue or in bold, how are you guys doing this? Thanks. -Chris Date: Mon, 12 Apr 2010 11:47:20 -0700 From: jacque...@yahoo.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... Hi Jaweed, If you already produced a number of files with the CHAR(13) as the end of line separator, you probably still handle them with excel, if you change the file extension from .csv to .txt and when you are in Excel import wizard, specify that the origin of the file is Macintosh.Since the Mac uses CHAR(13) as an EOL separator, the import should work. - Original Message From: Robert Houben robert.hou...@fwic.net To: U2 Users List u2-users@listserver.u2ug.org Sent: Mon, April 12, 2010 12:01:03 PM Subject: Re: [U2] import data to excel sheet... Hi Jaweed, I haven't looked too closely at your code, but I did notice on thing that I would consider the most likely candidate: CHAR(13) is a carriage return, not a line feed. Try using CHAR(10) instead for your LINE.FEED variable. You might have to use CHAR(13):CHAR(10), but I think CHAR(10) should do it. Good luck! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jaweed Sent: Monday, April 12, 2010 7:53 AM To: u2-users@listserver.u2ug.org Subject: [U2] import data to excel sheet... Can anyone tell me what is wrong with the following code for importing data from txt file to an excel sheet, when its launch Excel it giving some error message, but when I open the downloaded file from my disc, all the data is in one line with some especial Chars. Any help will be appreciated. 034: ** 035: * OPEN FILES * 036: ** 037: ERRMSG = '' 038: OPEN TEXT.FILE.NAME TO TEXT.FILE ELSE 039: ERRMSG = 'CANNOT OPEN THE ' : TEXT.FILE.NAME : ' FILE!' 040: END 041: IF ERRMSG THEN 042: GOSUB 91000 043: RETURN 044: END *--: P 045: ** 046: * INITIALIZE * 047: ** 048: PAGE.FEED = CHAR(12) 049: LINE.FEED = CHAR(13) 050: ;* 051: ;* Read the text file to convert 052: ;* 053: READ TEXT.REC FROM TEXT.FILE, TEXT.REC.NAME ELSE 054: ERRMSG = ITEM : TEXT.REC.NAME : DOES NOT EXIST IN : TEXT.FILE.NA ME 055: GOSUB 91000 056: RETURN 057: END 058: ;* 059: ;* Set default values (none are used now) 060: ;* 061: VALUE = DEFAULT 062: LOCATE PROPERTY IN FORMATSTR1 SETTING PTR THEN 063: VALUE = FORMATSTR2,PTR 064: END 065: ;* 066: ;* Remove the first line if it contains a PAGE.FEED *--: P 067: ;* 068: LOOP WHILE TEXT.REC1 = PAGE.FEED DO 069: TEXT.REC = DELETE(TEXT.REC,1) 070: REPEAT 071: IF TEXT.REC1[1,1] = PAGE.FEED THEN 072: TEXT.REC1 = TEXT.REC1[2,LEN(TEXT.REC)] 073: END 074: ;* 075: ;* Find the header for each page 076: ;* 077: SLK.REC = '' 078: SLK.ROWS = 0 079: SLK.HEADER.LINES = 0 080: IF TEXT.REC1[1,10] = RUN D - T: THEN 081: SLK.REC-1 = F;SD;R3 082: SLK.REC-1 = F;SD;R4 083: SLK.REC-1 = C;Y1;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[1,30],';',';; '))) 084: SLK.REC-1 = C;Y2;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[1,30],';',';; '))) 085: SLK.REC-1 = C;Y3;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[31,90],';','; ;'))) 086: SLK.REC-1 = C;Y4;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[31,90],';','; ;'))) 087: SLK.ROWS = 4 088: SLK.HEADER.LINES = 2 *--: P 089: END 090: ;* 091: ;* Find the number of lines in the header 092: ;* 093: LINES = DCOUNT(TEXT.REC,@AM) 094: SLK.HEADER.CNT = 0 095: FOR IDX = SLK.HEADER.LINES+1 TO LINES 096: IF INDEX(TEXT.RECIDX,---,1) THEN 097: SLK.HEADER.CNT = IDX 098: EXIT 099: END 100: NEXT IDX 101: ;* 102: ;* Find the column locations 103: ;* 104: COL.LIST = '' 105: IF SLK.HEADER.CNT THEN 106: DASHES.LINE = : TEXT.RECSLK.HEADER.CNT : 107: DASHES.LINE = TRIM(DASHES.LINE,'-') 108: DASHES.CNT = COUNT(DASHES.LINE,'-') 109: DASHES.LINE = TEXT.RECSLK.HEADER.CNT 110: FOR DASHES.IDX = 1 TO DASHES.CNT *--: P 111: COL.START = INDEX(DASHES.LINE,'-',1) 112: DASHES.REMAIN = LEN(DASHES.LINE) - COL.START + 1 113: COL.LEN = INDEX(DASHES.LINE[DASHES.REMAIN],' ',1)-1 114: IF COL.LEN 1 THEN COL.LEN = LEN(DASHES.LINE) 115: COL.LISTDASHES.IDX,1 = COL.START
Re: [U2] import data to excel sheet...
Chris I'm using OpenXML to create spreadsheets in Excel 2007 format: and writing a series in Spectrum about it. You can create the spreadsheets directly from UniVerse by treating the content as XML (with a number of caveats) or you can call out to something external that will build it using the OpenXML SDK 2.0 or an equivalent toolkit. Brian -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Chris Austin Sent: 13 April 2010 3:48 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... I was curious if there is an easy way to add font formatting to the .CSV files I have been creating from UniVerse. Right now we just write to a sequential file and call it name.csv and just use comma's to separate the data. We then use a carriage return CHAR(13) to return to the next line in the spreadsheet. If I wanted to make a word appear as blue or in bold, how are you guys doing this? Thanks. -Chris Date: Mon, 12 Apr 2010 11:47:20 -0700 From: jacque...@yahoo.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... Hi Jaweed, If you already produced a number of files with the CHAR(13) as the end of line separator, you probably still handle them with excel, if you change the file extension from .csv to .txt and when you are in Excel import wizard, specify that the origin of the file is Macintosh.Since the Mac uses CHAR(13) as an EOL separator, the import should work. - Original Message From: Robert Houben robert.hou...@fwic.net To: U2 Users List u2-users@listserver.u2ug.org Sent: Mon, April 12, 2010 12:01:03 PM Subject: Re: [U2] import data to excel sheet... Hi Jaweed, I haven't looked too closely at your code, but I did notice on thing that I would consider the most likely candidate: CHAR(13) is a carriage return, not a line feed. Try using CHAR(10) instead for your LINE.FEED variable. You might have to use CHAR(13):CHAR(10), but I think CHAR(10) should do it. Good luck! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jaweed Sent: Monday, April 12, 2010 7:53 AM To: u2-users@listserver.u2ug.org Subject: [U2] import data to excel sheet... Can anyone tell me what is wrong with the following code for importing data from txt file to an excel sheet, when its launch Excel it giving some error message, but when I open the downloaded file from my disc, all the data is in one line with some especial Chars. Any help will be appreciated. 034: ** 035: * OPEN FILES * 036: ** 037: ERRMSG = '' 038: OPEN TEXT.FILE.NAME TO TEXT.FILE ELSE 039: ERRMSG = 'CANNOT OPEN THE ' : TEXT.FILE.NAME : ' FILE!' 040: END 041: IF ERRMSG THEN 042: GOSUB 91000 043: RETURN 044: END *--: P 045: ** 046: * INITIALIZE * 047: ** 048: PAGE.FEED = CHAR(12) 049: LINE.FEED = CHAR(13) 050: ;* 051: ;* Read the text file to convert 052: ;* 053: READ TEXT.REC FROM TEXT.FILE, TEXT.REC.NAME ELSE 054: ERRMSG = ITEM : TEXT.REC.NAME : DOES NOT EXIST IN : TEXT.FILE.NA ME 055: GOSUB 91000 056: RETURN 057: END 058: ;* 059: ;* Set default values (none are used now) 060: ;* 061: VALUE = DEFAULT 062: LOCATE PROPERTY IN FORMATSTR1 SETTING PTR THEN 063: VALUE = FORMATSTR2,PTR 064: END 065: ;* 066: ;* Remove the first line if it contains a PAGE.FEED *--: P 067: ;* 068: LOOP WHILE TEXT.REC1 = PAGE.FEED DO 069: TEXT.REC = DELETE(TEXT.REC,1) 070: REPEAT 071: IF TEXT.REC1[1,1] = PAGE.FEED THEN 072: TEXT.REC1 = TEXT.REC1[2,LEN(TEXT.REC)] 073: END 074: ;* 075: ;* Find the header for each page 076: ;* 077: SLK.REC = '' 078: SLK.ROWS = 0 079: SLK.HEADER.LINES = 0 080: IF TEXT.REC1[1,10] = RUN D - T: THEN 081: SLK.REC-1 = F;SD;R3 082: SLK.REC-1 = F;SD;R4 083: SLK.REC-1 = C;Y1;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[1,30],';',';; '))) 084: SLK.REC-1 = C;Y2;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[1,30],';',';; '))) 085: SLK.REC-1 = C;Y3;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[31,90],';','; ;'))) 086: SLK.REC-1 = C;Y4;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[31,90],';','; ;'))) 087: SLK.ROWS = 4 088: SLK.HEADER.LINES = 2 *--: P 089: END 090: ;* 091: ;* Find the number of lines in the header 092: ;* 093: LINES = DCOUNT(TEXT.REC,@AM) 094: SLK.HEADER.CNT = 0 095: FOR IDX = SLK.HEADER.LINES+1 TO LINES 096: IF INDEX(TEXT.RECIDX,---,1) THEN 097: SLK.HEADER.CNT = IDX 098: EXIT 099: END 100: NEXT IDX 101: ;* 102: ;* Find the column locations 103: ;* 104: COL.LIST
Re: [U2] import data to excel sheet...
also see: http://blogs.msdn.com/brian_jones/archive/2005/06/27/433152.aspx -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- boun...@listserver.u2ug.org] On Behalf Of Brian Leach Sent: Tuesday, April 13, 2010 11:45 AM To: 'U2 Users List' Subject: Re: [U2] import data to excel sheet... Chris I'm using OpenXML to create spreadsheets in Excel 2007 format: and writing a series in Spectrum about it. You can create the spreadsheets directly from UniVerse by treating the content as XML (with a number of caveats) or you can call out to something external that will build it using the OpenXML SDK 2.0 or an equivalent toolkit. Brian -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Chris Austin Sent: 13 April 2010 3:48 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... I was curious if there is an easy way to add font formatting to the .CSV files I have been creating from UniVerse. Right now we just write to a sequential file and call it name.csv and just use comma's to separate the data. We then use a carriage return CHAR(13) to return to the next line in the spreadsheet. If I wanted to make a word appear as blue or in bold, how are you guys doing this? Thanks. -Chris Date: Mon, 12 Apr 2010 11:47:20 -0700 From: jacque...@yahoo.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... Hi Jaweed, If you already produced a number of files with the CHAR(13) as the end of line separator, you probably still handle them with excel, if you change the file extension from .csv to .txt and when you are in Excel import wizard, specify that the origin of the file is Macintosh.Since the Mac uses CHAR(13) as an EOL separator, the import should work. - Original Message From: Robert Houben robert.hou...@fwic.net To: U2 Users List u2-users@listserver.u2ug.org Sent: Mon, April 12, 2010 12:01:03 PM Subject: Re: [U2] import data to excel sheet... Hi Jaweed, I haven't looked too closely at your code, but I did notice on thing that I would consider the most likely candidate: CHAR(13) is a carriage return, not a line feed. Try using CHAR(10) instead for your LINE.FEED variable. You might have to use CHAR(13):CHAR(10), but I think CHAR(10) should do it. Good luck! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jaweed Sent: Monday, April 12, 2010 7:53 AM To: u2-users@listserver.u2ug.org Subject: [U2] import data to excel sheet... Can anyone tell me what is wrong with the following code for importing data from txt file to an excel sheet, when its launch Excel it giving some error message, but when I open the downloaded file from my disc, all the data is in one line with some especial Chars. Any help will be appreciated. 034: ** 035: * OPEN FILES * 036: ** 037: ERRMSG = '' 038: OPEN TEXT.FILE.NAME TO TEXT.FILE ELSE 039: ERRMSG = 'CANNOT OPEN THE ' : TEXT.FILE.NAME : ' FILE!' 040: END 041: IF ERRMSG THEN 042: GOSUB 91000 043: RETURN 044: END *--: P 045: ** 046: * INITIALIZE * 047: ** 048: PAGE.FEED = CHAR(12) 049: LINE.FEED = CHAR(13) 050: ;* 051: ;* Read the text file to convert 052: ;* 053: READ TEXT.REC FROM TEXT.FILE, TEXT.REC.NAME ELSE 054: ERRMSG = ITEM : TEXT.REC.NAME : DOES NOT EXIST IN : TEXT.FILE.NA ME 055: GOSUB 91000 056: RETURN 057: END 058: ;* 059: ;* Set default values (none are used now) 060: ;* 061: VALUE = DEFAULT 062: LOCATE PROPERTY IN FORMATSTR1 SETTING PTR THEN 063: VALUE = FORMATSTR2,PTR 064: END 065: ;* 066: ;* Remove the first line if it contains a PAGE.FEED *--: P 067: ;* 068: LOOP WHILE TEXT.REC1 = PAGE.FEED DO 069: TEXT.REC = DELETE(TEXT.REC,1) 070: REPEAT 071: IF TEXT.REC1[1,1] = PAGE.FEED THEN 072: TEXT.REC1 = TEXT.REC1[2,LEN(TEXT.REC)] 073: END 074: ;* 075: ;* Find the header for each page 076: ;* 077: SLK.REC = '' 078: SLK.ROWS = 0 079: SLK.HEADER.LINES = 0 080: IF TEXT.REC1[1,10] = RUN D - T: THEN 081: SLK.REC-1 = F;SD;R3 082: SLK.REC-1 = F;SD;R4 083: SLK.REC-1 = C;Y1;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[1,30],';',';; '))) 084: SLK.REC-1 = C;Y2;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[1,30],';',';; '))) 085: SLK.REC-1 = C;Y3;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[31,90],';','; ;'))) 086: SLK.REC-1 = C;Y4;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[31,90],';','; ;'))) 087
Re: [U2] import data to excel sheet...
Brian, I was curious if you had an example of how you can setup your spreadsheet directly from UniVerse by treating the content as XML? Are you physically writing to a .XLS type file from universe but in an XML format? Also, I wouldn't mind looking into OpenXML, that sounds like it may be very useful for our needs here. I appreciate the response. -Chris From: br...@brianleach.co.uk To: u2-users@listserver.u2ug.org Date: Tue, 13 Apr 2010 16:44:31 +0100 Subject: Re: [U2] import data to excel sheet... Chris I'm using OpenXML to create spreadsheets in Excel 2007 format: and writing a series in Spectrum about it. You can create the spreadsheets directly from UniVerse by treating the content as XML (with a number of caveats) or you can call out to something external that will build it using the OpenXML SDK 2.0 or an equivalent toolkit. Brian -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Chris Austin Sent: 13 April 2010 3:48 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... I was curious if there is an easy way to add font formatting to the .CSV files I have been creating from UniVerse. Right now we just write to a sequential file and call it name.csv and just use comma's to separate the data. We then use a carriage return CHAR(13) to return to the next line in the spreadsheet. If I wanted to make a word appear as blue or in bold, how are you guys doing this? Thanks. -Chris Date: Mon, 12 Apr 2010 11:47:20 -0700 From: jacque...@yahoo.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... Hi Jaweed, If you already produced a number of files with the CHAR(13) as the end of line separator, you probably still handle them with excel, if you change the file extension from .csv to .txt and when you are in Excel import wizard, specify that the origin of the file is Macintosh.Since the Mac uses CHAR(13) as an EOL separator, the import should work. - Original Message From: Robert Houben robert.hou...@fwic.net To: U2 Users List u2-users@listserver.u2ug.org Sent: Mon, April 12, 2010 12:01:03 PM Subject: Re: [U2] import data to excel sheet... Hi Jaweed, I haven't looked too closely at your code, but I did notice on thing that I would consider the most likely candidate: CHAR(13) is a carriage return, not a line feed. Try using CHAR(10) instead for your LINE.FEED variable. You might have to use CHAR(13):CHAR(10), but I think CHAR(10) should do it. Good luck! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jaweed Sent: Monday, April 12, 2010 7:53 AM To: u2-users@listserver.u2ug.org Subject: [U2] import data to excel sheet... Can anyone tell me what is wrong with the following code for importing data from txt file to an excel sheet, when its launch Excel it giving some error message, but when I open the downloaded file from my disc, all the data is in one line with some especial Chars. Any help will be appreciated. 034: ** 035: * OPEN FILES * 036: ** 037: ERRMSG = '' 038: OPEN TEXT.FILE.NAME TO TEXT.FILE ELSE 039: ERRMSG = 'CANNOT OPEN THE ' : TEXT.FILE.NAME : ' FILE!' 040: END 041: IF ERRMSG THEN 042: GOSUB 91000 043: RETURN 044: END *--: P 045: ** 046: * INITIALIZE * 047: ** 048: PAGE.FEED = CHAR(12) 049: LINE.FEED = CHAR(13) 050: ;* 051: ;* Read the text file to convert 052: ;* 053: READ TEXT.REC FROM TEXT.FILE, TEXT.REC.NAME ELSE 054: ERRMSG = ITEM : TEXT.REC.NAME : DOES NOT EXIST IN : TEXT.FILE.NA ME 055: GOSUB 91000 056: RETURN 057: END 058: ;* 059: ;* Set default values (none are used now) 060: ;* 061: VALUE = DEFAULT 062: LOCATE PROPERTY IN FORMATSTR1 SETTING PTR THEN 063: VALUE = FORMATSTR2,PTR 064: END 065: ;* 066: ;* Remove the first line if it contains a PAGE.FEED *--: P 067: ;* 068: LOOP WHILE TEXT.REC1 = PAGE.FEED DO 069: TEXT.REC = DELETE(TEXT.REC,1) 070: REPEAT 071: IF TEXT.REC1[1,1] = PAGE.FEED THEN 072: TEXT.REC1 = TEXT.REC1[2,LEN(TEXT.REC)] 073: END 074: ;* 075: ;* Find the header for each page 076: ;* 077: SLK.REC = '' 078: SLK.ROWS = 0 079: SLK.HEADER.LINES = 0 080: IF TEXT.REC1[1,10] = RUN D - T: THEN 081: SLK.REC-1 = F;SD;R3 082: SLK.REC-1 = F;SD;R4 083: SLK.REC-1 = C;Y1;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[1,30],';',';; '))) 084: SLK.REC-1 = C;Y2;X1;K
Re: [U2] import data to excel sheet...
Chris: You may want to simplify your life and pick up NebulaXLite. http://nebula-rnd.com/products/xlite.htm HTH, Bill Chris Austin said the following on 4/13/2010 7:48 AM: I was curious if there is an easy way to add font formatting to the .CSV files I have been creating from UniVerse. Right now we just write to a sequential file and call it name.csv and just use comma's to separate the data. We then use a carriage return CHAR(13) to return to the next line in the spreadsheet. If I wanted to make a word appear as blue or in bold, how are you guys doing this? Thanks. -Chris Date: Mon, 12 Apr 2010 11:47:20 -0700 From: jacque...@yahoo.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... Hi Jaweed, If you already produced a number of files with the CHAR(13) as the end of line separator, you probably still handle them with excel, if you change the file extension from .csv to .txt and when you are in Excel import wizard, specify that the origin of the file is Macintosh.Since the Mac uses CHAR(13) as an EOL separator, the import should work. - Original Message From: Robert Houben robert.hou...@fwic.net To: U2 Users List u2-users@listserver.u2ug.org Sent: Mon, April 12, 2010 12:01:03 PM Subject: Re: [U2] import data to excel sheet... Hi Jaweed, I haven't looked too closely at your code, but I did notice on thing that I would consider the most likely candidate: CHAR(13) is a carriage return, not a line feed. Try using CHAR(10) instead for your LINE.FEED variable. You might have to use CHAR(13):CHAR(10), but I think CHAR(10) should do it. Good luck! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jaweed Sent: Monday, April 12, 2010 7:53 AM To: u2-users@listserver.u2ug.org Subject: [U2] import data to excel sheet... Can anyone tell me what is wrong with the following code for importing data from txt file to an excel sheet, when its launch Excel it giving some error message, but when I open the downloaded file from my disc, all the data is in one line with some especial Chars. Any help will be appreciated. 034: ** 035: * OPEN FILES * 036: ** 037: ERRMSG = '' 038: OPEN TEXT.FILE.NAME TO TEXT.FILE ELSE 039: ERRMSG = 'CANNOT OPEN THE ' : TEXT.FILE.NAME : ' FILE!' 040: END 041: IF ERRMSG THEN 042: GOSUB 91000 043: RETURN 044: END *--: P 045: ** 046: * INITIALIZE * 047: ** 048: PAGE.FEED = CHAR(12) 049: LINE.FEED = CHAR(13) 050: ;* 051: ;* Read the text file to convert 052: ;* 053: READ TEXT.REC FROM TEXT.FILE, TEXT.REC.NAME ELSE 054: ERRMSG = ITEM : TEXT.REC.NAME : DOES NOT EXIST IN : TEXT.FILE.NA ME 055: GOSUB 91000 056: RETURN 057: END 058: ;* 059: ;* Set default values (none are used now) 060: ;* 061: VALUE = DEFAULT 062: LOCATE PROPERTY IN FORMATSTR1 SETTING PTR THEN 063: VALUE = FORMATSTR2,PTR 064: END 065: ;* 066: ;* Remove the first line if it contains a PAGE.FEED *--: P 067: ;* 068: LOOP WHILE TEXT.REC1 = PAGE.FEED DO 069: TEXT.REC = DELETE(TEXT.REC,1) 070: REPEAT 071: IF TEXT.REC1[1,1] = PAGE.FEED THEN 072: TEXT.REC1 = TEXT.REC1[2,LEN(TEXT.REC)] 073: END 074: ;* 075: ;* Find the header for each page 076: ;* 077: SLK.REC = '' 078: SLK.ROWS = 0 079: SLK.HEADER.LINES = 0 080: IF TEXT.REC1[1,10] = RUN D - T: THEN 081: SLK.REC-1 = F;SD;R3 082: SLK.REC-1 = F;SD;R4 083: SLK.REC-1 = C;Y1;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[1,30],';',';; '))) 084: SLK.REC-1 = C;Y2;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[1,30],';',';; '))) 085: SLK.REC-1 = C;Y3;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[31,90],';','; ;'))) 086: SLK.REC-1 = C;Y4;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[31,90],';','; ;'))) 087: SLK.ROWS = 4 088: SLK.HEADER.LINES = 2 *--: P 089: END 090: ;* 091: ;* Find the number of lines in the header 092: ;* 093: LINES = DCOUNT(TEXT.REC,@AM) 094: SLK.HEADER.CNT = 0 095: FOR IDX = SLK.HEADER.LINES+1 TO LINES 096: IF INDEX(TEXT.RECIDX,---,1) THEN 097: SLK.HEADER.CNT = IDX 098: EXIT 099: END 100: NEXT IDX 101: ;* 102: ;* Find the column locations 103: ;* 104: COL.LIST = '' 105: IF SLK.HEADER.CNT THEN 106: DASHES.LINE = : TEXT.RECSLK.HEADER.CNT : 107: DASHES.LINE = TRIM(DASHES.LINE,'-') 108: DASHES.CNT = COUNT(DASHES.LINE,'-') 109: DASHES.LINE = TEXT.RECSLK.HEADER.CNT 110: FOR DASHES.IDX = 1 TO DASHES.CNT *--: P 111: COL.START = INDEX(DASHES.LINE,'-',1) 112: DASHES.REMAIN = LEN(DASHES.LINE) - COL.START + 1 113: COL.LEN = INDEX(DASHES.LINE[DASHES.REMAIN],' ',1)-1 114: IF COL.LEN 1 THEN COL.LEN = LEN(DASHES.LINE) 115
Re: [U2] import data to excel sheet...
Gerorge, Thanks a ton! I think I will try to get OpenXML working now that I see how the XML documents are built up and make up the spreadsheet. It just seems very tedious to manually create the XML files. Now as far as getting OpenXML to work with universe, is there any documentation out there for that? I know Brian mentioned he was writing an article, I would be very inclined to read it. -Chris From: ggal...@wyanokegroup.com To: u2-users@listserver.u2ug.org Date: Tue, 13 Apr 2010 10:47:58 -0500 Subject: Re: [U2] import data to excel sheet... also see: http://blogs.msdn.com/brian_jones/archive/2005/06/27/433152.aspx -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- boun...@listserver.u2ug.org] On Behalf Of Brian Leach Sent: Tuesday, April 13, 2010 11:45 AM To: 'U2 Users List' Subject: Re: [U2] import data to excel sheet... Chris I'm using OpenXML to create spreadsheets in Excel 2007 format: and writing a series in Spectrum about it. You can create the spreadsheets directly from UniVerse by treating the content as XML (with a number of caveats) or you can call out to something external that will build it using the OpenXML SDK 2.0 or an equivalent toolkit. Brian -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Chris Austin Sent: 13 April 2010 3:48 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... I was curious if there is an easy way to add font formatting to the .CSV files I have been creating from UniVerse. Right now we just write to a sequential file and call it name.csv and just use comma's to separate the data. We then use a carriage return CHAR(13) to return to the next line in the spreadsheet. If I wanted to make a word appear as blue or in bold, how are you guys doing this? Thanks. -Chris Date: Mon, 12 Apr 2010 11:47:20 -0700 From: jacque...@yahoo.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... Hi Jaweed, If you already produced a number of files with the CHAR(13) as the end of line separator, you probably still handle them with excel, if you change the file extension from .csv to .txt and when you are in Excel import wizard, specify that the origin of the file is Macintosh.Since the Mac uses CHAR(13) as an EOL separator, the import should work. - Original Message From: Robert Houben robert.hou...@fwic.net To: U2 Users List u2-users@listserver.u2ug.org Sent: Mon, April 12, 2010 12:01:03 PM Subject: Re: [U2] import data to excel sheet... Hi Jaweed, I haven't looked too closely at your code, but I did notice on thing that I would consider the most likely candidate: CHAR(13) is a carriage return, not a line feed. Try using CHAR(10) instead for your LINE.FEED variable. You might have to use CHAR(13):CHAR(10), but I think CHAR(10) should do it. Good luck! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jaweed Sent: Monday, April 12, 2010 7:53 AM To: u2-users@listserver.u2ug.org Subject: [U2] import data to excel sheet... Can anyone tell me what is wrong with the following code for importing data from txt file to an excel sheet, when its launch Excel it giving some error message, but when I open the downloaded file from my disc, all the data is in one line with some especial Chars. Any help will be appreciated. 034: ** 035: * OPEN FILES * 036: ** 037: ERRMSG = '' 038: OPEN TEXT.FILE.NAME TO TEXT.FILE ELSE 039: ERRMSG = 'CANNOT OPEN THE ' : TEXT.FILE.NAME : ' FILE!' 040: END 041: IF ERRMSG THEN 042: GOSUB 91000 043: RETURN 044: END *--: P 045: ** 046: * INITIALIZE * 047: ** 048: PAGE.FEED = CHAR(12) 049: LINE.FEED = CHAR(13) 050: ;* 051: ;* Read the text file to convert 052: ;* 053: READ TEXT.REC FROM TEXT.FILE, TEXT.REC.NAME ELSE 054: ERRMSG = ITEM : TEXT.REC.NAME : DOES NOT EXIST IN : TEXT.FILE.NA ME 055: GOSUB 91000 056: RETURN 057: END 058: ;* 059: ;* Set default values (none are used now) 060: ;* 061: VALUE = DEFAULT 062: LOCATE PROPERTY IN FORMATSTR1 SETTING PTR THEN 063: VALUE = FORMATSTR2,PTR 064: END 065: ;* 066: ;* Remove the first line if it contains a PAGE.FEED *--: P 067: ;* 068: LOOP WHILE TEXT.REC1 = PAGE.FEED DO 069: TEXT.REC = DELETE(TEXT.REC,1) 070: REPEAT
Re: [U2] import data to excel sheet...
That program actually seems perfect for what I'm looking to do Bill. Do you guys use this at your work? Is it a decent product? -Chris Date: Tue, 13 Apr 2010 09:11:25 -0700 From: wphask...@advantos.net To: U2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... Chris: You may want to simplify your life and pick up NebulaXLite. http://nebula-rnd.com/products/xlite.htm HTH, Bill Chris Austin said the following on 4/13/2010 7:48 AM: I was curious if there is an easy way to add font formatting to the .CSV files I have been creating from UniVerse. Right now we just write to a sequential file and call it name.csv and just use comma's to separate the data. We then use a carriage return CHAR(13) to return to the next line in the spreadsheet. If I wanted to make a word appear as blue or in bold, how are you guys doing this? Thanks. -Chris Date: Mon, 12 Apr 2010 11:47:20 -0700 From: jacque...@yahoo.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... Hi Jaweed, If you already produced a number of files with the CHAR(13) as the end of line separator, you probably still handle them with excel, if you change the file extension from .csv to .txt and when you are in Excel import wizard, specify that the origin of the file is Macintosh.Since the Mac uses CHAR(13) as an EOL separator, the import should work. - Original Message From: Robert Houben robert.hou...@fwic.net To: U2 Users List u2-users@listserver.u2ug.org Sent: Mon, April 12, 2010 12:01:03 PM Subject: Re: [U2] import data to excel sheet... Hi Jaweed, I haven't looked too closely at your code, but I did notice on thing that I would consider the most likely candidate: CHAR(13) is a carriage return, not a line feed. Try using CHAR(10) instead for your LINE.FEED variable. You might have to use CHAR(13):CHAR(10), but I think CHAR(10) should do it. Good luck! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jaweed Sent: Monday, April 12, 2010 7:53 AM To: u2-users@listserver.u2ug.org Subject: [U2] import data to excel sheet... Can anyone tell me what is wrong with the following code for importing data from txt file to an excel sheet, when its launch Excel it giving some error message, but when I open the downloaded file from my disc, all the data is in one line with some especial Chars. Any help will be appreciated. 034: ** 035: * OPEN FILES * 036: ** 037: ERRMSG = '' 038: OPEN TEXT.FILE.NAME TO TEXT.FILE ELSE 039: ERRMSG = 'CANNOT OPEN THE ' : TEXT.FILE.NAME : ' FILE!' 040: END 041: IF ERRMSG THEN 042: GOSUB 91000 043: RETURN 044: END *--: P 045: ** 046: * INITIALIZE * 047: ** 048: PAGE.FEED = CHAR(12) 049: LINE.FEED = CHAR(13) 050: ;* 051: ;* Read the text file to convert 052: ;* 053: READ TEXT.REC FROM TEXT.FILE, TEXT.REC.NAME ELSE 054: ERRMSG = ITEM : TEXT.REC.NAME : DOES NOT EXIST IN : TEXT.FILE.NA ME 055: GOSUB 91000 056: RETURN 057: END 058: ;* 059: ;* Set default values (none are used now) 060: ;* 061: VALUE = DEFAULT 062: LOCATE PROPERTY IN FORMATSTR1 SETTING PTR THEN 063: VALUE = FORMATSTR2,PTR 064: END 065: ;* 066: ;* Remove the first line if it contains a PAGE.FEED *--: P 067: ;* 068: LOOP WHILE TEXT.REC1 = PAGE.FEED DO 069: TEXT.REC = DELETE(TEXT.REC,1) 070: REPEAT 071: IF TEXT.REC1[1,1] = PAGE.FEED THEN 072: TEXT.REC1 = TEXT.REC1[2,LEN(TEXT.REC)] 073: END 074: ;* 075: ;* Find the header for each page 076: ;* 077: SLK.REC = '' 078: SLK.ROWS = 0 079: SLK.HEADER.LINES = 0 080: IF TEXT.REC1[1,10] = RUN D - T: THEN 081: SLK.REC-1 = F;SD;R3 082: SLK.REC-1 = F;SD;R4 083: SLK.REC-1 = C;Y1;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[1,30],';',';; '))) 084: SLK.REC-1 = C;Y2;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[1,30],';',';; '))) 085: SLK.REC-1 = C;Y3;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[31,90],';','; ;'))) 086: SLK.REC-1 = C;Y4;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[31,90],';','; ;'))) 087: SLK.ROWS = 4 088: SLK.HEADER.LINES = 2 *--: P 089: END 090: ;* 091: ;* Find the number of lines in the header 092: ;* 093: LINES = DCOUNT(TEXT.REC,@AM) 094: SLK.HEADER.CNT = 0 095: FOR IDX = SLK.HEADER.LINES+1 TO LINES 096: IF INDEX(TEXT.RECIDX,---,1) THEN 097: SLK.HEADER.CNT = IDX 098: EXIT
Re: [U2] import data to excel sheet...
I use an Excel template with a VBA macro to do the formatting - fonts, colours, totals and so on. I start the template within the Basic program after the csv-file has been written to the user's temp directory on the c: drive. The macro automatically loads the csv-file, formats it, then copies the formatted worksheet into an Excel file and exits without saving itself. I use this technique for some fancy printing (i.e. fit report on one page) as well. The main disadvantage is that the user needs macros enabled in Excel to do this. Probably not the most elegant way, but what can you do if you still have to work with UD 5.2? Well, at least I have SB+ and that has some standard Basic subroutines to do most of the work for me. Mecki Chris Austin wrote: I was curious if there is an easy way to add font formatting to the .CSV files I have been creating from UniVerse. Right now we just write to a sequential file and call it name.csv and just use comma's to separate the data. We then use a carriage return CHAR(13) to return to the next line in the spreadsheet. If I wanted to make a word appear as blue or in bold, how are you guys doing this? Thanks. -Chris Date: Mon, 12 Apr 2010 11:47:20 -0700 From: jacque...@yahoo.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... Hi Jaweed, If you already produced a number of files with the CHAR(13) as the end of line separator, you probably still handle them with excel, if you change the file extension from .csv to .txt and when you are in Excel import wizard, specify that the origin of the file is Macintosh.Since the Mac uses CHAR(13) as an EOL separator, the import should work. - Original Message From: Robert Houben robert.hou...@fwic.net To: U2 Users List u2-users@listserver.u2ug.org Sent: Mon, April 12, 2010 12:01:03 PM Subject: Re: [U2] import data to excel sheet... Hi Jaweed, I haven't looked too closely at your code, but I did notice on thing that I would consider the most likely candidate: CHAR(13) is a carriage return, not a line feed. Try using CHAR(10) instead for your LINE.FEED variable. You might have to use CHAR(13):CHAR(10), but I think CHAR(10) should do it. Good luck! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jaweed Sent: Monday, April 12, 2010 7:53 AM To: u2-users@listserver.u2ug.org Subject: [U2] import data to excel sheet... Can anyone tell me what is wrong with the following code for importing data from txt file to an excel sheet, when its launch Excel it giving some error message, but when I open the downloaded file from my disc, all the data is in one line with some especial Chars. Any help will be appreciated. 034: ** 035: * OPEN FILES * 036: ** 037: ERRMSG = '' 038: OPEN TEXT.FILE.NAME TO TEXT.FILE ELSE 039: ERRMSG = 'CANNOT OPEN THE ' : TEXT.FILE.NAME : ' FILE!' 040: END 041: IF ERRMSG THEN 042: GOSUB 91000 043: RETURN 044: END *--: P 045: ** 046: * INITIALIZE * 047: ** 048: PAGE.FEED = CHAR(12) 049: LINE.FEED = CHAR(13) 050: ;* 051: ;* Read the text file to convert 052: ;* 053: READ TEXT.REC FROM TEXT.FILE, TEXT.REC.NAME ELSE 054: ERRMSG = ITEM : TEXT.REC.NAME : DOES NOT EXIST IN : TEXT.FILE.NA ME 055: GOSUB 91000 056: RETURN 057: END 058: ;* 059: ;* Set default values (none are used now) 060: ;* 061: VALUE = DEFAULT 062: LOCATE PROPERTY IN FORMATSTR1 SETTING PTR THEN 063: VALUE = FORMATSTR2,PTR 064: END 065: ;* 066: ;* Remove the first line if it contains a PAGE.FEED *--: P 067: ;* 068: LOOP WHILE TEXT.REC1 = PAGE.FEED DO 069: TEXT.REC = DELETE(TEXT.REC,1) 070: REPEAT 071: IF TEXT.REC1[1,1] = PAGE.FEED THEN 072: TEXT.REC1 = TEXT.REC1[2,LEN(TEXT.REC)] 073: END 074: ;* 075: ;* Find the header for each page 076: ;* 077: SLK.REC = '' 078: SLK.ROWS = 0 079: SLK.HEADER.LINES = 0 080: IF TEXT.REC1[1,10] = RUN D - T: THEN 081: SLK.REC-1 = F;SD;R3 082: SLK.REC-1 = F;SD;R4 083: SLK.REC-1 = C;Y1;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[1,30],';',';; '))) 084: SLK.REC-1 = C;Y2;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[1,30],';',';; '))) 085: SLK.REC-1 = C;Y3;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[31,90],';','; ;'))) 086: SLK.REC-1 = C;Y4;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[31,90],';','; ;'))) 087: SLK.ROWS = 4 088: SLK.HEADER.LINES = 2 *--: P 089: END 090: ;* 091: ;* Find the number of lines in the header 092: ;* 093: LINES = DCOUNT(TEXT.REC,@AM) 094: SLK.HEADER.CNT = 0 095: FOR IDX = SLK.HEADER.LINES+1 TO LINES 096: IF INDEX(TEXT.RECIDX,---,1) THEN 097: SLK.HEADER.CNT = IDX 098: EXIT 099: END 100
Re: [U2] import data to excel sheet...
my only problem was I was trying to get it to work with Excel 2000, which has a very limited XML ability. But anything above 2003 if fairly XML compliant. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- boun...@listserver.u2ug.org] On Behalf Of Chris Austin Sent: Tuesday, April 13, 2010 12:03 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... Gerorge, Thanks a ton! I think I will try to get OpenXML working now that I see how the XML documents are built up and make up the spreadsheet. It just seems very tedious to manually create the XML files. Now as far as getting OpenXML to work with universe, is there any documentation out there for that? I know Brian mentioned he was writing an article, I would be very inclined to read it. -Chris ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] import data to excel sheet... [ad]
From: Bill Haskett You may want to simplify your life and pick up NebulaXLite. http://nebula-rnd.com/products/xlite.htm From: Chris Austin That program actually seems perfect for what I'm looking to do Bill. Do you guys use this at your work? Is it a decent product? Thanks for the reference, Bill. As the author of NebulaXLite I'll summarize a couple key points: - We have lots of U2 sites running it. The most common response we get is Management loves it. - It's a Lite little utility that just works. Frankly I'm hesitent to make too many more changes. - It generates spec-compliant docs that can be used with Excel, OpenOffice, and Google Spreadsheets. - It's completely free for developers and VARs. - It's only US$200 for production servers - that's it. - Whether for development or production, you get free support and all upgrades and fixes are also free. There are no maintenance fees, add-ons, connectivity licenses, ads, or up-sells. I encourage you to download and install the software, get it activated, and contact us with comments and questions. You have nothing to lose. I'm hoping some of our developers and end-users can comment here on their own experience, perhaps if only to verify my claims. I look forward to hearing from you. Tony Gravagno Nebula Research and Development TG@ remove.pleaseNebula-RnD.com Nebula RD sells Pick/MultiValue products worldwide, and provides related development services remove.pleaseNebula-RnD.com/blog Visit PickWiki.com! Contribute! http://Twitter.com/TonyGravagno ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] import data to excel sheet...
Office 2007 and above stores its data in xml files now. .xls files are old. All you need is to lookup the xml spec on msdn and write out to it in databasic. There are heaps of resources on the xml format - just google it. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Chris Austin Sent: 13 April 2010 16:54 To: u2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... Brian, I was curious if you had an example of how you can setup your spreadsheet directly from UniVerse by treating the content as XML? Are you physically writing to a .XLS type file from universe but in an XML format? Also, I wouldn't mind looking into OpenXML, that sounds like it may be very useful for our needs here. I appreciate the response. -Chris From: br...@brianleach.co.uk To: u2-users@listserver.u2ug.org Date: Tue, 13 Apr 2010 16:44:31 +0100 Subject: Re: [U2] import data to excel sheet... Chris I'm using OpenXML to create spreadsheets in Excel 2007 format: and writing a series in Spectrum about it. You can create the spreadsheets directly from UniVerse by treating the content as XML (with a number of caveats) or you can call out to something external that will build it using the OpenXML SDK 2.0 or an equivalent toolkit. Brian -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Chris Austin Sent: 13 April 2010 3:48 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... I was curious if there is an easy way to add font formatting to the .CSV files I have been creating from UniVerse. Right now we just write to a sequential file and call it name.csv and just use comma's to separate the data. We then use a carriage return CHAR(13) to return to the next line in the spreadsheet. If I wanted to make a word appear as blue or in bold, how are you guys doing this? Thanks. -Chris Date: Mon, 12 Apr 2010 11:47:20 -0700 From: jacque...@yahoo.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... Hi Jaweed, If you already produced a number of files with the CHAR(13) as the end of line separator, you probably still handle them with excel, if you change the file extension from .csv to .txt and when you are in Excel import wizard, specify that the origin of the file is Macintosh.Since the Mac uses CHAR(13) as an EOL separator, the import should work. - Original Message From: Robert Houben robert.hou...@fwic.net To: U2 Users List u2-users@listserver.u2ug.org Sent: Mon, April 12, 2010 12:01:03 PM Subject: Re: [U2] import data to excel sheet... Hi Jaweed, I haven't looked too closely at your code, but I did notice on thing that I would consider the most likely candidate: CHAR(13) is a carriage return, not a line feed. Try using CHAR(10) instead for your LINE.FEED variable. You might have to use CHAR(13):CHAR(10), but I think CHAR(10) should do it. Good luck! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jaweed Sent: Monday, April 12, 2010 7:53 AM To: u2-users@listserver.u2ug.org Subject: [U2] import data to excel sheet... Can anyone tell me what is wrong with the following code for importing data from txt file to an excel sheet, when its launch Excel it giving some error message, but when I open the downloaded file from my disc, all the data is in one line with some especial Chars. Any help will be appreciated. 034: ** 035: * OPEN FILES * 036: ** 037: ERRMSG = '' 038: OPEN TEXT.FILE.NAME TO TEXT.FILE ELSE 039: ERRMSG = 'CANNOT OPEN THE ' : TEXT.FILE.NAME : ' FILE!' 040: END 041: IF ERRMSG THEN 042: GOSUB 91000 043: RETURN 044: END *--: P 045: ** 046: * INITIALIZE * 047: ** 048: PAGE.FEED = CHAR(12) 049: LINE.FEED = CHAR(13) 050: ;* 051: ;* Read the text file to convert 052: ;* 053: READ TEXT.REC FROM TEXT.FILE, TEXT.REC.NAME ELSE 054: ERRMSG = ITEM : TEXT.REC.NAME : DOES NOT EXIST IN : TEXT.FILE.NA ME 055: GOSUB 91000 056: RETURN 057: END 058: ;* 059: ;* Set default values (none are used now) 060: ;* 061: VALUE = DEFAULT 062: LOCATE PROPERTY IN FORMATSTR1 SETTING PTR THEN 063: VALUE = FORMATSTR2,PTR 064: END 065: ;* 066: ;* Remove the first line if it contains a PAGE.FEED *--: P 067: ;* 068: LOOP WHILE TEXT.REC1 = PAGE.FEED DO 069: TEXT.REC = DELETE(TEXT.REC,1) 070: REPEAT 071: IF TEXT.REC1[1,1] = PAGE.FEED THEN 072
Re: [U2] import data to excel sheet...
I've used the NebulaXlite -- it's good -- has a learning curve - but once you get the rhythm it makes getting a 'pretty' spreadsheet put together not that tough. I have a customer that uses it weekly to produce what was a problem as a CSV file just because of the 'perception' that it should look 'pretty'. The data is just as 'right' as ever, but now has more credibility! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Chris Austin Sent: Tuesday, April 13, 2010 11:15 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... That program actually seems perfect for what I'm looking to do Bill. Do you guys use this at your work? Is it a decent product? -Chris Date: Tue, 13 Apr 2010 09:11:25 -0700 From: wphask...@advantos.net To: U2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... Chris: You may want to simplify your life and pick up NebulaXLite. http://nebula-rnd.com/products/xlite.htm HTH, Bill Chris Austin said the following on 4/13/2010 7:48 AM: I was curious if there is an easy way to add font formatting to the .CSV files I have been creating from UniVerse. Right now we just write to a sequential file and call it name.csv and just use comma's to separate the data. We then use a carriage return CHAR(13) to return to the next line in the spreadsheet. If I wanted to make a word appear as blue or in bold, how are you guys doing this? Thanks. -Chris Date: Mon, 12 Apr 2010 11:47:20 -0700 From: jacque...@yahoo.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... Hi Jaweed, If you already produced a number of files with the CHAR(13) as the end of line separator, you probably still handle them with excel, if you change the file extension from .csv to .txt and when you are in Excel import wizard, specify that the origin of the file is Macintosh.Since the Mac uses CHAR(13) as an EOL separator, the import should work. - Original Message From: Robert Houben robert.hou...@fwic.net To: U2 Users List u2-users@listserver.u2ug.org Sent: Mon, April 12, 2010 12:01:03 PM Subject: Re: [U2] import data to excel sheet... Hi Jaweed, I haven't looked too closely at your code, but I did notice on thing that I would consider the most likely candidate: CHAR(13) is a carriage return, not a line feed. Try using CHAR(10) instead for your LINE.FEED variable. You might have to use CHAR(13):CHAR(10), but I think CHAR(10) should do it. Good luck! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jaweed Sent: Monday, April 12, 2010 7:53 AM To: u2-users@listserver.u2ug.org Subject: [U2] import data to excel sheet... Can anyone tell me what is wrong with the following code for importing data from txt file to an excel sheet, when its launch Excel it giving some error message, but when I open the downloaded file from my disc, all the data is in one line with some especial Chars. Any help will be appreciated. 034: ** 035: * OPEN FILES * 036: ** 037: ERRMSG = '' 038: OPEN TEXT.FILE.NAME TO TEXT.FILE ELSE 039: ERRMSG = 'CANNOT OPEN THE ' : TEXT.FILE.NAME : ' FILE!' 040: END 041: IF ERRMSG THEN 042: GOSUB 91000 043: RETURN 044: END *--: P 045: ** 046: * INITIALIZE * 047: ** 048: PAGE.FEED = CHAR(12) 049: LINE.FEED = CHAR(13) 050: ;* 051: ;* Read the text file to convert 052: ;* 053: READ TEXT.REC FROM TEXT.FILE, TEXT.REC.NAME ELSE 054: ERRMSG = ITEM : TEXT.REC.NAME : DOES NOT EXIST IN : TEXT.FILE.NA ME 055: GOSUB 91000 056: RETURN 057: END 058: ;* 059: ;* Set default values (none are used now) 060: ;* 061: VALUE = DEFAULT 062: LOCATE PROPERTY IN FORMATSTR1 SETTING PTR THEN 063: VALUE = FORMATSTR2,PTR 064: END 065: ;* 066: ;* Remove the first line if it contains a PAGE.FEED *--: P 067: ;* 068: LOOP WHILE TEXT.REC1 = PAGE.FEED DO 069: TEXT.REC = DELETE(TEXT.REC,1) 070: REPEAT 071: IF TEXT.REC1[1,1] = PAGE.FEED THEN 072: TEXT.REC1 = TEXT.REC1[2,LEN(TEXT.REC)] 073: END 074: ;* 075: ;* Find the header for each page 076: ;* 077: SLK.REC = '' 078: SLK.ROWS = 0 079: SLK.HEADER.LINES = 0 080: IF TEXT.REC1[1,10] = RUN D - T: THEN 081: SLK.REC-1 = F;SD;R3 082: SLK.REC-1 = F;SD;R4 083: SLK.REC-1 = C;Y1;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[1,30],';',';; '))) 084: SLK.REC-1 = C;Y2;X1;K : QUOTE(TRIM(CHANGE
[U2] import data to excel sheet...
Can anyone tell me what is wrong with the following code for importing data from txt file to an excel sheet, when its launch Excel it giving some error message, but when I open the downloaded file from my disc, all the data is in one line with some especial Chars. Any help will be appreciated. 034: ** 035: * OPEN FILES * 036: ** 037: ERRMSG = '' 038: OPEN TEXT.FILE.NAME TO TEXT.FILE ELSE 039: ERRMSG = 'CANNOT OPEN THE ' : TEXT.FILE.NAME : ' FILE!' 040: END 041: IF ERRMSG THEN 042: GOSUB 91000 043: RETURN 044: END *--: P 045: ** 046: * INITIALIZE * 047: ** 048: PAGE.FEED = CHAR(12) 049: LINE.FEED = CHAR(13) 050: ;* 051: ;* Read the text file to convert 052: ;* 053: READ TEXT.REC FROM TEXT.FILE, TEXT.REC.NAME ELSE 054: ERRMSG = ITEM : TEXT.REC.NAME : DOES NOT EXIST IN : TEXT.FILE.NA ME 055: GOSUB 91000 056: RETURN 057: END 058: ;* 059: ;* Set default values (none are used now) 060: ;* 061: VALUE = DEFAULT 062: LOCATE PROPERTY IN FORMATSTR1 SETTING PTR THEN 063: VALUE = FORMATSTR2,PTR 064: END 065: ;* 066: ;* Remove the first line if it contains a PAGE.FEED *--: P 067: ;* 068: LOOP WHILE TEXT.REC1 = PAGE.FEED DO 069: TEXT.REC = DELETE(TEXT.REC,1) 070: REPEAT 071: IF TEXT.REC1[1,1] = PAGE.FEED THEN 072: TEXT.REC1 = TEXT.REC1[2,LEN(TEXT.REC)] 073: END 074: ;* 075: ;* Find the header for each page 076: ;* 077: SLK.REC = '' 078: SLK.ROWS = 0 079: SLK.HEADER.LINES = 0 080: IF TEXT.REC1[1,10] = RUN D - T: THEN 081: SLK.REC-1 = F;SD;R3 082: SLK.REC-1 = F;SD;R4 083: SLK.REC-1 = C;Y1;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[1,30],';',';; '))) 084: SLK.REC-1 = C;Y2;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[1,30],';',';; '))) 085: SLK.REC-1 = C;Y3;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[31,90],';','; ;'))) 086: SLK.REC-1 = C;Y4;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[31,90],';','; ;'))) 087: SLK.ROWS = 4 088: SLK.HEADER.LINES = 2 *--: P 089: END 090: ;* 091: ;* Find the number of lines in the header 092: ;* 093: LINES = DCOUNT(TEXT.REC,@AM) 094: SLK.HEADER.CNT = 0 095: FOR IDX = SLK.HEADER.LINES+1 TO LINES 096: IF INDEX(TEXT.RECIDX,---,1) THEN 097: SLK.HEADER.CNT = IDX 098: EXIT 099: END 100: NEXT IDX 101: ;* 102: ;* Find the column locations 103: ;* 104: COL.LIST = '' 105: IF SLK.HEADER.CNT THEN 106: DASHES.LINE = : TEXT.RECSLK.HEADER.CNT : 107: DASHES.LINE = TRIM(DASHES.LINE,'-') 108: DASHES.CNT = COUNT(DASHES.LINE,'-') 109: DASHES.LINE = TEXT.RECSLK.HEADER.CNT 110: FOR DASHES.IDX = 1 TO DASHES.CNT *--: P 111: COL.START = INDEX(DASHES.LINE,'-',1) 112: DASHES.REMAIN = LEN(DASHES.LINE) - COL.START + 1 113: COL.LEN = INDEX(DASHES.LINE[DASHES.REMAIN],' ',1)-1 114: IF COL.LEN 1 THEN COL.LEN = LEN(DASHES.LINE) 115: COL.LISTDASHES.IDX,1 = COL.START 116: COL.LISTDASHES.IDX,2 = COL.LEN 117: DASHES.LINE[COL.START,COL.LEN] = SPACE(COL.LEN) 118: NEXT DASHES.IDX 119: END 120: ;* 121: ;* Extract the column titles 122: ;* 123: COL.CNT = DCOUNT(COL.LIST,@AM) 124: FOR HDR.IDX = SLK.HEADER.LINES+1 TO SLK.HEADER.CNT-1 125: SLK.ROWS = SLK.ROWS + 1 126: SLK.Y = Y : SLK.ROWS : ; 127: ;* 128: ;* Make sure the header properly fits into the column 129: ;* If it does not, then add it to the spreadsheet's first column 130: ;* 131: CHECK.LINE = TEXT.RECHDR.IDX 132: GOSUB CHECK.COLUMNS *--: P 133: IF NOT(CHECK.LINE) THEN 134: SLK.TEXT = TRIM(CHANGE(TEXT.RECHDR.IDX,';',';;')) 135: SLK.REC-1 = C; : SLK.Y : X1;K : QUOTE(SLK.TEXT) 136: CONTINUE 137: END 138: ;* 139: ;* The header fits, so add it to the column names 140: ;* 141: IF HDR.IDX = SLK.HEADER.CNT-1 THEN 142: SLK.REC-1 = F;SBD;R : SLK.ROWS 143: END ELSE 144: SLK.REC-1 = F;SD;R : SLK.ROWS 145: END 146: FOR COL.IDX = 1 TO COL.CNT 147: COL.START = COL.LISTCOL.IDX,1 148: COL.LEN = COL.LISTCOL.IDX,2 149: SLK.X = X : COL.IDX : ; 150: SLK.TEXT = TEXT.RECHDR.IDX[COL.START,COL.LEN] 151: SLK.TEXT = TRIM(CHANGE(SLK.TEXT,';',';;')) 152: IF SLK.TEXT # '' THEN 153: SLK.REC-1 = C; : SLK.Y : SLK.X : K : QUOTE(SLK.TEXT) 154: SLK.Y = '' *--: P 155: END 156: NEXT COL.IDX 157: NEXT HDR.IDX 158: ;* 159: ;* Begin conversion 160: ;* 161: LINES = DCOUNT(TEXT.REC,@AM) 162: FOR IDX = SLK.HEADER.CNT+1 TO LINES 163: ;* 164: ;* Check for a pagefeed, otherwise insert the next row of data 165: ;* 166: FINDSTR PAGE.FEED IN TEXT.RECIDX SETTING NEW.PAGE.MARKER THEN 167: IDX = IDX + SLK.HEADER.CNT 168: CONTINUE 169: END 170: SLK.ROWS = SLK.ROWS + 1 171: SLK.Y = Y :
Re: [U2] import data to excel sheet...
Hi Jaweed, I haven't looked too closely at your code, but I did notice on thing that I would consider the most likely candidate: CHAR(13) is a carriage return, not a line feed. Try using CHAR(10) instead for your LINE.FEED variable. You might have to use CHAR(13):CHAR(10), but I think CHAR(10) should do it. Good luck! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jaweed Sent: Monday, April 12, 2010 7:53 AM To: u2-users@listserver.u2ug.org Subject: [U2] import data to excel sheet... Can anyone tell me what is wrong with the following code for importing data from txt file to an excel sheet, when its launch Excel it giving some error message, but when I open the downloaded file from my disc, all the data is in one line with some especial Chars. Any help will be appreciated. 034: ** 035: * OPEN FILES * 036: ** 037: ERRMSG = '' 038: OPEN TEXT.FILE.NAME TO TEXT.FILE ELSE 039: ERRMSG = 'CANNOT OPEN THE ' : TEXT.FILE.NAME : ' FILE!' 040: END 041: IF ERRMSG THEN 042: GOSUB 91000 043: RETURN 044: END *--: P 045: ** 046: * INITIALIZE * 047: ** 048: PAGE.FEED = CHAR(12) 049: LINE.FEED = CHAR(13) 050: ;* 051: ;* Read the text file to convert 052: ;* 053: READ TEXT.REC FROM TEXT.FILE, TEXT.REC.NAME ELSE 054: ERRMSG = ITEM : TEXT.REC.NAME : DOES NOT EXIST IN : TEXT.FILE.NA ME 055: GOSUB 91000 056: RETURN 057: END 058: ;* 059: ;* Set default values (none are used now) 060: ;* 061: VALUE = DEFAULT 062: LOCATE PROPERTY IN FORMATSTR1 SETTING PTR THEN 063: VALUE = FORMATSTR2,PTR 064: END 065: ;* 066: ;* Remove the first line if it contains a PAGE.FEED *--: P 067: ;* 068: LOOP WHILE TEXT.REC1 = PAGE.FEED DO 069: TEXT.REC = DELETE(TEXT.REC,1) 070: REPEAT 071: IF TEXT.REC1[1,1] = PAGE.FEED THEN 072: TEXT.REC1 = TEXT.REC1[2,LEN(TEXT.REC)] 073: END 074: ;* 075: ;* Find the header for each page 076: ;* 077: SLK.REC = '' 078: SLK.ROWS = 0 079: SLK.HEADER.LINES = 0 080: IF TEXT.REC1[1,10] = RUN D - T: THEN 081: SLK.REC-1 = F;SD;R3 082: SLK.REC-1 = F;SD;R4 083: SLK.REC-1 = C;Y1;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[1,30],';',';; '))) 084: SLK.REC-1 = C;Y2;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[1,30],';',';; '))) 085: SLK.REC-1 = C;Y3;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[31,90],';','; ;'))) 086: SLK.REC-1 = C;Y4;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[31,90],';','; ;'))) 087: SLK.ROWS = 4 088: SLK.HEADER.LINES = 2 *--: P 089: END 090: ;* 091: ;* Find the number of lines in the header 092: ;* 093: LINES = DCOUNT(TEXT.REC,@AM) 094: SLK.HEADER.CNT = 0 095: FOR IDX = SLK.HEADER.LINES+1 TO LINES 096: IF INDEX(TEXT.RECIDX,---,1) THEN 097: SLK.HEADER.CNT = IDX 098: EXIT 099: END 100: NEXT IDX 101: ;* 102: ;* Find the column locations 103: ;* 104: COL.LIST = '' 105: IF SLK.HEADER.CNT THEN 106: DASHES.LINE = : TEXT.RECSLK.HEADER.CNT : 107: DASHES.LINE = TRIM(DASHES.LINE,'-') 108: DASHES.CNT = COUNT(DASHES.LINE,'-') 109: DASHES.LINE = TEXT.RECSLK.HEADER.CNT 110: FOR DASHES.IDX = 1 TO DASHES.CNT *--: P 111: COL.START = INDEX(DASHES.LINE,'-',1) 112: DASHES.REMAIN = LEN(DASHES.LINE) - COL.START + 1 113: COL.LEN = INDEX(DASHES.LINE[DASHES.REMAIN],' ',1)-1 114: IF COL.LEN 1 THEN COL.LEN = LEN(DASHES.LINE) 115: COL.LISTDASHES.IDX,1 = COL.START 116: COL.LISTDASHES.IDX,2 = COL.LEN 117: DASHES.LINE[COL.START,COL.LEN] = SPACE(COL.LEN) 118: NEXT DASHES.IDX 119: END 120: ;* 121: ;* Extract the column titles 122: ;* 123: COL.CNT = DCOUNT(COL.LIST,@AM) 124: FOR HDR.IDX = SLK.HEADER.LINES+1 TO SLK.HEADER.CNT-1 125: SLK.ROWS = SLK.ROWS + 1 126: SLK.Y = Y : SLK.ROWS : ; 127: ;* 128: ;* Make sure the header properly fits into the column 129: ;* If it does not, then add it to the spreadsheet's first column 130: ;* 131: CHECK.LINE = TEXT.RECHDR.IDX 132: GOSUB CHECK.COLUMNS *--: P 133: IF NOT(CHECK.LINE) THEN 134: SLK.TEXT = TRIM(CHANGE(TEXT.RECHDR.IDX,';',';;')) 135: SLK.REC-1 = C; : SLK.Y : X1;K : QUOTE(SLK.TEXT) 136: CONTINUE 137: END 138: ;* 139: ;* The header fits, so add it to the column names 140: ;* 141: IF HDR.IDX = SLK.HEADER.CNT-1 THEN 142: SLK.REC-1 = F;SBD;R : SLK.ROWS 143: END ELSE 144: SLK.REC-1 = F;SD;R : SLK.ROWS 145: END 146: FOR COL.IDX = 1 TO COL.CNT 147: COL.START = COL.LISTCOL.IDX,1 148: COL.LEN = COL.LISTCOL.IDX,2 149: SLK.X = X : COL.IDX : ; 150: SLK.TEXT = TEXT.RECHDR.IDX[COL.START,COL.LEN] 151: SLK.TEXT = TRIM(CHANGE(SLK.TEXT,';',';;')) 152: IF SLK.TEXT # '' THEN 153
Re: [U2] import data to excel sheet...
CSV.FILE.NAME = 'c:\path\testCSV.csv' OPENSEQ CSV.FILE.NAME TO CSV.FILE THEN WEOFSEQ CSV.FILE CSV.REC := 'data1, data2, data3':CHAR(13) WRITESEQ CSV.REC TO CSV.FILE THEN NULL The above is a really easy way to write to a CSV file. Maybe you want to get a simple read from the txt and write to the CSV going and add in the other layers as you go. From: robert.hou...@fwic.net To: u2-users@listserver.u2ug.org Date: Mon, 12 Apr 2010 09:01:03 -0700 Subject: Re: [U2] import data to excel sheet... Hi Jaweed, I haven't looked too closely at your code, but I did notice on thing that I would consider the most likely candidate: CHAR(13) is a carriage return, not a line feed. Try using CHAR(10) instead for your LINE.FEED variable. You might have to use CHAR(13):CHAR(10), but I think CHAR(10) should do it. Good luck! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jaweed Sent: Monday, April 12, 2010 7:53 AM To: u2-users@listserver.u2ug.org Subject: [U2] import data to excel sheet... Can anyone tell me what is wrong with the following code for importing data from txt file to an excel sheet, when its launch Excel it giving some error message, but when I open the downloaded file from my disc, all the data is in one line with some especial Chars. Any help will be appreciated. 034: ** 035: * OPEN FILES * 036: ** 037: ERRMSG = '' 038: OPEN TEXT.FILE.NAME TO TEXT.FILE ELSE 039: ERRMSG = 'CANNOT OPEN THE ' : TEXT.FILE.NAME : ' FILE!' 040: END 041: IF ERRMSG THEN 042: GOSUB 91000 043: RETURN 044: END *--: P 045: ** 046: * INITIALIZE * 047: ** 048: PAGE.FEED = CHAR(12) 049: LINE.FEED = CHAR(13) 050: ;* 051: ;* Read the text file to convert 052: ;* 053: READ TEXT.REC FROM TEXT.FILE, TEXT.REC.NAME ELSE 054: ERRMSG = ITEM : TEXT.REC.NAME : DOES NOT EXIST IN : TEXT.FILE.NA ME 055: GOSUB 91000 056: RETURN 057: END 058: ;* 059: ;* Set default values (none are used now) 060: ;* 061: VALUE = DEFAULT 062: LOCATE PROPERTY IN FORMATSTR1 SETTING PTR THEN 063: VALUE = FORMATSTR2,PTR 064: END 065: ;* 066: ;* Remove the first line if it contains a PAGE.FEED *--: P 067: ;* 068: LOOP WHILE TEXT.REC1 = PAGE.FEED DO 069: TEXT.REC = DELETE(TEXT.REC,1) 070: REPEAT 071: IF TEXT.REC1[1,1] = PAGE.FEED THEN 072: TEXT.REC1 = TEXT.REC1[2,LEN(TEXT.REC)] 073: END 074: ;* 075: ;* Find the header for each page 076: ;* 077: SLK.REC = '' 078: SLK.ROWS = 0 079: SLK.HEADER.LINES = 0 080: IF TEXT.REC1[1,10] = RUN D - T: THEN 081: SLK.REC-1 = F;SD;R3 082: SLK.REC-1 = F;SD;R4 083: SLK.REC-1 = C;Y1;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[1,30],';',';; '))) 084: SLK.REC-1 = C;Y2;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[1,30],';',';; '))) 085: SLK.REC-1 = C;Y3;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[31,90],';','; ;'))) 086: SLK.REC-1 = C;Y4;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[31,90],';','; ;'))) 087: SLK.ROWS = 4 088: SLK.HEADER.LINES = 2 *--: P 089: END 090: ;* 091: ;* Find the number of lines in the header 092: ;* 093: LINES = DCOUNT(TEXT.REC,@AM) 094: SLK.HEADER.CNT = 0 095: FOR IDX = SLK.HEADER.LINES+1 TO LINES 096: IF INDEX(TEXT.RECIDX,---,1) THEN 097: SLK.HEADER.CNT = IDX 098: EXIT 099: END 100: NEXT IDX 101: ;* 102: ;* Find the column locations 103: ;* 104: COL.LIST = '' 105: IF SLK.HEADER.CNT THEN 106: DASHES.LINE = : TEXT.RECSLK.HEADER.CNT : 107: DASHES.LINE = TRIM(DASHES.LINE,'-') 108: DASHES.CNT = COUNT(DASHES.LINE,'-') 109: DASHES.LINE = TEXT.RECSLK.HEADER.CNT 110: FOR DASHES.IDX = 1 TO DASHES.CNT *--: P 111: COL.START = INDEX(DASHES.LINE,'-',1) 112: DASHES.REMAIN = LEN(DASHES.LINE) - COL.START + 1 113: COL.LEN = INDEX(DASHES.LINE[DASHES.REMAIN],' ',1)-1 114: IF COL.LEN 1 THEN COL.LEN = LEN(DASHES.LINE) 115: COL.LISTDASHES.IDX,1 = COL.START 116: COL.LISTDASHES.IDX,2 = COL.LEN 117: DASHES.LINE[COL.START,COL.LEN] = SPACE(COL.LEN) 118: NEXT DASHES.IDX 119: END 120: ;* 121: ;* Extract the column titles 122: ;* 123: COL.CNT = DCOUNT(COL.LIST,@AM) 124: FOR HDR.IDX = SLK.HEADER.LINES+1 TO SLK.HEADER.CNT-1 125: SLK.ROWS = SLK.ROWS + 1 126: SLK.Y = Y : SLK.ROWS : ; 127: ;* 128: ;* Make sure the header properly fits into the column 129: ;* If it does not, then add it to the spreadsheet's first column 130: ;* 131: CHECK.LINE = TEXT.RECHDR.IDX 132: GOSUB CHECK.COLUMNS *--: P 133
Re: [U2] import data to excel sheet...
Hey Robert Houben, It worked!!! You are the man, thanks for the help. Jaweed -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Robert Houben Sent: Monday, April 12, 2010 11:01 AM To: U2 Users List Subject: Re: [U2] import data to excel sheet... Hi Jaweed, I haven't looked too closely at your code, but I did notice on thing that I would consider the most likely candidate: CHAR(13) is a carriage return, not a line feed. Try using CHAR(10) instead for your LINE.FEED variable. You might have to use CHAR(13):CHAR(10), but I think CHAR(10) should do it. Good luck! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jaweed Sent: Monday, April 12, 2010 7:53 AM To: u2-users@listserver.u2ug.org Subject: [U2] import data to excel sheet... Can anyone tell me what is wrong with the following code for importing data from txt file to an excel sheet, when its launch Excel it giving some error message, but when I open the downloaded file from my disc, all the data is in one line with some especial Chars. Any help will be appreciated. 034: ** 035: * OPEN FILES * 036: ** 037: ERRMSG = '' 038: OPEN TEXT.FILE.NAME TO TEXT.FILE ELSE 039: ERRMSG = 'CANNOT OPEN THE ' : TEXT.FILE.NAME : ' FILE!' 040: END 041: IF ERRMSG THEN 042: GOSUB 91000 043: RETURN 044: END *--: P 045: ** 046: * INITIALIZE * 047: ** 048: PAGE.FEED = CHAR(12) 049: LINE.FEED = CHAR(13) 050: ;* 051: ;* Read the text file to convert 052: ;* 053: READ TEXT.REC FROM TEXT.FILE, TEXT.REC.NAME ELSE 054: ERRMSG = ITEM : TEXT.REC.NAME : DOES NOT EXIST IN : TEXT.FILE.NA ME 055: GOSUB 91000 056: RETURN 057: END 058: ;* 059: ;* Set default values (none are used now) 060: ;* 061: VALUE = DEFAULT 062: LOCATE PROPERTY IN FORMATSTR1 SETTING PTR THEN 063: VALUE = FORMATSTR2,PTR 064: END 065: ;* 066: ;* Remove the first line if it contains a PAGE.FEED *--: P 067: ;* 068: LOOP WHILE TEXT.REC1 = PAGE.FEED DO 069: TEXT.REC = DELETE(TEXT.REC,1) 070: REPEAT 071: IF TEXT.REC1[1,1] = PAGE.FEED THEN 072: TEXT.REC1 = TEXT.REC1[2,LEN(TEXT.REC)] 073: END 074: ;* 075: ;* Find the header for each page 076: ;* 077: SLK.REC = '' 078: SLK.ROWS = 0 079: SLK.HEADER.LINES = 0 080: IF TEXT.REC1[1,10] = RUN D - T: THEN 081: SLK.REC-1 = F;SD;R3 082: SLK.REC-1 = F;SD;R4 083: SLK.REC-1 = C;Y1;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[1,30],';',';; '))) 084: SLK.REC-1 = C;Y2;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[1,30],';',';; '))) 085: SLK.REC-1 = C;Y3;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[31,90],';','; ;'))) 086: SLK.REC-1 = C;Y4;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[31,90],';','; ;'))) 087: SLK.ROWS = 4 088: SLK.HEADER.LINES = 2 *--: P 089: END 090: ;* 091: ;* Find the number of lines in the header 092: ;* 093: LINES = DCOUNT(TEXT.REC,@AM) 094: SLK.HEADER.CNT = 0 095: FOR IDX = SLK.HEADER.LINES+1 TO LINES 096: IF INDEX(TEXT.RECIDX,---,1) THEN 097: SLK.HEADER.CNT = IDX 098: EXIT 099: END 100: NEXT IDX 101: ;* 102: ;* Find the column locations 103: ;* 104: COL.LIST = '' 105: IF SLK.HEADER.CNT THEN 106: DASHES.LINE = : TEXT.RECSLK.HEADER.CNT : 107: DASHES.LINE = TRIM(DASHES.LINE,'-') 108: DASHES.CNT = COUNT(DASHES.LINE,'-') 109: DASHES.LINE = TEXT.RECSLK.HEADER.CNT 110: FOR DASHES.IDX = 1 TO DASHES.CNT *--: P 111: COL.START = INDEX(DASHES.LINE,'-',1) 112: DASHES.REMAIN = LEN(DASHES.LINE) - COL.START + 1 113: COL.LEN = INDEX(DASHES.LINE[DASHES.REMAIN],' ',1)-1 114: IF COL.LEN 1 THEN COL.LEN = LEN(DASHES.LINE) 115: COL.LISTDASHES.IDX,1 = COL.START 116: COL.LISTDASHES.IDX,2 = COL.LEN 117: DASHES.LINE[COL.START,COL.LEN] = SPACE(COL.LEN) 118: NEXT DASHES.IDX 119: END 120: ;* 121: ;* Extract the column titles 122: ;* 123: COL.CNT = DCOUNT(COL.LIST,@AM) 124: FOR HDR.IDX = SLK.HEADER.LINES+1 TO SLK.HEADER.CNT-1 125: SLK.ROWS = SLK.ROWS + 1 126: SLK.Y = Y : SLK.ROWS : ; 127: ;* 128: ;* Make sure the header properly fits into the column 129: ;* If it does not, then add it to the spreadsheet's first column 130: ;* 131: CHECK.LINE = TEXT.RECHDR.IDX 132: GOSUB CHECK.COLUMNS *--: P 133: IF NOT(CHECK.LINE) THEN 134: SLK.TEXT = TRIM(CHANGE(TEXT.RECHDR.IDX,';',';;')) 135: SLK.REC-1 = C; : SLK.Y : X1;K : QUOTE(SLK.TEXT) 136: CONTINUE 137: END 138: ;* 139: ;* The header fits, so add it to the column names 140: ;* 141: IF HDR.IDX = SLK.HEADER.CNT-1 THEN 142: SLK.REC-1 = F;SBD;R : SLK.ROWS 143: END ELSE 144: SLK.REC-1 = F;SD;R : SLK.ROWS 145: END
Re: [U2] import data to excel sheet...
Always glad to help! :) -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jaweed Sent: Monday, April 12, 2010 9:23 AM To: 'U2 Users List' Subject: Re: [U2] import data to excel sheet... Hey Robert Houben, It worked!!! You are the man, thanks for the help. Jaweed -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Robert Houben Sent: Monday, April 12, 2010 11:01 AM To: U2 Users List Subject: Re: [U2] import data to excel sheet... Hi Jaweed, I haven't looked too closely at your code, but I did notice on thing that I would consider the most likely candidate: CHAR(13) is a carriage return, not a line feed. Try using CHAR(10) instead for your LINE.FEED variable. You might have to use CHAR(13):CHAR(10), but I think CHAR(10) should do it. Good luck! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jaweed Sent: Monday, April 12, 2010 7:53 AM To: u2-users@listserver.u2ug.org Subject: [U2] import data to excel sheet... Can anyone tell me what is wrong with the following code for importing data from txt file to an excel sheet, when its launch Excel it giving some error message, but when I open the downloaded file from my disc, all the data is in one line with some especial Chars. Any help will be appreciated. 034: ** 035: * OPEN FILES * 036: ** 037: ERRMSG = '' 038: OPEN TEXT.FILE.NAME TO TEXT.FILE ELSE 039: ERRMSG = 'CANNOT OPEN THE ' : TEXT.FILE.NAME : ' FILE!' 040: END 041: IF ERRMSG THEN 042: GOSUB 91000 043: RETURN 044: END *--: P 045: ** 046: * INITIALIZE * 047: ** 048: PAGE.FEED = CHAR(12) 049: LINE.FEED = CHAR(13) 050: ;* 051: ;* Read the text file to convert 052: ;* 053: READ TEXT.REC FROM TEXT.FILE, TEXT.REC.NAME ELSE 054: ERRMSG = ITEM : TEXT.REC.NAME : DOES NOT EXIST IN : TEXT.FILE.NA ME 055: GOSUB 91000 056: RETURN 057: END 058: ;* 059: ;* Set default values (none are used now) 060: ;* 061: VALUE = DEFAULT 062: LOCATE PROPERTY IN FORMATSTR1 SETTING PTR THEN 063: VALUE = FORMATSTR2,PTR 064: END 065: ;* 066: ;* Remove the first line if it contains a PAGE.FEED *--: P 067: ;* 068: LOOP WHILE TEXT.REC1 = PAGE.FEED DO 069: TEXT.REC = DELETE(TEXT.REC,1) 070: REPEAT 071: IF TEXT.REC1[1,1] = PAGE.FEED THEN 072: TEXT.REC1 = TEXT.REC1[2,LEN(TEXT.REC)] 073: END 074: ;* 075: ;* Find the header for each page 076: ;* 077: SLK.REC = '' 078: SLK.ROWS = 0 079: SLK.HEADER.LINES = 0 080: IF TEXT.REC1[1,10] = RUN D - T: THEN 081: SLK.REC-1 = F;SD;R3 082: SLK.REC-1 = F;SD;R4 083: SLK.REC-1 = C;Y1;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[1,30],';',';; '))) 084: SLK.REC-1 = C;Y2;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[1,30],';',';; '))) 085: SLK.REC-1 = C;Y3;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[31,90],';','; ;'))) 086: SLK.REC-1 = C;Y4;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[31,90],';','; ;'))) 087: SLK.ROWS = 4 088: SLK.HEADER.LINES = 2 *--: P 089: END 090: ;* 091: ;* Find the number of lines in the header 092: ;* 093: LINES = DCOUNT(TEXT.REC,@AM) 094: SLK.HEADER.CNT = 0 095: FOR IDX = SLK.HEADER.LINES+1 TO LINES 096: IF INDEX(TEXT.RECIDX,---,1) THEN 097: SLK.HEADER.CNT = IDX 098: EXIT 099: END 100: NEXT IDX 101: ;* 102: ;* Find the column locations 103: ;* 104: COL.LIST = '' 105: IF SLK.HEADER.CNT THEN 106: DASHES.LINE = : TEXT.RECSLK.HEADER.CNT : 107: DASHES.LINE = TRIM(DASHES.LINE,'-') 108: DASHES.CNT = COUNT(DASHES.LINE,'-') 109: DASHES.LINE = TEXT.RECSLK.HEADER.CNT 110: FOR DASHES.IDX = 1 TO DASHES.CNT *--: P 111: COL.START = INDEX(DASHES.LINE,'-',1) 112: DASHES.REMAIN = LEN(DASHES.LINE) - COL.START + 1 113: COL.LEN = INDEX(DASHES.LINE[DASHES.REMAIN],' ',1)-1 114: IF COL.LEN 1 THEN COL.LEN = LEN(DASHES.LINE) 115: COL.LISTDASHES.IDX,1 = COL.START 116: COL.LISTDASHES.IDX,2 = COL.LEN 117: DASHES.LINE[COL.START,COL.LEN] = SPACE(COL.LEN) 118: NEXT DASHES.IDX 119: END 120: ;* 121: ;* Extract the column titles 122: ;* 123: COL.CNT = DCOUNT(COL.LIST,@AM) 124: FOR HDR.IDX = SLK.HEADER.LINES+1 TO SLK.HEADER.CNT-1 125: SLK.ROWS = SLK.ROWS + 1 126: SLK.Y = Y : SLK.ROWS : ; 127: ;* 128: ;* Make sure the header properly fits into the column 129: ;* If it does not, then add it to the spreadsheet's first column 130: ;* 131: CHECK.LINE = TEXT.RECHDR.IDX 132: GOSUB CHECK.COLUMNS *--: P 133: IF NOT(CHECK.LINE) THEN 134: SLK.TEXT = TRIM(CHANGE(TEXT.RECHDR.IDX,';',';;')) 135: SLK.REC-1 = C; : SLK.Y : X1;K : QUOTE(SLK.TEXT) 136
Re: [U2] import data to excel sheet...
Hi Jaweed, If you already produced a number of files with the CHAR(13) as the end of line separator, you probably still handle them with excel, if you change the file extension from .csv to .txt and when you are in Excel import wizard, specify that the origin of the file is Macintosh.Since the Mac uses CHAR(13) as an EOL separator, the import should work. - Original Message From: Robert Houben robert.hou...@fwic.net To: U2 Users List u2-users@listserver.u2ug.org Sent: Mon, April 12, 2010 12:01:03 PM Subject: Re: [U2] import data to excel sheet... Hi Jaweed, I haven't looked too closely at your code, but I did notice on thing that I would consider the most likely candidate: CHAR(13) is a carriage return, not a line feed. Try using CHAR(10) instead for your LINE.FEED variable. You might have to use CHAR(13):CHAR(10), but I think CHAR(10) should do it. Good luck! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jaweed Sent: Monday, April 12, 2010 7:53 AM To: u2-users@listserver.u2ug.org Subject: [U2] import data to excel sheet... Can anyone tell me what is wrong with the following code for importing data from txt file to an excel sheet, when its launch Excel it giving some error message, but when I open the downloaded file from my disc, all the data is in one line with some especial Chars. Any help will be appreciated. 034: ** 035: * OPEN FILES * 036: ** 037: ERRMSG = '' 038: OPEN TEXT.FILE.NAME TO TEXT.FILE ELSE 039: ERRMSG = 'CANNOT OPEN THE ' : TEXT.FILE.NAME : ' FILE!' 040: END 041: IF ERRMSG THEN 042: GOSUB 91000 043: RETURN 044: END *--: P 045: ** 046: * INITIALIZE * 047: ** 048: PAGE.FEED = CHAR(12) 049: LINE.FEED = CHAR(13) 050: ;* 051: ;* Read the text file to convert 052: ;* 053: READ TEXT.REC FROM TEXT.FILE, TEXT.REC.NAME ELSE 054: ERRMSG = ITEM : TEXT.REC.NAME : DOES NOT EXIST IN : TEXT.FILE.NA ME 055: GOSUB 91000 056: RETURN 057: END 058: ;* 059: ;* Set default values (none are used now) 060: ;* 061: VALUE = DEFAULT 062: LOCATE PROPERTY IN FORMATSTR1 SETTING PTR THEN 063: VALUE = FORMATSTR2,PTR 064: END 065: ;* 066: ;* Remove the first line if it contains a PAGE.FEED *--: P 067: ;* 068: LOOP WHILE TEXT.REC1 = PAGE.FEED DO 069: TEXT.REC = DELETE(TEXT.REC,1) 070: REPEAT 071: IF TEXT.REC1[1,1] = PAGE.FEED THEN 072: TEXT.REC1 = TEXT.REC1[2,LEN(TEXT.REC)] 073: END 074: ;* 075: ;* Find the header for each page 076: ;* 077: SLK.REC = '' 078: SLK.ROWS = 0 079: SLK.HEADER.LINES = 0 080: IF TEXT.REC1[1,10] = RUN D - T: THEN 081: SLK.REC-1 = F;SD;R3 082: SLK.REC-1 = F;SD;R4 083: SLK.REC-1 = C;Y1;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[1,30],';',';; '))) 084: SLK.REC-1 = C;Y2;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[1,30],';',';; '))) 085: SLK.REC-1 = C;Y3;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[31,90],';','; ;'))) 086: SLK.REC-1 = C;Y4;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[31,90],';','; ;'))) 087: SLK.ROWS = 4 088: SLK.HEADER.LINES = 2 *--: P 089: END 090: ;* 091: ;* Find the number of lines in the header 092: ;* 093: LINES = DCOUNT(TEXT.REC,@AM) 094: SLK.HEADER.CNT = 0 095: FOR IDX = SLK.HEADER.LINES+1 TO LINES 096: IF INDEX(TEXT.RECIDX,---,1) THEN 097: SLK.HEADER.CNT = IDX 098: EXIT 099: END 100: NEXT IDX 101: ;* 102: ;* Find the column locations 103: ;* 104: COL.LIST = '' 105: IF SLK.HEADER.CNT THEN 106: DASHES.LINE = : TEXT.RECSLK.HEADER.CNT : 107: DASHES.LINE = TRIM(DASHES.LINE,'-') 108: DASHES.CNT = COUNT(DASHES.LINE,'-') 109: DASHES.LINE = TEXT.RECSLK.HEADER.CNT 110: FOR DASHES.IDX = 1 TO DASHES.CNT *--: P 111: COL.START = INDEX(DASHES.LINE,'-',1) 112: DASHES.REMAIN = LEN(DASHES.LINE) - COL.START + 1 113: COL.LEN = INDEX(DASHES.LINE[DASHES.REMAIN],' ',1)-1 114: IF COL.LEN 1 THEN COL.LEN = LEN(DASHES.LINE) 115: COL.LISTDASHES.IDX,1 = COL.START 116: COL.LISTDASHES.IDX,2 = COL.LEN 117: DASHES.LINE[COL.START,COL.LEN] = SPACE(COL.LEN) 118: NEXT DASHES.IDX 119: END 120: ;* 121: ;* Extract the column titles 122: ;* 123: COL.CNT = DCOUNT(COL.LIST,@AM) 124: FOR HDR.IDX = SLK.HEADER.LINES+1 TO SLK.HEADER.CNT-1 125: SLK.ROWS = SLK.ROWS + 1 126: SLK.Y = Y : SLK.ROWS : ; 127: ;* 128: ;* Make sure the header properly fits into the column 129: ;* If it does not, then add it to the spreadsheet's first column 130: ;* 131: CHECK.LINE = TEXT.RECHDR.IDX 132: GOSUB CHECK.COLUMNS *--: P 133: IF NOT(CHECK.LINE) THEN 134: SLK.TEXT = TRIM(CHANGE(TEXT.RECHDR.IDX,';',';;')) 135: SLK.REC-1 = C; : SLK.Y : X1;K : QUOTE(SLK.TEXT) 136: CONTINUE 137: END
Re: [U2] import data to excel sheet...
This is a function of the operating system. On Unix systems only a linefeed character is added to the end of a line whereas on Windows both carriage return and linefeed characters are added to the end of a line. If you open a file created on a Unix system with the notepad app it will have one line with strange characters in it. A quick fix, if you open the same file with word pad it will be converted and you can save it. If you created a file on a Unix system for use on a Windows system you have to add a carriage return character to the end of the line. Jerry Banker -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Robert Houben Sent: Monday, April 12, 2010 11:01 AM To: U2 Users List Subject: Re: [U2] import data to excel sheet... Hi Jaweed, I haven't looked too closely at your code, but I did notice on thing that I would consider the most likely candidate: CHAR(13) is a carriage return, not a line feed. Try using CHAR(10) instead for your LINE.FEED variable. You might have to use CHAR(13):CHAR(10), but I think CHAR(10) should do it. Good luck! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jaweed Sent: Monday, April 12, 2010 7:53 AM To: u2-users@listserver.u2ug.org Subject: [U2] import data to excel sheet... Can anyone tell me what is wrong with the following code for importing data from txt file to an excel sheet, when its launch Excel it giving some error message, but when I open the downloaded file from my disc, all the data is in one line with some especial Chars. Any help will be appreciated. 034: ** 035: * OPEN FILES * 036: ** 037: ERRMSG = '' 038: OPEN TEXT.FILE.NAME TO TEXT.FILE ELSE 039: ERRMSG = 'CANNOT OPEN THE ' : TEXT.FILE.NAME : ' FILE!' 040: END 041: IF ERRMSG THEN 042: GOSUB 91000 043: RETURN 044: END *--: P 045: ** 046: * INITIALIZE * 047: ** 048: PAGE.FEED = CHAR(12) 049: LINE.FEED = CHAR(13) 050: ;* 051: ;* Read the text file to convert 052: ;* 053: READ TEXT.REC FROM TEXT.FILE, TEXT.REC.NAME ELSE 054: ERRMSG = ITEM : TEXT.REC.NAME : DOES NOT EXIST IN : TEXT.FILE.NA ME 055: GOSUB 91000 056: RETURN 057: END 058: ;* 059: ;* Set default values (none are used now) 060: ;* 061: VALUE = DEFAULT 062: LOCATE PROPERTY IN FORMATSTR1 SETTING PTR THEN 063: VALUE = FORMATSTR2,PTR 064: END 065: ;* 066: ;* Remove the first line if it contains a PAGE.FEED *--: P 067: ;* 068: LOOP WHILE TEXT.REC1 = PAGE.FEED DO 069: TEXT.REC = DELETE(TEXT.REC,1) 070: REPEAT 071: IF TEXT.REC1[1,1] = PAGE.FEED THEN 072: TEXT.REC1 = TEXT.REC1[2,LEN(TEXT.REC)] 073: END 074: ;* 075: ;* Find the header for each page 076: ;* 077: SLK.REC = '' 078: SLK.ROWS = 0 079: SLK.HEADER.LINES = 0 080: IF TEXT.REC1[1,10] = RUN D - T: THEN 081: SLK.REC-1 = F;SD;R3 082: SLK.REC-1 = F;SD;R4 083: SLK.REC-1 = C;Y1;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[1,30],';',';; '))) 084: SLK.REC-1 = C;Y2;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[1,30],';',';; '))) 085: SLK.REC-1 = C;Y3;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC1[31,90],';','; ;'))) 086: SLK.REC-1 = C;Y4;X1;K : QUOTE(TRIM(CHANGE(TEXT.REC2[31,90],';','; ;'))) 087: SLK.ROWS = 4 088: SLK.HEADER.LINES = 2 *--: P 089: END 090: ;* 091: ;* Find the number of lines in the header 092: ;* 093: LINES = DCOUNT(TEXT.REC,@AM) 094: SLK.HEADER.CNT = 0 095: FOR IDX = SLK.HEADER.LINES+1 TO LINES 096: IF INDEX(TEXT.RECIDX,---,1) THEN 097: SLK.HEADER.CNT = IDX 098: EXIT 099: END 100: NEXT IDX 101: ;* 102: ;* Find the column locations 103: ;* 104: COL.LIST = '' 105: IF SLK.HEADER.CNT THEN 106: DASHES.LINE = : TEXT.RECSLK.HEADER.CNT : 107: DASHES.LINE = TRIM(DASHES.LINE,'-') 108: DASHES.CNT = COUNT(DASHES.LINE,'-') 109: DASHES.LINE = TEXT.RECSLK.HEADER.CNT 110: FOR DASHES.IDX = 1 TO DASHES.CNT *--: P 111: COL.START = INDEX(DASHES.LINE,'-',1) 112: DASHES.REMAIN = LEN(DASHES.LINE) - COL.START + 1 113: COL.LEN = INDEX(DASHES.LINE[DASHES.REMAIN],' ',1)-1 114: IF COL.LEN 1 THEN COL.LEN = LEN(DASHES.LINE) 115: COL.LISTDASHES.IDX,1 = COL.START 116: COL.LISTDASHES.IDX,2 = COL.LEN 117: DASHES.LINE[COL.START,COL.LEN] = SPACE(COL.LEN) 118: NEXT DASHES.IDX 119: END 120: ;* 121: ;* Extract the column titles 122: ;* 123: COL.CNT = DCOUNT(COL.LIST,@AM) 124: FOR HDR.IDX = SLK.HEADER.LINES+1 TO SLK.HEADER.CNT-1 125: SLK.ROWS = SLK.ROWS + 1 126: SLK.Y = Y : SLK.ROWS : ; 127: ;* 128: ;* Make sure the header properly fits into the column 129: ;* If it does not, then add it to the spreadsheet's first column 130: ;* 131: CHECK.LINE
