How do I make a file that is delimited by pipes readable by a human?

by on July 6, 2011

Q: I have this file that my supervisor gave me : sample.txt that contains :
1|BC Hydro|BC|90006390|Minimum 1 Maximum 11|All numeric|Following business day|Utilities|2007-04-04 15:35:00|Y|BC Hydro|Y||1 2|TELUS Communications|National|90031111|Minimum 10 Maximum 10|All Numeric. Must start with 0,1,2,3,4,5,6 or 8. Mod 10(2,1,2,1). To calculate the check digit, add “315” to the registered account number and apply MOD 10 formula. Test accounts: 1250762858, 2286470104, 2323188313|Following business day|Telecommunications|2006-05-24 11:14:00|Y||Y||2 4|Rogers Cablesystems|ON|90007326|12 digits|All Numeric. Must start with 220, 221, 222, 230, 231, 232, 233, 234, 239, 240, 260 or 265.|Following business day|Cable|2006-04-12 08:53:00|Y|Rogers Cablesystems|Y||3

3 lines only if u put it in a txt file.

I need to sort it somehow to let other people understand it. how would i do that ?
i think i need to delete that delimiter | and make tike columns so that we can read the txt clearly.
I noticed that the | in each line is 13.
Any help ?


7 Responses to “How do I make a file that is delimited by pipes readable by a human?”
  1.  

    The easiest thing to do is to open it in Excel or some other spreadsheet program like Open Office. So open whatever you want to use then do a File -> Open and select the file.

    Depending on what software you are using it should ask if the fields are delimited or fixed width and the answer here is fixed width. Then the next step should ask you what it is delimited with and in this case it is a pipe so select that or selected other and type in a pipe (it usually defaults to tab). Then from there it might ask you misc stuff like date and you can probably ignore that.

    Once you are done you should have your file should be opened in excel and you can just do a FIle -> Save As… to change it to an spreadsheet file and you can do anything you want with it from there. If you don’t do a Save As… your file will be saved as a text file that is pipe delimited and anyone you send it to will have the same problem you had so it is an important step.

     
  2.  

    Thx Matt , I did not mention I need this in Powershell.
    EB thx for the help , reallly appreciated , this is my second week doing PS and i love. I learned alot form the previous script. but I now know what I do rerallt need.

    I will give u an example.
    let’s say i have this line in my file , say line 347. They ask me to change the Flollowing business day To previous busnies day.
    i dont wanna go to the file , search and change , I want powershell PS to parse data on a final result.

    1|BC Hydro|BC|90006390|Minimum 1 Maximum 11|All numeric|Following business day|Utilities|2007-04-04 15:35:00|Y|BC Hydro|Y||1 2|TELUS

    So everytime I receive a file (NO Header for this files available) and they ask to make a changes , I must be able to parse the $finalresult wit 1-2 ps code and be able to do it.

    My approchah for this is to divide my file into sections (does not have to be FIX , lines could be different lengh of delimter) then USE
    $obj= New-Object System.Object
    $obj | Add-Member I guess to my sections somwhow using foreach () ……we want to give it a header so we can parse the result on that Obg | add-member

    got me ?

     
    •  

      As a software engineer my first question is

      How would you know you need to modify line 347? Would they just tell you that is the line you need to modify or would they give you an account number that you need to look for and it would be your job, using a script, to find that line and do the edit?

       
  3.  

    and what ‘s ur paypall ?

     
  4.  

    They just call or send email saying , we need to change the third column in line 300 or 230 or 2 …..anything ….I want be able to chnage it from powershell script. of course I still need to write 2-3 words of coding but want be able to do that easyl.

    example of my use :

    setcontent $result.HeaderLable2 from hello world to Hi world

     
  5.  

    PS: all the files have fixed delimited but the number of delimited might vary from file to another

     
  6.  

    I have never used PowerShell before but it seems like you can put the file into a variable then loop though until you get to the correct line and do the same for the column, change it, and then you are done.

    I cannot be of any help with the actual syntax though since I have never worked with PowerShell.