300K rows of data ...conversion

[el__marcin]

hi I am using MS office Access 2003. ANybody knows how to
convert more than 300-400K rows from text into e.g. number ?
I tried to do so a few times and access told me there aint
enough memory or something. Anybody knows quick fix to that
problem ? thanks

[MGFoster]

el********@o2.pl wrote:

hi I am using MS office Access 2003. ANybody knows how to
convert more than 300-400K rows from text into e.g. number ?
I tried to do so a few times and access told me there aint
enough memory or something. Anybody knows quick fix to that
problem ? thanks

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll have to create a new column in the table w/ the new numeric data
type. Then run an UPDATE query that copies/converts the text value into
the numeric column.

If you get the "not enough memory" error during the UPDATE, it may be
because you really don't have enough memory (RAM) in your computer, or
Access is just getting grumpy. During development Access starts to eat
a lot of memory; to avoid the error you could try closing Access & then
re-opening it & immediately run the UPDATE query.

If that doesn't work, try updating chunks of the data instead of the
whole table; use WHERE clauses like this:

(say your data covers 5 years, with about 20,000 rows per year)

WHERE date_column BETWEEN #1/1/2004# AND #12/31/2004#

then change the date range to the next year & re-run the UPDATE. Repeat
until you've covered all the date ranges in the table.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRSlRxYechKqOuFEgEQLLMwCgnyo3DPx8bJccLtQdWJBn5q uKJ+kAnRO3
+WiAU2Di5BKYlvJJkpYYd32Q
=pr95
-----END PGP SIGNATURE-----

[Tom van Stiphout]

On Sun, 08 Oct 2006 19:30:11 GMT, MGFoster <[e-mail]:

Indeed, or use something like "TOP 10000". Most Execute methods return
a count of records affected, so you could loop until that number is 0.

-Tom.
<clip>

>
If that doesn't work, try updating chunks of the data instead of the
whole table; use WHERE clauses like this:

(say your data covers 5 years, with about 20,000 rows per year)

WHERE date_column BETWEEN #1/1/2004# AND #12/31/2004#

then change the date range to the next year & re-run the UPDATE. Repeat
until you've covered all the date ranges in the table.

[MLH]

I'm not sure how these guys answered
your question. They must-a-read a lot more
into it than you wrote.

Forget the 300,000 to 400,000 records of
whatever it is you're talking about and give
is one clear example. What are you trying
to do. Just what is it you mean by
"convert ... from text into e.g. number"
What exactly is an "e.g. number", huh?

Are you trying to convert "four" to 4 or
"thirty-three" to 33? I don't know what
these guys are answering. And I sure
can't reverse-engineer your question out
of their answer.

[Tim Marshall]

MLH wrote:

Are you trying to convert "four" to 4 or
"thirty-three" to 33? I don't know what
these guys are answering. And I sure
can't reverse-engineer your question out
of their answer.

While your interpretation might also be vaid, Tom and MG are talking
about using an update query to convert data types. Their response
couldn't be any simpler.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[MLH]

You are right. Responses are simple.
I cannot infer the question from either
response. That's all I was tellin' the guy.

[el__marcin]

to clarify:
I usually export huge amount of data from our accounting system (300K
being small sample). While importing txt file into access I receive all
the 3 columns in text format. What I was thinking is that ...in order
to do any computation I need to convert revenue text format into number
format ... hence my question. What I just discovered is

1) MS access will perform 4 basic mathematical operation (*, /, -, +)
on text data (numbers) .. returning results in number format !!!

Basically what I need to do is to multiply any non number format text
by 1 and I will receive a column with number data (providing it is
really number )...which I do not really need considering #1 ;-) ...it
is all teamwork ... !!

[pietlinden]

el********@o2.pl wrote:

to clarify:
I usually export huge amount of data from our accounting system (300K
being small sample). While importing txt file into access I receive all
the 3 columns in text format. What I was thinking is that ...in order
to do any computation I need to convert revenue text format into number
format ... hence my question. What I just discovered is

1) MS access will perform 4 basic mathematical operation (*, /, -, +)
on text data (numbers) .. returning results in number format !!!

Basically what I need to do is to multiply any non number format text
by 1 and I will receive a column with number data (providing it is
really number )...which I do not really need considering #1 ;-) ...it
is all teamwork ... !!

You mean that if you create an import specification and run the import,
Access _still_ treats your numeric data as text? I would start with
creating an import spec and then testing on a small sample so you don't
run out of memory. Once you have that sorted, I would try importing an
entire file.

[MLH]

On 9 Oct 2006 12:04:54 -0700, "el********@o2.pl" <el********@o2.pl>
wrote:

>to clarify:
I usually export huge amount of data from our accounting system (300K
being small sample). While importing txt file into access I receive all
the 3 columns in text format. What I was thinking is that ...in order
to do any computation I need to convert revenue text format into number
format ... hence my question. What I just discovered is

1) MS access will perform 4 basic mathematical operation (*, /, -, +)
on text data (numbers) .. returning results in number format !!!

Basically what I need to do is to multiply any non number format text
by 1 and I will receive a column with number data (providing it is
really number )...which I do not really need considering #1 ;-) ...it
is all teamwork ... !!

I think I understand you a bit better. From what you said, I would
imagine a single record you have something like this after importing
to MS Access. Let's call your table tblTransactions.

[TextField1] [TextField2] [TextField3]
"Line Item A" "33" "Other text"
"Line Item B" "104" "Blah Blah"
"Line Item C" "250" "Blah Blah"
....

If this is true, you could reference table fields containing
text-string representations of numbers as if they were numbers
using CInt, CLng, CCur - to name a few. For example,

SELECT CCur([TextField2])+12 AS CalcField,
tblTransactions.TextField1FROM tblTransactions;

would allow you to list your first 2 table fields, adding 12 to the
value expressed in the 2nd field and displaying it as currency.
I'm of the opinion that, although multiplying the number 1 times
the string "33" may produce the numerical result you seek, it
only does so as the result of MS Access coercing the string
value to 33 to a numeric value because it 'assumes' that's what
you want to do. Its probably not always in your best interest to
let someone on the Access Development Team make your
decisions for you like that. I say this in spite of the fact that
what you tried worked.

[Terry Kreft]

The usual reason for this error (in my experience) is not lack of ram, as
Access will page out, it's lack of temp disc space or (on one famous
occasion) lack of physical disc space.

--

Terry Kreft
"MGFoster" <[e-mail] in message
news:7l*****************@newsread1.news.pas.earthl ink.net...

el********@o2.pl wrote:

hi I am using MS office Access 2003. ANybody knows how to
convert more than 300-400K rows from text into e.g. number ?
I tried to do so a few times and access told me there aint
enough memory or something. Anybody knows quick fix to that
problem ? thanks

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll have to create a new column in the table w/ the new numeric data
type. Then run an UPDATE query that copies/converts the text value into
the numeric column.

If you get the "not enough memory" error during the UPDATE, it may be
because you really don't have enough memory (RAM) in your computer, or
Access is just getting grumpy. During development Access starts to eat
a lot of memory; to avoid the error you could try closing Access & then
re-opening it & immediately run the UPDATE query.

If that doesn't work, try updating chunks of the data instead of the
whole table; use WHERE clauses like this:

(say your data covers 5 years, with about 20,000 rows per year)

WHERE date_column BETWEEN #1/1/2004# AND #12/31/2004#

then change the date range to the next year & re-run the UPDATE. Repeat
until you've covered all the date ranges in the table.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRSlRxYechKqOuFEgEQLLMwCgnyo3DPx8bJccLtQdWJBn5q uKJ+kAnRO3
+WiAU2Di5BKYlvJJkpYYd32Q
=pr95
-----END PGP SIGNATURE-----

[Terry Kreft]

I don't believe MLH is saying the answers are unclear, the question is.
He's asking for clarification from the OP not criticising Tom and MGF.

--

Terry Kreft
"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumwrote in message
news:eg**********@coranto.ucs.mun.ca...

MLH wrote:

Are you trying to convert "four" to 4 or
"thirty-three" to 33? I don't know what
these guys are answering. And I sure
can't reverse-engineer your question out
of their answer.

While your interpretation might also be vaid, Tom and MG are talking
about using an update query to convert data types. Their response
couldn't be any simpler.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me