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: [email protected] [mailto:[email protected]] On Behalf Of Chris Austin Sent: 13 April 2010 16:54 To: [email protected] 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: [email protected] > To: [email protected] > 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: [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 _________________________________________________________________ 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=PID283 26::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_4 _______________________________________________ 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
