Text records in Excel

[Mervyn Thomas]

I am creating a text file within excel and I do not know how to create what
I think is needed and that is an end of line character for each line. The
text is all in column A, one record in each cell. The text file that excel
creates appears to be
different to the model I am using in Notepad at the end of the line.
Anyone any experience in this?

 

[David McRitchie]

Your question is ambiguous both in what you get and what you are comparing
it to, and even whether it makes a difference or not to your usage.

In fact, I don't even know if you are trying to create a CSV file, text in columns,
or just writing records i.e. HTML records..

From with VBA you can use xlCR or CHR(13) for carriage return,
xlLF or CHR(10) for line feed, cor xlCRLF or CHR(13)&CHR(10) for CRLF.
if producing one long string.

 

[Mervyn Thomas]

Sorry I was not understood. I am seeking to produce an excel file where
each record is in A1,A2 (one record per cell) etc and then saving the file
in text format to be able to import the records into a banking system. Each
record needs to be right padded with blanks to produce a record that is 178
characters wide. I have a user function that produces the right padding OK
but when the txt file is viewed in TEXTPAD it does not have a line feed
character in position 179 and after all the records are shown there appears
to be loads of space after the records have finished which the system does
not like.

I am building the file in VB by copying a function in one cell to the
others - it looks like:
=CONCATENATE("1ROYTEST2.TXT",TEXT(Import!$G$1,"00000000"),TEXT(Import!$H$1,"000000"),RIGHTPAD("
",152),D9)

So how do I build your CHR(10) for example into this and is there another
one to ensure the complete file ends at the last record? Where do I find
documentation on these CHR characters?

Thanks for your patience!
Mervyn

Your question is ambiguous both in what you get and what you are
comparing
it to, and even whether it makes a difference or not to your usage.

In fact, I don't even know if you are trying to create a CSV file, text
in columns,
or just writing records i.e. HTML records..

From with VBA you can use xlCR or CHR(13) for carriage return,
xlLF or CHR(10) for line feed, cor xlCRLF or CHR(13)&CHR(10) for
CRLF.
if producing one long string.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I am creating a text file within excel and I do not know how to create
what
I think is needed and that is an end of line character for each line.
The
text is all in column A, one record in each cell. The text file that
excel
creates appears to be
different to the model I am using in Notepad at the end of the line.
Anyone any experience in this?

 

[David McRitchie]

In your worksheet formula you would include CHAR(10)
-- the use of CHR(10) would be for the same but for use in Visual Basic

to make sure you have right padding
=LEFT(A1& REPT(" ",178),178)
which would not require checking if you are below or past 178 characters.
You would include the equivalent of the above in your formula.

However since your purpose is to create a text file, it would probably be better
to write the file directly with VBA macro code.

BTW, Most people would not use the concatenate worksheet formula
but instead do their concatenation like this
H4: =A4 & " " & B4 & " " & C4
instead of
H4: =CONCATENATE(A4," ", B4, " ", C4)
means exactly the same thing.:
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Sorry I was not understood. I am seeking to produce an excel file where
each record is in A1,A2 (one record per cell) etc and then saving the file
in text format to be able to import the records into a banking system. Each
record needs to be right padded with blanks to produce a record that is 178
characters wide. I have a user function that produces the right padding OK
but when the txt file is viewed in TEXTPAD it does not have a line feed
character in position 179 and after all the records are shown there appears
to be loads of space after the records have finished which the system does
not like.

I am building the file in VB by copying a function in one cell to the
others - it looks like:
=CONCATENATE("1ROYTEST2.TXT",TEXT(Import!$G$1,"00000000"),TEXT(Import!$H$1,"000000"),RIGHTPAD("
",152),D9)

So how do I build your CHR(10) for example into this and is there another
one to ensure the complete file ends at the last record? Where do I find
documentation on these CHR characters?

Thanks for your patience!
Mervyn

Your question is ambiguous both in what you get and what you are
comparing
it to, and even whether it makes a difference or not to your usage.

In fact, I don't even know if you are trying to create a CSV file, text
in columns,
or just writing records i.e. HTML records..

From with VBA you can use xlCR or CHR(13) for carriage return,
xlLF or CHR(10) for line feed, cor xlCRLF or CHR(13)&CHR(10) for
CRLF.
if producing one long string.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I am creating a text file within excel and I do not know how to create
what
I think is needed and that is an end of line character for each line.
The
text is all in column A, one record in each cell. The text file that
excel
creates appears to be
different to the model I am using in Notepad at the end of the line.
Anyone any experience in this?

 

[Mervyn Thomas]

I'm beginning to think that Excel2000 does not save text properly but seems
to pad out each line with a number of "tab rights" both for each record and
after the last record as blank lines. I have tried the CHAR(10) as right
now I am trying to get the file right manually(in Excel) rather than VB.
This does NOT put an end of line character in the text file. Would VB work
any different in saving the text file format?

I have now got a work around by stripping out the "tab horizontals" in
TEXTPAD (replacing Hex(9)with blanks) but I would like to understand what
Excel is doing!

Thanks for your continuing help
Mervyn

In your worksheet formula you would include CHAR(10)
-- the use of CHR(10) would be for the same but for use in Visual Basic

to make sure you have right padding
=LEFT(A1& REPT(" ",178),178)
which would not require checking if you are below or past 178 characters.
You would include the equivalent of the above in your formula.

However since your purpose is to create a text file, it would probably
be better
to write the file directly with VBA macro code.

BTW, Most people would not use the concatenate worksheet formula
but instead do their concatenation like this
H4: =A4 & " " & B4 & " " & C4
instead of
H4: =CONCATENATE(A4," ", B4, " ", C4)
means exactly the same thing.:
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Sorry I was not understood. I am seeking to produce an excel file where
each record is in A1,A2 (one record per cell) etc and then saving the
file
in text format to be able to import the records into a banking system.
Each
record needs to be right padded with blanks to produce a record that is
178
characters wide. I have a user function that produces the right padding
OK
but when the txt file is viewed in TEXTPAD it does not have a line feed
character in position 179 and after all the records are shown there
appears
to be loads of space after the records have finished which the system
does
not like.

I am building the file in VB by copying a function in one cell to the
others - it looks like:
=CONCATENATE("1ROYTEST2.TXT",TEXT(Import!$G$1,"00000000"),TEXT(Import!$H$1,"000000"),RIGHTPAD("
",152),D9)

So how do I build your CHR(10) for example into this and is there another
one to ensure the complete file ends at the last record? Where do I find
documentation on these CHR characters?

Thanks for your patience!
Mervyn

Your question is ambiguous both in what you get and what you are
comparing
it to, and even whether it makes a difference or not to your usage.

In fact, I don't even know if you are trying to create a CSV file,
text
in columns,
or just writing records i.e. HTML records..

From with VBA you can use xlCR or CHR(13) for carriage return,
xlLF or CHR(10) for line feed, cor xlCRLF or CHR(13)&CHR(10) for
CRLF.
if producing one long string.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I am creating a text file within excel and I do not know how to create
what
I think is needed and that is an end of line character for each line.
The
text is all in column A, one record in each cell. The text file that
excel
creates appears to be
different to the model I am using in Notepad at the end of the line.
Anyone any experience in this?

 

[David McRitchie]

Depends on what you are using
Char(10) is LF line-feed used in Excel and a lot of Microsoft things
Char(13) is CR Carriage-return used in Unix
Char(13)&Char(10) is CRLF used in text files, and was what was universal
until some systems decided they could save a byte.

Char(9) is TAB

For VBA you can look at Chip Pearson's
Exporting to Text Files
http://www.cpearson.com/excel/imptext.htm#Export
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I'm beginning to think that Excel2000 does not save text properly but seems
to pad out each line with a number of "tab rights" both for each record and
after the last record as blank lines. I have tried the CHAR(10) as right
now I am trying to get the file right manually(in Excel) rather than VB.
This does NOT put an end of line character in the text file. Would VB work
any different in saving the text file format?

I have now got a work around by stripping out the "tab horizontals" in
TEXTPAD (replacing Hex(9)with blanks) but I would like to understand what
Excel is doing!

Thanks for your continuing help
Mervyn

In your worksheet formula you would include CHAR(10)
-- the use of CHR(10) would be for the same but for use in Visual Basic

to make sure you have right padding
=LEFT(A1& REPT(" ",178),178)
which would not require checking if you are below or past 178 characters.
You would include the equivalent of the above in your formula.

However since your purpose is to create a text file, it would probably
be better
to write the file directly with VBA macro code.

BTW, Most people would not use the concatenate worksheet formula
but instead do their concatenation like this
H4: =A4 & " " & B4 & " " & C4
instead of
H4: =CONCATENATE(A4," ", B4, " ", C4)
means exactly the same thing.:
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Sorry I was not understood. I am seeking to produce an excel file where
each record is in A1,A2 (one record per cell) etc and then saving the
file
in text format to be able to import the records into a banking system.
Each
record needs to be right padded with blanks to produce a record that is
178
characters wide. I have a user function that produces the right padding
OK
but when the txt file is viewed in TEXTPAD it does not have a line feed
character in position 179 and after all the records are shown there
appears
to be loads of space after the records have finished which the system
does
not like.

I am building the file in VB by copying a function in one cell to the
others - it looks like:
=CONCATENATE("1ROYTEST2.TXT",TEXT(Import!$G$1,"00000000"),TEXT(Import!$H$1,"000000"),RIGHTPAD("
",152),D9)

So how do I build your CHR(10) for example into this and is there another
one to ensure the complete file ends at the last record? Where do I find
documentation on these CHR characters?

Thanks for your patience!
Mervyn



Your question is ambiguous both in what you get and what you are
comparing
it to, and even whether it makes a difference or not to your usage.

In fact, I don't even know if you are trying to create a CSV file,
text
in columns,
or just writing records i.e. HTML records..

From with VBA you can use xlCR or CHR(13) for carriage return,
xlLF or CHR(10) for line feed, cor xlCRLF or CHR(13)&CHR(10) for
CRLF.
if producing one long string.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I am creating a text file within excel and I do not know how to create
what
I think is needed and that is an end of line character for each line.
The
text is all in column A, one record in each cell. The text file that
excel
creates appears to be
different to the model I am using in Notepad at the end of the line.
Anyone any experience in this?