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 FORMATSTR<1> SETTING PTR THEN > > > > 063: VALUE = FORMATSTR<2,PTR> > > > > 064: END > > > > 065: ;* > > > > 066: ;* Remove the first line if it contains a PAGE.FEED > > > > *--: P > > > > 067: ;* > > > > 068: LOOP WHILE TEXT.REC<1> = PAGE.FEED DO > > > > 069: TEXT.REC = DELETE(TEXT.REC,1) > > > > 070: REPEAT > > > > 071: IF TEXT.REC<1>[1,1] = PAGE.FEED THEN > > > > 072: TEXT.REC<1> = TEXT.REC<1>[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.REC<1>[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.REC<1>[1,30],';',';; > > > > '))) > > > > 084: SLK.REC<-1> = "C;Y2;X1;K" : > > QUOTE(TRIM(CHANGE(TEXT.REC<2>[1,30],';',';; > > > > '))) > > > > 085: SLK.REC<-1> = "C;Y3;X1;K" : > > QUOTE(TRIM(CHANGE(TEXT.REC<1>[31,90],';','; > > > > ;'))) > > > > 086: SLK.REC<-1> = "C;Y4;X1;K" : > > QUOTE(TRIM(CHANGE(TEXT.REC<2>[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.REC<IDX>,"---",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.REC<SLK.HEADER.CNT> : " " > > > > 107: DASHES.LINE = TRIM(DASHES.LINE,'-') > > > > 108: DASHES.CNT = COUNT(DASHES.LINE,'-') > > > > 109: DASHES.LINE = TEXT.REC<SLK.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.LIST<DASHES.IDX,1> = COL.START > > > > 116: COL.LIST<DASHES.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.REC<HDR.IDX> > > > > 132: GOSUB CHECK.COLUMNS > > > > *--: P > > > > 133: IF NOT(CHECK.LINE) THEN > > > > 134: SLK.TEXT = TRIM(CHANGE(TEXT.REC<HDR.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.LIST<COL.IDX,1> > > > > 148: COL.LEN = COL.LIST<COL.IDX,2> > > > > 149: SLK.X = "X" : COL.IDX : ";" > > > > 150: SLK.TEXT = TEXT.REC<HDR.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.REC<IDX> 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" : SLK.ROWS : ";" > > > > 172: ;* > > > > 173: ;* Make sure the row properly fits into the column > > > > 174: ;* > > > > 175: CHECK.LINE = TEXT.REC<IDX> > > > > 176: GOSUB CHECK.COLUMNS > > > > *--: P > > > > 177: IF NOT(CHECK.LINE) THEN > > > > 178: SLK.TEXT = TRIM(CHANGE(TEXT.REC<IDX>,';',';;')) > > > > 179: SLK.REC<-1> = "C;" : SLK.Y : "X1;K" : QUOTE(SLK.TEXT) > > > > 180: CONTINUE > > > > 181: END > > > > 182: FOR COL.IDX = 1 TO COL.CNT > > > > 183: COL.START = COL.LIST<COL.IDX,1> > > > > 184: COL.LEN = COL.LIST<COL.IDX,2> > > > > 185: SLK.X = "X" : COL.IDX : ";" > > > > 186: SLK.TEXT = TEXT.REC<IDX>[COL.START,COL.LEN] > > > > 187: SLK.TEXT = TRIM(CHANGE(SLK.TEXT,';',';;')) > > > > 188: IF SLK.TEXT # '' THEN > > > > 189: SLK.REC<-1> = "C;" : SLK.Y : SLK.X : "K" : QUOTE(SLK.TEXT) > > > > 190: SLK.Y = '' > > > > 191: END > > > > 192: NEXT COL.IDX > > > > 193: NEXT IDX > > > > 194: ;* > > > > 195: ;* Insert SLK format header into record > > > > 196: ;* > > > > 197: SLK.REC.FMT = 'ID;PPRIMAC' > > > > 198: SLK.REC.FMT<-1> = 'P;ECourier' > > > > *--: P > > > > 199: SLK.REC.FMT<-1> = 'P;ECourier;SB' > > > > 200: SLK.REC.FMT<-1> = 'B;Y' : SLK.ROWS : ';X' : COL.CNT > > > > 201: FOR COL.IDX = 1 TO COL.CNT > > > > 202: COL.LEN = COL.LIST<COL.IDX,2> > > > > 203: SLK.REC.FMT<-1> = 'F;W' : COL.IDX : ' ' : COL.IDX : ' ' : > COL.LEN+1 > > > > 204: NEXT COL.IDX > > > > 205: SLK.REC = SLK.REC.FMT : @AM : SLK.REC : @AM : "E" > > > > 206: ;* > > > > 207: ;* Write SLK record > > > > 208: ;* > > > > 209: SLK.REC.PATH = "./":TEXT.FILE.NAME:"/":TEXT.REC.NAME:".slk" > > > > 210: SWAP @AM WITH LINE.FEED IN SLK.REC > > > > 211: OSWRITE SLK.REC ON SLK.REC.PATH > > > > 212: RETURN > > > > 213: * > > > > 214: CHECK.COLUMNS: > > > > 215: FOR CHECK.IDX = 1 TO COL.CNT > > > > 216: CHECK.START = COL.LIST<CHECK.IDX,1> > > > > 217: CHECK.LEN = COL.LIST<CHECK.IDX,2> > > > > 218: CHECK.LINE[CHECK.START,CHECK.LEN] = SPACE(CHECK.LEN) > > > > 219: NEXT CHECK.IDX > > > > 220: IF TRIM(CHECK.LINE) = '' THEN > > > > *--: P > > > > 221: CHECK.LINE = 1 > > > > 222: END ELSE > > > > 223: CHECK.LINE = 0 > > > > 224: END > > > > 225: RETURN > > > > 226: ******************** > > > > 227: * CALLS FOR SYSCOM * > > > > 228: ******************** > > > > 229: 91000* > > > > 230: ERR.TYPE = 1 ; CALL SYSCOM(MAT SYSCOM.REC) > > > > 231: RETURN > > > > 232: * > > > > 233: 99999 * > > > > 234: RETURN > > > > 235: END > > > > Bottom. > > > > _______________________________________________ > > 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 > > _________________________________________________________________ > 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:W > L:en-US:WM_HMP:042010_2 > _______________________________________________ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 9.0.801 / Virus Database: 271.1.1/2807 - Release Date: 04/12/10 > 19:32:00 > > _______________________________________________ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users _________________________________________________________________ The New Busy is not the too busy. Combine all your e-mail accounts with Hotmail. http://www.windowslive.com/campaign/thenewbusy?tile=multiaccount&ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_4 _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
