Parsing data from a .dat file and putting the data into Excel

[tennisfanatic]

Aloha! I have a data file (.dat) that has about 3000+ lines of data that has the format for the first few characters

Position   Field Name   Length   Description
01-14      Item #       9(14)    Item Number
15-19      Vendor       9(05)    Vender/Manufacturer ID Number
20-44      Model #      x(25)    Manufacturer Model Number
45-60      UPC          9(16)    Universal Product Code
61-62      Buy Dept     x(02)    Wholesale buying department

It actual goes to 500 characters in each lines with 70 different fields.

Can someone please help me out and start me out in how to go about parsing the data automatically and put the data in respective fields (or columns) for each item number in Excel? Or maybe the program will automatically create a file (that is delimited that can then be imported into Excel?

Mahalo! I really don't want to count each record byte by byte.

[Killer42]

Does it have to be done in VB? Perhaps you can just do a text import (with fixed-length fields) in Excel.

[tennisfanatic]

Does it have to be done in VB? Perhaps you can just do a text import (with fixed-length fields) in Excel.

Thank you for your response. I think I can do it with text import, but how do I automate it every time I get this file (it is a retail store file that has about 500+ fields and headquarters sends the file every week)? There's got to be an easier way.

Mahalo! (Thank you!)

[Killer42]

Thank you for your response. I think I can do it with text import, but how do I automate it every time I get this file (it is a retail store file that has about 500+ fields and headquarters sends the file every week)? There's got to be an easier way.

Well, I just started recording a macro then imported a text file, defining some fixed-width fields. Here is what was generated. This should give you some info to work from...

Workbooks.OpenText Filename:="C:\Sample.TXT", Origin:=xlMSDOS, StartRow _
    :=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(2, 2), Array( _
    6, 1)), TrailingMinusNumbers:=True

If you need to know more about the parameters, look them up, or try importing with different settings and see what changes in the recorded macro.

[tennisfanatic]

Well, I just started recording a macro then imported a text file, defining some fixed-width fields. Here is what was generated. This should give you some info to work from...

Workbooks.OpenText Filename:="C:\Sample.TXT", Origin:=xlMSDOS, StartRow _
    :=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(2, 2), Array( _
    6, 1)), TrailingMinusNumbers:=True

If you need to know more about the parameters, look them up, or try importing with different settings and see what changes in the recorded macro.

Mahalo!
I think you and I were on the same page! I tried importing it with the macro recording on and after several times I think I have it all finished the parsing correctly! Thank you sooo much for steering me in the right direction!!!

[Killer42]

Mahalo!
I think you and I were on the same page! I tried importing it with the macro recording on and after several times I think I have it all finished the parsing correctly! Thank you sooo much for steering me in the right direction!!!

Glad to help! :)