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
