pulling a number out of a mySQL text field?

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

"LRW" <deja@celticbear.com> wrote in message
news:3a1d1813.0408090804.7a25bf7d@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.

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
:)

"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

Nel wrote:

> "LRW" <deja@celticbear.com> wrote in message
> news:3a1d1813.0408090804.7a25bf7d@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,
>
> 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? :-)

deja@celticbear.com (LRW) wrote in message
news:<3a1d1813.0408090804.7a25bf7d@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