add data from txt file to mysql column by column (php- PDO)

Question

I have a TXT file with no punctuation between them. I would like to shred this file by the table column widths in the database and save it.

Let me tell you this step by step…

1. I’m creating a table in the database with my tabloolustur.php page. The column numbers and column widths of the tables I create will not be the same.

2. There are no punctuation marks between the TXT file data. First, I want to split the TXT file rows by column width.

   $result = $baglanti->prepare("SHOW COLUMNS FROM customers where Field NOT IN('id')");
   
   $result->execute();
   $colcount = $result->columnCount()-1;
   
   $columLen = array();
   
   foreach($result as $key => $col){
   
      preg_match('/\d+/', $col['Type'], $len);
      $len = (isset($len[0]))? $len[0] : '';  
      $fieldname = $col['Field'];            
      $columLen[$fieldname]  = $len;  
   
   }
   

   For this, I get the number of columns and column widths with the code.

3. Then, I separate the data with commas with the following function.

   function txtBol($metin, $genislik){
       $parcala=array();
   
       foreach ($genislik  as $sutunadi => $lenght)
       {
           $parcala[$sutunadi] = substr($metin, 0, $lenght);
           $metin = substr($metin, $lenght);
   
       }
   return $parcala;
   }
   

4. I also get column names with the following code. (ps: to use in a query)

   $KolAdi = $baglanti->query("SHOW COLUMNS FROM customers  where Field NOT IN('id')");
   $KolAdi->execute();
   $colonAdi= $KolAdi->fetchAll(PDO::FETCH_COLUMN);
   $colonAdi=implode(',', $colonAdi);
   

   It prints the data i split correctly when printing it to the screen. So far, so good. But I can’t create the right query with PDO. How should I create the query? (ps: Table column names and column widths are not the same. There will be different numbers and width columns for each table) I would appreciate it if you could help. I proceeded by implementing some solutions from your site.

Table:

id	name	cev1	cev2	cev3
1	MARTIN EDEN	AAAAAA	BBBBB	CCCC

txt:

MARTIN EDEN........AAAAAABBBBBDDDD

Answer 1

Assuming a valid PDO connection in $PDO, you can do the whole job like this. It reads the column data once and uses that to create a query to prepare and a regular expression to extract the data.

$table = 'customer';
$txtFile = 'cust.txt';

// No need to prepare this since there's no untrusted data here.
$result = $pdo->query("show columns from `$table` where Field <> 'id'");

// Get column names and widths in arrays
while($columns = $result->fetch(PDO::FETCH_ASSOC)) {
    $colNames[] = $columns['Field'];
    preg_match('/\d+/', $columns['Type'], $len);
    $colLengths[] = $len[0]??'';
}
// Create regex to extract data from lines:
// Looks like this: /(.{20})(.{10})(.{5})/
$regex = '/(.{'.implode('})(.{',$colLengths).'})/u';

// Create skeleton query with table name and field names and placeholders
// Looks like this: INSERT customer (name,addr1,postcode) VALUES (?,?,?)
$query = "INSERT $table (`".implode('`,`', $colNames).'`) VALUES ('.str_repeat('?,', count($colNames)-1).'?)';

// Read text file
if ($fileData = file($txtFile)) {
    // Prepare the query only once
    $stmt = $pdo->prepare($query);
    foreach ($fileData as $row) {
        // Get the data using the regex from above
        preg_match($regex, $row, $rowData);
        // Remove the first row of the regex matches - see PHP manual for why
        array_shift($rowData);
        // Now execute the prepared query using the data extracted by the regex.
        $stmt->execute($rowData);
    }
}

Note that this codes assumes that the table consists of a series of contiguous columns from which it can extract column widths, and no other columns except id, which it ignores. If your actual table structure is different you'll need to modify the SHOW COLUMNS query to omit those columns, or modify the code that extracts the column data to extract only the relevant columns.

{Edit] Updated the code to include /u UTF-8 modifier on the regex, and to wrap the column names in the INSERT query in back ticks.

Answer 2

you can create a table like this and you don't need to check a number of fields by rows let me know if this useful

CREATE TABLE new_table ( id INT NOT NULL AUTO_INCREMENT, row_id INT NULL DEFAULT 0, row_field_name VARCHAR(50) NULL DEFAULT NULL COMMENT 'Index of ', row_value VARCHAR(255) NULL DEFAULT NULL, PRIMARY KEY (id));