Get Data Type

  • Thread starter Thread starter IanOxon via AccessMonster.com
  • Start date Start date
I

IanOxon via AccessMonster.com

Hi All,

I'm looking for a way to evaluate a string and return a possible data type in
..NET, Classic VB, or VBScript (I'm not fussed which). For example:

String Passed ADO DataTypeEnum DAO DataTypeEnum

"12/12/2004" adDate dbDate
"True" adBoolean dbBoolean
"G12" adVarChar dbText

Are there are existing functions/methods that handle this?
 
IanOxon via AccessMonster.com said:
Hi All,

I'm looking for a way to evaluate a string and return a possible data type
in
.NET, Classic VB, or VBScript (I'm not fussed which). For example:

String Passed ADO DataTypeEnum DAO DataTypeEnum

"12/12/2004" adDate dbDate
"True" adBoolean dbBoolean
"G12" adVarChar dbText

Are there are existing functions/methods that handle this?
Click to expand...


None of the languages you mention have anything to do with Microsoft Access.
If you are using Access, then the language is written in VBA which ,like VB
and VB.Net, is a strongly-typed language as opposed to VBScript which is
not. You say you are not fussed which, but it matters a great deal since
using VBScript you cannot write:
Dim MyString As String
Since no variable is of any particular type - they are all variants.

But back to Access and VBA. Assuming you really have a string, then it is a
string - not a date nor a number. But, could it be converted to a date or a
number? Well, possibly... and you could use the CDate or Clng functions to
check. These will raise a runtine error if it cannot be done. For example,
writing Clng("egg") will cause an error.
However, just because it might be able to be changed into a long, it doesn't
mean it should be. So if you string had a value of 42.5 you would not say
this was a long integer. But Clng(42.5) does not fail because it converts
it to 42. In other words, it is easy to tell that "egg" cannot be converted
to a number, but could "24" be converted to a date. Yes it could, but it
could also be converted to text, long, integer,boolean.

So if you are working with strings, then you might be better to write you
own function that compares the string to a pattern. However, I have no idea
how you would distinguish the text value "True" from the boolean value true.

The short answer is, if you neeed to work with different types, then use
different types together with the variant type then you have available the
built in functions TypeName and VarType.

E.g.
Dim dteDate as Date
dteDate=DateSerial(1965,01,01)
MsgBox TypeName(dteDate)

However, if you only use strings this can't work
Dim strDate as String
strDate="1965-01-01"
MsgBox TypeName(strDate)
 
Hi Brian,

Thanks for your reply. I take your point about VBScript, but what I had in
mind was an uber string/data handling (or similar) class/function in any of
the three languages, (or reference files based on them). If there was, then
adding a reference to the appropriate type library or ocx should expose the
methods and properties of the object.

If I've got to do it the hard way I've been thinking along the lines of:

1. Ignoring empty strings (no information about possible data type)
2. Using IsNumeric(Value) to filter for numeric and text values.
3. Filtering numeric values with IsDate(Value).
4. Using Length(Value) for IsNumeric(Value) = False values to distinguish
bewteen dbText and dbGUID & dbMemo.

And so on... But this will be a long selection process and I'm just trying
to avoid re-invent the wheel!

Cheers


Ian

Brian said:
Hi All,
Click to expand...
[quoted text clipped - 9 lines]
Are there are existing functions/methods that handle this?
Click to expand...

None of the languages you mention have anything to do with Microsoft Access.
If you are using Access, then the language is written in VBA which ,like VB
and VB.Net, is a strongly-typed language as opposed to VBScript which is
not. You say you are not fussed which, but it matters a great deal since
using VBScript you cannot write:
Dim MyString As String
Since no variable is of any particular type - they are all variants.

But back to Access and VBA. Assuming you really have a string, then it is a
string - not a date nor a number. But, could it be converted to a date or a
number? Well, possibly... and you could use the CDate or Clng functions to
check. These will raise a runtine error if it cannot be done. For example,
writing Clng("egg") will cause an error.
However, just because it might be able to be changed into a long, it doesn't
mean it should be. So if you string had a value of 42.5 you would not say
this was a long integer. But Clng(42.5) does not fail because it converts
it to 42. In other words, it is easy to tell that "egg" cannot be converted
to a number, but could "24" be converted to a date. Yes it could, but it
could also be converted to text, long, integer,boolean.

So if you are working with strings, then you might be better to write you
own function that compares the string to a pattern. However, I have no idea
how you would distinguish the text value "True" from the boolean value true.

The short answer is, if you neeed to work with different types, then use
different types together with the variant type then you have available the
built in functions TypeName and VarType.

E.g.
Dim dteDate as Date
dteDate=DateSerial(1965,01,01)
MsgBox TypeName(dteDate)

However, if you only use strings this can't work
Dim strDate as String
strDate="1965-01-01"
MsgBox TypeName(strDate)
Click to expand...
 
You seem to want to determine the probable data type of the content of
a known string. So, if the string was "123", you'd say the datatype was
Numeric (because 123 is a number). If the string content was "true",
you'd say the datatype was Boolean, and so on.

Is that correct? If so, why do you want to do this? What are you really
trying to achieve?

TC (MVP Access)
http://tc2.atspace.com
 
IanOxon via AccessMonster.com said:
Hi Brian,

Thanks for your reply. I take your point about VBScript, but what I had
in
mind was an uber string/data handling (or similar) class/function in any
of
the three languages, (or reference files based on them). If there was,
then
adding a reference to the appropriate type library or ocx should expose
the
methods and properties of the object.

If I've got to do it the hard way I've been thinking along the lines of:

1. Ignoring empty strings (no information about possible data type)
2. Using IsNumeric(Value) to filter for numeric and text values.
3. Filtering numeric values with IsDate(Value).
4. Using Length(Value) for IsNumeric(Value) = False values to distinguish
bewteen dbText and dbGUID & dbMemo.

And so on... But this will be a long selection process and I'm just
trying
to avoid re-invent the wheel!

Cheers


Ian
Click to expand...



Hi Ian
I hope I made the following clear:
The easiest and most technically correct way to do what you wish to do is to
use variables of different types.

If you were asked to design a database for contacts which had a uniqueID, a
first name, a last name, a date of birth and a height in metres then it
would be pretty unusual to have each field in the table as text and then try
and work out what sort of text it is. Normally, the ID would be a long
integer, the names would be text, the dob a date and the height perhaps a
single.
When I read the values from the table, I read them into appropriate
variables:

e.g.
strFirstName=MyRecordset.Fields("FirstName")
lngContactID=MyRecordset.Fields("ContactID")

If I then needed to find out what datatype these variables were I could use:
?TypeName(strFirstName) which returns "String"
?TypeName(lngContactID) which returns "Long"

Alternatively
?VarType(strFirstName) which returns the constant vbString=8
?VarType(lngContactID) which returns the constant vbLong=3


THIS REALLY IS THE WAY TO GO. However, imagine you have no choice and for
some reason you are passed a series of strings and have to work out what
datatype they are, then what could you do? Well I would not use any of the
built-in functions because they accept too wide a range of values. If I
wanted to know if a string represented a positive long integer then I would
want to know that each character in the string was 0-9 so I might ask
If strValue Like String$(Len(strValue), "#")

or for a date in international format, I could look for:
If strValue Like "####-##-## ##:##:##" Then

Finally, as a double check explicitly convert to the datatype to force an
error if it can't be converted.
THE PROBLEM YOU STILL HAVE is how do you know if 562 is supposed to be text,
an integer, a long integer - or how can you distinguish between the literal
text value of "True" and the boolean datatype. The truth is you cannot.
 
Hi TC,

I want to examine a delimited text file, parse it, and make a best guess on
the data types of the resultant columns - similiar to the decisions made by
the Link Table wizard when creating a link to a csv file.
This would be useful if you have no control over creating the text file,
and/or the creator changes the format of the text file output.

Brian thanks again for your input, but your assuming you know the column data
type to begin with.


All the Best


Ian

Brian said:
Hi Brian,
Click to expand...
[quoted text clipped - 23 lines]
Ian
Click to expand...

Hi Ian
I hope I made the following clear:
The easiest and most technically correct way to do what you wish to do is to
use variables of different types.

If you were asked to design a database for contacts which had a uniqueID, a
first name, a last name, a date of birth and a height in metres then it
would be pretty unusual to have each field in the table as text and then try
and work out what sort of text it is. Normally, the ID would be a long
integer, the names would be text, the dob a date and the height perhaps a
single.
When I read the values from the table, I read them into appropriate
variables:

e.g.
strFirstName=MyRecordset.Fields("FirstName")
lngContactID=MyRecordset.Fields("ContactID")

If I then needed to find out what datatype these variables were I could use:
?TypeName(strFirstName) which returns "String"
?TypeName(lngContactID) which returns "Long"

Alternatively
?VarType(strFirstName) which returns the constant vbString=8
?VarType(lngContactID) which returns the constant vbLong=3

THIS REALLY IS THE WAY TO GO. However, imagine you have no choice and for
some reason you are passed a series of strings and have to work out what
datatype they are, then what could you do? Well I would not use any of the
built-in functions because they accept too wide a range of values. If I
wanted to know if a string represented a positive long integer then I would
want to know that each character in the string was 0-9 so I might ask
If strValue Like String$(Len(strValue), "#")

or for a date in international format, I could look for:
If strValue Like "####-##-## ##:##:##" Then

Finally, as a double check explicitly convert to the datatype to force an
error if it can't be converted.
THE PROBLEM YOU STILL HAVE is how do you know if 562 is supposed to be text,
an integer, a long integer - or how can you distinguish between the literal
text value of "True" and the boolean datatype. The truth is you cannot.
Click to expand...
 
IanOxon via AccessMonster.com said:
Hi TC,

I want to examine a delimited text file, parse it, and make a best guess
on
the data types of the resultant columns - similiar to the decisions made
by
the Link Table wizard when creating a link to a csv file.
This would be useful if you have no control over creating the text file,
and/or the creator changes the format of the text file output.

Brian thanks again for your input, but your assuming you know the column
data
type to begin with.


All the Best


Ian
Click to expand...

The only thing you can do here is to write your own function. If you have
no idea of what data might be in the file and all you can do is guess, then
things could get complicated. It would help if you had any prior knowledge
of what might be coming - such as there will be text values no longer than
255 characters, or that dates will be in a yyyy-mm-dd format. If you can't
even know that then what would you do with "27/02/1969"? Assume it is a
European style date meaning 27th Feb 1969 or is this an invalid date? Do
you need to check the fields for any form of dates "Mon 27 Feb 1969", etc.

In any case pattern-matching may well be part of the answer so you can ask:
are all characters numeric? is there a decimal point? etc etc. Also I
would import the text file with all columns as text and then once I have
succesfully done this I would start to think about changing the datatypes.
A full import routine with all the checks you might want to build in could
be a few days work.
 
Um, I wasn't assuming anything! I was asking you to clarify what you
are trying to do.

You're trying to guess the data type of each column of data.
Unfortunately, this is not as simple as it seems.

For example, take a column of values, each of which is 0 or -1. Is that
column Numeric - or Boolean? Take the numeric value 38798 - is that
Numeric, or is it a Date? (Try CDate(38798) and see what you get!)

As a first cut, maybe try something like this:

if all the values are 0 or -1
it's probably Boolean
elseif all the characters are digits 0-9
it's probably Numeric, or Dates
elseif there are non-numeric characters other-than date seperators
it's a string
else
it's a formatted date?

Or somesuch!

HTH,
TC (MVP Access)
http://tc2.atspace.com
 
Sorry TC, I didn't think you were assuming anything. I was replying to
Brians post as well - I haven't got the hang of this TreeView layout yet. :-
) Anyway, ta for your further comments. T'will be food for thought on this
long journey! :-0
 
PMFJI,

To the extent that it's not insoluble in the ways already pointed out in
this thread, it sounds like a pattern matching problem - but ISTM the
way to approach might be to start by transposing the columns in the
input file into rows.

This would give a file with one record for each field in the original,
each record consisting of a delimited string containing all the values
in the corresponding field in the original. Each of these strings could
then be tested against a series of regular expressions - each aimed at
_excluding_ a given field type.
 
Back
Top