phpsqlexportexport-to-csvexport-to-text

How to export a .out file into a .txt file or a sql file?


How can I convert a .out file into a .txt file, CSV, or SQL file? The records present in the .out file do not have delimiters instead each segment in the record has a fixed start and endpoint. Is there an online tool or a simple PHP script in which I can easily define the start and endpoint of each segment? Then, finally, export it with delimiter | between each segment of the record. Not necessarily, if I can directly export this .out file to a sql file.

Records Example:

I299207075410 07  OCCLUSAL-HP                        LIQ17%                          LMedicis              B000001000000000001EA 8428010080529100   1072363   20030101000000016750000000016750000000000167500200101010000000000000000000000000000000001218000000000000000000000000000000000000000000000000020021231262436018510(W/BRUSH APPLICATOR)     TPLIQ     
 299207085060R01  LUZU                               CRE1%                           SBausch               C000006000000000001EA 8404080054930829 1 1309011   20180105000000590530000000098421700000000902967000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000                                     TPCRE     

P.S. Even blank spaces are part of different segments. Some segments have a length of 1 char; in those blank or space means NULL value.

For example, the first segment has a length of 1 char. As, you can see the 2nd record does not have a value instead a blank space to represent a NULL value.

Please suggest, thank you.


Solution

  • Built on the answer provided here: https://stackoverflow.com/a/62157247/1427345

    I developed this bit of code, you will need to modify the $fields variable with the correct name and length of each column. I just made guesses.

    <?php
    
    $raw = file('data.out');
    
    $fields = [
        'id' => 1,
        'id2' => 12,
        'code' => 5,
        'category' => 35,
        'code2' => 32,
        'category2' => 22,
        'code3' => 22,
        'code5' => 17,
        'code6' => 2,
        'code7' => 10,
        'code8' => 186,
        'code9' => 10
    ];
    
    $unpack = [];
    foreach ($fields as $name => $length) {
        $unpack[] = 'A'.$length.$name;
    }
    $unpack_string = implode('/', $unpack);
    
    $data = [];
    foreach ($raw as $line) {
        $data[] = unpack($unpack_string, $line);
    }
    
    var_dump($data);
    
    $export = fopen("data.csv", "w");
    foreach ($data as $row) {
        fputcsv($export, $row, "|");
    }
    fclose($export);
    

    Results in:

    array(2) {
      [0]=>
      array(12) {
        ["id"]=>
        string(1) "I"
        ["id2"]=>
        string(12) "299207075410"
        ["code"]=>
        string(3) " 07"
        ["category"]=>
        string(11) "OCCLUSAL-HP"
        ["code2"]=>
        string(6) "LIQ17%"
        ["category2"]=>
        string(8) "LMedicis"
        ["code3"]=>
        string(21) "B000001000000000001EA"
        ["code5"]=>
        string(16) "8428010080529100"
        ["code6"]=>
        string(0) ""
        ["code7"]=>
        string(7) "1072363"
        ["code8"]=>
        string(181) "20030101000000016750000000016750000000000167500200101010000000000000000000000000000000001218000000000000000000000000000000000000000000000000020021231262436018510(W/BRUSH APPLICATOR)"
        ["code9"]=>
        string(5) "TPLIQ"
      }
      [1]=>
      array(12) {
        ["id"]=>
        string(0) ""
        ["id2"]=>
        string(12) "299207085060"
        ["code"]=>
        string(3) "R01"
        ["category"]=>
        string(4) "LUZU"
        ["code2"]=>
        string(5) "CRE1%"
        ["category2"]=>
        string(7) "SBausch"
        ["code3"]=>
        string(21) "C000006000000000001EA"
        ["code5"]=>
        string(16) "8404080054930829"
        ["code6"]=>
        string(1) "1"
        ["code7"]=>
        string(7) "1309011"
        ["code8"]=>
        string(149) "20180105000000590530000000098421700000000902967000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"
        ["code9"]=>
        string(5) "TPCRE"
      }
    }