We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Problem opening CSV file thru Oledb and JET

Slimshaneey
Slimshaneey asked
on
Medium Priority
1,325 Views
Last Modified: 2012-06-27
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?


Comment
Watch Question

Author

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

Commented:
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
CERTIFIED EXPERT
Commented:
or replace

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

with

Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + SMDRfile + ";Extended Properties=""text;HDR=No;FMT=Delimited""")



Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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!!

Author

Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.