[jens]

Hi!

Working on a small program that will read a textfile and store the rows as records in a database. At the moment I split the rows (on a given delimiter) and count how many strings I get then create a table with columns of type nvarchar(xx) and store the strings in those columns.

But suppose a row looks like this:
Jens;354298;1.345,0,1,false

I'd like to figure out how these could be stored (default string).
354298 could be an integer or maybe a float
1.345 could be a float
0 and 1 could be integers or booleans
false could be a string or boolean.

Any ideas how to go about this?

Regards
Jens

This post has been edited by jens: 06 April 2010 - 02:44 AM

[PsychoCoder]

For starters stay away from NVARCHAR, it cannot be indexed or searched on. If you use a string type then use VARCHAR.

As far as what data types you should use it guess that depends on what the data will be used for. If you're going to do any mathematical calculations then use INT, if you're going to just display the data then using VARCHAR should be fine.

I guess it really depends on what you need the data to do

[jens]

Mhmmm... Ok, what I want to do is to figure out - without knowing beforehand - how I should store an unknown textfile in a way that is as versatile as possible. In my example above it would be smart (according to me ) to store e.g. "1.345" as a float since that makes calculations possible.

So I'm trying to make a program that will "figure out" the best way to store given data, create columns according to this and store the rows one by one. This of course implies that the rows in the text file have the same kind of strings on every row.

Textfile
--------
Jens;1.23;1;0;blurb
John;2.760;0;0;blarb
Kay;973.221;0;1;flump

Looking at these three rows and knowing that the rest of the rows follow the same pattern I could create a table with columns:

Table
-----
varchar(50)
decimal
boolean
boolean
varchar(50)

Then read all rows from the file, convert the strings to appropriate data types and store them in the DB.

So, all in all, what I want to do is just to store them in a convenient way. And the question is; how can I identify what data types a given string can be converted to?

Regards
Jens

This post has been edited by jens: 05 April 2010 - 04:58 PM

[keakTheGEEK]

MSSQL and MySQL currently don't support boolean datatype. I typically use a bit for this in MSSQL. A bit can be set to either 1,0 or NULL


Edit: In MySQL, Bool/Boolean are supported, but these types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.

This post has been edited by keakTheGEEK: 05 April 2010 - 05:32 PM

[jens]

I am aware of this but point taken. The main problem however is to figure out a way to identyfy a .NET datatype from the interpretation of a string. .NET -> database conversion is pretty simple.

So, how do I programmatically identify what .NET datatypes these strings can be converted to?
"34.76544"
"1"
"234"
"1234567890"

Regards
Jens

Edit: Changed subject heading to better describe the subject as it is now.

This post has been edited by jens: 06 April 2010 - 02:46 AM

[eclipsed4utoo]

I think this will simply be you writing multiple IF statements trying to convert the value to a datatype. if it fails, you try another datatype. you keep doing this until you find a type that doesn't fail.

as far as I know, there is no internal way of doing this.

[Charles:)]

If I were going to do this then for each 'item' I would:

• Check if it contains alpha characters or whether it's strictly numeric. If alpha then store it in the Db as a string, else go to next step.
  
• Check whether it contains a decimal point or not. If so then store it as float or decimal or something of that sort, else go to next step.
  
• Check whether value <= max value of byte. If so store it in db as byte, else go to next step.
  
• Check whether value <= max value of small in. If so store in db as small int, else go to next step.
  
• Check whether value <= max value of int. If so store in db as int, else go to next step.
  
• Etc etc

I won't type the whole thing out but I'm sure you get the idea. I'm not sure if this is the best way to do it, it's just the first thing that came to my mind when I read your problem.

[eclipsed4utoo]

Charles:), on 06 April 2010 - 06:41 AM, said:

If I were going to do this then for each 'item' I would:

• Check if it contains alpha characters or whether it's strictly numeric. If alpha then store it in the Db as a string, else go to next step.
  
• Check whether it contains a decimal point or not. If so then store it as float or decimal or something of that sort, else go to next step.
  
• Check whether value <= max value of byte. If so store it in db as byte, else go to next step.
  
• Check whether value <= max value of small in. If so store in db as small int, else go to next step.
  
• Check whether value <= max value of int. If so store in db as int, else go to next step.
  
• Etc etc

I won't type the whole thing out but I'm sure you get the idea. I'm not sure if this is the best way to do it, it's just the first thing that came to my mind when I read your problem.

You could use the TryParse methods of each of the .Net datatypes(I believe all of them have it) to see if it can be converted to that type.

In my opinion, testing it as an alpha string should be last. If the data is "false", doing the alpha check first would signify this as a string instead of a boolean.

You will also have to do a little more custom work with the numeric values. For example, an integer can be a double, but would you want that integer to be an int in the database or a float/decimal?

This post has been edited by eclipsed4utoo: 06 April 2010 - 05:01 AM

[baavgai]

PsychoCoder, on 05 April 2010 - 05:12 PM, said:

For starters stay away from NVARCHAR, it cannot be indexed or searched on.

I don't believe this is true. While NVARCHAR is twice as large as VARCHAR, and their is an index size restriction (<1000?), I've never had a problem. Perhaps you're thinking of a BINARY or LOB type, for which this is the case?