pulling a number out of a mySQL text field?

[LRW]

I have an automated process which uploads a comma separated
spreadsheet (csv) and inserts it into a database:

$sql = "LOAD DATA INFILE '".$uploadfile."' INTO TABLE `tbl_tracking`
FIELDS TERMINATED BY ','".
"OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'".
"( `tr_trackno` , `tr_ordernum` , `tr_shipname` , `tr_shipaddr` ,
`tr_citystate` , `tr_zip` , `tr_weight` , `tr_method` , `tr_shipdate`
)";

Now, one of the columns is supposed to be a 5-digit integer
column...but the person who generates the spreadsheet (a 3rd party I
have no control over) ocassionally has other text in that column in
some records. Say 3 out of 50.

For example, what it's supposed to have is data like: '12345', but
instead it sometimes has: '12345 Some Extra Text Here', and sometimes
there will be a doublequoute in front: '"12345 Some Extra Text'.

No my question is, is there a way to pull a 5-digit number out of a
field? If I could be gaurenteed that the number would always be the
1st 5 digits, I could just do a len() and then crop everything after
the 5th character. But when there's ocassionally a character in front
of the number, that won't work.

What if I make the database column a 5-digit init field? Would it
automatically only insert the 5-digit part of the field? (Doubt it.)

Thanks for any suggestions!!
Liam

[Nel]

"LRW" <de**@celticbear.com> wrote in message
news:3a**************************@posting.google.c om...

I have an automated process which uploads a comma separated
spreadsheet (csv) and inserts it into a database:

$sql = "LOAD DATA INFILE '".$uploadfile."' INTO TABLE `tbl_tracking`
FIELDS TERMINATED BY ','".
"OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'".
"( `tr_trackno` , `tr_ordernum` , `tr_shipname` , `tr_shipaddr` ,
`tr_citystate` , `tr_zip` , `tr_weight` , `tr_method` , `tr_shipdate`
)";

Now, one of the columns is supposed to be a 5-digit integer
column...but the person who generates the spreadsheet (a 3rd party I
have no control over) ocassionally has other text in that column in
some records. Say 3 out of 50.

For example, what it's supposed to have is data like: '12345', but
instead it sometimes has: '12345 Some Extra Text Here', and sometimes
there will be a doublequoute in front: '"12345 Some Extra Text'.

No my question is, is there a way to pull a 5-digit number out of a
field? If I could be gaurenteed that the number would always be the
1st 5 digits, I could just do a len() and then crop everything after
the 5th character. But when there's ocassionally a character in front
of the number, that won't work.

What if I make the database column a 5-digit init field? Would it
automatically only insert the 5-digit part of the field? (Doubt it.)

Thanks for any suggestions!!
Liam

If I were you I would split the first colum up into two fields
e.g. number - INTEGER
comments - TEXT

Check form input for the number field and make sure it's always a number.
If not print error and return user to form, else add to database. If needs
be you could also check to ensure the input number is 5 digits long.

IMHO best to start right than trying to bodge through dirty data.

Nel.

[Michael Austin]

LRW wrote:

I have an automated process which uploads a comma separated
spreadsheet (csv) and inserts it into a database:

$sql = "LOAD DATA INFILE '".$uploadfile."' INTO TABLE `tbl_tracking`
FIELDS TERMINATED BY ','".
"OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'".
"( `tr_trackno` , `tr_ordernum` , `tr_shipname` , `tr_shipaddr` ,
`tr_citystate` , `tr_zip` , `tr_weight` , `tr_method` , `tr_shipdate`
)";

Now, one of the columns is supposed to be a 5-digit integer
column...but the person who generates the spreadsheet (a 3rd party I
have no control over) ocassionally has other text in that column in
some records. Say 3 out of 50.

For example, what it's supposed to have is data like: '12345', but
instead it sometimes has: '12345 Some Extra Text Here', and sometimes
there will be a doublequoute in front: '"12345 Some Extra Text'.

No my question is, is there a way to pull a 5-digit number out of a
field? If I could be gaurenteed that the number would always be the
1st 5 digits, I could just do a len() and then crop everything after
the 5th character. But when there's ocassionally a character in front
of the number, that won't work.

What if I make the database column a 5-digit init field? Would it
automatically only insert the 5-digit part of the field? (Doubt it.)

Thanks for any suggestions!!
Liam

yes, but you will not be able to use LOAD DATAFILE to do it.. you will basically
need to "roll-your-own".

you should be getting errors on those fields if the column data type is number
or similar. What I would do is run the normal load, then have a seperate
"alternate" process that reads the file, determines any abnormal occurances of
text in a number field and then use a regexp to change the record and load it.
The load feature does not appear to write an exceptions file for those records
that might fail - at least not that I have seen.... maybe this should be an
enhancement request for later versions...

--
Michael Austin.
Consultant - Available.
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)

[steve]

"LRW" wrote:

I have an automated process which uploads a comma separated
spreadsheet (csv) and inserts it into a database:

$sql = "LOAD DATA INFILE ’".$uploadfile."’ INTO TABLE
`tbl_tracking`
FIELDS TERMINATED BY ’,’".
"OPTIONALLY ENCLOSED BY ’\"’ LINES TERMINATED BY
’\n’".
"( `tr_trackno` , `tr_ordernum` , `tr_shipname` , `tr_shipaddr` ,
`tr_citystate` , `tr_zip` , `tr_weight` , `tr_method` , `tr_shipdate` )";

Now, one of the columns is supposed to be a 5-digit integer
column...but the person who generates the spreadsheet (a 3rd party I have no control over) ocassionally has other text in that column in
some records. Say 3 out of 50.

For example, what it’s supposed to have is data like:
’12345’, but
instead it sometimes has: ’12345 Some Extra Text Here’,
and sometimes
there will be a doublequoute in front: ’"12345 Some Extra
Text’.

No my question is, is there a way to pull a 5-digit number out of a
field? If I could be gaurenteed that the number would always be the
1st 5 digits, I could just do a len() and then crop everything after the 5th character. But when there’s ocassionally a character in
front
of the number, that won’t work.

What if I make the database column a 5-digit init field? Would it
automatically only insert the 5-digit part of the field? (Doubt it.)
Thanks for any suggestions!!
Liam

read the free-form text field
let’s call it
$freeform = "abcz23557 xyz";
preg_match("/\D\d{5}\D/", $freeform, $Arr);
array $Arr[1] would be the first 5 digit integer, $Arr[2] would be the
2nd one and so forth.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/PHP-pulling-...ict137884.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=460750

[Michael Austin]

Nel wrote:

"LRW" <de**@celticbear.com> wrote in message
news:3a**************************@posting.google.c om...

I have an automated process which uploads a comma separated
spreadsheet (csv) and inserts it into a database:

$sql = "LOAD DATA INFILE '".$uploadfile."' INTO TABLE `tbl_tracking`
FIELDS TERMINATED BY ','".
"OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'".
"( `tr_trackno` , `tr_ordernum` , `tr_shipname` , `tr_shipaddr` ,
`tr_citystate` , `tr_zip` , `tr_weight` , `tr_method` , `tr_shipdate`
)";

Now, one of the columns is supposed to be a 5-digit integer
column...but the person who generates the spreadsheet (a 3rd party I
have no control over) ocassionally has other text in that column in
some records. Say 3 out of 50.

For example, what it's supposed to have is data like: '12345', but
instead it sometimes has: '12345 Some Extra Text Here', and sometimes
there will be a doublequoute in front: '"12345 Some Extra Text'.

No my question is, is there a way to pull a 5-digit number out of a
field? If I could be gaurenteed that the number would always be the
1st 5 digits, I could just do a len() and then crop everything after
the 5th character. But when there's ocassionally a character in front
of the number, that won't work.

What if I make the database column a 5-digit init field? Would it
automatically only insert the 5-digit part of the field? (Doubt it.)

Thanks for any suggestions!!
Liam

If I were you I would split the first colum up into two fields
e.g. number - INTEGER
comments - TEXT

Check form input for the number field and make sure it's always a number.
If not print error and return user to form, else add to database. If needs
be you could also check to ensure the input number is 5 digits long.

IMHO best to start right than trying to bodge through dirty data.

Nel.

Nel,

FYI, the OP said the source is a third-party that sends a file so there is no
"form" to check. :)
--
Michael Austin.
Consultant - Available.
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)

[Nel]

> Nel,

FYI, the OP said the source is a third-party that sends a file so there is no "form" to check. :)
--
Michael Austin.
Consultant - Available.
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)

Did I mention I can't read? :-)

[Nikolai Chuvakhin]

de**@celticbear.com (LRW) wrote in message
news:<3a**************************@posting.google. com>...

I have an automated process which uploads a comma separated
spreadsheet (csv) and inserts it into a database:

$sql = "LOAD DATA INFILE '".$uploadfile."' INTO TABLE `tbl_tracking`
FIELDS TERMINATED BY ','".
"OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'".
"( `tr_trackno` , `tr_ordernum` , `tr_shipname` , `tr_shipaddr` ,
`tr_citystate` , `tr_zip` , `tr_weight` , `tr_method` , `tr_shipdate`
)";

Now, one of the columns is supposed to be a 5-digit integer
column...but the person who generates the spreadsheet (a 3rd party I
have no control over) ocassionally has other text in that column in
some records. Say 3 out of 50.

For example, what it's supposed to have is data like: '12345', but
instead it sometimes has: '12345 Some Extra Text Here', and sometimes
there will be a doublequoute in front: '"12345 Some Extra Text'.

No my question is, is there a way to pull a 5-digit number out of a
field?

Yes:

$data = '"12345 Some Extra Text';
$data = str_replace('"', '', $data);
list($data,) = explode(' ', $data);

Cheers,
NC