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: [email protected] > To: [email protected] > 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: [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 _________________________________________________________________ The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with Hotmail. http://www.windowslive.com/campaign/thenewbusy?tile=multicalendar&ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5 _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
