Dynamic data import / export

datenbank

simply more productive
Up to 10,000 records can be processed with the freeware version!

Convert an MS DOS ASCII CSV to a Windows ANSI flat file

With this example we wish to demonstrate how easy it is to convert text files to other formats (codepages and locales) with the TextFile Adapter and at the same time perform dynamic calculations.

Convert MS-DOS ASCII to Windows ANSI
Convert MS-DOS ASCII to Windows ANSI

You can open the example directly in FlowHeater using the menu option "Examples->TextFile Adapter->ASCII CSV to Windows ANSI Flat File". You can see it done the other way around (ANSI to ASCII) here.

Note: The same procedure is used to convert to UNICODE (utf7, utf8, ...), EBCDIC, etc.

Task

Let us suppose we received an MS DOS ASCII CSV file from a supplier in the USA. In the CSV text file the date format is m.d.yyyy (month.day.year). Numbers have the format of comma as thousand-separator, point as decimal character and the currency symbol is the dollar sign (e.g. $1,000.34 ).
The description field contains two different pieces of information divided by a # symbol. In order to make it a little more difficult, the description often contains the CSV separator character itself.

 

Result

From the CSV file described above we now want to generate a normal Windows ANSI flat (report) file. In so doing, the fields will be output with fixed lengths/column widths. We also want to use a German format for the date and convert the currency to Euro. In addition we want to calculate an uplift of the sales price at 23%.

 

What we have to do

Open the FlowHeater Designer and save a new Definition file using the menu option "File->New". Select in the dropdowns the TextFile Adapter on both sides.

 

On the READ (left) side

  1. open the Configurator for the READ (left) side
  2. for the filename enter "ascii-csv.txt"
  3. check the checkbox "First row contains field names"
  4. for the codepage select "437, IBM 437,OEM United States"
  5. switch to the "Separators" tab and enter as the separator string "abc"
  6. switch the the "Format" tab and choose for the locale "English (USA)"

That is all for this side.

 

On the WRITE (right) side

  1. open the Configurator for the WRITE (right) side
  2. for the filename enter something like "output.txt"
  3. check the checkbox "First row contains field names"
  4. under the delimitation radio options click on "Fixed widths"
  5. make sure that the codepage is configured as "1252, Windows-1252,Western European (Windows)"
  6. switch to the "Fields / Data types" tab and click on the COPY button labeled "Copy fields from the READ Adapter"
  7. amend the field name from "Description" to "Description-1"
  8. amend the field name from "Price" to "PP" (= purchase price)
  9. insert two new fields "Description-2" and "SP" (= selling price)
  10. assign the data type of Currency for the PP (purchase price) and SP (selling price) fields
  11. for the Date field assign the data type of Date
  12. switch to the "Format" tab and ensure that the locale is set to "German (Germany)"

That is all for this side.

 

Finishing up

Now all we need is to connect the individual fields together and introduce a handful of Heaters in between. How to do this is described in the Hello World (1, 2, 3) examples, or simply inspect the completed Definition provided in the FlowHeater examples directory.