Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Coder
    Join Date
    Aug 2012
    Posts
    17
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Question How to: filter csv, show results (??)

    Hello everyone,

    after days of unsuccessful attempts I unfortunately did not find a solution for my problem so I am trying to find help here...

    I have this kind of .csv file:
    Code:
    1000,date,description1,200,300,400,code1,code2,500,DIA
    1001,date,description2,201,301,410,code3,code4,510,NOA
    1000,date,description3,202,302,420,code5,code6,520,DIA
    1000,date,description4,203,303,430,code6,code8,530,DIA
    1002,date,description5,204,304,440,code8,code9,540,EEF
    etc etc
    My idea was to create a php script to filter only those lines(rows) which contains in first column value "1000" and in last column value "DIA". I also wanted to save all results between those values (date,description, (...),codeX,5X0) to a .csv file named 1000.csv.

    Note: above mentioned .csv file has 3500+ lines, numbers are different.

    Hope that someone would help me... I used a lot of examples like this one but nothing work....

    Because I am very confused by this issue, so any help will be kindly appreciated! Thank you...
    Kissing Jane.

  • #2
    Senior Coder
    Join Date
    Sep 2010
    Posts
    1,903
    Thanks
    15
    Thanked 226 Times in 226 Posts
    I can't tell you how to do it with php but I sure the heck can write a shell script that you can call with php, or just use directly with the command line, that would do that little job in a few seconds.You need to have a 'nix machine that has a bash shell, which includes the sed editor.

    PS: Here it is in all it's SEDy glory, the first line prints only lines that start with 1000, the second only prints lines that end with DIA, so between them you get what you wanted.
    Code:
    #!/bin/sh
    IFS=$'\t\n'
    
     sed -n '/^1000/p'   < input.csv > temp.txt;
     sed -n '/DIA$/p'    < temp.txt  > output.csv;
     rm -f temp.txt;
    
    exit 0
    You can give whatever names you want to the files. It's possible to refine it so that you could enter the filenames and the starting and ending text. Just name the file csv.sh and call it with 'bash csv.sh' and it will make the output csv.
    Last edited by DrDOS; 06-24-2013 at 05:21 AM.
    Welcome to http://www.myphotowizard.net

    where you can edit images, make a photo calendar, add text to images, and do much more.


    When you know what you're doing it's called Engineering, when you don't know, it's called Research and Development. And you can always charge more for Research and Development.

  • #3
    Regular Coder Arcticwarrio's Avatar
    Join Date
    May 2012
    Location
    UK
    Posts
    721
    Thanks
    20
    Thanked 84 Times in 84 Posts
    this should work for you.

    it doesn't output anything on the screen but the files will be created.

    PHP Code:

    <?php


    function csv_to_array($filename=''$delimiter=',')
    {
        if(!
    file_exists($filename) || !is_readable($filename))
            return 
    FALSE;

        
    $header NULL;
        
    $data = array();
        if ((
    $handle fopen($filename'r')) !== FALSE)
        {
            while ((
    $row fgetcsv($handle1000$delimiter)) !== FALSE)
            {
                if(!
    $header)
                    
    $header $row;
                else
                    
    $data[] = array_combine($header$row);
            }
            
    fclose($handle);
        }
        return 
    $data;
    }

    $mydata csv_to_array('input.csv'); //set filename here

    foreach ($mydata as $id => $row){
        
    $line '';
        foreach (
    $row as $field => $value){
            
    $line .= $value.',';
        }
        
    $line substr($line0, -1);
        
    $line $line .PHP_EOL;
        
    file_put_contents($mydata[$id][0].'.csv'$lineFILE_APPEND LOCK_EX);
    }

    ?>
    There are 10 types of people on CodingForums,
    Those who understand Binary and those who dont.
    Get Cloud Hosting now from only£59 / month

  • #4
    New Coder
    Join Date
    Aug 2012
    Posts
    17
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Arcticwarrio View Post
    this should work for you.

    it doesn't output anything on the screen but the files will be created.
    Hello ArcticWarrio,
    thank you for a script, but it does not work for me... I filled it with path to file, but nothing happened... What I am doing wrong? It is needed to fill to code something more?

    Jane

  • #5
    Regular Coder Arcticwarrio's Avatar
    Join Date
    May 2012
    Location
    UK
    Posts
    721
    Thanks
    20
    Thanked 84 Times in 84 Posts
    try saving the page with this script in the same folder as the csv file
    There are 10 types of people on CodingForums,
    Those who understand Binary and those who dont.
    Get Cloud Hosting now from only£59 / month

  • #6
    New Coder
    Join Date
    Aug 2012
    Posts
    17
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Arcticwarrio View Post
    try saving the page with this script in the same folder as the csv file
    That is also unsuccessful... But I will continue trying to fix is somehow...

    Jane

  • #7
    Regular Coder Arcticwarrio's Avatar
    Join Date
    May 2012
    Location
    UK
    Posts
    721
    Thanks
    20
    Thanked 84 Times in 84 Posts
    with that script as it it and both files in the same folder i used this saved as input.csv

    oh sorry just realised something lol

    add this to the first line of your input csv file:
    0,1,2,3,4,5,6,7,8,9

    ie:
    Code:
    0,1,2,3,4,5,6,7,8,9
    1000,date,description1,200,300,400,code1,code2,500,DIA
    1001,date,description2,201,301,410,code3,code4,510,NOA
    1000,date,description3,202,302,420,code5,code6,520,DIA
    1000,date,description4,203,303,430,code6,code8,530,DIA
    1002,date,description5,204,304,440,code8,code9,540,EEF
    There are 10 types of people on CodingForums,
    Those who understand Binary and those who dont.
    Get Cloud Hosting now from only£59 / month

  • Users who have thanked Arcticwarrio for this post:

    Jana.Werid (06-26-2013)

  • #8
    Senior Coder
    Join Date
    Sep 2010
    Posts
    1,903
    Thanks
    15
    Thanked 226 Times in 226 Posts
    Are you not able to run my code? It works perfectly and does exactly what you want.
    Welcome to http://www.myphotowizard.net

    where you can edit images, make a photo calendar, add text to images, and do much more.


    When you know what you're doing it's called Engineering, when you don't know, it's called Research and Development. And you can always charge more for Research and Development.

  • #9
    New Coder
    Join Date
    Aug 2012
    Posts
    17
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by DrDOS View Post
    Are you not able to run my code? It works perfectly and does exactly what you want.
    Well I am not familiar with so special kind of coding - I am sorry, but I do not know what to do with your code

    J.

  • #10
    Senior Coder
    Join Date
    Sep 2010
    Posts
    1,903
    Thanks
    15
    Thanked 226 Times in 226 Posts
    Quote Originally Posted by Jana.Werid View Post
    Well I am not familiar with so special kind of coding - I am sorry, but I do not know what to do with your code

    J.
    It's not so special coding. It's just a bash shell script, which can be called by php, if you have shell access. That is, Safe Mode needs to be turned off, and you need to have the files on a 'nix (Unix, Linux, Mac, BSD) machine. Put this on a single php page and upload it to the server in question:
    PHP Code:
    <?php phpinfo(); ?>
    Just that one line on the page. Then access the page by the web, just as you would any other page. It will tell you the configuration of php, including whether Safe Mode is on or off. If it's off, I will write the code for using the script, if it's on we can arrange to send me the file, I will run the script and send you the results. Or you may be on a Mac or Linus, in which case you can run the script and get the results.
    Welcome to http://www.myphotowizard.net

    where you can edit images, make a photo calendar, add text to images, and do much more.


    When you know what you're doing it's called Engineering, when you don't know, it's called Research and Development. And you can always charge more for Research and Development.

  • #11
    New Coder
    Join Date
    Aug 2012
    Posts
    17
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by DrDOS View Post
    (...)
    Just that one line on the page. Then access the page by the web, just as you would any other page. It will tell you the configuration of php, including whether Safe Mode is on or off. If it's off, I will write the code for using the script, if it's on we can arrange to send me the file, I will run the script and send you the results. Or you may be on a Mac or Linus, in which case you can run the script and get the results.
    I done it - PHP Version 5.4.11, sql.safe_mode is Off.

  • #12
    Senior Coder
    Join Date
    Sep 2010
    Posts
    1,903
    Thanks
    15
    Thanked 226 Times in 226 Posts
    Alas, that's just the SQ Lite safe mode, I'm looking for the PHP safe mode, it will be under the Core listing, and it will just be named safe_mode. If it say Off I will write you a script, not a big deal.
    Welcome to http://www.myphotowizard.net

    where you can edit images, make a photo calendar, add text to images, and do much more.


    When you know what you're doing it's called Engineering, when you don't know, it's called Research and Development. And you can always charge more for Research and Development.

  • #13
    New Coder
    Join Date
    Aug 2012
    Posts
    17
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Well I did not find it so I asked support and they told me that "Safe mode" feature has been removed as of PHP version 5.4.0.
    I have 5.4.11

  • #14
    Senior Coder
    Join Date
    Sep 2010
    Posts
    1,903
    Thanks
    15
    Thanked 226 Times in 226 Posts
    We've got it. Two files, name the HTML csv.php and the shell script csv.sh, give them 0777 permissions. Lets you preview the results, will give errors for wrong entries, but no biggie, it works and is tested. Put them in the same dir as the input file, or give the full paths. Good luck.
    PHP Code:
    <!DOCTYPE html>
    <head>
    <title>CSV List extracter</title>
    <meta content="text/html; charset=utf-8" http-equiv="content-type">
    <style type="text/css">
    body 
    {
    background:midnightblue;width:960px;margin: 0 auto;
    font-size:24px;color:white;font-family:Verdana;
    }
    #container {margin-top: 200px;}
    </style>
    <body >
    <div id="container">
    <form action="./csv.php" method="post">
    <input name="csvdata" type="text" size=40 ><br />
    Separate fields by upright stroke, e.g 1000|DIA|input.csv|output.csv<br />
    Enter data in this order: <br />
    Start of line. e.g. 1000<br />
    End of line, e.g. DIA<br />
    Name of input file, e.g. customer.csv<br />
    Name of output file, e.g. output.csv<br />
    <input  type="submit" value="Submit">
    </form>
    <pre>
    <?php
    if(isset($_POST["csvdata"]))
        {
    $data $_POST["csvdata"];
    $datum explode("|",$data);
    `
    bash csv.sh $datum[0] $datum[1] $datum[2] $datum[3]`;
    echo 
    file_get_contents($datum[3]);
        }
    ?>
    </pre>
    </div>
    <script>

    </script>
    </body>
    </html>

    #!/bin/sh
    IFS=$'\t\n'
     sed -n '/^'$1'/p'   < $3 > temp.txt;
     sed -n '/'$2'$/p'   < temp.txt  > $4;
     rm -f temp.txt;
    exit 0
    Welcome to http://www.myphotowizard.net

    where you can edit images, make a photo calendar, add text to images, and do much more.


    When you know what you're doing it's called Engineering, when you don't know, it's called Research and Development. And you can always charge more for Research and Development.

  • Users who have thanked DrDOS for this post:

    Jana.Werid (06-26-2013)

  • #15
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    Wow over complicate things much

    PHP Code:
    $handle fopen("file.csv""r");  //open the file file.csv located in the same folder as this script
    while (($data fgetcsv($handle100000",")) !== FALSE) {  // loop through each line of the file opened above
        
    if ($data[0]==1000 && $data[9]=="DIA") {
            
    //only process rows where first column = 1000 and last column = DIA
           
    print_r($data);  // output to screen
           // you can also take this and put it into the data the variables are stored in $data and can be referenced with array locations [0] through to [9]
        
    }

    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • Users who have thanked hinch for this post:

    Jana.Werid (06-26-2013)


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •