Comma delimited file shows date when it is text

  • Thread starter Thread starter Darin
  • Start date Start date
D

Darin

I have a comma-delimited text file that I am reading via a connection
string:

junk = "Provider=Microsoft.Jet.OLEDB.4.0;"
junk &= "Data Source=\inware\data\df;"
junk &= "Extended Properties=""text;HDR=No;FMT=Delimited"""

And ...

strSQL = "SELECT * FROM " & Trim(in_file) & ".TXT"
Dim da As New System.Data.OleDb.OleDbDataAdapter(strSQL, tmpConnection)
Dim ds As New DataSet("Workbook")
Dim dt As DataTable
Dim drRow As DataRow

da.Fill(ds, "Sheet1")
dt = ds.Tables("Sheet1")

recordcnt = 0
For Each drRow In dt.Rows
Next

One column in the text file has 5-500.00, which I would presume would be
brought in as a string, but it isn't. It is being brought in as
12/30/1899. But, I can take the same text file and open it in Excel and
that column is correct, showing the 5-500.00.

Help. I need to read their data and convert it, so I need to read it as
5-500.00 and not a bogus date.

Thanks

Darin
 
¤ I have a comma-delimited text file that I am reading via a connection
¤ string:
¤
¤ junk = "Provider=Microsoft.Jet.OLEDB.4.0;"
¤ junk &= "Data Source=\inware\data\df;"
¤ junk &= "Extended Properties=""text;HDR=No;FMT=Delimited"""
¤
¤ And ...
¤
¤ strSQL = "SELECT * FROM " & Trim(in_file) & ".TXT"
¤ Dim da As New System.Data.OleDb.OleDbDataAdapter(strSQL, tmpConnection)
¤ Dim ds As New DataSet("Workbook")
¤ Dim dt As DataTable
¤ Dim drRow As DataRow
¤
¤ da.Fill(ds, "Sheet1")
¤ dt = ds.Tables("Sheet1")
¤
¤ recordcnt = 0
¤ For Each drRow In dt.Rows
¤ Next
¤
¤ One column in the text file has 5-500.00, which I would presume would be
¤ brought in as a string, but it isn't. It is being brought in as
¤ 12/30/1899. But, I can take the same text file and open it in Excel and
¤ that column is correct, showing the 5-500.00.
¤
¤ Help. I need to read their data and convert it, so I need to read it as
¤ 5-500.00 and not a bogus date.
¤

Since the Text driver is interpreting the column as a date you will probably need to use a
schema.ini file to specify the structure of the file:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top