{"id":45984,"date":"2026-11-13T06:46:05","date_gmt":"2026-11-13T06:46:05","guid":{"rendered":"https:\/\/www.withdata.com\/blog\/?p=45984"},"modified":"2026-11-13T06:46:05","modified_gmt":"2026-11-13T06:46:05","slug":"fixed-width-text-file-columns-by-position","status":"publish","type":"post","link":"https:\/\/www.withdata.com\/blog\/datafileconverter\/fixed-width-text-file-columns-by-position.html","title":{"rendered":"Fixed-Width Text Files: Columns by Position, Not by Separator"},"content":{"rendered":"<p>A fixed-width file has no delimiter. There is no comma, no tab, no header row \u2014 the columns are defined by <em>character positions<\/em>, and the only thing that says where one ends and the next begins is a specification somewhere else. That is why the format outlived the machines that invented it, and why it still breaks spreadsheets today: every tool that expects &#8220;separator then value&#8221; reads the whole line as one column.<\/p>\n<h2>Where these files still come from<\/h2>\n<ul>\n<li><strong>Mainframe and COBOL exports<\/strong> \u2014 a COBOL <code>PIC X(20)<\/code> field is twenty bytes, always, padded with spaces.<\/li>\n<\/ul>\n<p>The layout lives in a copybook, not in the file.<\/p>\n<ul>\n<li><strong>Bank and payment files<\/strong> \u2014 fixed record lengths with numeric fields whose sign is stored in the last digit<\/li>\n<\/ul>\n<p>(zoned or packed decimal), so <code>0000123N<\/code> is a number, not text.<\/p>\n<ul>\n<li><strong>Government and EDI exchanges<\/strong> \u2014 the receiving system validates by position; a shifted character is a<\/li>\n<\/ul>\n<p>rejected file, not a warning.<\/p>\n<ul>\n<li><strong>Old reporting tools<\/strong> \u2014 printed reports &#8220;saved as text&#8221;, where column alignment came from the printer.<\/li>\n<\/ul>\n<h2>What a layout looks like<\/h2>\n<p>A customer record, 60 characters wide, as it appears in the file:<\/p>\n<p>&#8220;<code> 000000123ACME EXPORTS        ACCT-44      20241130    0000000152500 <\/code>&#8220;<\/p>\n<p>and the specification behind it:<\/p>\n<table>\n<thead>\n<tr>\n<th>Position<\/th>\n<th>Width<\/th>\n<th>Field<\/th>\n<th>Type<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>1\u20139<\/td>\n<td>9<\/td>\n<td>customer id<\/td>\n<td>numeric, zero-padded<\/td>\n<\/tr>\n<tr>\n<td>10\u201329<\/td>\n<td>20<\/td>\n<td>name<\/td>\n<td>text, space-padded right<\/td>\n<\/tr>\n<tr>\n<td>30\u201341<\/td>\n<td>12<\/td>\n<td>account<\/td>\n<td>text<\/td>\n<\/tr>\n<tr>\n<td>42\u201349<\/td>\n<td>8<\/td>\n<td>date<\/td>\n<td><code>YYYYMMDD<\/code><\/td>\n<\/tr>\n<tr>\n<td>50\u201360<\/td>\n<td>11<\/td>\n<td>amount in cents<\/td>\n<td>numeric, zero-padded<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Nothing in the file says any of that. The widths above are the whole schema \u2014 which is why the same file without its specification is a wall of 60-character lines.<\/p>\n<h2>The three things that actually hurt<\/h2>\n<h3>1. Without a copybook, the boundaries are a guess<\/h3>\n<p>You have a file of 200-byte records and no specification. The practical route is to look at a slice of raw lines in a monospaced editor with a character ruler: values that repeat in the same character range are the same column. Two clues make it reliable \u2014 a column that is blank in every row is padding, and a column whose values are all digits at a constant width is numeric with leading zeros, not text.<\/p>\n<h3>2. Leading zeros and padded spaces are data<\/h3>\n<p><code>000000123<\/code> is not <code>123<\/code>. In a fixed-width file the width <em>is<\/em> the type: a nine-character numeric field that holds <code>123<\/code> is written <code>000000123<\/code>, and the zeros survive only if the reader keeps them. The same applies at the other end \u2014 a 20-character name field is right-padded with spaces, and if those spaces are not trimmed, every later comparison (<code>WHERE name = 'ACME'<\/code>) stops matching.<\/p>\n<h3>3. A record is not necessarily a line<\/h3>\n<p>Most fixed-width exports put one record per line. Some do not: mainframe transfers often concatenate records with no line break at all, so the file&#8217;s length is a multiple of the record length and reading it line by line produces one enormous &#8220;line&#8221;. Check first: if the file has newlines, the line length tells you the record length; if it has none, divide the file size by the record length and see whether the remainder is zero.<\/p>\n<h2>Encoding is a separate question<\/h2>\n<p>Fixed-width files are usually single-byte: cp1252 for Western European text, cp037 (EBCDIC) for older mainframe transfers. There is no BOM and no declaration; the only way to know is the specification or the source system. A file read with the wrong code page produces coherent-looking garbage \u2014 accented characters become <code>\u201a<\/code> or <code>?<\/code>, and the column positions stay correct, which makes it look like a data problem rather than an encoding one.<\/p>\n<h2>Converting it into something a spreadsheet can read<\/h2>\n<p>The job is the same in every direction: <strong>declare the column widths, then write a delimited file or a workbook<\/strong>. In practice:<\/p>\n<ol>\n<li><strong>Detect the widths<\/strong> from a sample \u2014 most converters can propose boundaries from repeated whitespace<\/li>\n<\/ol>\n<p>patterns; treat the proposal as a starting point.<\/p>\n<ol>\n<li><strong>Edit the boundaries by hand<\/strong> until the preview shows the columns you expect. This is the step where a<\/li>\n<\/ol>\n<p>single character of drift turns a numeric column into two text columns.<\/p>\n<ol>\n<li><strong>Choose the target<\/strong> \u2014 CSV when something else will read it, XLSX when a person will. Keep leading zeros by<\/li>\n<\/ol>\n<p>writing those columns as text, not as numbers.<\/p>\n<ol>\n<li><strong>Verify one known record<\/strong> against the specification, not just the row count: a file can have the right<\/li>\n<\/ol>\n<p>number of rows and every value in the wrong column.<\/p>\n<p>The same widths apply to the reverse direction: writing a fixed-width file from a spreadsheet means right-padding text, zero-padding numbers, and truncating anything longer than the field \u2014 silently, because the format has no way to complain.<\/p>\n<h2>When the file is 2 GB<\/h2>\n<p>Fixed-width files are often big, because that is what they were designed for. Splitting is safe <em>only<\/em> if the split respects the record length \u2014 cutting at an arbitrary byte offset produces a first file that ends mid-record. Convert first (a delimited file is line-based, and splitting by rows is then trivial), or split by record count rather than by size.<\/p>\n<h2>What to check before trusting the output<\/h2>\n<ul>\n<li>One record copied from the source and compared field by field with the converted row.<\/li>\n<li>The column count in every row \u2014 a truncated final record is the most common defect in these files.<\/li>\n<li>Leading zeros and trailing spaces on a known value (<code>000000123<\/code>, <code>ACME    <\/code>).<\/li>\n<li>The total row count against the specification, if one exists.<\/li>\n<\/ul>\n<p>The fixed-width tools start from the same step: the widths. Once those are right, the rest is a normal conversion.<\/p>\n<ul>\n<li><a href=\"\/tools\/file-to-file\/fixedwidth-to-csv\">Convert fixed-width text to CSV<\/a> \u2014 set the widths, preview the columns,<\/li>\n<\/ul>\n<p>write a delimited file<\/p>\n<ul>\n<li><a href=\"\/tools\/file-to-file\/csv-to-fixedwidth\">Write a fixed-width file from CSV<\/a> \u2014 the reverse direction, with padding<\/li>\n<\/ul>\n<p>and truncation done for you<\/p>\n<ul>\n<li><a href=\"\/tools\/file\/fixedwidth\/list\">All fixed-width tools<\/a> \u2014 import into a database, split a huge file, merge several<\/li>\n<\/ul>\n<p>If the file is not fixed-width after all \u2014 if the columns are separated by runs of spaces that shift by a character here and there \u2014 that is a different problem, and <a href=\"\/blog\/datafileconverter\/txt-to-csv.html\">a TXT to CSV conversion<\/a> is where to start.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A fixed-width file has no delimiter. There is no comma, no tab, no header row \u2014 the columns are defined by character positions, and the only thing that says where one ends and the next begins is a specification somewhere else. That is why the format outlived the machines that invented it, and why it [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1477],"tags":[13887,13751,13889,13885,13886,13888],"class_list":["post-45984","post","type-post","status-publish","format-standard","hentry","category-datafileconverter","tag-cobol-copybook","tag-datafileconverter","tag-fixed-length-record","tag-fixed-width-file","tag-fixed-width-to-csv","tag-mainframe-export"],"_links":{"self":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/45984","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/comments?post=45984"}],"version-history":[{"count":1,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/45984\/revisions"}],"predecessor-version":[{"id":45985,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/45984\/revisions\/45985"}],"wp:attachment":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/media?parent=45984"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/categories?post=45984"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/tags?post=45984"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}