Solved

Problem opening CSV file thru Oledb and JET

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

705 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now