|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Codewalkers Forums Sponsor:
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
January 21st, 2008, 09:58 AM
|
|||
|
|||
|
[RESOLVED] LOAD DATA INFILE (fixed width file) Help Needed!
Is there any one that can help me solve this problem, I am trying to
up load 6 large file to the database, I am having issue with the LOAD DATA LOCAL INFILE function of MySQL??? It does not matter if I do: Code:
LOAD DATA LOCAL INFILE 'www/dev/uploads/EO2.LST' INTO TABLE irs_rawdata which gives me the errors: Query OK, 570845 rows affected, 65535 warnings (20.71 sec) Records: 570845 Deleted: 0 Skipped: 0 Warnings: 18267040 the effect of that in the table is that it loads the first field of each line only -------------------------- if I do: Code:
LOAD DATA LOCAL INFILE 'www/uploads/EO2.LST'
INTO TABLE irs_rawdata
(@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 ) ,
GEN = substr( @var1 , 183, 4 ) ,
Subsection_Code = substr( @var1 , 187, 2 ) ,
Affiliation_Code = substr( @var1 , 189, 1 ) ,
Classification_Code = substr( @var1 , 190, 4 ) ,
Ruling_Date = substr( @var1 , 194, 6 ) ,
Deductibility_Code = substr( @var1 , 200, 1 ) ,
Foundation_Code = substr( @var1 , 201, 2 ) ,
Activity_Code = substr( @var1 , 203, 9 ) ,
Organization_Code = substr( @var1 , 212, 1 ) ,
UL_Code = substr( @var1 , 213, 2 ) ,
AFED = substr( @var1 , 215, 6 ) ,
Tax_Period = substr( @var1 , 221, 6 ) ,
Asset_Code = substr( @var1 , 227, 1 ) ,
Income_Code = substr( @var1 , 228, 1 ) ,
FR_Code = substr( @var1 , 229, 3 ) ,
Blanks = substr( @var1 , 232, 3 ) ,
Accounting_Period = substr( @var1 , 235, 2 ) ,
Asset_Amount = substr( @var1 , 237, 13 ) ,
Income_Amount = substr( @var1 , 250, 13 ) ,
IAN_NS = substr( @var1 , 263, 1 ) ,
F_990_RA = substr( @var1 , 264, 13 ) ,
RAN_NS = substr( @var1 , 277, 1 ) ,
NTEE_Code = substr( @var1 , 278, 4 ) ,
SN_SNL = substr( @var1 , 282, 35 ) ,
Line_Feed = substr( @var1 , 317, 1 );
which gives me the errors: Query OK, 1 row affected, 65535 warnings (1 min 15.87 sec) Records: 570845 Deleted: 0 Skipped: 570844 Warnings: 262040 the efffect is: it only puts in one row and with that row it does not enter any thing for the first field then it takes the last char of the first field puts it in the next field and pushs all other fields one to the right PLEASE any help would be great this is holding up the ablility to filter out and start work on the data thats in the files Sincerely, Christopher Last edited by spiritssight : January 23rd, 2008 at 11:04 AM.
|
|
#2
January 23rd, 2008, 09:16 AM
|
||||
|
||||
|
how is the infile organized? is it delimited? if so - how?
If it is all one continuous fixed length string - I personally would dynamically handle the inserts with a programming language (substr a line to get the parts and then shove the values into an INSERT query and send it to the DB) Not very hard to do and if it is only going to be a one-offer it will get things done and over with. References: http://dev.mysql.com/doc/refman/5.0/en/load-data.html http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html
__________________
life is a game.... Have fun ----------------------------- http://www.phpwomen.org strength in unity PHPCommunity IRC #phpc on freenode
|
|
#3
January 23rd, 2008, 10:21 AM
|
|||
|
|||
|
its a fixed-width file as the subject states, and its every month the total records being uploaded is 3m
Thank you for your respone and help Sincerely, Christopher
|
|
#4
January 23rd, 2008, 10:43 AM
|
||||
|
||||
|
With no delimiter I would do it programatically and see if there is a way to make the import file something more palletable to LOAD INFILE for the future.
|
|
| Viewing: Codewalkers Forums > Other Technologies > Database Help > LOAD DATA INFILE (fixed width file) Help Needed! |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
