Topic: Converting Excel tab file to fixed length (1 of 23), Read 77 times Conf: Converting, Translating From: barry ennis Date: Monday, August 16, 2004 12:50 PM I have a need to convert Excel tab delimited files to fixed format for up loading to mainframe. The .prn format of excel maxes out at about 200 characters and my mainframe program does not handle tabbed or comma delimited files correctly. Can Vedit convert an Excel "tabbed" or "comma" delimited file to a fixed format file to a max lrecl of 1000.

Topic: Re: Converting Excel tab file to fixed length (2 of 23), Read 72 times Conf: Converting, Translating From: Ted Green Date: Monday, August 16, 2004 04:48 PM At 12:51 PM 8/16/2004, you wrote: >I have a need to convert Excel tab delimited files to fixed format for up loading to mainframe. The .prn format of excel maxes out at about 200 characters and my mainframe program does not handle tabbed or comma delimited files correctly. >Can Vedit convert an Excel "tabbed" or "comma" delimited file to a fixed format file to a max lrecl of 1000. We just finished a flat-to-csv file conversion macro (to be released soon), but don't have the opposite. It is almost trivial to convert a tab-delimited file to flat-file format; simply untab it with {EDIT, Convert, Detab}. VEDIT has {EDIT, Convert, All lines same length} and {USER, Check lines same length} to help with getting the correct record length. Note that many mainframes also need four record-length bytes in front of each record; sometimes they also need tape-blocking. For more complex conversions, our EBCDIC Level-4 software ($944) can convert from ASCII to EBCDIC with packed fields. Ted.

Topic: Re: Converting Excel tab file to fixed length (3 of 23), Read 71 times Conf: Converting, Translating From: barry ennis Date: Tuesday, August 17, 2004 04:20 PM Thanks for the info ted. The four byte record length for mainframe you describe is called the "Record descriptor record" and is used for Variable length records. Fixed length records do not contain this field. What is the name and cost of the product that converts tabs to fixed.

Topic: Re: Converting Excel tab file to fixed length (4 of 23), Read 65 times Conf: Converting, Translating From: Ted Green Date: Tuesday, August 17, 2004 05:08 PM At 04:20 PM 8/17/2004, you wrote: >The four byte record length for mainframe you describe is called the "Record descriptor record" and is used for Variable length records. Fixed length records do not contain this field. Some mainframes need the record-descriptor field anyway. >What is the name and cost of the product that converts tabs to fixed. Just the basic $89 VEDIT should be all you need. ($79 ordered online). Ted.

Topic: Re: Converting Excel tab file to fixed length (5 of 23), Read 67 times Conf: Converting, Translating From: Christian Ziemski Date: Thursday, August 19, 2004 04:44 AM On Mon, 16 Aug 2004 00:50:00 -0400, barry ennis wrote: >I have a need to convert Excel tab delimited files to fixed format for >up loading to mainframe. The .prn format of excel maxes out at about >200 characters and my mainframe program does not handle tabbed or >comma delimited files correctly. >Can Vedit convert an Excel "tabbed" or "comma" delimited file to a >fixed format file to a max lrecl of 1000. As Ted wrote: VEDIT is able to do this. But perhaps that doesn't work correctly depending on your data format. The de-tabbing relies on the correct set TAB positions. And that may not be easy to do manually... I wrote a little conversion macro that may help to convert a TABed file to fixed length: http://ziemski.privat.t-online.de/vedit/macros/tab2fix.vdm It detects the longest field for every data column and sets the TABs accordingly. Then it does a De-Tab. As result there is a fixed length data file with one additional space character between the fields (technically necessary here). Since the macro uses VEDITs internal TAB handling it is restricted to 32 TABs in this version! If needed it would be possible to use other algorithms: - All fields same length, with VEDITs TAB routines (up to col 65535) - No limitations (but slower in execution then). (Only ideas, I don't have those macros yet! ;-) Christian

Topic: Re: Converting Excel tab file to fixed length (6 of 23), Read 70 times Conf: Converting, Translating From: barry ennis Date: Thursday, August 19, 2004 09:37 AM Thanks Christian, your input was very helpful. The tabs in my file would be tabs that excel creates when a file is saved as "Text(Tab delimited)(*.txt)". From the start the routine will be marginally helpful in that most of the files I need to convert have more than 32 excel columns but less than 50. You mention that the new release will accommodate larger tab files. Will the macro you supplied work or will it have to be tweaked.

Topic: Re: Converting Excel tab file to fixed length (7 of 23), Read 67 times Conf: Converting, Translating From: Christian Ziemski Date: Thursday, August 19, 2004 10:58 AM On Thu, 19 Aug 2004 09:37:00 -0400, barry ennis wrote: >From the start the routine will be marginally helpful in that most of >the files I need to convert have more than 32 excel columns but less >than 50. > >You mention that the new release will accommodate larger tab files. It's already in 6.12, but still somehow restricted... >Will the macro you supplied work or will it have to be tweaked. It has to be modified. I uploaded the new experimental version here: http://ziemski.privat.t-online.de/vedit/macros/tab2fix-2.vdm That one is restricted to 100 TABs max. (Internally there are other restrictions too. See inside the macro.) Since I don't have files for testing, you have to do that ;-) Christian

Topic: Re: Converting Excel tab file to fixed length (9 of 23), Read 68 times Conf: Converting, Translating From: Ted Green Date: Thursday, August 19, 2004 11:48 AM At 10:58 AM 8/19/2004, you wrote: >From: "Christian Ziemski" > >That one is restricted to 100 TABs max. (Internally there are other >restrictions too. See inside the macro.) I will create a vpw.exe with at least 100 tab stops this weekend. Ted.

Topic: Re: Converting Excel tab file to fixed length (10 of 23), Read 67 times Conf: Converting, Translating From: Christian Ziemski Date: Friday, August 20, 2004 02:05 AM Since my version tab2fix-2.vdm was also too limited I did a new one: http://ziemski.privat.t-online.de/vedit/macros/tab2fix-3.vdm That one uses uniform TAB stops sized by the largest data field. This may blow up the data file a bit (dependent on your data), but it seems to have no limit regarding the number of TABs. Christian

Topic: Re: Converting Excel tab file to fixed length (11 of 23), Read 64 times Conf: Converting, Translating From: barry ennis Date: Friday, August 20, 2004 12:36 PM Christian, I've tried tabfix-2 and tabfix-3. I like the way tab-fix2 works in that it uniformly spaces each field in a record to the largest record of the field plus one space. If you had a version that overcame the 32 tab stop limitation that would be great. Tabfix-3 creates too big a file and if one field in the file is say 1,000 characters with 100 fields, wow that would be huge.

Topic: Re: Converting Excel tab file to fixed length (12 of 23), Read 64 times Conf: Converting, Translating From: Ted Green Date: Friday, August 20, 2004 12:51 PM At 12:36 PM 8/20/2004, you wrote: >From: "barry ennis" > >Christian, I've tried tabfix-2 and tabfix-3. I like the way tab-fix2 works in that it uniformly spaces each field in a record to the largest record of the field plus one space. If you had a version that overcame the 32 tab stop limitation that would be great. I will try to create a VEDIT with 100+ tab stops this weekend. Ted.

Topic: Re: Converting Excel tab file to fixed length (13 of 23), Read 64 times Conf: Converting, Translating From: Christian Ziemski Date: Friday, August 20, 2004 01:40 PM On Fri, 20 Aug 2004 00:36:00 -0400, barry ennis wrote: >Tabfix-3 creates too big a file and if one field in the file is >say 1,000 characters with 100 fields, wow that would be huge. Yes, but it has no limit regarding the number of TABs ;-)) TAB2FIX-3 was only meant as workaround for the few hours until VEDIT can handle more than 32 TABs like Ted already wrote yesterday! Christian

Topic: Re: Converting Excel tab file to fixed length (8 of 23), Read 67 times Conf: Converting, Translating From: Ted Green Date: Thursday, August 19, 2004 11:30 AM At 04:45 AM 8/19/2004, you wrote: >I wrote a little conversion macro that may help to convert a TABed >file to fixed length: >http://ziemski.privat.t-online.de/vedit/macros/tab2fix.vdm Thank you for the macro Christian. >Since the macro uses VEDITs internal TAB handling it is restricted to >32 TABs in this version! I forgot about that limitation; I do plan to increase this number and fix the reported problem related to large tab numbers. Ted,

Topic: Re: Converting Excel tab file to fixed length (14 of 23), Read 64 times Conf: Converting, Translating From: barry ennis Date: Friday, August 20, 2004 02:40 PM Thanks all looking forward to new Macro. On second thought I see a use for the tabfix-3 macro. In my previous job I had to append Mainframe data to user generated Excel data. Because my mainframe program did not handle tabbed files I created a fixed length file for each excel file and an accompanying schema to read it. This was a tedious process since sometimes I received 5 excel files each having a different number of columns. I thought that if I could somehow create say 20 generic schema's on the mainframe side and have excel or some middle ware pgm ex. (I used Rexx) to create fixed column sizes I would save a lot of time and tedium.

Topic: Re: Converting Excel tab file to fixed length (15 of 23), Read 67 times Conf: Converting, Translating From: Ian Binnie Date: Friday, August 20, 2004 09:43 PM On 8/20/2004 2:40:44 PM, barry ennis wrote: >Thanks all looking forward to >new Macro. On second thought >I see a use for the tabfix-3 >macro. In my previous job I >had to append Mainframe data >to user generated Excel data. >Because my mainframe program >did not handle tabbed files I >created a fixed length file >for each excel file and an >accompanying schema to read >it. This was a tedious process >since sometimes I received 5 >excel files each having a >different number of columns. I >thought that if I could >somehow create say 20 generic >schema's on the mainframe side >and have excel or some middle >ware pgm ex. (I used Rexx) to >create fixed column sizes I >would save a lot of time and >tedium. I often had to export Excel data to different formats. I found the best way to handle this was a VBA routine which explicitly set the column widths e.g.:- ActiveWindow.DisplayZeros = False Columns("A:A").Select Selection.ColumnWidth = 14 ...

Topic: Re: Converting Excel tab file to fixed length (18 of 23), Read 67 times Conf: Converting, Translating From: barry ennis Date: Monday, August 23, 2004 09:51 AM When you set the column widths, what was the save as format used for the resulting file.

Topic: Re: Converting Excel tab file to fixed length (19 of 23), Read 68 times Conf: Converting, Translating From: Ted Green Date: Monday, August 23, 2004 11:07 AM Sorry, but I did not complete a new VEDIT with more tab stops this weekend. Hopefully by the end of this week. Ted.

Topic: Re: Converting Excel tab file to fixed length (20 of 23), Read 68 times Conf: Converting, Translating From: Christian Ziemski Date: Monday, August 23, 2004 03:39 PM On Mon, 23 Aug 2004 11:07:00 -0400, Ted Green wrote: >Sorry, but I did not complete a new VEDIT with more tab stops >this weekend. >Hopefully by the end of this week. In the meantime I wrote http://ziemski.privat.t-online.de/vedit/macros/tab2fix-4.vdm to enjoy Barry ;-) Barry: That new macro should work up to 100 TABs. Since it does things manually it will be slower on huge data files than versions 1-3. Christian

Topic: Re: Converting Excel tab file to fixed length (23 of 23), Read 66 times Conf: Converting, Translating From: Christian Ziemski Date: Wednesday, August 25, 2004 01:30 AM I found a bug in version 4: The last entry could get lost. Sorry for that. So here the fixed one: http://ziemski.privat.t-online.de/vedit/macros/tab2fix-4.vdm Barry: Please let me know whether the new version works for you. Since it doesn't require a new vpw.exe it should ... Christian

Topic: Re: Converting Excel tab file to fixed length (21 of 23), Read 65 times Conf: Converting, Translating From: Ian Binnie Date: Monday, August 23, 2004 08:40 PM On 8/23/2004 9:51:26 AM, barry ennis wrote: >When you set the column >widths, what was the save as >format used for the resulting >file. Most of my applications required dbase .dbf format, which is fixed record length with a header (this is readable by Vedit). The width also works with .prn files, which I believe you were already using. My comment was related to the need to set explicit column widths to produce consistent results, rather than letting Excel guess.

Topic: Re: Converting Excel tab file to fixed length (22 of 23), Read 68 times Conf: Converting, Translating From: Christian Ziemski Date: Tuesday, August 24, 2004 12:51 AM On 8/23/2004 8:40:26 PM, Ian Binnie wrote: >On 8/23/2004 9:51:26 AM, barry ennis >wrote: >>When you set the column >>widths, what was the save as >>format used for the resulting >>file. > > >Most of my applications required dbase >.dbf format, which is fixed record >length with a header (this is readable >by Vedit). > >The width also works with .prn files, >which I believe you were already using. >My comment was related to the need to >set explicit column widths to produce >consistent results, rather than letting >Excel guess. Ian: Sorry for my wrong/too early reply to Barry's message ("ASCII"). I misinterpreted his one and thought he would asking in reply to one of my own messages... Somehow Webboard together with my Newsreader mixed up the threading. Christian

Topic: Re: Converting Excel tab file to fixed length (16 of 23), Read 67 times Conf: Converting, Translating From: Ian Binnie Date: Saturday, August 21, 2004 04:19 AM On 8/19/2004 4:44:41 AM, Christian Ziemski wrote: >I wrote a little conversion macro that >may help to convert a TABed >file to fixed length: >http://ziemski.privat.t-online.de/vedit/ >macros/tab2fix.vdm > >It detects the longest field for every >data column and sets the TABs >accordingly. Then it does a De-Tab. > This is a useful macro, I will make a version that only sets tab stops. NOTE the Config_Tab command should have a LOCAL flag.

Topic: Re: Converting Excel tab file to fixed length (17 of 23), Read 72 times Conf: Converting, Translating From: Christian Ziemski Date: Saturday, August 21, 2004 05:31 AM On Sat, 21 Aug 2004 04:19:00 -0400, Ian Binnie wrote: >>macros/tab2fix.vdm >This is a useful macro, I will make a version that only sets tab stops. > >NOTE the Config_Tab command should have a LOCAL flag. I plan to combine some of my TAB-related macros into one menu-driven macro with several options. Christian