LOAD DATA with 0s in field

webnick

Hi, I've got a database import that runs daily and pulls 100000 records into a 600 column table. Is there a way to keep the leading zeros with a LOAD DATA query? They don't seem to end up in the records and are required.

LOAD DATA 
INFILE "z:\\folder\\file" 
INTO TABLE `db`.`tble` 
FIELDS TERMINATED BY '\t' 
IGNORE 1 LINES;

The table looks like

CREATE TABLE  `db`.`table` (
`c1_ID`	 mediumint(7)	 NOT NULL default '0',
`c2_GROUP`	 smallint(2)	 NOT NULL default '0',
`c3_LNAME`	 varchar(30)	 default NULL,

NogDog

 CREATE TABLE  `db`.`table` (
`c1_ID`     mediumint(7)    ZEROFILL  NOT NULL default '0',
`c2_GROUP`     smallint(2)    ZEROFILL  NOT NULL default '0',
`c3_LNAME`     varchar(30)     default NULL,

tomhath

What do you mean by leading zeros? I suspect you are loading some kind of IDs that happen to be all digits. Those are not numbers, they're strings. Define the column as varchar.

webnick

Some of the fields have leading zeros that are not honored using LOAD DATA INFILE. For example '000036' ends up '36'. That's in a mediumint(7) column. It's not the client because I see this through phpMyAdmin or the command line.

Other columns have a value of '0' which is different from NULL and would be nice to honor.

Thanks for any ideas.

tomhath

If you need the string "000036" then you need to store it in a character column (char or varchar). A numeric column such as mediumint will store the number 36.

My rule of thumb is to ask if the value will ever be used in arithmetic calculations (does it make sense to add two c2_GROUP values like 000036 and 000042 to get 000078? Can you multiple it by 4 to get 000144?). If arithmetic doesn't make sense, it's a string.

NogDog

tomhath wrote:
If you need the string "000036" then you need to store it in a character column (char or varchar). A numeric column such as mediumint will store the number 36.

My rule of thumb is to ask if the value will ever be used in arithmetic calculations (does it make sense to add two c2_GROUP values like 000036 and 000042 to get 000078? Can you multiple it by 4 to get 000144?). If arithmetic doesn't make sense, it's a string.

Or (1) use the ZEROFILL option I proposed above for the column definitions, or (2) my preferred method is to just store it as the applicable type of numeric field and prepend zeros via my PHP code during output, such as via the printf/sprintf functions:

printf("Acct. number: %06d", $row['acct_number']);

webnick

This is very helpful. I think they'll get zero filled so that it's easier to check conformity on such long rows.

Another quick question -
What should a column default be to get NOW() inserted with LOAD DATA? I'd like a last update field but it seems to get '00000000000000'. This last column is one longer than the loaded rows.

 ALTER TABLE `table` CHANGE `c_lastupdate` `c_lastupdate` TIMESTAMP( 14 ) NOT NULL

Thanks for any reply.