I am trying to take a text file exported from a mainframe system, edit it in
excel, then import it back to the mainframe. All spaces are significant, but
whether I parse the file by fixed width directly from the text file or by
using Text to Columns any spaces that start or end a column are lost. Also,
if the entire column consists of spaces they are not brought in.
Is there a setting I can use to force the spaces to carry over?
Thanks in advance for any assistance.
ed

Data Import External Data Import Data.. tell the Wizard the name of the
file and use a delimiter that does not exit. Then specifiy the format as
Text.
--
Gary''s Student - gsnu200777


"expect_ed" wrote:

I am trying to take a text file exported from a mainframe system, edit it in
excel, then import it back to the mainframe. All spaces are significant, but
whether I parse the file by fixed width directly from the text file or by
using Text to Columns any spaces that start or end a column are lost. Also,
if the entire column consists of spaces they are not brought in.
Is there a setting I can use to force the spaces to carry over?
Thanks in advance for any assistance.
ed

Thanks for the quick reply. However the solution does not allow me to parse
the data into columns, only to work with an entire row of data as one column.
As soon as I try to parse it out using Text to Columns I still end up with
coumns that should contain 8 spaces but contain nothing.
I need to do an import with fixed width settings so I can replace certain
columns with new values and leave the others untouched.
thanks again.
ed

"Gary''s Student" wrote:

Data Import External Data Import Data.. tell the Wizard the name of the
file and use a delimiter that does not exit. Then specifiy the format as
Text.
--
Gary''s Student - gsnu200777


"expect_ed" wrote:

I am trying to take a text file exported from a mainframe system, edit it in
excel, then import it back to the mainframe. All spaces are significant, but
whether I parse the file by fixed width directly from the text file or by
using Text to Columns any spaces that start or end a column are lost. Also,
if the entire column consists of spaces they are not brought in.
Is there a setting I can use to force the spaces to carry over?
Thanks in advance for any assistance.
ed

Here is a macro that will read fixed width data. Modify the number of
columns and Folder as necessary.


Sub FixedWidth()

Const Folder = "C:\temp\test"

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Const StartCol = 1
Const Colwidth = 2
Dim ColWidths(7, 2)
Dim Data(7)
ColWidths(1, StartCol) = 1
ColWidths(1, Colwidth) = 21
ColWidths(2, StartCol) = 22
ColWidths(2, Colwidth) = 21
ColWidths(3, StartCol) = 43
ColWidths(3, Colwidth) = 14
ColWidths(4, StartCol) = 57
ColWidths(4, Colwidth) = 23
ColWidths(5, StartCol) = 80
ColWidths(5, Colwidth) = 15
ColWidths(6, StartCol) = 95
ColWidths(6, Colwidth) = 16
ColWidths(7, StartCol) = 111
ColWidths(7, Colwidth) = 16


Set fsread = CreateObject("Scripting.FileSystemObject")

'open files
ChDir (Folder)
FName = Application.GetOpenFilename("CSV (*.csv),*.csv")
If FName < False Then

Set fread = fsread.GetFile(FName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)
RowCount = 1
Do While tsread.atendofstream = False

InputLine = tsread.ReadLine
For DataField = 1 To 7
Cells(RowCount, DataField) = Trim(Mid(InputLine, _
ColWidths(DataField, StartCol), _
ColWidths(DataField, Colwidth)))
Next DataField

RowCount = RowCount + 1
Loop
End If
End Sub


"expect_ed" wrote:

Thanks for the quick reply. However the solution does not allow me to parse
the data into columns, only to work with an entire row of data as one column.
As soon as I try to parse it out using Text to Columns I still end up with
coumns that should contain 8 spaces but contain nothing.
I need to do an import with fixed width settings so I can replace certain
columns with new values and leave the others untouched.
thanks again.
ed

"Gary''s Student" wrote:

Data Import External Data Import Data.. tell the Wizard the name of the
file and use a delimiter that does not exit. Then specifiy the format as
Text.
--
Gary''s Student - gsnu200777


"expect_ed" wrote:

I am trying to take a text file exported from a mainframe system, edit it in
excel, then import it back to the mainframe. All spaces are significant, but
whether I parse the file by fixed width directly from the text file or by
using Text to Columns any spaces that start or end a column are lost. Also,
if the entire column consists of spaces they are not brought in.
Is there a setting I can use to force the spaces to carry over?
Thanks in advance for any assistance.
ed

Hi Joel,
I haven't run this macro, not sure exactly where to substitute my values,
but I don't think this is going to do it. Especially since it seems you are
intentionally trimming the inputs with:
Trim(Mid(InputLine, _
ColWidths(DataField, StartCol), _
ColWidths(DataField, Colwidth)))
My goal is actually the inverse, to get the untrimmed data. Is there some
reason I need to do this through a macro rather than by a manual import or
Text to Columns that will preserve the leading and trailing spaces and fixed
columns that contain only spaces?

Sorry if I am misinterpreting your help.
Thanks
ed

"Joel" wrote:

Here is a macro that will read fixed width data. Modify the number of
columns and Folder as necessary.


Sub FixedWidth()

Const Folder = "C:\temp\test"

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Const StartCol = 1
Const Colwidth = 2
Dim ColWidths(7, 2)
Dim Data(7)
ColWidths(1, StartCol) = 1
ColWidths(1, Colwidth) = 21
ColWidths(2, StartCol) = 22
ColWidths(2, Colwidth) = 21
ColWidths(3, StartCol) = 43
ColWidths(3, Colwidth) = 14
ColWidths(4, StartCol) = 57
ColWidths(4, Colwidth) = 23
ColWidths(5, StartCol) = 80
ColWidths(5, Colwidth) = 15
ColWidths(6, StartCol) = 95
ColWidths(6, Colwidth) = 16
ColWidths(7, StartCol) = 111
ColWidths(7, Colwidth) = 16


Set fsread = CreateObject("Scripting.FileSystemObject")

'open files
ChDir (Folder)
FName = Application.GetOpenFilename("CSV (*.csv),*.csv")
If FName < False Then

Set fread = fsread.GetFile(FName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)
RowCount = 1
Do While tsread.atendofstream = False

InputLine = tsread.ReadLine
For DataField = 1 To 7
Cells(RowCount, DataField) = Trim(Mid(InputLine, _
ColWidths(DataField, StartCol), _
ColWidths(DataField, Colwidth)))
Next DataField

RowCount = RowCount + 1
Loop
End If
End Sub


"expect_ed" wrote:

Thanks for the quick reply. However the solution does not allow me to parse
the data into columns, only to work with an entire row of data as one column.
As soon as I try to parse it out using Text to Columns I still end up with
coumns that should contain 8 spaces but contain nothing.
I need to do an import with fixed width settings so I can replace certain
columns with new values and leave the others untouched.
thanks again.
ed

"Gary''s Student" wrote:

Data Import External Data Import Data.. tell the Wizard the name of the
file and use a delimiter that does not exit. Then specifiy the format as
Text.
--
Gary''s Student - gsnu200777


"expect_ed" wrote:

I am trying to take a text file exported from a mainframe system, edit it in
excel, then import it back to the mainframe. All spaces are significant, but
whether I parse the file by fixed width directly from the text file or by
using Text to Columns any spaces that start or end a column are lost. Also,
if the entire column consists of spaces they are not brought in.
Is there a setting I can use to force the spaces to carry over?
Thanks in advance for any assistance.
ed