Automating conversion from one CSV format to another.

CSVMaker

The CSV Maker takes CSV files and using a mapping file converts them from one layout to another. This solves the problem where a file is supplied from another system on a recurring basis but the layout does not match the format you need to import into your system, so every month someone has to open it in Excel, add columns and rearrange columns as well as inserting static text such as F9.

What do we mean by conversion?

Well it might be more accurate to say transformation, because with a map file and CSV Maker you can rearrange the order of columns, skip columns and insert additional static information such as key press info for your import routines.

If you use the keyword [DATE] in any filenames it will swap that to the current date using the dateformat in the application config file, so you can convert c:\exports\customers[DATE].csv and it will find the daily export etc.

Switches

/<map>=C:\Program Files\PolyB2B\Maps\CustomerMap.csv (You will have a different map file for every conversion – use the CSV Mapper to easily make these)

/<source>=C:\Program Files\PolyB2B\incoming\customers.CSV

/<dest>=C:\Program Files\PolyB2B\outgoing\customers.CSV (the name of the output file, usually a CSV but could be another format such as KFI)

/<delimiter>=, (you can select a custom delimiter is you are not using commas in the source file)

/<Header> =True (output the column names in the first row)

CSV Maker supports the [DATE] keyword in filenames. When you run the application it will replace any occurrence of the text [DATE] with the current date using the format specified in the supporting application configuration file.

The CSV Maker do not need or have a user interface, think of it as a robot.

Attache Users

CSV make converts from one csv format to another. It just so happens that the KFI format is a formatted CSV so you could use the CSV Maker and a map file to convert from a csv to KFI. This solves the problem where a file is supplied from another system on a recurring basis but the layout does not match the KFI format, so every month someone has to open it in Excel, add columns and rearrange columns as well as inserting key press test such as F9.

The CSV maker can also append an options file to instruct KFI about which screen to use, the speed and whether to minimize.

CSV Mapper

The CSV Mapper is a visual tool designed to create a mapping file for use with the CSV Maker Application. The Map file is used to transform or rearrange the source CSV. If you get csv files from a third party application that you want to import on a regular basis, you are probably currently opening this in Excel, rearranging the columns manually and inserting columns with actions such as F9 or TAB.

The CSV Mapper will make a map file that describes the process of rearranging the columns and inserting static text or “actions” so that the CSV Maker (see above) can do this for you. You define the map once and the CSV Maker uses this to do the work for you and coupled with the scheduler, this can now be automated.

Because the end map file is only a formatted CSV file, you could easily edit this file using Excel. The CSV Mapper simply makes this process a bit easier.

CSVMapper

Using the CSVMapper

The CSV Mapper is very simple. Just choose a source filename to load the list of column names from the first row. To add columns to the output, select a column in the list on the left of the screen. This can be done by double clicking the source columns (they are added to the end of the output list) or click a column and then click on the add button. To reorder the output, use the up and down buttons.

To insert any actions, click on the actions combo and choose an action then click Add Action.

To wrap the text of any columns click the check box.

Once you have the layout in the order that you need, choose a map file name and then click make. This saved map file is now ready to be used with the CSV Maker tool.

Actions

Allows you to insert pieces of static text, these are used by many import applications to perform some sort of action or can be a static value for a field. Either type a value or choose one form the supplied list. You can update the actions.txt file if you want your own actions to show in the list.

Options

You can choose KFI options such as speed and save these options to an options file.

These are shown on the KFI Options tab as shown above, however to gain access to this tab you should run the program with the switch

/<Attache>=True

Simply browse to the file using the folder icon to the right of the file name. When you select it the CSV Mapper will ask if you want to load the existing settings.

Transforming your source CSV file to your output CSV

The resulting map file can be used on the command line, with the Poly Scheduler or by clicking on the Transform button.

The related files

Map.csv

sourcecol,destcol,action,wrap
3,1,0,False
1,2,0,False
2,3,0,True
0,4,<SKIP FIELD>,False
0,5,CUS,False
0,6,<F6>,False

Looking at the sample map.csv you can see that it has four columns

Sourcecol The number of the source column – where the data will be found

Destcol The number this column need to be placed in the output

Action A zero indicates no action or a data column. Otherwise the action will be a keypress or a static value – for example, if you always want branch AK.

Wrap Some text fields need to be wrapped, this option lets you tell KFI Maker to put quotes around this column as it converts it.

Actions The actions.txt file contains a list of actions that can be inserted into the map file. To add your own custom actions or to refer to static text such as a GL SET, simply edit this file in notepad and next time you run CSV Mapper it will be available.

<PgDn> <PgUp> <F4> <F5> <F6> <F7> <F8> <F9> <TAB>

KFI Options (Attache Users)

You can define an options file and the CSVMaker can append this to the start of your file to tell the unattended KFI what screen to use, how fast to run and whether to minimize the screen.

Options.txt

<Speed=100><Minimize><Object_ID=#565>

KFI Screens (Attache Users)

A list of screens for selection as part of the options file are defined in a file called KFIScreens.csv. As new screens are added to Attache7 this file is easily updated. If a new screen is added to Attache you can simply add it here without the need to get a new copy of CSVMapper.

Want to try it out first?

We understand that you might want to take a test drive so just email is for a demo version.

 

Like what you see?

For just $995 you can have a full copy. 

This price includes all of the b2b suite modules, CSV Conversion, ODBC Exporting, Scheduling, Scripting and Unattended FTP. Talk to us today and let us show you how you could use your web presence more effectively and improve the value of this vital business resource.

Prices are in NZ dollars and exclude GST.