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:WL:en-US:WM_HMP:042010_2 _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users