Problem with CSV Date column

[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

[=?Utf-8?B?UmljaA==?=]

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" wrote:

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

[Bill Nguyen]

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" <Ri**@discussions.microsoft.comwrote in message
news:26**********************************@microsof t.com...

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" wrote:

>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

[VJ]

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" <bi*****************@jaco.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...

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" <Ri**@discussions.microsoft.comwrote in message
news:26**********************************@microsof t.com...

>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" wrote:

>>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

[VJ]

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" <bi*****************@jaco.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...

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" <no***********@yahoo.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...

>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" <bi*****************@jaco.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...

>>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" <Ri**@discussions.microsoft.comwrote in message
news:26**********************************@micros oft.com...
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" wrote:

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
>
>
>

[Cor Ligthert [MVP]]

Bill.

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

Cor

"Bill Nguyen" <bi*****************@jaco.comschreef in bericht
news:%2****************@TK2MSFTNGP04.phx.gbl...

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" <Ri**@discussions.microsoft.comwrote in message
news:26**********************************@microsof t.com...

>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" wrote:

>>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

[Bill Nguyen]

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" <no***********@yahoo.comwrote in message
news:OG****************@TK2MSFTNGP06.phx.gbl...

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" <bi*****************@jaco.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...

>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" <no***********@yahoo.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...

>>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" <bi*****************@jaco.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl.. .
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" <Ri**@discussions.microsoft.comwrote in message
news:26**********************************@micro soft.com...
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" wrote:
>
>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
>>
>>
>>