Search Text file and export to spreadsheet

[DCSage]

I have a rather funky text file, in which most of the information runs in columns and one portion runs in a row:

  Unit address / Type / Name  |   |  Authorization Tier Mask
------------------------------ -------- 

000-03136-65330-122     DC2    Cat epoch # 045, ID 0606 00800000000000000000000000000000000000000000000 
( 511- 256)
COLUMBUS                       Blackouts enabled        
                               Reg 002, Post      43212 
                               Ratings region 000       
                               Time zone -300, DST      
                               Unit Group 0             
                               Virtual Network 0        
                               Multiport Upd disabled   

          MSO NAME: Oxon Hill 
          UNIT ADDRESS: 03136-65330
          HEADEND PHONE NUMBER: 999-999-9999
          CITY: COLUMBUS
          STATE: VA
          SERVICE: XYZ EAST

I need to search the text file for all MSO Names, Unit Address, Phone, Etc, as well as the number that comes before [DC2] and then place all results in a spreadsheet.

Is the best way to accomplish this through regular expressions? I searched the web, but can't find what i need. Can someone provide suggestions or sites?

Thank you.

 

[jmeckley]

if all you need are MSO names, then this will work. it's not the efficient, but it gets the job done

IEnumerable<string> ParseMsoNamesFrom(string path)
{
   const string mso = "MSO NAME:";
   foreach(var line in File.ReadLines(path))
   {
      var trimmed = line.Trim();
      if(trimmed.StartsWith(mso) == false) continue;

      yield return line.Replace(mso, "");
   }
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[DCSage]

Thank you jmeckly for your reply.

i actually need the MSO Name, the address, phone, city, state, service and the account number before dc2 (that's why I said the spreadhseet is funky).

As I am getting more of these requests from my users with botched data, I need to know the best tactics for solving.

I am looking into the example IEnumerable, but can I also use this object to not only collect the data, but to export it (from a loop) to a spreadsheet?

Thanks in advance.

 

[jmeckley]

i actually need the MSO Name, the address, phone, city, state, service and the account number before dc2

in that case, read a block of rows at one time (15?) then then parse the values from that block.

public IEnumerable<User> ParseUsersFrom(string path)
{
   return ParseBlocks(path).Select(ParseUser);
}

IEnumerable<string[]> ParseBlocks(string path)
{
   const int COMPLETE_BLOCK = 15;
   var withoutHeader = File.ReadLines(path).Skip(3);
   var block = List<string>();
   foreach(var line in withoutHeader)
   {
      block.Add(line);
      if(block.Count != COMPLETE_BLOCK) continue;

      yield return block.ToArray();
      block.Clear();
   }
}

User ParseUser(string[] block)
{
   //for each property prase
   return new User {
               Name = parse name from block. line 10,
               Address = //parse name from block. line 11
               ...
           };
}

you can then get a list of users like this

var source = new Source();
var users = source.ParseUsersFrom(path);

var destination = new Destination();
destination.Save(users);

as for saving the data. lets focus on getting the data out. once it's out of the file (and into an object) you can do whatever you want with it.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[DCSage]

the public IEnumerable<User> is being derived from the using system.collections.generic namespace right? when i enter public IEnumerable<...User is not an option. I am placing everything in a class file. do i need another type of namespace?

 

[jmeckley]

User is a class you design.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[DCSage]

Thank You!!