We help IT Professionals succeed at work.

Import ascii and .dat tables into access

JohnEddins asked
Medium Priority
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 vb.net 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
Watch Question

So try to catch your exception.


How would you suggest doing that?
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


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


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


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()
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)

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

Ask the Experts


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?
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?


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

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


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?
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
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.


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.