Load Data in File

PerceptiveJohn has asked for the wisdom of the Perl Monks concerning the following question:

Hello Again. New to Perl.

I have a simple text file that has 2 fields separated by commas and each line ends with a new-line character. I want to aad this text file into a table. Description of table:

Create Table testtable (
field1 int(6) Primry Key,
field2 int(4))
engine=myisam;
[download]

For the sake of example, my text file has 1 record:
First Field: 055555 Second Field: 1234
Thus the format 055555,1234

Problem: Because both fields in the table are described as INT, if any of the fields coming from the text file have a leading zero, the zero is dropped in the table field. If I change the table field to CHAR instead of INT it works fine.

Here's the Load Data instructions:

LOAD DATA INFILE 'text.txt'
Into Table testtable
Fields terminated by ','
Lines terminated by '\n';
[download]

Any ideas? Thank you.

Edit (davorg): Removed SHOUTING from title

Comment on Load Data in File Select or Download Code

Replies are listed 'Best First'.
Re: Load Data in File by davorg (Chancellor) on Jul 11, 2006 at 14:04 UTC
If the leading zero in your data is significant then that field is a string and not a number and should therefore be stored in a text field[1]. Perl isn't doing anything to the data, it's MySQL that is storing "055555" in a number field and thereby converting it to the smallest equivalent number - 55555. If you have text, then store it in a text field. [1] Ignoring the possibility that it's an octal number - in which case you should convert it to decimal before storing in the database. -- <http://dave.org.uk> "The first rule of Perl club is you do not talk about Perl club." -- Chip Salzenberg	[reply]
Re: LOAD DATA INFILE by ptum (Priest) on Jul 11, 2006 at 14:26 UTC
When I have numerical data, I like to store it as such in whatever database I'm using, so that I can take advantage of numeric database functions if I need to. But when it comes time to manipulate, compare or display the data, Perl doesn't really care -- it is all just a scalar. I often use sprintf to format data that I select from a database but need to have in a particular format: `use strict; use warnings; use DBI; # assuming $dbh is a connected database handle my $result_ref = $dbh->selectall_arrayref("select field1, field2 fro +m testtable"); if ($DBI::errstr) { print "An error was encountered in retrieving data : $DBI::errstr" +; } foreach my $thisrow (@$result_ref) { my $field1 = sprintf "%06d",$thisrow->[0]; my $field2 = $thisrow->[1]; # do whatever you need to do with those values }` [download] Of course, many relational databases also have internal formatting instructions (e.g. Oracle's lpad or rpad) which can take care of this for you as well. No good deed goes unpunished. -- (attributed to) Oscar Wilde	[reply] [d/l]