fixed length file to excel? VBA macro possible?

[MaryLindholm]

have a text file I need to convert to an excel spreadsheet. I
wouldn't have a problem making an array code if it were a straight file
but the trick is that each line could be different.

For example:

Line one is

01330948951000312 eeid jan boerenkamp

line 2 is

023330948951000312 00370058274800350010112/01/2002

Line 3 is

05330948951000312 31065327123107533811

any time a line begins with 01 it will follow that format

any time a line begins with 02 it will follow the format of line 2 and
so on

Is there a way to make an array to put into Excel so that if a line
starts with 01 it splits it one way and if a line begins with 02 it
splits another way and so on?

Thanks in advance

 

[Bernie Deitrick]

Mary,

The macro below shows one way of doing this....

HTH,
Bernie
MS Excel MVP

Sub VariedLineImport()
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double

FileName = Application.GetOpenFilename

If FileName = "" Then End

FileNum = FreeFile()
Open FileName For Input As #FileNum

Application.ScreenUpdating = False

Workbooks.Add Template:=xlWorksheet

Counter = 1

Do While Seek(FileNum) <= LOF(FileNum)

'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 2) = "01" Then
MsgBox "That line started with 01"
'do the splitting here, like
Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1)
'Etc
ElseIf Left(ResultStr, 2) = "02" Then
MsgBox "That line started with 02"
'do the splitting here, like
Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1)
'Etc
ElseIf Left(ResultStr, 2) = "03" Then
MsgBox "That line started with 03"
'do the splitting here, like
Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1)
'Etc
'And so on for different starting values
End If
'Increment the Counter By 1
Counter = Counter + 1
Loop
'Close The Open Text File
Close
End Sub

 

[Guest]

Maybe something like the below pseudo code if the file is spaced delimited
Sub ReadData()
Dim ff as Long, rw as Long, v as Variant, line as String
Dim i as Long
ff = FreeFile()
rw = 1
Open "C:\Myfolder\Myfile.txt" for input as #ff
do while not eof(ff)
Line Input #FF, line
line = Application.Trim(line)
v = Split(Line," ")
for i = lbound(v) to ubound(v)
cells(rw,i).value = v(i)
Next
rw = rw + 1
Loop
Close #ff
End Sub

 

[NickHK]

Using the reading techniques sugeested by others, this may be an opportunity
to use a set of classes to handle each situation, if you feel you wish to
expand your VBA knowledge.
Untested, but you get the idea:

<cls_01>
Public function ProcessLine (argInput as string) as variant
'Assuming single space separated values
'Add trim and/or processing if required
ProcessLine = split(argInput, " ")
End function
</cls_01>

<cls_02>
Public function ProcessLine (argInput as string) as variant
Dim Temp(2) as variant
'Assuming 18 Chars[SPACE]21 Chars[Date]
'Add trim and/or processing if required
Temp(0)=left(argInput,18)
Temp(1)=Mid(argInput,20,21)
Temp(2)=Right(argInput,8)
'Or, if in date type
'Temp(2)=cDate(Right(argInput,8))
ProcessLine =temp()
End function
</cls_02>
... etc for other formats.
It may be a good time to use an interface/Implements if you feel so
inclined.

To me the advantage of this approach is that if any format change, it very
clear and easy to change how each class processes its own data.
And also add to the list in the future, by simply creating a new class.

NickHK

 

[MaryLindholm]

I'm not sure I totally understand. When bring in a fixed length file I
get something like this:

Workbooks.OpenText FileName:= _
"C:\Documents and Settings\ca5ms\Desktop\name line.txt",
Origin:=437, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0,
1), Array(2, _
1), Array(11, 1), Array(24, 1), Array(31, 1), Array(36, 1),
Array(51, 1), Array(66, 1), _
Array(91, 1), Array(131, 1), Array(171, 1), Array(211, 1),
Array(241, 1), Array(246, 1), _
Array(257, 1), Array(259, 1), Array(264, 1), Array(274, 1),
Array(275, 1), Array(278, 1), _
Array(318, 1), Array(328, 1), Array(338, 1), Array(341, 1),
Array(346, 1), Array(356, 1), _
Array(357, 1), Array(363, 1), Array(372, 1), Array(382, 1)),
TrailingMinusNumbers:= _
True

Because i need to format the lines that start with say 02 differently
than those that start with 03. In your model where would I put the
line formatting?

Thanks!
Mary

 

[NickHK]

Excel will not be able to import it on own.
Whilst good at many tasks, it cannot guess your intenetions.
So you have to help out with some extra code.
See the other responses.

NickHK

 

[Guest]

Just to be clear, the code suggested (and mine) would be in lieu of your
recorded code. It represents a different approach.

 

[MaryLindholm]

I'm totally down with that. however, i'm just trying to figure out
where to update the code so that I can put in my markers so it
delineates in the places I need.

 

[NickHK]

Tom was pointing out that Excel expects every row to be the same, so you
cannot use your code to read file, because the records of different formats.
You need to open/close the file yourself, read each record (line), determine
the format ("01", "02" etc) and process it accordingly.

Hence,
- Open the file
- Keep reading until the end of the file
- Read the line into a variable
- Check the format
- Process according
- Loop
- Close the file

NickHK

 

[Bernie Deitrick]

Mary,

How, for example would you want

023330948951000312 00370058274800350010112/01/2002

split up? Are the last 10 characters a date? Any other meaningful splits, or just use spaces?

Post samples of how each string needs to be split, and we can help you with the code to do it.

HTH,
Bernie
MS Excel MVP

 

[MaryLindholm]

Yes, I understand that, but what I'm not sure of is how I get my
formatting in.

This line: Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1,
ResultStr," ") - 1)
is where I imagine the formatting is but I'm not sure what I need to
do...or will I just have to manually update every line as it comes in?

eg: if I need my breaks to be at 2, 10, 25, and 40 in row one (and that
represents 4 cells) would that get incorporated into the line I cited?

I'm thinking I'm way over my head here but I do so appreciate your
help.

 

[Bernie Deitrick]

Mary,

Something like this would need to go into the appropriate section (I hope you can see the pattern):

Cells(Counter, 1).Value = "'" & Mid(ResultStr, 1, 1)
Cells(Counter, 2).Value = "'" & Mid(ResultStr, 3, 9)
Cells(Counter, 3).Value = "'" & Mid(ResultStr, 11, 15)
Cells(Counter, 4).Value = "'" & Mid(ResultStr, 26, 15)
Cells(Counter, 5).Value = "'" & Mid(ResultStr, 41, 15)

HTH,
Bernie
MS Excel MVP

 

[MaryLindholm]

I'm going from a fixed length file to excel

an 02 row would be start 1 end 2, start 3 end 11, start 12, end 31,
start 32 end 36
an 01 row would be start 1 end 2, start 3 end 28, start 29 end 37,
start 38 end 97
03 ould be different, etc.

Maybe there is no way to actually get it to do what I want. I'm just
hoping that I can take these fixed length rows and translate them into
excel. There are 6 possible records and each one has slightly differnt
fixed lengths. The file can be made up of thousands of records each of
which is a 1 through 6.

 

[Bernie Deitrick]

Oops, I messed up my editing and math...

Cells(Counter, 1).Value = "'" & Mid(ResultStr, 1, 2)
Cells(Counter, 2).Value = "'" & Mid(ResultStr, 3, 8)
Cells(Counter, 3).Value = "'" & Mid(ResultStr, 11, 15)
Cells(Counter, 4).Value = "'" & Mid(ResultStr, 26, 15)
Cells(Counter, 5).Value = "'" & Mid(ResultStr, 41, 15)

etc...

HTH,
Bernie
MS Excel MVP

 

[MaryLindholm]

Ok. I get that. Now for some strange reason it doesn't work well...

I did:

Cells(Counter, 1).Value = "'" & Mid(ResultStr, 1, 2)
Cells(Counter, 2).Value = "'" & Mid(ResultStr, 3, 11)
Cells(Counter, 3).Value = "'" & Mid(ResultStr, 12, 31)
Cells(Counter, 4).Value = "'" & Mid(ResultStr, 32, 36)
Cells(Counter, 5).Value = "'" & Mid(ResultStr, 37, 51)
Cells(Counter, 6).Value = "'" & Mid(ResultStr, 52, 66)
Cells(Counter, 7).Value = "'" & Mid(ResultStr, 67, 91)

for counter 2 it is adding places 12 & 13, yet on counter 3 it is
putting those 12 $ 13 back in.
For counter 5 it is running from 37 all the way through 91. Each one
contains part of the next element (or all) when I spread out the column
on the excel spread sheet.

 

[MaryLindholm]

Geniuses! Thank you so much Bernie! And every one else! This has
been so eye openeing and I even understand! I bow before you!!!

 

[Bernie Deitrick]

Mary,

This

Cells(Counter, 1).Value = "'" & Mid(ResultStr, 1, 2)
Cells(Counter, 2).Value = "'" & Mid(ResultStr, 3, 11)
Cells(Counter, 3).Value = "'" & Mid(ResultStr, 12, 31)
Cells(Counter, 4).Value = "'" & Mid(ResultStr, 32, 36)
Cells(Counter, 5).Value = "'" & Mid(ResultStr, 37, 51)
Cells(Counter, 6).Value = "'" & Mid(ResultStr, 52, 66)
Cells(Counter, 7).Value = "'" & Mid(ResultStr, 67, 91)

Should be this - MID takes a length, not the right-most str. position:

Cells(Counter, 1).Value = "'" & Mid(ResultStr, 1, 2)
Cells(Counter, 2).Value = "'" & Mid(ResultStr, 3, 9)
Cells(Counter, 3).Value = "'" & Mid(ResultStr, 12, 20)
Cells(Counter, 4).Value = "'" & Mid(ResultStr, 32, 10)
Cells(Counter, 5).Value = "'" & Mid(ResultStr, 37, 5)
Cells(Counter, 6).Value = "'" & Mid(ResultStr, 52, 15)
Cells(Counter, 7).Value = "'" & Mid(ResultStr, 67, 24)

HTH,
Bernie
MS Excel MVP