Import ascii and .dat tables into access

Posted on 2006-03-25
Last Modified: 2010-04-23
  Before I yank any more hair out tryign to figure this one out I figured I would ask the experts... I'm new to and am trying to write a program that will allow for end users to navigate to 2 files, *.asc and *.dat each with its own openfiledialog and inport the data in these files to tables that are created in an access database...
Any help if much appreciated

Below is some of the code i am Using to get the files into a datagrid, but the .dat fiel is generating an error that oledbexception was unhandled. after I get the data in the datagrid, how do i ge tit into the access table i want...

Code snipit...

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim catl = New ADOX.Catalog
        Dim sDBName As String
        '        Dim sDBPass As String
        Dim sAppPath As String
        Dim dbPath As String
        Dim ADOXtable1 As New ADOX.Table
        Dim ADOXTab2 As New ADOX.Table
        Dim ADOXTab3 As New ADOX.Table
        Dim ADOXindex As New ADOX.Index
        Dim Col As New ADOX.Column
        Dim Col2 As New ADOX.Column
        Dim Col3 As New ADOX.Column

        sAppPath = System.Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData)
        dbPath = "&sAppPath & sDBName"
        sDBName = "//PntCon_V2.mdb"
Private Sub btnASCII_Impt_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnASCII_Impt.Click
        OpenFileDialog1.Title = "Select ASCII File to be converted"

        OpenFileDialog1.Filter = "ASCII File (*.txt)|*.txt"

        Dim sFilePath1 As String
        sFilePath1 = OpenFileDialog1.FileName
        If sFilePath1 = "" Then Exit Sub
        txtAscII.Text = sFilePath1
        'Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFilePath1 & ";Extended Properties=text;"
        'Dim objConn As New OleDbConnection(sConnectionString)
        Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\; Extended Properties=Text;"
        Dim objConn As New OleDbConnection(sConnectionString)
        Dim objCmdSelect As New OleDbCommand("SELECT * FROM " & sFilePath1 & "", objConn)
        Dim objAdapter1 As New OleDbDataAdapter()
        objAdapter1.SelectCommand = objCmdSelect

        Dim objDataset As New DataSet()
        objAdapter1.Fill(objDataset, "Pnts_DT")
        DataGrid1.DataSource = objDataset.Tables(0).DefaultView
    End Sub

    Private Sub btnOutputLoc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOutputLoc.Click
        SaveFileDialog1.Title = "Select ASCII File to be Saved"

        SaveFileDialog1.Filter = "ASCII File (*.asc)|*.asc"

        'Exit if no map document is selected
        Dim sFilePath2 As String
        sFilePath2 = SaveFileDialog1.FileName
        If sFilePath2 = "" Then Exit Sub
    End Sub

    Private Sub btnCLine_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCLine.Click
        'Open a file dialog for selecting map documents
        OpenFileDialog2.Title = "Select C-Line File (Must have file extension .txt)"

        OpenFileDialog2.Filter = ".dat file (*.dat)|*.dat"

        'Exit if no map document is selected
        Dim sFilePath2 As String
        sFilePath2 = OpenFileDialog2.FileName
        If sFilePath2 = "" Then Exit Sub
        txtCline.Text = sFilePath2
        Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=Text;"
        Dim objConn2 As New OleDbConnection(sConnectionString)
        Dim objCmdSelect2 As New OleDbCommand("SELECT * FROM " & sFilePath2 & "", objConn2)
        Dim objAdapter2 As New OleDbDataAdapter()
        objAdapter2.SelectCommand = objCmdSelect2

        Dim objDataset2 As New DataSet()
        objAdapter2.Fill(objDataset2, " Clin")
        DataGrid2.DataSource = objDataset2.Tables(0).DefaultView
    End Sub
Question by:JohnEddins
    LVL 14

    Expert Comment

    So try to catch your exception.

    Author Comment

    How would you suggest doing that?
    LVL 14

    Expert Comment

    you might put a try/catch around your code, and see what's inside your exception.. if you post the exact message here, it gets more clear what's your problem

    Author Comment

    here is a copy of the text in the .dat file (which i have converted to .txt to try and get working)

    the entire contents


    This is generating the OLEDBexception every time and i have no clue why , I am going to read up on the try/catch method now to see if that helps

    Author Comment

    Well, I gave the try/catch shot and the code ran successfully, but the error was just bypassed... any ideas on what I need to do to correct this problem? The program creates a datbase in a locationa along with 3 tables, it is then going to insert the .dat file and the .asc file into the corrosponfding table, which i am then going to run several trig functions against to generate an output file.
       Suggestions or ideas on how what the root of the error may be and what I need to do to fix it? Thanks

    Author Comment

    Here is the excption log:
    System.Data.OleDb.OleDbException was unhandled
      Message="Syntax error in FROM clause."
      Source="Microsoft JET Database Engine"
           at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
           at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
           at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
           at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
           at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
           at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
           at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
           at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
           at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
           at Duke_GPSOffset.Form1.btnCLine_Click(Object sender, EventArgs e) in C:\Projects\Energy\Duke\Trans_buffer\Design_2\Duke_GPSOffset\Duke_GPSOffset\Form1.vb:line 72
           at System.Windows.Forms.Control.OnClick(EventArgs e)
           at System.Windows.Forms.Button.OnClick(EventArgs e)
           at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
           at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ButtonBase.WndProc(Message& m)
           at System.Windows.Forms.Button.WndProc(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
           at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
           at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.Run(ApplicationContext context)
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
           at Duke_GPSOffset.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
           at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
    LVL 14

    Accepted Solution

    Uhm, so your FROM query is wrong.
    What I suspect that's going wrong:
    You should provide the folder of your file in your connectionstring
    and the filename in the FROM query.

    Now you're providing c:\ as the folder
    So it will work if your sFilePath contains e.g. test.txt
    but when I interpret your code, it's containing the filename from the OpenFileDialog, which is a filename as FullPath.

    use this to get the path for your connection string
    Dim sPath as string =System.IO.Path.GetDirectoryName(sFilePath1 )
    use this to get the filename for your query
    dim sFileName as string =        System.IO.Path.GetFileName(sFilePath1)

    Author Comment

    okay, here is what I tried to do...

            Dim sPath As String = System.IO.Path.GetDirectoryName(sFilePath2)
            ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & sPath & " ;" & "Extended Properties=""Text;HDR=No;"""

            Dim TextConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)

            Dim sFileName As String = System.IO.Path.GetFileName(sFilePath2)
            Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " & sFileName & "", TextConnection)
            ' Try
            Dim ds As New DataSet("TextFiles")
            da.Fill(ds, "ta")
            DataGrid2.DataSource = ds.Tables(0).DefaultView

    And I am still reciving the Syntax error in the from clause of the < da.fill(ds, "ta") > Line
    Any thoughts?
    LVL 14

    Expert Comment

    hmm...working like a charm here... I copy/pasted your new code into a project, and tried a random CSV file I had.
    What kind of encoding you are using in your source file?

    Author Comment

    not sure i understand the question as far as encoding (Do you mean txt or ascii format?) Let me thorw on more thing at you real qucik, do you think the imports I have could be presenting a problem?

    Option Explicit On
    Imports System.Data.odbc
    Imports ADOX
    Imports ADODB
    Imports System.Data
    Imports System.Data.OleDb
    Imports System
    Imports System.IO
    Imports System.Collections
    Imports System.Text
    Imports VB = Microsoft.VisualBasic
    Imports System.Math

    LVL 14

    Expert Comment

    Hmm.. where you need all these imports for?
    Are you using ADOX and ADODB and odbc as well?

    Usually you don't need to import system explicitly.
    Your environment puts default references in your solution already.
    Also the Microsoft.VisualBasic I would avoid, since it's mostly used out of compatibility with old stuff, and you no longer need it.
    Try to find your equivalents in the .NET framework

    Author Comment

    PockyMAster, I am eternaly greatfull... you are the man (or woman...) anyhow, thanks for the help, The encoding was the...  you earned your points and thanks again!

    On a side note, do you have any code sniptis floating around for inserting the records from a data grid (like the one above) in an access database table?
    LVL 14

    Expert Comment

    No sorry, I haven't got any snippets.. most is from memory..
    But I recall some people ask the same question last week around here.. So it should be easy to find it :D

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
    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…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    755 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

    25 Experts available now in Live!

    Get 1:1 Help Now