Hello all,
I am writing to ask what is the best way to write the sql statement to upload a fixed width file?
like
0123456789Company name Boston MA02215-1234
first field starts on 0 and has 9 char
next filed starts on 9 and has 22 char
next field starts on 30 and has 10 char
and etc etc
the numbers that I am using may be wrong as I am just creating a example of the file which is much bigger
Here is my code:
LOAD DATA INFILE '/home/dev/www/uploads/EO1.LST'
INTO TABLE irs_eoml
(@var1)
SET
EIN = substr( @var1 , 0, 9 ) ,
PNO = substr( @var1 , 9, 70 ) ,
ICN = substr( @var1 , 79, 35 ) ,
Street_Address = substr( @var1 , 114, 35 ) ,
City = substr( @var1 , 149, 22 ) ,
State = substr( @var1 , 171, 2 ) ,
Postal_Code = substr( @var1 , 173, 10 )";
When this script is ran it is pushing things to the right, so for the post code it only has 9 char instead of the 10 char 12345-1234 it shows 12345-123
Now I was told that all fields start on 0 with mysql but as I am playing with this it is working better if I change them by adding one to the start prosition, is this correct, also its only uploading one and its skipping all the rest and its telling me the record count
out put here from mysql using PuTTY:
Query OK, 1 row affected, 65535 warnings (0.56 sec)
Records: 3946 Deleted: 0 Skipped: 3945 Warnings: 130218
Thanks for the help!
Sincerely,
Christopher
