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.

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 Text 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 regex replace

Flat file before regex replace

 

after regex replace, we have a tab delimited file

 

 

 

Leave a comment