?
Solved

Problem opening CSV file thru Oledb and JET

Posted on 2006-10-24
5
Medium Priority
?
1,181 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month11 days, 21 hours left to enroll

752 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