Problem with CSV Date column

  • Thread starter Thread starter Bill Nguyen
  • Start date Start date
B

Bill Nguyen

I'm reading a CSV file with the date colum formatted as "YYMMDD" -> "070310"
when viewed in notepad or similar trext editor.
However, in my app, using ODBCReader, the column value becomes "70310"
(integer value). This invalidates my attempt to convert the field into a
date field. I notice that this happened to other columns as well.
Is there a way to keep the original format (or to convert into date field
for this column?

Thanks

Bill
 
Greetings,

Try reading your data this way:

Imports System.Data.OleDb
Imports System.Data

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim da As OleDbDataAdapter, ds As DataSet, conn As OleDbConnection
conn = New OleDbConnection
conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data
source=C:\bin\test1.xls;Extended Properties=Excel 8.0"
da = New OleDbDataAdapter
ds = New DataSet
da.SelectCommand = New OleDbCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandText = "Select * From [SubscribersList$]"
da.Fill(ds, "tbl1")
dgrv1.DataSource = ds.Tables("tbl1")
End Sub

My test Excel file included a date field, and the dates displayed correctly
in the datagridview.

hth
Rich
 
Rich;
Thanks but this is propbably not the solution.
I need the data to be in text format (with leading zero) so that I can
convert it into a date field.

Thanks again

Bill
Rich said:
Greetings,

Try reading your data this way:

Imports System.Data.OleDb
Imports System.Data

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim da As OleDbDataAdapter, ds As DataSet, conn As OleDbConnection
conn = New OleDbConnection
conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data
source=C:\bin\test1.xls;Extended Properties=Excel 8.0"
da = New OleDbDataAdapter
ds = New DataSet
da.SelectCommand = New OleDbCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandText = "Select * From [SubscribersList$]"
da.Fill(ds, "tbl1")
dgrv1.DataSource = ds.Tables("tbl1")
End Sub

My test Excel file included a date field, and the dates displayed
correctly
in the datagridview.

hth
Rich



Bill Nguyen said:
I'm reading a CSV file with the date colum formatted as "YYMMDD" ->
"070310"
when viewed in notepad or similar trext editor.
However, in my app, using ODBCReader, the column value becomes "70310"
(integer value). This invalidates my attempt to convert the field into a
date field. I notice that this happened to other columns as well.
Is there a way to keep the original format (or to convert into date field
for this column?

Thanks

Bill
Click to expand...
Click to expand...
 
Bill, if you could post a short example of your problem code , along with
only line of data from text file. I will try to help

VJ

Bill Nguyen said:
Rich;
Thanks but this is propbably not the solution.
I need the data to be in text format (with leading zero) so that I can
convert it into a date field.

Thanks again

Bill
Rich said:
Greetings,

Try reading your data this way:

Imports System.Data.OleDb
Imports System.Data

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim da As OleDbDataAdapter, ds As DataSet, conn As OleDbConnection
conn = New OleDbConnection
conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data
source=C:\bin\test1.xls;Extended Properties=Excel 8.0"
da = New OleDbDataAdapter
ds = New DataSet
da.SelectCommand = New OleDbCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandText = "Select * From [SubscribersList$]"
da.Fill(ds, "tbl1")
dgrv1.DataSource = ds.Tables("tbl1")
End Sub

My test Excel file included a date field, and the dates displayed
correctly
in the datagridview.

hth
Rich



Bill Nguyen said:
I'm reading a CSV file with the date colum formatted as "YYMMDD" ->
"070310"
when viewed in notepad or similar trext editor.
However, in my app, using ODBCReader, the column value becomes "70310"
(integer value). This invalidates my attempt to convert the field into a
date field. I notice that this happened to other columns as well.
Is there a way to keep the original format (or to convert into date
field
for this column?

Thanks

Bill
Click to expand...
Click to expand...
Click to expand...
 
Ok CDate is the culpirt I think. Please use DateTime.String(....) function
to format, so you can specific to include any format you want.

HTH
VJ

Bill Nguyen said:
VJ;
leading zero in Item(15) was removed during the reading process.

Please review the attached CSV file

Thanks

Bill
------------


Dim csvConnection As OdbcConnection
Dim ConnectionString As String = "Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq=" & _

mcsvPath & ";"

Try

' Create connection to folder containing CSV file

csvConnection = New OdbcConnection(ConnectionString)

csvConnection.Open()

Dim csvCommand As OdbcCommand

Dim csvReader As OdbcDataReader

Try

' Select all rows from the

csvCommand = csvConnection.CreateCommand()

csvCommand.CommandText = "select * from " & mcsvDoc

csvCommand.CommandType = CommandType.Text

' Use a reader to iterate through the rows

csvReader = csvCommand.ExecuteReader()

If csvReader.Read() Then

Do



If IsDBNull(csvReader.Item(15)) = False Then

mShipDate = csvReader.Item(15)

' mShipdate2 = CDate(csvReader.Item(15))

Else

mShipDate = "NA"

End If

If IsDBNull(csvReader.Item(16)) = False Then

mShipTime = csvReader.Item(16)

Else

mShipTime = "NA"

End If


VJ said:
Bill, if you could post a short example of your problem code , along with
only line of data from text file. I will try to help

VJ

Bill Nguyen said:
Rich;
Thanks but this is propbably not the solution.
I need the data to be in text format (with leading zero) so that I can
convert it into a date field.

Thanks again

Bill
Greetings,

Try reading your data this way:

Imports System.Data.OleDb
Imports System.Data

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim da As OleDbDataAdapter, ds As DataSet, conn As
OleDbConnection
conn = New OleDbConnection
conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data
source=C:\bin\test1.xls;Extended Properties=Excel 8.0"
da = New OleDbDataAdapter
ds = New DataSet
da.SelectCommand = New OleDbCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandText = "Select * From
[SubscribersList$]"
da.Fill(ds, "tbl1")
dgrv1.DataSource = ds.Tables("tbl1")
End Sub

My test Excel file included a date field, and the dates displayed
correctly
in the datagridview.

hth
Rich



:

I'm reading a CSV file with the date colum formatted as "YYMMDD" ->
"070310"
when viewed in notepad or similar trext editor.
However, in my app, using ODBCReader, the column value becomes "70310"
(integer value). This invalidates my attempt to convert the field into
a
date field. I notice that this happened to other columns as well.
Is there a way to keep the original format (or to convert into date
field
for this column?

Thanks

Bill
Click to expand...
Click to expand...
Click to expand...
 
Bill.

I really don't see why Rich solution would not fit.
Dit you try it?

Cor

Bill Nguyen said:
Rich;
Thanks but this is propbably not the solution.
I need the data to be in text format (with leading zero) so that I can
convert it into a date field.

Thanks again

Bill
Rich said:
Greetings,

Try reading your data this way:

Imports System.Data.OleDb
Imports System.Data

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim da As OleDbDataAdapter, ds As DataSet, conn As OleDbConnection
conn = New OleDbConnection
conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data
source=C:\bin\test1.xls;Extended Properties=Excel 8.0"
da = New OleDbDataAdapter
ds = New DataSet
da.SelectCommand = New OleDbCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandText = "Select * From [SubscribersList$]"
da.Fill(ds, "tbl1")
dgrv1.DataSource = ds.Tables("tbl1")
End Sub

My test Excel file included a date field, and the dates displayed
correctly
in the datagridview.

hth
Rich



Bill Nguyen said:
I'm reading a CSV file with the date colum formatted as "YYMMDD" ->
"070310"
when viewed in notepad or similar trext editor.
However, in my app, using ODBCReader, the column value becomes "70310"
(integer value). This invalidates my attempt to convert the field into a
date field. I notice that this happened to other columns as well.
Is there a way to keep the original format (or to convert into date
field
for this column?

Thanks

Bill
Click to expand...
Click to expand...
Click to expand...
 
VJ;
Please forgive my ignorance.
IF the output is "70312" how can I use DateTime function to convert it into
a date of 03/12/2007 ? What's the syntax?

Thanks
Bill
VJ said:
Ok CDate is the culpirt I think. Please use DateTime.String(....) function
to format, so you can specific to include any format you want.

HTH
VJ

Bill Nguyen said:
VJ;
leading zero in Item(15) was removed during the reading process.

Please review the attached CSV file

Thanks

Bill
------------


Dim csvConnection As OdbcConnection
Dim ConnectionString As String = "Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq=" & _

mcsvPath & ";"

Try

' Create connection to folder containing CSV file

csvConnection = New OdbcConnection(ConnectionString)

csvConnection.Open()

Dim csvCommand As OdbcCommand

Dim csvReader As OdbcDataReader

Try

' Select all rows from the

csvCommand = csvConnection.CreateCommand()

csvCommand.CommandText = "select * from " & mcsvDoc

csvCommand.CommandType = CommandType.Text

' Use a reader to iterate through the rows

csvReader = csvCommand.ExecuteReader()

If csvReader.Read() Then

Do



If IsDBNull(csvReader.Item(15)) = False Then

mShipDate = csvReader.Item(15)

' mShipdate2 = CDate(csvReader.Item(15))

Else

mShipDate = "NA"

End If

If IsDBNull(csvReader.Item(16)) = False Then

mShipTime = csvReader.Item(16)

Else

mShipTime = "NA"

End If


VJ said:
Bill, if you could post a short example of your problem code , along
with
only line of data from text file. I will try to help

VJ

Rich;
Thanks but this is propbably not the solution.
I need the data to be in text format (with leading zero) so that I can
convert it into a date field.

Thanks again

Bill
Greetings,

Try reading your data this way:

Imports System.Data.OleDb
Imports System.Data

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim da As OleDbDataAdapter, ds As DataSet, conn As
OleDbConnection
conn = New OleDbConnection
conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data
source=C:\bin\test1.xls;Extended Properties=Excel 8.0"
da = New OleDbDataAdapter
ds = New DataSet
da.SelectCommand = New OleDbCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandText = "Select * From
[SubscribersList$]"
da.Fill(ds, "tbl1")
dgrv1.DataSource = ds.Tables("tbl1")
End Sub

My test Excel file included a date field, and the dates displayed
correctly
in the datagridview.

hth
Rich



:

I'm reading a CSV file with the date colum formatted as "YYMMDD" ->
"070310"
when viewed in notepad or similar trext editor.
However, in my app, using ODBCReader, the column value becomes
"70310"
(integer value). This invalidates my attempt to convert the field
into a
date field. I notice that this happened to other columns as well.
Is there a way to keep the original format (or to convert into date
field
for this column?

Thanks

Bill
Click to expand...
Click to expand...
Click to expand...
 
Back
Top