Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel to dataset issue.

Posted on 2010-01-03
13
Medium Priority
?
310 Views
Last Modified: 2012-05-08
I am trying to load a csv file into a dataset. When the dataset loads I fill a DGV and the column that holds a time value get needlessly converted. The format is military time so 14:34:12 get converted to 12/30/1899 03:34 which causes lots of errors as you could guess. The csv file is not corrupt and shows the right values, so why is this happening? How do I stop this?

Thanks
0
Comment
Question by:Hawkvalley1
13 Comments
 
LVL 11

Expert Comment

by:nordtorp
ID: 26165901
Does any of these solutions work:

1. Convert the DataSet to a Typed Dataset.
2. Convert the DateTime Column into a string DataType.
3. Parse the string DataType into DateTime after you receive the string.

or
http://support.microsoft.com/default.aspx?scid=kb;en-us;842545
0
 
LVL 10

Expert Comment

by:Fayaz
ID: 26165912
Set the time format for the columns or the cells, if you could post two lines from the CSV file would be helpful.
0
 
LVL 9

Author Comment

by:Hawkvalley1
ID: 26166325
@Fayas, Due to the info in the lines I cannot show them.
@nordtorp, I made a Typed Dataset, filled the dataset with my OLEDB objects, the DGV is bound thru a bindingsource, the bindingsource is bound to the new dataset, and nothing fills the DGV except the column headers. What am I missing?
The cvs file comes from an Excel, I cannot even change the format of the cells in the native file - which is wierd. I was hoping that a cvs file didn't care what the format was, so I could parse the values.
Thanks so far...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 11

Expert Comment

by:nordtorp
ID: 26166403
Could you alternate two lines from the CSV file and post it?

Just to see how you have aligned everything.

Example:
Duck, Donald, 6.9.1934, 12:45
0
 
LVL 9

Author Comment

by:Hawkvalley1
ID: 26166506
The csv file has a header row, then:
100000345, something, 12:23:00, 17:34:01
I then Date.Parse(datarow(3)) - Date.Parse(datarow(2)) then get the diff via a timespan.totalminutes. The above numbers get converted to the 12/30/1899 12:04 blah blah blah.
Thanks...
0
 
LVL 11

Expert Comment

by:nordtorp
ID: 26166656
I think you have to try something like DateParse.Subtract.

Here are some examples:

C# DateTime Subtract Method
http://dotnetperls.com/datetime-subtract
0
 
LVL 9

Author Comment

by:Hawkvalley1
ID: 26166677
The problem is the format. I know there are several ways to skin a cat. I just need the format to stop changing. Why does my Typed dataset not fill?
0
 
LVL 11

Expert Comment

by:nordtorp
ID: 26166746
Do you have the code snippet where you do FillDataset?
0
 
LVL 9

Author Comment

by:Hawkvalley1
ID: 26166797
OK...

Dim ExcelConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Users\Boss\Hog Projects" & ";Extended Properties=Text;")
Dim ExcelCommand As New OleDbCommand("SELECT * FROM myCSVfile.csv", ExcelConnection)
Dim ExcelAdapter As New OleDbDataAdapter(ExcelCommand)
ExcelConnection.Open()
ExcelAdapter.Fill(ExcelDataSet)
ExcelConnection.Close()

Open in new window

0
 
LVL 13

Accepted Solution

by:
Ashok earned 2000 total points
ID: 26168429
Best thing to do is save your csv file as actual .xls file.  
Then follow code at http://www.codeproject.com/KB/database/ReadExcel07.aspx

HTH
Ashok
0
 
LVL 9

Author Comment

by:Hawkvalley1
ID: 26168511
I have tried that, but I get "Could not find installable ISAM", so I am looking into this now.
Thanks...
0
 
LVL 9

Author Comment

by:Hawkvalley1
ID: 26172499
My research is finding that 12/30/1899 is the default value returned when a DateTime only contains the time value. I think a different solution will be required. @ashok111 I have followed the directions for fixing that specific problem and no luck so far, else this would be a poss solution. The registry contain the right file name, I have registered the dll and it says it registered successfully, I guess I will try to download the dll again and re-register it or running the repair tool on Office. I am using Excel 2007 so I was using the 12.0 reference and used it in the ExtendedProperties <- this being where the error occurs. If I use Text as the ExtendedProperties value it just does the convertion on the DateTime. Oh what fun...
0
 
LVL 9

Author Closing Comment

by:Hawkvalley1
ID: 31672134
After much trouble yes this does work.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Loops Section Overview

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question