How to convert a CSV file into a fixed width file

Yes, totally possible.

Define an import table with fixed width char fields, import CSV, then export that table into a fixed width text file.

Bye, Olaf.

Thanks guys,

In the first instance I just wanted to know whether it could be done and as usual I perhaps have not explained myself well enough (apologies Coachman!) or probably not used the correct terminology.

An example type of record I am importing is as follows
123456,88888888,A N Other,,900.00

This is a record composed of 5 fields (the fourth in my example is blank but could be populated)

My aim is to import that file (with many similar records) into a program which would then create a file with records with a fixed length, say of 106 characters.

Therefore the data held in the original CSV file would  insert in specific locations of that long record.

So far, Is that still possible? and should I explain further?

Please, I hope I do not offend if I am coming across badly, it is not my intention.

Thanks

< (apologies Coachman!) >
No need to apologize, ...I consider us old friends now...
;-)

Jeff

Coachman ...lolol...thanks

Thanks all,

I am going to work on it..... I'll get back as soon as I can

PatHartman

You have highlighted a concern i have, i.e. padding and space filling:

Could you explain this :-

<<Text values are left justified and space filled.  The process of space filling is a little more complicated since it requires multiple nested functions.  
... YourField & Space(10 - Nz(Len(YourField), 0)) >>

Thanks

I explained it but I'll try again.  To "fill" a field with spaces, my example used three functions.  Space() - which returns the specified number of space characters which in the example would be 10.  But, in a file of fields of random width, we can't know ahead of time exactly how many filler characters a particular field in each row will require.  That is where the Nz() and Len() come.  Len() tells us how long a value is so Len("abc") would be 3 but Len() returns null if the field is null so we need Nz() to substitute a 0 when the length of the field is null.  Since 10 is the actual fixed length of the field, we subtract the value returned by the len() function to know how many space characters we need to append to any field.

So if the value is "xyz", Len() will return 3 so 10 - 3 = 7 space characters will be concatenated.
If the value is "abcdefghij", Len() will return 10 so 10 - 10 = 0 so no additional space characters will be concatenated.
If the value is null, Len() will return 0 so 10 -0 = 10  space characters will be concatenated.

Thanks

all valid comments