How to import DBM data to a mysql database

This article describes how to import DBM data to a mysql database. If you need it, refer to this example.

First of all, I used PERL to store files in the DBM database. There are more than 50 thousand records and each record has 15 fields. I want to use MYSQL now. I want to export the record.
Step 1: import all DBM records to a text file, and separate each field by TAB (that is, "t"). The line feed of each record ends.
Second, read the data into the array, open the database, and pre-process the definition as follows:

The Code is as follows:

Copy code

<? Php $ Dbline = file ("g:/allrecord.txt"); // read database records to an array $ Collen = array (8, 50, 80, 20, 30, 3, 20, 20, 35, 35, 2); # length value of each field in the table $ Db = new mysqli ('localhost', 'me', 'mypass', 'allcdb '); If (mysqli_connect_errno ()){ Echo "Can not connect db! "; Exit; } $ Q-> query ("SET names 'gb2312 '"); $ Q = "insert into MERs VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) "; $ S = $ db-> prepare ($ q); # pre-defined insert records //

The database has 15 fields. For easy import, all types have been set to CHAR and the length ranges from 1 to 100, which is equivalent to the value of $ collen;
And there is no field set UNIQUE or primary key;
The record is successfully inserted using the INSERT command on the command line.
//

The Code is as follows:

Copy code

Foreach ($ dbline as $ line ){ $ Rec = explode ("t", trim ($ line )); // It has been tested repeatedly here. The $ rec array has 15 values and I have strval it. // For ($ j = 0; $ j <count ($ rec); $ j ++ ){ $ Rec [$ j] = substr ($ rec [$ j], 0, $ collen [$ j]); } For ($ I = 0; $ I <count ($ rec); $ I ++ ){ $ Rec [$ I] = strval ($ rec [$ I]); } $ S-> bind_param ("sssssssssssssss", $ rec [0], $ rec [1], $ rec [2], $ rec [3], $ rec [4], $ rec [5], $ rec [6], $ rec [7], $ rec [8], $ rec [9], $ rec [10], $ rec [11], $ rec [12], $ rec [13], $ rec [14]); $ S-> execute (); } $ S-> close (); $ Db-> close (); ?>

After several hours of debugging, it is found that any string that exceeds the length of the field cannot be added, so the following processing is done.
1) First, the length of each field in each record is processed. The Code is as follows:
 

The Code is as follows:	Copy code
For ($ j = 0; $ j <count ($ rec); $ j ++ ){ $ Rec [$ j] = substr ($ rec [$ j], 0, $ collen [$ j]); }

2) reprocess Inserts

The Code is as follows:	Copy code
$ S-> bind_param ("sssssssssssssss", $ rec [0], $ rec [1], $ rec [2], $ rec [3], $ rec [4], $ rec [5], $ rec [6], $ rec [7], $ rec [8], $ rec [9], $ rec [10], $ rec [11], $ rec [12], $ rec [13], $ rec [14]);

$ S-> execute ();
In this way, you can insert records.

Currently, the problem is that no record with Chinese characters can be inserted (in the command line ).
After checking the information, run alter database crm character set 'gb2312' on the command line and add $ db-> query ("SET names 'gb2312'") before the script query '")
It's useless.
I cannot find my. ini to change default-character-set = gb2312
Does php5.5 currently not support Chinese characters?

There are too many strange tips in MYSQL, which cannot solve the problem. All the text records that are installed with SQLITE are imported in the command line, and the problem is found:

1) Some fields have line breaks, but do not wrap or display them in any text compiler. This will change one row to two rows during the import, and the column parameter is naturally incorrect.
Solution: Use str_replace($line,char(13)) to replace the line breaks in each line, and then save all the records to tmp.txt

2) then import the command line

The Code is as follows:	Copy code
Sqlite3>. import tmp.txt MERs Sqlite3> select count (*) from cusomers;

57491

The import is successful.

It can be seen that during batch import, you must handle the hidden special characters.
MYSQL is not tested due to the time relationship. Check whether the same solution is feasible at night.