When working with different information systems sometimes you need to convert data files from one format to another. Not all systems can generate or export data in common or standard ways. Lately, I had to convert a huge Fixed-Width file to a Tab-delimited one in order to load it to my system, so I thought I’d share one quick way of doing so.

Update: if you’re interested in the reverse conversion  I’ve added a post for a Windows app that converts tab-delimited or CSV to flat file.

Convert CSV to Flat-File

What is a Fix-Width or a Flat file?

Those are usually text files which contain data that is organized in a table like order, each field has a fixed width of data, and if the data itself is shorter than the defined width, it gets filled with spaces (or zeros for numbers), thus filling the entire file with characters. Below is an example of a flat-file.

Fixed Width (Flat file) text file

Convert fixed width file to Tab delimited or CSV

With notepad++ this is done very quickly.

1. Open the flat file with notepad++
2. Toggle the “Replace” tool with Ctrl+H
3. Select “Regular Expressions” in the Search Mode Section
4. Enter  ( ){2,}  in the “Find What” field. This string searches for all the spaces (more than 2) because we allow for spaces in our data like in “William Jefferson Clinton”
5. Enter  \t   in the “Replace With” filed (or  ,  for CSV file)
6. Click on “Replace All” and you’re done!

Below are screenshots of before the regex replace, and after

Flat file before regex replace

after regex replace, we have a tab delimited file