Re: Load Data Infile confusion
Hello Robert ,, I have no explanation , just a dummy question , 1) Are all values u try to put into column 5 a float with . ? 2) what if u try with integer ?? without . ? 3) can u input float values in column 3 ? 4) are u running mysqlcc from same pc ? 5 could it be some settings with decimal point is set to something diff than . depending of ur enviroment ? Not much help , only a kind of maybe list :) HTH Kim G. Pedersen This one has had me scratching my head all afternoon. I have a comma delimited text file that I am using to import data from a legacy system into a MyISAM table. My server is 4.0.16 as is my client. Both run on Redhat 8.0. An example line of imput looks like 041502f,1,25.00,$,25.00,,2003-1-15T17:47:50,O with my table format being CREATE TABLE IF NOT EXISTS FeeSplit( FileNumber VARCHAR( 50 ) BINARY NOT NULL, AppraiserCode VARCHAR( 08 ) BINARY NOT NULL, SplitNumber FLOAT DEFAULT 0.00, SplitDesignator CHAR( 01 ) DEFAULT '$', CalculatedSplitAmt FLOAT DEFAULT 0.00, DateFeePaid DATE, DateTimeRecordAdded DATETIME, PersonDesignatorCHAR( 01 ), SplitComments TEXT, LastModifiedTIMESTAMP, LastModifiedBy VARCHAR( 08 ), PRIMARY KEY ( FileNumber, AppraiserCode, SplitNumber, SplitDesignator, CalculatedSplitAmt, DateFeePaid, DateTimeRecordAdded ), INDEX FileNumberDesignatorIndex ( FileNumber, PersonDesignator ), INDEX AppraiserDesignatorIndex ( FileNumber, AppraiserCode, PersonDesignator ), INDEX AppraiserCodeIndex ( AppraiserCode ), INDEX FileNumberIndex ( FileNumber ); To load my data, I execute the following commands mysql --user=xxx --password=xxx -e USE Live_Tables; DELETE QUICK FROM FeeSplit; FLUSH TABLES; mysql --user=xxx --password=xxx -e USE Live_Tables; LOAD DATA INFILE 'Converted_FeeSplit.txt' INTO TABLE FeeSplit FIELDS TERMINATED BY ',' ENCLOSED BY '\' ESCAPED BY '~'; When these commands have completed, the value of field 5 is *always* 0.00. This in and of itself has had me confused - especially since I can execute the same SQL commands in MySQLCC and the contents of field 5 will be whatever is in my input file. I had this problem with 4.0.13 as well as now with 4.0.16. Has anyone seen this before? And possibly has a cure or fix? Thanks! Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] mvh Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Infile confusion
Thanks for the reply Kim! On Tuesday 18 November 2003 12:47 pm, you wrote: Hello Robert ,, I have no explanation , just a dummy question , 1) Are all values u try to put into column 5 a float with . ? Always floats. 2) what if u try with integer ?? without . ? haven't yet tried. 3) can u input float values in column 3 ? From MySQLCC - yes. 4) are u running mysqlcc from same pc ? Yes - against a local copy of mysqld 5 could it be some settings with decimal point is set to something diff than . depending of ur enviroment ? Not that I can tell. I have 3 seperate MySQL servers (Production, Test and Development) The problem appears to be happening on this one table on all 3 machines. And to make matters worse, the value in field 3 is a float too. The *only* thing I can think of is that the value of field 6 being simply 2 quotes is causing some confusion for field 5. But that I can import from MySQLCC, I would say that I've messed up the data somehow. Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Load Data Infile confusion
This one has had me scratching my head all afternoon. I have a comma delimited text file that I am using to import data from a legacy system into a MyISAM table. My server is 4.0.16 as is my client. Both run on Redhat 8.0. An example line of imput looks like 041502f,1,25.00,$,25.00,,2003-1-15T17:47:50,O with my table format being CREATE TABLE IF NOT EXISTS FeeSplit( FileNumberVARCHAR( 50 ) BINARY NOT NULL, AppraiserCode VARCHAR( 08 ) BINARY NOT NULL, SplitNumber FLOAT DEFAULT 0.00, SplitDesignator CHAR( 01 ) DEFAULT '$', CalculatedSplitAmtFLOAT DEFAULT 0.00, DateFeePaid DATE, DateTimeRecordAdded DATETIME, PersonDesignator CHAR( 01 ), SplitComments TEXT, LastModified TIMESTAMP, LastModifiedByVARCHAR( 08 ), PRIMARY KEY ( FileNumber, AppraiserCode, SplitNumber, SplitDesignator, CalculatedSplitAmt, DateFeePaid, DateTimeRecordAdded ), INDEX FileNumberDesignatorIndex ( FileNumber, PersonDesignator ), INDEX AppraiserDesignatorIndex ( FileNumber, AppraiserCode, PersonDesignator ), INDEX AppraiserCodeIndex ( AppraiserCode ), INDEX FileNumberIndex ( FileNumber ); To load my data, I execute the following commands mysql --user=xxx --password=xxx -e USE Live_Tables; DELETE QUICK FROM FeeSplit; FLUSH TABLES; mysql --user=xxx --password=xxx -e USE Live_Tables; LOAD DATA INFILE 'Converted_FeeSplit.txt' INTO TABLE FeeSplit FIELDS TERMINATED BY ',' ENCLOSED BY '\' ESCAPED BY '~'; When these commands have completed, the value of field 5 is *always* 0.00. This in and of itself has had me confused - especially since I can execute the same SQL commands in MySQLCC and the contents of field 5 will be whatever is in my input file. I had this problem with 4.0.13 as well as now with 4.0.16. Has anyone seen this before? And possibly has a cure or fix? Thanks! Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
