I've used the NebulaXlite -- it's good -- has a learning curve - but once you get the rhythm it makes getting a 'pretty' spreadsheet put together not that tough. I have a customer that uses it weekly to produce what was a problem as a "CSV" file just because of the 'perception' that it should look 'pretty'. The data is just as 'right' as ever, but now has more credibility!
-----Original Message----- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Chris Austin Sent: Tuesday, April 13, 2010 11:15 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] import data to excel sheet... That program actually seems perfect for what I'm looking to do Bill. Do you guys use this at your work? Is it a decent product? -Chris > Date: Tue, 13 Apr 2010 09:11:25 -0700 > From: wphask...@advantos.net > To: U2-users@listserver.u2ug.org > Subject: Re: [U2] import data to excel sheet... > > Chris: > > You may want to simplify your life and pick up NebulaXLite. > > http://nebula-rnd.com/products/xlite.htm > > HTH, > > Bill > > > > > ------------------------------------------------------------------------ > > Chris Austin said the following on 4/13/2010 7:48 AM: > >> 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 > >> > _______________________________________________ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users _________________________________________________________________ The New Busy is not the old busy. Search, chat and e-mail from your inbox. http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:W L:en-US:WM_HMP:042010_3 _______________________________________________ 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
