Import ASCII files into database

By: Sujendra      Posted On: 18 Dec, 2017      Category: PHP

Import ASCII files into database

Most of time we got business information in CSV or Spreadsheet format and we easily import any CSV and Excel into database tables.

Today we are going to learn import ASCHI files into database tables. Below is example of ASCHI file.

 

FIELD1 FIELD2 FIELD3  FIELD4 FIELD5

1      7      14      21     29          

---    ---   ------ -------- -------------

aaa    aaa   AACC    0       1600

bbb    bbb   AACC    1       1400

ccc    ccc   AACC    2       1400

ddd    ddd   AACC    3       1200

eee    eee   AACC    4       1400

fff    fff   AACC    5       900

ggg    ggg   AACC    6       700

hhh    hhh   AACC    7       600

iii    iii   AACC    8       400

jjj    jjj   AACC    9       200

kkk    kkk   AACC    10      0

 

When we import CSV or Excel, we have comma or space delimiter to separate records . CSV or EXCEL import script is easily available online.  In CSV , each row have fixed delimiter(comma), but in ASCHI file, delimiter is white space, but it's not fixed for each row.

 

Understanding of ASCHI file format

In this file, 1st row show column name, 2nd row show position of data with respective column. 3Rd row is separator between header information (Column and position) and data information, and from 4th row actual data. In above example file we can easily understand

 

FIELD1 value starts from 1st character

FIELD2 value starts at 7th character

FIELD3 value starts at 14th character

FIELD4 value start at 21th character

FIELD5 value starts at 29th character

 

How to import ASCHI files information into database?

We need to follow below steps to import ACHI files information into database.

Step 1: Ready ASCHI file

First, we need to reach file and store each line of row in a array. Below script read file and store each line in a array

 

$filename = "demo.file";

$file = fopen($filename, "r");

$file_row = "";

while (!feof($file)) {

    $file_row[] = fgets($file);

}

fclose($file);

//echo '<pre>'; print_r($file_row);die;

Output :

Array

(

    [0] => FIELD1 FIELD2 FIELD3  FIELD4 FIELD5

    [1] => 1      7      14      21     29       

    [2] => ---    ---   ------ -------- -------------

    [3] => aaa    aaa   AACC    0       1600

    [4] => bbb    bbb   AACC    1       1400

    [5] => ccc    ccc   AACC    2       1400

    [6] => ddd    ddd   AACC    3       1200

    [7] => eee    eee   AACC    4       1400

    [8] => fff    fff   AACC    5       900

    [9] => ggg    ggg   AACC    6       700

    [10] => hhh    hhh   AACC    7       600

    [11] => iii    iii   AACC    8       400

    [12] => jjj    jjj   AACC    9       200

    [13] => kkk    kkk   AACC    10      0

)

 

Step2: Read each line of text and separate them by position.

Below script will do same and return an array with each column data. As we know

$file_data = array();

$intervals = preg_split('/[\s]{1}/', $file_row[1], -1, PREG_SPLIT_NO_EMPTY);

foreach ($file_row as $key => $value) {   

    if (!empty($value)) {       

        $file_data[$key] = getSplittedByPositions($value, $intervals);

    }

}



//echo '<pre>'; print_r($file_data);die;

Output:

Array
(
    [0] => Array
        (
            [0] => FIELD1
            [1] => FIELD2
            [2] => FIELD3
            [3] => FIELD4
            [4] => FIELD5
 
        )
 
    [1] => Array
        (
            [0] => 1
            [1] => 7
            [2] => 14
            [3] => 21
            [4] => 29           
 
        )
 
    [2] => Array
        (
            [0] => ---
            [1] => ---
            [2] => ------
            [3] => --------
            [4] => -------------
 
        )
 
    [3] => Array
        (
            [0] => aaa
            [1] => aaa
            [2] => AACC
            [3] => 0
            [4] => 1600
 
        )
 
    [4] => Array
        (
            [0] => bbb
            [1] => bbb
            [2] => AACC
            [3] => 1
            [4] => 1400
 
        )
 
    [5] => Array
        (
            [0] => ccc
            [1] => ccc
            [2] => AACC
            [3] => 2
            [4] => 1400
 
        )
 
    [6] => Array
        (
            [0] => ddd
            [1] => ddd
            [2] => AACC
            [3] => 3
            [4] => 1200
 
        )
 
    [7] => Array
        (
            [0] => eee
            [1] => eee
            [2] => AACC
            [3] => 4
            [4] => 1400
 
        )
 
    [8] => Array
        (
            [0] => fff
            [1] => fff
            [2] => AACC
            [3] => 5
            [4] => 900
 
        )
 
    [9] => Array
        (
            [0] => ggg
            [1] => ggg
            [2] => AACC
            [3] => 6
            [4] => 700
 
        )
 
    [10] => Array
        (
            [0] => hhh
            [1] => hhh
            [2] => AACC
            [3] => 7
            [4] => 600
 
        )
 
    [11] => Array
        (
            [0] => iii
            [1] => iii
            [2] => AACC
            [3] => 8
            [4] => 400
 
        )
 
    [12] => Array
        (
            [0] => jjj
            [1] => jjj
            [2] => AACC
            [3] => 9
            [4] => 200
 
        )
 
    [13] => Array
        (
            [0] => kkk
            [1] => kkk
            [2] => AACC
            [3] => 10
            [4] => 0
        )
 
)

Stpe3 : Build dynamic insert sql

Below script will generate dynamic insert sql. I have written as function.

function build_sql($table , $data)

{

   global $mysqli;



    $sql = "SHOW COLUMNS FROM $table";

    $result = $mysqli->query($sql);   

    $fields = array();

    while($row = mysqli_fetch_array($result)){

        $fields[] = $row['Field'];       

    }   

   

    $field_sql = "";

    foreach($fields as $key=>$value){

        $field_sql .= '`'.$value.'`,';

    }

    $field_sql = '('.substr($field_sql, 0, -1).')';

   

    $value_sql = "";

    foreach($data as $key=>$value){       

        if($key < 3){ continue;}       

       

        $row_sql = "NULL,"; //First column should be primary key and auto incremented

        foreach($value as $k=>$v){

            $row_sql .= '"'.clean($v).'",';   

        }       

        $row_sql = substr($row_sql, 0, -1);

        $value_sql .= '('.$row_sql.'),';

    }

   

    $value_sql = substr($value_sql, 0, -1);   

    /*

     * INSERT INTO `CVG01035_N11` (`id`, `FIELD1`, `FIELD2`, `FIELD3`, `FIELD4`, `FIELD5`) VALUES

        (1, NULL, NULL, NULL, NULL, NULL),

        (2, NULL, NULL, NULL, NULL, NULL);

     */

    $sql = 'INSERT INTO `'.$table.'` '.$field_sql.' VALUES '.$value_sql.'';   

    //echo $sql; die;

    return $sql;   

}



$sql = build_sql($table, $file_data);

echo $sql;die;

 

Output:

INSERT INTO `demo` (`id`,`first_name`,`last_name`,`email`,`age`,`contact`) VALUES (NULL,"aaa","aaa","AACC","0","1600"),(NULL,"bbb","bbb","AACC","1","1400"),(NULL,"ccc","ccc","AACC","2","1400"),(NULL,"ddd","ddd","AACC","3","1200"),(NULL,"eee","eee","AACC","4","1400"),(NULL,"fff","fff","AACC","5","900"),(NULL,"ggg","ggg","AACC","6","700"),(NULL,"hhh","hhh","AACC","7","600"),(NULL,"iii","iii","AACC","8","400"),(NULL,"jjj","jjj","AACC","9","200"),(NULL,"kkk","kkk","AACC","10","0");

Execute above sql in mysql console or save sql as file and import it in database.

Download Sample Code

 

You may like: