Need to change data type in large access file

[fisher1977]

Need your help!

I have a large spread sheet with about 700000 rows, which is imported from a text file. I need to change data type in some columns from text to number but Access doesn't allow me to do so. It says "not enough memory/space/storage to complete...". I have tried to change the MaxlocksPerFile, and also run a update query to update the data, but it doesn't work. Can you guys help me on that?

Thanks a lot!

 

[gemma-the-husky]

in access try

a) adding another column to the table, as a number type. then copy the datra from the text column to the number column (update query) then change the column names

or b) asusming you have a csv file, write an import spec to import the errant column as a number

 

[fisher1977]

Thanks!

1. I tried the a) but the access doesn't allow me to create a new column with number format. I guess it's over the limit? It's really weird!

2. Yeah, i do import from a cvs file. Can you teach me how to write an import spec to import the errant column as a number? I just know some basic about SQL.

Appreciate it!

 

[boblarson]

Can you teach me how to write an import spec

Perhaps this tutorial on creating an Import Spec will help:
http://www.btabdevelopment.com/main...teanImportSpecification/tabid/98/Default.aspx

 

[fisher1977]

Thanks for your help Bob, but under "data type" drop down mean, why there is no "number" option? I selected long integer and I lost the decimals Have no clue....

 

[boblarson]

For decimals you would want to choose from between:

Single
Double
or Currency

It all depends on the data as to what it is and how many digits you will be having.

 

[boblarson]

and by the way Number is a generic name but the actual data type is

Integer
Long Integer
Single
Double
Currency (also known as Decimal in some areas of Access)

 

[fisher1977]

Thanks! Here is my number looks like:
1. in column B:

3.2345678
1.235678999
3.45678888
2.356
......
12345.6789
2222.23666665

if i want to keep all the decimals to make the accuracy of my analysis, which data type do i need to choose?

2. in column C:
12:12:00 PM
1:57:29 PM
1:57:56 PM
1:58:29 PM
........
1:59:59 PM

I also want to calculate, say, the difference (in second) between 1:57:56pm and 1:57:29pm, what data type should i change to column C? in excel i jsut do [column C]*60*60*24 so that i change the type to number and thus can calculate. How to do the same in Access?

Appreciate your patience!

 

[The_Doc_Man]

Be careful if trying to compare date fields. You'll get screwy results because a data field is a typecast of type DOUBLE with an implied offset of days from something like 31-Dec-1899 as day 0, 1-Jan-1900 as day 1. So the difference between two times that are maybe a few seconds apart will be very small indeed.

What you suggest, multiplying by 86400 (=24*60*60) is one way to get inter seconds.

You can also look up DateDiff and give it the raw times plus specify an answer in seconds.