|| Feedback | Part Two of text files ||
Excel allows users to save workbooks in a variety of formats including text (TXT), comma separated values (CSV) and XML files. Occasionally however you may either only want to export a particular range of cells or the destination application may not accept the precise file format created by Excel. It can then be useful to export data by creating your own text files.
This section contains various examples of simple macros which will export data to (or import data from) text files.
|6||Charges to Medical staff||Items||6|
|7||NAME||ACCOUNT||ADDRESS 1||ADDRESS 2||PHONE||DESCRIPTION 1||AMOUNT|
|8||DR MOSS||12563||43 THE STREET||CANTERBURY||01899 852258||Telephone calls 21/3||14.63|
|9||PROF FLOWER||1225||28 NORTH ROAD||MARGATE||01899 562236||Room hire 14/3-16/3||85|
|10||MISS DAISY||15523||26 MARGATE DRIVE||WHITSTABLE||06223 223556||Damage to desk||122.32|
|11||DR WEED||1300||GATSBY, DOVER AVENUE||PADDOCK WOOD||06223 224514||Overpayment in June||48.6|
|12||DR BINDING||13330||224 CANTERBURY STOUR||CANTERBURY||01688 512555||Telephone calls 24/3||5.23|
|13||MR GREENACRE||25336||RED FARM, STURRY RD||CHARING||01899 852114||Mess bill - extra milk||14.5|
Many program interfaces require a fixed width string of text to import data. Within these strings, different fields are consolidated and each piece of data is placed at a specific position in every record. For example an address field may begin at character 23. This format requires that fields are limited to a maximum number of characters in order not to overlap with the next data item. It is also necessary to provide padding in between items to properly space them.
A regular table of data (such as in the example above) can either be exported directly to a text file or the fixed width strings could be created in another range of cells, ready to be copied or exported as a separate step.
A macro can be linked to a button which exports a table of data to a text file such as the following:
PROF FLOWER 1225 28 NORTH ROAD MARGATE 01899 562236 Room hire 14/3-16/3 0000085.00
MISS DAISY 15523 26 MARGATE DRIVE WHITSTABLE 06223 223556 Damage to desk 0000122.32
DR WEED 1300 GATSBY, DOVER AVENUE PADDOCK WOOD 06223 224514 Overpayment in June 0000048.60
DR BINDING 13330 224 CANTERBURY STOUR CANTERBURY 01688 512555 Telephone calls 24/3 0000005.23
MR GREENACRE 25336 RED FARM, STURRY RD CHARING 01899 852114 Mess bill - extra milk 0000014.50
This file was produced by the following macro.
See also help in creating macros or assigning macros to buttons.
|19||Compiled data using a formula||Items||3|
|21||DR MOSS 12563 43 THE STREET CANTERBURY 01899 852258 Telephone calls 21/3 0000014.63|
|22||PROF FLOWER 1225 28 NORTH ROAD MARGATE 01899 562236 Room hire 14/3-16/3 0000085.00|
|23||MISS DAISY 15523 26 MARGATE DRIVE WHITSTABLE 06223 223556 Damage to desk 0000122.32|
The formula (in cell A21) to create this string is as follows:
=A8 & REPT(" ",15-LEN(A8)) & B8 & REPT(" ",7-LEN(B8)) & C8 & REPT(" ",20-LEN(C8)) & D8 & REPT(" ",15-LEN(D8)) & E8 & REPT(" ",13-LEN(E8)) & F8 & REPT(" ",25-LEN(F8)) & TEXT(G8,"0000000.00")
The REPT() function is used to pad the spaces in between each of the data items. It adds a number of spaces, dependent upon the length of the text value in each cell. Instead of padding the final numeric value, the TEXT() function has been used to convert its format.
|26||"DR WEED",1300,"GATSBY, DOVER AVENUE","PADDOCK WOOD","06223 224514","Overpayment in June",48.6|
|27||"DR BINDING",13330,"224 CANTERBURY STOUR","CANTERBURY","01688 512555","Telephone calls 24/3",5.23|
|28||"MR GREENACRE",25336,"RED FARM, STURRY RD","CHARING","01899 852114","Mess bill - extra milk",14.5|
The formula (in cell A26) to create this string is as follows:
=CHAR(34) & A11 & CHAR(34) & "," & B11& "," & CHAR(34) & C11& CHAR(34) & "," &CHAR(34) & D11& CHAR(34) & "," & CHAR(34) & E11& CHAR(34) & "," & CHAR(34) & F11& CHAR(34) & "," & G11
Note that the function CHAR(34) inserts speech marks ("). Numeric fields are not normally enclosed with speech marks.
It is helpful to format cells containing fixed width strings with a font such as Courier which spaces each character evenly. Otherwise a True Type font (such as Ariel) will shrink the padded spaces and obscure the formatting.
There may be little call for producing a comma separated field with this long manual method. Excel will save an identical text file simply by supplying each of the fields as columns or fields on a form (see next example). There may however be occasions when fussy programs expect CSV files to be in a slightly different format - such as with no speech marks around the text.
Part two - exporting data from a form
|This page is included in
the Excel functions guide
|Click here for details about
obtaining this file
|file: xlexport-text1.htm.htm||Last updated Aug06||© MeadInKent.co.uk 2016||CMIDX S5 P20 Y|