also see: http://blogs.msdn.com/brian_jones/archive/2005/06/27/433152.aspx
> -----Original Message----- > From: [email protected] [mailto:u2-users- > [email protected]] 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: [email protected] > [mailto:[email protected]] On Behalf Of Chris Austin > Sent: 13 April 2010 3:48 PM > To: [email protected] > 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: [email protected] > > To: [email protected] > > 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 <[email protected]> > > To: U2 Users List <[email protected]> > > 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: [email protected] > [mailto:[email protected]] On Behalf Of Jaweed > > Sent: Monday, April 12, 2010 7:53 AM > > To: [email protected] > > 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 > > [email protected] > > http://listserver.u2ug.org/mailman/listinfo/u2-users > > _______________________________________________ > > U2-Users mailing list > > [email protected] > > http://listserver.u2ug.org/mailman/listinfo/u2-users > > > > > > > > > > _______________________________________________ > > U2-Users mailing list > > [email protected] > > 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 > [email protected] > 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 > [email protected] > http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
