import errors

[Bill]

I designed an access table(office 2003) and imported from a .csv file.
The import works if the table fields are text. But the first two
columns are numbers and so I would really like to have the the first
two table columns a numeric type. However, if I design the table with
the first two fields as "Number" over 58,000 of the nearly 89,000 rows
hit a type conversion error and do not import. As a result, I cannot
do the sql select statement that I want, but instead have to create a
dataset containing all the rows and then iterate through the dataset
with a for loop, converting the values in the first two columns to
long integers so that I can do a lookup on a specific value. The for
loop is able to convert every one of the strings in the first two
fields to a long, so that establishes all of the data is numeric and
in correct format. Anyone know why the import failed? After importing
as all text, I also tried changing the field type of the first two
fields from text to Number(long int) and that also resulted in type
conversion errors.

The csv file is available at http://ip-to-country.webhosting.info/.
click the downloads link to get it.

 

[KARL DEWEY]

There may be many reasons for the failure - leading space for one.

Easy way is to import as text, add two new number fields, run an update
query converting text into the new number fields.

 

[John W. Vinson]

I designed an access table(office 2003) and imported from a .csv file.
The import works if the table fields are text. But the first two
columns are numbers and so I would really like to have the the first
two table columns a numeric type. However, if I design the table with
the first two fields as "Number" over 58,000 of the nearly 89,000 rows
hit a type conversion error and do not import. As a result, I cannot
do the sql select statement that I want, but instead have to create a
dataset containing all the rows and then iterate through the dataset
with a for loop, converting the values in the first two columns to
long integers so that I can do a lookup on a specific value. The for
loop is able to convert every one of the strings in the first two
fields to a long, so that establishes all of the data is numeric and
in correct format. Anyone know why the import failed? After importing
as all text, I also tried changing the field type of the first two
fields from text to Number(long int) and that also resulted in type
conversion errors.

The csv file is available at http://ip-to-country.webhosting.info/.
click the downloads link to get it.

Long Integers are limited to the range -2^31 to 2^31-1, i.e. -2147483648 to
2147483647. A lot of the records in this csv are outside that range (between
2147483647 and 4294967295) - they're the error records!

I'd suggest using a Decimal or (oddly enough) Currency datatype which does
allow that range.

 

[John W. Vinson]

Long Integers are limited to the range -2^31 to 2^31-1, i.e. -2147483648 to
2147483647. A lot of the records in this csv are outside that range (between
2147483647 and 4294967295) - they're the error records!

I'd suggest using a Decimal or (oddly enough) Currency datatype which does
allow that range.

I was curious enough to import the file into a database table (using Currency
datatypes for the two url fields). You might find this little function helpful
to recast the Currency numbers in the familiar 192.168.254.255 format:

Public Function Quartet(url As Currency) As String
Dim iNext As Currency
Dim strOut As String
Dim iPos As Integer
strOut = ""
For iPos = 1 To 4
iNext = url - 256 * Fix(url / 256)
strOut = Format(iNext, "000") & "." & strOut
url = Fix(url / 256)
Next iPos
Quartet = Left(strOut, 15)
End Function

Maybe not the ideal function but it's free..