asked on

Problem opening CSV file thru Oledb and JET

Hi there. Im trying to open a CSV file to query, using Jet 4.0 and OLEDB in vb.net. This worked yesterday but for some reason has stopped now. Here is the code I am using to connect to the CSV file:

Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & SMDRfile & ";Extended Properties=""Excel 8.0;"" ")

cn.Open()

I get an exception (External Table is no in the expected format) on the open command though with the following info:
System.Data.OleDb.OleDbException was unhandled
ErrorCode=-2147467259
Message="External table is not in the expected format."
Source="Microsoft JET Database Engine"
StackTrace:
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at CallStatUpdater.modCallLogger.GetSMDRReader(String SMDRfile, DateTime FromDate)
at CallStatUpdater.modCallLogger.Main()
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

Anyone got any ideas?

Slimshaneey

ASKER

BTW, The SMDRfile is loaded with the following string : "c:\smdr.csv"

YZlat

use this function instead, to read data from csv file into a datatable.

Call it the following way

yourdatatable=ReadDataFromCSV(SMDRfile)

Public Function ReadDataFromCSV(ByVal path As String, Optional ByVal delim As String = ",") As System.Data.DataTable
Dim fulltext As String
Dim sr As StreamReader
Dim arrColumnNames() As String
Dim arrColumnValues() As String
Dim arrRows() As String
Dim i, j, n As Integer
Dim dt As New System.Data.DataTable
Dim row As DataRow
Try
'' check that the file exists before opening it
If (File.Exists(path)) Then

sr = New StreamReader(path)
fulltext = sr.ReadToEnd
arrRows = Split(fulltext, vbCrLf)
arrColumnNames = Split(arrRows(0), delim)
''add columns to a datatable
For n = 0 To arrColumnNames.Length - 1
dt.Columns.Add(New DataColumn(arrColumnNames(n), System.Type.GetType("System.String")))
Next
For i = 1 To arrRows.Length - 1
arrColumnValues = Split(arrRows(i), delim)
row = dt.NewRow
For j = 0 To (arrColumnNames.Length - 1)
Try
If Not IsDBNull(arrColumnValues(j)) Then
row(arrColumnNames(j)) = arrColumnValues(j)
Else
row(arrColumnNames(j)) = ""
End If
Catch ex As Exception
Console.Write("ERROR: " & ex.Message)
End Try
Next
dt.Rows.Add(row)
Next
End If
Catch ex As Exception
Console.Write("ERROR: " & ex.Message)
Finally
sr.Close()
End Try

Return dt
End Function

ASKER CERTIFIED SOLUTION

YZlat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Slimshaneey

ASKER

I dont really want to go down the custom parser. I would prefer the DB type route as I nned to query the CSV file once Its opened to begin the import process.
Anyway, for your second suggestion, I get another error now:

"'C:\smdr.csv' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides."

However the file does exist in that exact place. How frustrating!!

Slimshaneey

ASKER

I figured it out. You dont include the smdr.csv bit until the query. I cant supply parameters but I can just do an if...then in the loop.