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
