Complex fixed width import MYSQL

Question

I have some huge files which I have to import to the DB. They have no separators so would be fixed width. The thing is that I'd like to have the avg of some of the fields as one column, and only from the ones which are not 0.

example text(no spaces in real one):

ASDD 000 000 003
BBBB 006 004 000
CCCC 001 002 000

the resultant insert I need is

ASDD | 3
BBBB | 5     [(6+4)/2]
CCCC | 1.5   [(1+2)/2]

Right now I'm using the next code, which is obviously wrong.

LOAD DATA LOCAL
INFILE '{$file}'
INTO TABLE data
  (@var1)
    SET
    name=SUBSTR(@var1,1,4)
    avg=(SUBSTR(@var1,5,3)+SUBSTR(@var1,8,3)+SUBSTR(@var1,11,3))/3

Which generates the next entries:

 ASDD | 1
 BBBB | 3.33
 CCCC | 1

My question is, Is there any way to do it properly with LOAD DATA LOCAL (or any other SQL directly), or should I use php istead?

The problem with PHP is that the real files has like 9M rows, 900 columns and 10k char per line and I think the import would be quite slow.

I like when people try to code on mysql side... :-) – Alex May 06 '15 at 18:36 — Alex, May 06 '15 at 18:36

score 0 · Answer 1 · answered May 06 '15 at 18:38

0

name=SUBSTR(@var1,1,4)
num = IF(SUBSTR(@var1,5,3)=0,0,1)+IF(SUBSTR(@var1,8,3)=0,0,1)+IF(SUBSTR(@var1,11,3)=0,0,1) 
avg=(SUBSTR(@var1,5,3)+SUBSTR(@var1,8,3)+SUBSTR(@var1,11,3))/num

answered May 06 '15 at 18:38

Alex

16,739
1
28
51

This needs a new column 'num', which will also be stored. Is it right? – user3725565 May 06 '15 at 23:20
why? it is just a variable. if you don't need it you can not keep it – Alex May 07 '15 at 01:05
tried it and got an error: Can not identify column num. I guess it's about LOAD LOCAL, which inserts automatically name, num and avg. – user3725565 May 07 '15 at 07:36

Complex fixed width import MYSQL

1 Answers1