Import ascii and .dat tables into access

Posted on 2006-03-25
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
Question by:JohnEddins
  • 7
  • 6
LVL 14

Expert Comment

ID: 16293018
So try to catch your exception.

Author Comment

ID: 16293925
How would you suggest doing that?
LVL 14

Expert Comment

ID: 16293939
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 16294102
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

ID: 16294136
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

ID: 16294149
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

PockyMaster earned 2000 total points
ID: 16294171
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

ID: 16294230
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

ID: 16294273
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

ID: 16294291
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

ID: 16294317
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

ID: 16294383
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

ID: 16294498
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month13 days, 9 hours left to enroll

750 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