Converting Fixed-Width Text Files To CSV in C++ (and for free)

C++ Logo

Large Padded Data

A recent data acquisition brought forth the requirement to process fixed-width text files that comprise the data. This would not have been much of a discussion point were it not for the fact that some of the files were huge – 60Gb in one case. Most of these large files comprise the space character, serving as padding for the fixed-width fields; this serves to illustrate how inefficient fixed-width text files are, but that is not the point we’re making here today.

Converting Using Existing Applications

We decided to convert each file into a CSV file, which can easily be read, edited and loaded into a database. There are applications that are able to convert massive text files to CSV, but during our brief trial of a few programs we found:-

  • One application was unreliable (crashed)
  • One application was buggy (unable to handle commas and/or quotes)
  • One application was expensive (several hundred dollars)

… and anyway, programming your own is much more fun. We created a C++ application in order to process these large files as quickly as possible.

Our C++ Application

We created a C++ command line application called texttocsv. We complied it as a Windows 32 executable, but the code is ANSI C++, used no Windows API code, uses no other libraries, and will compile for other operating systems.

texttocsv can read an 8 bit character fixed with text file (there is no support for Unicode) and quickly convert each row to CSV. It will enclose each field in quotes only where needed, and correctly escape quotes within fields.

A Short Example

Consider the following fixed-width text file (test.txt) with two fixed columns of 20 and 50 characters:-

Jupiter             A planet                 
Andromeda           A "nearby" galaxy
Sirius              A star
Eros                An asteroid, a rocky body
Titan               A moon

…we used texttocsv.exe to create the following CSV (test.csv):-

Jupiter,A planet
Andromeda,"A ""nearby"" galaxy"
Sirius,A star
Eros,"An asteroid, a rocky body"
Titan,A moon

Note that the “Andromeda” row has the quotes around “nearby” correctly escaped.

How To Use

The parameters for texttocsv are:-

  1. Destination file
  2. Source file
  3. List of column widths in the source file, separated by a comma
  4. Start column number, 0 based. This parameter is optional.

The example above was created with the following command:-

texttocsv.exe test.csv test.txt 20,50

Our C++ Source Code

Introduction

The application was created in ANSI C++. We followed the RAII programming technique, creating classes whose destructors release their resources, and which cannot be created using new.

Execute Sequence

The program is entered at main, and receives the parameters as entered by the user. The paramaters are read, and any missing paramters are reported to the user. If all mandatory parameters are present, an instance of CMyProcess (which is derived from CTextToCSV) is created on the stack. The member function Process is executed, and the returned error code is checked and reported.

CTextToCSV Class

The processing is done using an instance of a class derived from CTextToCSV. The derived class should include the method Progress which is invoked for each row processed; our application simply reports to console every 10000 rows, but if the class was used in a GUI, a progress bar could be displayed. The class must be created on the stack – new is not permitted.

CTextToCSV includes some error codes in an enum named ErrCode. The project code should normally interpret the errors and display to the user, as our simple application does.

CTextToCSV will close any open files in its destructor, and the instances of the other classes used (CCharBuffer and CWidthList) free their allocated buffers in their own destructors. Each class instance is created on the stack, guaranteeing that any resources are released.

The classes include buffer overwrite protection. Memory consumption is modest and is proportional to total width of the fixed text file, and memory allocation errors (if the system is short on memory) are handled, returning the ErrCode value OutOfMem.

All memory allocated is freed by the class destructors.

The Code

#include
#include
#include

//class to hold the array of column widths
class CWidthList
{
private:
   unsigned int* m_Column_Width;
   unsigned int m_Num_Cols;
   unsigned int m_Total_Width;

   //prevent new from being used - force
   //any instance to be on the stack
   void * operator new (size_t);
   void * operator new[] (size_t);

public:
   //return width of column number (zero based)
   //returns 0 if column number was invalid
   unsigned int GetWidth(unsigned int column_number)
   {
      if(column_number>=0 && column_numberm_Column_Width[column_number]);
      }

      return 0;
   }

   //return total width of columns
   unsigned int GetTotalWidth(void)
   {
      return(this->m_Total_Width);
   }

   //return the number of columns
   unsigned int GetNumCols(void)
   {
      return(this->m_Num_Cols);
   }

   //parse the widths string
   //returns false if out of memory
   bool ParseWidths(const char* p_widths)
   {
      unsigned int total = 0;

      unsigned int i=0;
      unsigned int len=strlen(p_widths);

      //count number of commas in the width string
      this->m_Num_Cols=1;
      while(im_Num_Cols++;
         }
         i++;
      }

      //create the buffer for the column widths

      try
      {
         this->m_Column_Width=new unsigned int[this->m_Num_Cols];
      }
      catch (std::bad_alloc)
      {
         //out of memory for the buffer
         return false;
      }

      //set all widths to 0
      i=0;
      while(i      {
         m_Column_Width[i]=0;
         i++;
      }

      char val[8];
      int valpos=0;
      int w;
      i=0;
      unsigned int cur_col=0;
      while(im_Column_Width[cur_col]=w;
            total+=w;
            cur_col++;
            valpos=0;
         }
         else if(c>='0' && cm_Column_Width[cur_col] = w;
      total += w;
      this->m_Total_Width=total;

      return true;
   }

};

//simple 8 bit character buffer class
class CCharBuffer
{
private:

   char* m_Data;
   unsigned int m_Max_Chars;      //maximum number of
                                  //chars allowed
   unsigned int m_Pos;            //current write pos

   //prevent new from being used -
   //force any instance to be on the stack
   void * operator new (size_t);
   void * operator new[] (size_t);

public:

   //constructor
   CCharBuffer()
   {
      try
      {
         //allocate num chars plus an extra
         this->m_Max_Chars = 32;
         this->m_Data=new char[this->m_Max_Chars+1];
      }
      catch (std::bad_alloc)
      {
         //out of memory when creating the buffer
         //so mark the buffer as not created
         this->m_Data=NULL;
         this->m_Max_Chars = 0;
      }

      this->m_Pos=0;
   }

   //destructor
   ~CCharBuffer()
   {
      //free the allocated buffer
      if(this->m_Data!=NULL)
      {
         delete this->m_Data;
         this->m_Data=NULL;
      }
   }

   //return false if failed to allocate a buffer
   bool CheckSpace(const unsigned int num_chars)
   {
      if(num_chars m_Max_Chars)
      {
         return true;
      }

      char* new_dest = NULL;

      try
      {
         //allocate num chars plus an extra
         new_dest = new char[num_chars+1];
      }
      catch (std::bad_alloc)
      {
         //out of memory when resizing destination buffer
         return false;
      }

      if(this->m_Pos>0 && this->m_Data!=NULL)
      {
         //copy the existing data into the new buffer
         memcpy(new_dest,this->m_Data,this->m_Pos);
      }

      if(this->m_Data!=NULL)
      {
         delete this->m_Data;      //delete the OLD buffer
                                   //(if it existed)
      }

      this->m_Data=new_dest;         //and use the new one
      this->m_Max_Chars=num_chars;

      return true;
   }

   //specify the current position
   void SetPos(const unsigned int val)
   {
      this->m_Pos=val;
      //ensure poos
      if(valm_Pos=0;
      }
      else if(val>=this->m_Max_Chars)
      {
         this->m_Pos=this->m_Max_Chars-1;
      }
   }

   const unsigned int GetPos(void)
   {
      return this->m_Pos;
   }

   //make space and add a character
   //returns false if failed to make space
   bool Add(const char c)
   {
      if(CheckSpace(this->m_Pos+1)==false)
      {
         return false;
      }

      this->m_Data[this->m_Pos]=c;
      this->m_Pos++;

      return true;
   }

   //make space and add a string
   bool Add(const char* src,const unsigned int num_chars)
   {
      if(CheckSpace(this->m_Pos+num_chars)==false)
      {
         return false;
      }

      memcpy(this->m_Data+this->m_Pos,src,num_chars);
      this->m_Pos+=num_chars;

      return true;
   }

   //read pointer to the buffer - only valid while the
   //instance is in scope
   char* Read(void)
   {
      return this->m_Data;
   }
};

//class to process a fixed width text file into a CSV
class CTextToCSV
{
public:

   //error codes
   enum ErrCode
   {
      None = 0,
      OutOfMem,
      FileNotFound,
      FileOpenForWriteFailed
   };

private:
//members
   FILE* m_Dest;            //destination (CSV) file
   FILE* m_Src;            //source text file

   CCharBuffer m_Src_Buffer;
   CCharBuffer m_Dest_Buffer;
   CWidthList m_Width;

   unsigned int m_Start_Col;   //start column (optional)

protected:
   //prevent new from being used - force any
   //instance to be on the stack
   void * operator new (size_t);
   void * operator new[] (size_t);

//private nethods
private:

   //read field into m_Dest_Buffer
   //returns OutOfMem if failed to resize dest buffer
   //
   ErrCode ReadField(const int curpos,const int width)
   {
   //first, scan src to get the trimmed extents
   //and to discover if comma is present

      int start=curpos;
      int end=curpos+width;

      //read
      const char* src_buf=this->m_Src_Buffer.Read();

      while(startstart)
      {
         if(src_buf[end]!=0x20)
         {
            //non space found
            break;
         }
         end--;
      }
      //start and end are inclusive

      bool enclose_in_commas = false;
      int i=start;
      while(im_Dest_Buffer.Add(
			 src_buf+start,bytes_to_copy)==false)
         {
            //insufficient space in the destination buffer
            return OutOfMem;
         }
      }
      else
      {
         //enclose in quotes and escape any double quote character

         //add opening quotes
         if(this->m_Dest_Buffer.Add('"')==false)
         {
            //insufficient space in the destination buffer
            return OutOfMem;
         }

         //copy all characters and escape any double quote
         while(startm_Dest_Buffer.Add('"');
               this->m_Dest_Buffer.Add('"');
            }
            else
            {
               //simply add the character
               if(this->m_Dest_Buffer.Add(src_buf[start])==false)
               {
                  //out of memory
                  return OutOfMem;
               }
            }
            start++;
         }

         //add closing quotes
         if(this->m_Dest_Buffer.Add('"')==false)
         {
            return OutOfMem;
         }
      }
      return None;
   }

   //process each row
   //returns ErrCode (normally None)
   ErrCode ProcessRow(void)
   {

      this->m_Dest_Buffer.SetPos(0);

      //if a CR is found, terminate the src buffer before it
      char* src_buf=this->m_Src_Buffer.Read();

      char* sp=strstr(src_buf,"\r");
      if(sp!=NULL)
      {
         sp[0]=0;
      }

      //pad the src buffer with spaces
      int len=strlen(src_buf);
      int pad_len=this->m_Width.GetTotalWidth()-len;
      if(pad_len>0)
      {
         //pad with spaces
         sp=src_buf + len;
         memset(sp,0x20,pad_len);
      }

      //read each field
      unsigned int x=0;
      int curpos=0;

      if(this->m_Start_Col>0 &&
      this->m_Start_Colm_Width.GetNumCols())
      {
         //specified start column is valid
         while(x < this->m_Start_Col)
         {
            curpos += this->m_Width.GetWidth(x);
            x++;
         }
      }

      while(x < this->m_Width.GetNumCols())
      {
         if(ReadField(curpos,
            this->m_Width.GetWidth(x))==OutOfMem)
         {
            //failed to read a field due to memory failure
            return OutOfMem;
         }

         x++;

         //add a comma UNLESS this is the last field
         if(xm_Width.GetNumCols())
         {
            if(this->m_Dest_Buffer.Add(',')==false)
            {
            //insufficient space in the destination buffer
               return OutOfMem;
            }
            //add the width of previous column
            curpos += this->m_Width.GetWidth(x-1);

         }

      }

      fwrite(this->m_Dest_Buffer.Read(),
             1,
             this->m_Dest_Buffer.GetPos(),
             this->m_Dest);

      fwrite("\r\n",1,2,this->m_Dest);

      return None;

   }

   //close files
   void Close(void)
   {
      //close src file
      if(this->m_Src!=NULL)
      {
         fclose(this->m_Src);
         this->m_Src=NULL;
      }

      //close dest file
      if(this->m_Dest!=NULL)
      {
         fclose(this->m_Dest);
         this->m_Dest=NULL;
      }

   }

protected:
   //process progress report as each row is read
   virtual void Progress(unsigned int row_num)
   {
   }

public:
   //constructor
   CTextToCSV()
   {
      this->m_Dest = NULL;
      this->m_Src = NULL;
   }

   //destructor
   ~CTextToCSV()
   {
      //close open files and free allocated buffers
      Close();
   }

   ErrCode Process(const char* p_dest_file,
                   const char* p_src_file,
                   const char* p_widths,
                   const char* p_start_col)
   {
      this->m_Start_Col=0;

      //parse the widths string
      if(this->m_Width.ParseWidths(p_widths)==false)
      {
         return OutOfMem;
      }

      //ensure the src buffer has
      //sufficient space to read total width
      if(this->m_Src_Buffer.CheckSpace(
		  this->m_Width.GetTotalWidth()*2)==false)
		  //ensure src buffer min size
      {
         return OutOfMem;
      }

      //open source file
      this->m_Src=fopen(p_src_file,"rb");
      if(this->m_Src==NULL)
      {
         //failed to open src file

         return FileNotFound;
      }

      //open destination file
      this->m_Dest=fopen(p_dest_file,"wb");
      if(this->m_Dest==NULL)
      {
         //failed to open dest file
         return FileOpenForWriteFailed;
      }

      //read start column number if set
      if(p_start_col!=NULL)
      {
         this->m_Start_Col=atoi(p_start_col);
      }

      unsigned int row=0;      //row counter
      while(1==1)
      {
         void* result=fgets(this->m_Src_Buffer.Read(),
                            this->m_Width.GetTotalWidth()*2,
                            this->m_Src);
         if(result==NULL)
         {
            break;
         }
         row++;

         ErrCode err = ProcessRow();
         if(err!=None)
         {
            return err;
         }

         Progress(row);

      }

      //and close files and buffers
      Close();

      return None;
   }

};

//class derived from CTextToCSV to allow
//bespoke progress handling
class MyProcess : public CTextToCSV
{
public:

protected:
   //process progress report as each row is read
   void Progress(unsigned int row_num)
   {
      if((row_num%10000)==0)
      {
         printf("Row %d\r\n",row_num);
      }
   }

};

int main(int argc, char* argv[])
{
   printf("TextToCSV Version 1.0.0.1 (c) 2014\r\n\r\n");

   //read params
   int num_param=argc;
   if(num_param   {
      printf("parameters:-\r\n\r\n");
      printf("dest filename (e.g. mydata.csv)\r\n");
      printf("source filename (e.g. mydata.txt\r\n");
      printf("column widths (e.g. 10,10,20,30,50\r\n");
      printf("start column position (optional, 0 based)\r\n");

      return(0);
   }

   const char* src=NULL;
   const char* dest=NULL;
   const char* widths=NULL;
   const char* start_col=NULL;

   int i=1;
   while(i   {
      const char* pr=(const char*)argv[i];
      //assign each paramater
      if(pr)
      {
         if(dest==NULL)
         {
            dest=pr;
         }
         else if(src==NULL)
         {
            src = pr;
         }
         else if(widths==NULL)
         {
            widths = pr;
         }
         else if(start_col==NULL)
         {
            start_col = pr;
         }
      }
      i++;
   }

   if(src == NULL)
   {
      printf("Missing source filename");
      return -1;
   }
   if(dest == NULL)
   {
      printf("Missing dest filename");
      return -1;
   }
   if(widths == NULL)
   {
      printf("Missing column widths");
      return -1;
   }

   printf("Processing file %s into file %s\r\n\r\n",src,dest);

   //an instance of our class, derived from CTextToCSV
   //note that this instance is created on the stack which is simpler and
   //safer than using new and delete.
   //
   MyProcess curpos;

   //and process the file
   MyProcess::ErrCode err = curpos.Process(dest,src,widths,start_col);

   //read error code if and print a report to console
   if(err!=MyProcess::None)
   {
      switch(err)
      {
         case MyProcess::OutOfMem:
            printf("Error: out of memory\r\n\r\n");
            break;
         case MyProcess::FileNotFound:
            printf("Error: source file not found\r\n\r\n");
            break;
         case MyProcess::FileOpenForWriteFailed:
            printf("Error: unable to open destination file\r\n\r\n");
            break;

         default:
            break;

      }
   }
   else
   {
      printf("process completed, no errors\r\n");

   }

   return 0;
}

2 thoughts on “Converting Fixed-Width Text Files To CSV in C++ (and for free)

  1. Hi,

    any chance you provide a download link for the texttocsv.exe ?
    I stumbled onto this searching for something what could help me.
    I have a flat file importing into database procedure built in visual studio but it’s not good enough because source file is crap. And i really can’t control it (corporate environment :)). Example 3 lines from file (notice the crlf positions):
    0000000858FI OKOYFIHH 578007-20228068 EUR1 FI6357800720228068 OKOYFIHH crlf
    0000001537FI HANDFIHH 313130-1051135 EUR FI4331313001051135 HANDFIHH crlf
    0000001537SE BANKGIROT 8855421 BGC crlf
    So the file is missing nice junk of whitespace om line 3 and visual studio standard importing capabilities end here.

    • Hello

      We’ve uploaded a zipped version of texttocsv.exe, version 1.0.0.4

      This is a Windows 32 executable, will work on any Windows PC running XP or later. It does NOT need the latest Windows runtime. The zip file is just 59612 bytes.

      http://t2a.co/dl/texttocsv_1004.zip

      Your browser may impose security restrictions that will need to be relaxed in order to download the zip file.

Leave a Reply