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
