php - Convert fixed length text file into SQL -

i need convert fixed length text file mysql table.
biggest problem multiple cells contained on each line, , how file sent me, , main reason why want convert it.

the cells of specific length; included on 1 line.

for example first 3 positions (1 - 3) of line irt, next 3 positions (4 - 6) iftc next 5 positions (7 - 11) fsc, etc.

as file can contain 300 lines of records, need easy way import straight sql tables.

i have been searching net hours trying find solution, without comma separation haven't been able find working solution yet.

i code solution in php, if possible well. , willing long yards of working out how use function required if give me function name, don't expect people write code out me.

file:

testfile.txt (4 rows)  aaa11111xx bbb22222yy ccc33333zz ddd 444 aa 

table:

create table testloaddatainfile ( varchar(3) , b int(5) , c char(2) ) charset = latin1; 

code:

load data infile 'd:\\...\\testfile.txt' table testloaddatainfile fields terminated '' lines terminated '\r\n' ; 

result:

mysql> select * testloaddatainfile ; +-----+-------+----+ |   | b     | c  |  +-----+-------+----+  | aaa | 11111 | xx |  | bbb | 22222 | yy |  | ccc | 33333 | zz |  | ddd |   444 | aa |  +-----+-------+----+  

the load data infile documentation not @ point (fixed-size fields). here's related parts:

• if fields terminated , fields enclosed values both empty (''), fixed-row (nondelimited) format used. fixed-row format, no delimiters used between fields (but can still have line terminator). instead, column values read , written using field width wide enough hold values in field. tinyint, smallint, mediumint, int, , bigint, field widths 4, 6, 8, 11, , 20, respectively, no matter declared display width is.

lines terminated still used separate lines. if line not contain fields, rest of columns set default values. if not have line terminator, should set ''. in case, text file must contain fields each row.

fixed-row format affects handling of null values, described later. note that fixed-size format not work if using multi-byte character set.

---

null handling

with fixed-row format (which used when fields terminated , fields enclosed both empty), null written empty string. note causes both null values , empty strings in table indistinguishable when written file because both written empty strings. if need able tell 2 apart when reading file in, should not use fixed-row format.

---

some cases not supported load data infile:

• fixed-size rows (fields terminated , fields enclosed both empty) , blob or text columns.

---

• user variables cannot used when loading data fixed-row format because user variables not have display width.