Saving from excel to fixed width file

[pmsteph]

I need to save/copy data I have set up in Excel into a fixed width file for importing into a database.

Is this possible & how do I set it up so the columns are the correct widths?

Any help would be useful.

 

[Greg Truby]

We could certainly automate this with VBA; and perhaps someone with time to do it will stop by. But in the meantime, here's a simply way to get you going.

1. Create a row with your required widths. (Row 1 in my example.)
2. Create a formula that concatenates the columns together, using the LEN() and REPT() functions to pad appropriately.
3. Then you can copy the contents of your composite column over to NOTEPAD, paste in and save and your done.

book1
	A	B	C	D	E	F
1	5	10	10	8	1	Composite
2	aa	BBBBB	CCCCCCC	DDDDDD	Z	aa BBBBB CCCCCCC DDDDDD Z
3	a	BB	CCCCC	D	Z	a BB CCCCC D Z
4	AAAA	bbbbbbb	CcCc	dddd	Z	AAAA bbbbbbb CcCc dddd Z
Sheet1

Formula in F2 and downwards: <ul>[*]=A2&REPT(" ",A$1-LEN(A2))&B2&REPT(" ",B$1-LEN(B2))&C2&REPT(" ",C$1-LEN(C2))&D2&REPT(" ",D$1-LEN(D2))&E2&REPT(" ",E$1-LEN(E2))[/list]

HTH

 

[Greg Truby]

OK - this does actually work -- the problem is that the PHP software of this board strips out double spaces. So don't worry that column F doesn't look right in the posted example.