Inserting data into MYSQL from a TEXT FILE (not in CSV format)

Discussion in 'Programming/Scripts' started by reduday, Apr 1, 2008.

  1. reduday

    reduday New Member

    I am a novice to PHP and MYSQL, I have been asked to create a new database on MYSQL, I have problem with the data, right now I am in the process of understanding the data, but the data is in text format and I guess separated by spaces, it is not comma delimited and each record is divided into 4 lines. So how should my approach be towards creating the database and the reading the data?

    And do I have to write any script to read the data line by line and extract what I want and if so could any experts help me out with this.

    010281C 64870002722101 BAY COMMUNITY BANK RM BAY NN0215 0078 2001022615:08
    020126C 64870002722101 341741 757 5990895 PV
    030367C 64870002722101 ATTN; MARKETING DEPARTMENT PO BOX 940 RUTHER GLEN VA22546 P
    040109C 64870002722101 507082BAY COMMUNITY BANK
    990237 I 6487001 1 0.0002 1 0.0003 1 0.0004 1 0.00

    I have even added an example attachment on how it looks in a text file.

    Thank you all.
     

    Attached Files:

  2. falko

    falko Super Moderator ISPConfig Developer

    I think (or hope...) the fields are separated by tabs, not spaces, because otherwise a field like "BAY COMMUNITY BANK" would be split into three fields...

    I think you should write a script (e.g. in PHP) to read the file and split it up into lines, and then try to split the lines into fields (try \t as the field delimiter).
     
  3. reduday

    reduday New Member

    Thanks a lot Falko,

    I have never written a script on PHP, could you please give me an example or tell me how to write the script.

    Thanks again
     
  4. falko

    falko Super Moderator ISPConfig Developer

    Try something like this:
    PHP:
    <?php
    $link 
    mysql_connect('localhost''mysql_user''mysql_password');
    mysql_select_db('foo'$link);

    $contents readfile('/path/to/file');
    $lines explode("\n"$contents);
    foreach(
    $lines as $line){
      
    $line trim($line);
      list(
    $field1$field2$field3, ...) = explode("\t"$line);
      
    mysql_query("INSERT INTO mytable (field1, field2, field3) VALUES ('".$field1."', '".$field2."', '".$field3."')"$link);
    }

    mysql_close($link);
    ?>
     
  5. reduday

    reduday New Member

    Thank you

    Thank you,

    And sorry I was not able to reply very soon, was on vacation, but thanks again, I am gonna try using the code.
     

Share This Page