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

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.

CodePudding user response：

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"
  }
}