What is the best practice to convert a fixed width file to a CSV file?

Question

We're trying to convert a fixed width file to a CSV file.  The best I could come up with is a custom mapping but when files are consumed by the channel the first row is always skipped.

Comments

When I changed the header boolean to TRUE I get all my rows.  The channel seems to think my fixed width file has a header.  I don't see anywhere to tell the source or mapping that there is no header.

message = qie.createCSVMessage("", false, '"', ",", 'UTF-8', true);

Answer 1

Here is some code for a custom mapping that will convert your fixed width file to a CSV.

A few notes:

• * Modify the header in the message = qie.createCSVMessage() line to suit your needs
• * The code assumes whitespace in the fixed width file to separate the data elements
• * Make sure your fixed width file uses newline termination (i.e. each row ends in a \n character)

// Start with a new CSV message
message = qie.createCSVMessage('Header1,Header2,Header3', true, '"', ',', 'UTF-8', true);

// Loop through each row of the source fixed width message
for (var i = 0; i < source.getCount('/'); i++) {
   
   // Save the current row to a variable
   var fxWdth = source.getNode('/[' + i + ']') + '';
   
   // Convert to a CSV - this assumes spaces/whitespace as the
   // separator between data elements (\s+ below)
   var csv = fxWdth.replace(/\s+/g, ',');
   csv = csv.split(',');
   
   // Loop through the newly created CSV row
   for (var x = 0; x < csv.length; x++) {
      // Add the row to the CSV message
      message.setNode((x+1) + '[' + (i+1) + ']', csv[x]);
   }
}

 

 

Comments

Unfortunately it's a very tightly packed fixed-width file, there is no whitespace.  I resorted to syntax like below to parse out each element:

      //qie.info('contract: ' +  source.getNode('1,5[' + j + ']'));
      var contract = StringUtils.trim(source.getNode('1,5[' + j + ']'));
      
      //qie.info('plannumber: ' +  source.getNode('6,3[' + j + ']'));
      var plannumber = StringUtils.trim(source.getNode('6,3[' + j + ']'));
      
      //qie.info('planname: ' + source.getNode('9,50[' + j + ']'));
      var planname = StringUtils.trim(source.getNode('9,50[' + j + ']'));
      
      //qie.info('lastname: ' + source.getNode('59,24[' + j + ']'));
      var lastname = StringUtils.trim(source.getNode('59,24[' + j + ']'));