Case statement based on data type
-
Hello. I'm trying to read an Excel file using ADO, but am having problems b/c data in the same column is sometimes text, sometimes numeric, sometimes a date, sometimes null. It seems vb.net tries to type everything in a given column the same.
I'm wondering if I need to do a SELECT CASE statement and test the "type" of each element. Does anyone know of a way to do this.
For example, one column of data might be called "Description". In my code, through ADO I reference this as drv("Description").
It seems to me I should then test the case, and assigned the value to a properly typed variable. ie, if the value of that cell is a DATE, then I need to assign it to a variable typed as a date. Etc.
How would I set up a case statement like this? And am I on the right track?
Thanks much!
Paul -
maybe you can use
x = variable.getType
select case x
case "integer"
case "string"
.
.
.
.
end select -
Dear Paul,
You can just do
dim s as string = drv("Description").ToString
and it will get the vaule as a string and not cause a null value error.
If you don't need to work with the value as the dataType it is then thats it. If you do then I would suggest using regular expressions to test if its a number, date, etc and once you know the type then use a select case and cast it accordingly.
Regards
Brin -
Thanks Brin,
I've tried that, and I too thought it would work. But for some reason when I read an Excel file, it doesn't appear to work. For example, I have an XLS which has (in the column labeled "Description") some strings, some currency, some dates, and some nulls. I've set up a loop to read all Data Row Views and read them as strings (as you've described), but then when I look at the array which gets built, all the XLS rows which contained anything but string values show up as null in the array I've built. I can't figure it out. This is why I wonder if I need to test each single time I do the drv("Description") valuable, and set it to the appropriate type variable. Can you shed any further light on this?
If you're willing, I could email you the XLS and a code snippet.
Thanks again for taking the time.
G'day,
Paul -
Dear Paul,
It makes no sence to me that you can not get the drv value as a string regardless of what characters it contains. To try and locate the problem I would first display the drv values (say just in a listbox ) and if thats OK progress through your other code untill you strike the problem.
Let me know how you go.
Regards
Brin -
When using ADO.NET to read data from an XLS file it will read a certain number of rows and make a best guess as to what the type of the data in each column is, which I would say is the type of the greatest number of fields in that column. You can add something to the Extended Properties section of your connection string to specify how many rows are read to make this guess. I believe that the maximum may be 8 or 16, and I can't remember the name of the property I'm afraid. I believe I found it originally on MSDN so you could probably search for it, and then try Google if you don't see it there.
-
THANK YOU ... this was indeed the problem.
Problem and solution are described at http://support.microsoft.com/kb/194124/EN-US/
Thanks for pointing me to this ... I wasted WEEKS trying to figure out what was wrong.
Post a reply
ADO.NET forum discussion
-
how to transfer data from exel to sql
by wensosolutions (11 replies)
-
Database Independent ADO.NET 2.0
by dunking (4 replies)
-
Filtering and Sorting in ADO.NET
by dimonaus (7 replies)
-
Join Datasets
by konikula (2 replies)
-
Import a Text File To SqlServer
by TimL (2 replies)
Quick links
Recent activity
- arif ahmad replied to How to receive data in web ...
- William Thompson replied to What is the name of the Win...
- Sameera Piyadigamage replied to Point of Sale Developers: H...
- Scott Carline replied to 4 x C# Developers for large...
- Rajendra Dhakal replied to Restore SQL Server text dat...
- cloud rainda replied to How to convert between TS f...
Enter your message below
Sign in or Join us (it's free).