Question Reading column align text file

[lkrterp]

Hi All!

I have a question concerning how to read a text file that is column aligned. I understand how to read a delimited file (i.e.: comma, pipe, space, etc.) but the file I need to read is column aligned.

18830051 Exxon Branded                                      1567|8245 Marine Ave C 3033 Conv 87 Unleaded  11/06/09 19:00 2.0440 -0.0685   0.0000
18830051 Exxon Branded                                      1567|8245 Marine Ave C 3044 Conv 89 Midgrade  11/06/09 19:00 2.1340 -0.0685   0.0000
18830051 Exxon Branded                                      1567|8245 Marine Ave C 3022 Conv 93 Premium w 11/06/09 19:00 2.2840 -0.0685   0.0000
18830051 Exxon Branded                                      1660 Trans Frfx Picket 05 Ultra Low Sulfur #2 11/06/09 19:00 2.0397 -0.0506   0.0000
18830051 Exxon Branded                                      1660 Trans Frfx Picket 03 RFG 87 Unld with 10 11/06/09 19:00 2.0373 -0.0545   0.0000
18830051 Exxon Branded                                      1660 Trans Frfx Picket 04 RFG 89 Midgrade wit 11/06/09 19:00 2.1273 -0.0545   0.0000
18830051 Exxon Branded                                      1660 Trans Frfx Picket 02 RFG 93 Premium with 11/06/09 19:00 2.3123 -0.0545   0.0000
18830051 Exxon Branded                                      1671 KM New Terminal R 05 Ultra Low Sulfur #2 11/06/09 19:00 2.0397 -0.0506   0.0000
18830051 Exxon Branded                                      1671 KM New Terminal R 205 Low Sulfur #2 Dies 11/06/09 19:00 2.0457 -0.0488   0.0000
18830051 Exxon Branded                                      1671 KM New Terminal R 03 RFG 87 Unld with 10 11/06/09 19:00 2.0373 -0.0545   0.0000
18830051 Exxon Branded                                      1671 KM New Terminal R 04 RFG 89 Midgrade wit 11/06/09 19:00 2.1273 -0.0545   0.0000
18830051 Exxon Branded                                      1671 KM New Terminal R 02 RFG 93 Premium with 11/06/09 19:00 2.3123 -0.0545   0.0000
12320227 Citgo Unbranded                                    1562 Southport Ave Cit 04 RFG 89 Midgrade wit 11/06/09 18:00 2.0910 -0.0660   0.0000
12320227 Citgo Unbranded                                    1562 Southport Ave Cit 02 RFG 93 Premium with 11/06/09 18:00 2.2370 -0.0660   0.0000
12320095 Citgo Branded                                      1562 Southport Ave Cit Ultra Low Sulfur #2 Pr 11/06/09 18:00 2.0980 -0.0520   0.0000
12320095 Citgo Branded                                      1562 Southport Ave Cit 05 Ultra Low Sulfur #2 11/06/09 18:00 2.0930 -0.0520   0.0000
12320095 Citgo Branded                                      1562 Southport Ave Cit 21|11 Ultra Low Sulfur 11/06/09 18:00 2.0970 -0.0520   0.0000
12320095 Citgo Branded                                      1562 Southport Ave Cit 21 Ultra Low Sulfur #2 11/06/09 18:00 2.0980 -0.0520   0.0000
46860117 Mystik Brand Contract                              1562 Southport Ave Cit Ultra Low Sulfur #2 Pr 11/06/09 18:00 2.0980 -0.0520   0.0000
46860117 Mystik Brand Contract                              1562 Southport Ave Cit 05 Ultra Low Sulfur #2 11/06/09 18:00 2.0930 -0.0520   0.0000

Some columns contain spaces, so I can not use a space and the third column may contain two numbers separated by a pipe causing the forth column to be shortened.

When reading a text file, I am use to using StreamReader as below in a comma delimited example.

Dim fIn As StreamReader = File.OpenText("file location and name")
Dim sline as string
Dim aLine() as string

Do Until fIn.Peek() = -1
sline = fIn.Readline
aLine = sline.Split(",")

etc.......
Loop


Can anyone help me with the above text file?

 

[JohnH]

Insert the snippet 'Read a delimited text file" (Fundamentals, Filesystem) with context menu or menu Edit>Intellisense. Remove the SetDelimiters call and instead set TextFieldType to FixedWidth and SetFieldWidths, (8, 52, 45, 15, 7, 8, 9) in this case. The third column field you have to parse manually, get the index of the first space and split there.

 

[lkrterp]

Thank for you response John. I understand that I will have to parse the third column. I am going to have to do the same thing on the 4th column for the product code. I am confused on what you are saying in your first and second sentence. Can you show me an example of the actual code?

 

[JohnH]

I see now you're using VB 2003, so basically you're deemed to parse out the fields yourself, the TextFieldParser was new with .Net 2. It's not difficult, only more work. Read each line and use Substring method to get the fixed part of the line. String.Substring Method (Int32, Int32) (System)

 

[lkrterp]

My mistake when I posted. I am using vb 2008. Just the last time I read a text file I was using 2003. Can you please demonstrate your first response in code. I am confused on what you are saying in sentence form. I can tell by the SetFieldWidths that you are counting the number of characters/spaces in the columns.

 

[JohnH]

set TextFieldType to FixedWidth and SetFieldWidths, (8, 52, 45, 15, 7, 8, 9) in this case.

parser.TextFieldType = FieldType.FixedWidth
parser.SetFieldWidths(8, 52, 45, 15, 7, 8, 9)

Simple as that

 

[lkrterp]

OK
I understand now. Thanks bunches.

Oh and I changed my About me to reflect that I am now using .Net 3.5. Thanks for that catch.