0

I have a fixed-width file which is used by the interface as it is. Now, I have some 500 records for which one of the fields is blank. I have the information separately with me.

I was wondering if there is a way to convert the fixed width file into Excel, put the missing field in using Excel, and convert the file back to fixed-width without altering the layout. Is it possible? If yes, how? If no, is there an alternative method to do so?

Ben N
  • 42,308
Manik Agarwal
  • 1

2 Answers2

1

Excel will want to save a .txt file as tab delimited. To update a column while preserving your existing fixed columns, you will have to:
1) Import your file into excel using the import wizard to define your columns. Be careful to keep the columns lengths the same as the original.
2) Update the column with the values you wish.
3) Export the file using the .prn format.
4) Rename the file to .txt.

The .prn format will save the data in fixed positions.

Changing the font to Courier New or any other non-true type font will make it easier to see your columns line up.

B540Glenn
  • 1,095
1

Is conversion to/from Csv/Excel really the best way. It may work for 500 records but you will have trouble if there are 1,000,000 records. Using a Text-Editor / RecordEditor allow for editting

Possible solutions:

  1. Programming - Easy to do in your favorite scripting language. You could even write a program to convert files to/from CSV.
  2. If you are dedicated to converting the file, I think a specialist CSV editor is better option than Excell. If you do a lot of Fixed-Width <==> Csv-Conversion and have Cobol Copybooks viadee (in German) JRecord to convert Fixed width to/from CSV.
  3. Text Editor - some editor's will show the column the cursor is in (e.g. JEdit); this is useful for column based editing. Some let you do syntax highlighting via regular expression. VIM might be worth looking at.
  4. A fixed width editor like RecordEditor supports fixed width editting. Note: I am the autor of the RecordEditor

In the RecordEditor, have a look at "Syntax" Highlight a flatfile

  • Select the File and click the Layout Wizard button

enter image description here

  • For the first screen just click next button

enter image description here

  • On the second screen select the columns

enter image description here

  • Finally enter the field names

enter image description here

Community
  • 1
Bruce Martin
  • 260