Solved

Problem opening CSV file thru Oledb and JET

Posted on 2006-10-24
5
1,141 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?


0
Comment
Question by:Slimshaneey
  • 3
  • 2
5 Comments
 
LVL 11

Author Comment

by:Slimshaneey
ID: 17796380
BTW, The SMDRfile is loaded with the following string : "c:\smdr.csv"
0
 
LVL 35

Expert Comment

by:YZlat
ID: 17796582
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
0
 
LVL 35

Accepted Solution

by:
YZlat earned 500 total points
ID: 17796595
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""")



0
 
LVL 11

Author Comment

by:Slimshaneey
ID: 17796816
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!!
0
 
LVL 11

Author Comment

by:Slimshaneey
ID: 17796903
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.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question