We help IT Professionals succeed at work.

Import txt data into SQL using VB in Visual Studio

1,868 Views
Last Modified: 2012-05-06
Hi there,

MS Access background and new to Visual Studio. I am having to rewrite an Access DB in Visual Studio and I am having trouble reconstructing one of my import functions.

This code is only ran one time for new projects in my app upon initial setup, so the tables that it populates already exist but contain no data.

In Access this code opens an OpenFile dialog for 2 import files (azpoint.sp1, and action.sp1 - it will take whatever filenames selected with the dialog) these files are actually text files but have an extension of ".sp1", It renames the files to ".txt", imports the text data into 2 tables (PreAz, and PreAction), and then renames the files back to ".sp1", it then runs a series of queries that updates 2 other tables (Region Asc, and Region Des).

The PreAz table has 3 fields that are populated by the import, [SerialNo], [X], and [Y], [SerialNo] being the unique key. The PreAction table has identicle fields.

The first series of queries opens the PreAz table and creates a field called [RegionLine] with an expression that takes the first 4 digits from the [SerialNo] field.  It then groups all of the PreAz records by [RegionLine] and adds the fields [MinOfX] and [MaxOfX] to the query based on the min and max [X] values of the grouped records. It then updates the Region Asc table sorted by [MinOfX] Ascending with the fields, [RegionLine], [MinOfX], [MaxOfX], and [RegionNo]. (RegionNo is an auto-number field that assigns the region no based on the ascending sort, I know auto numbers are always risky and shabby to use like this, but it has worked thus far in the Access db.)

The last query populates the Region Des table with the same fields, only diference is the records are sorted by [MinOfX] descending and [RegionNo] numbered thus.

Sorry for the lengthy novel, I thought as good of a description of the code would help anyone that may be able to help me with this better understand what I am trying to achieve here.

Now for my problems. The text files that I am trying to bring in are not delimited. I had to create a fixed width specification in MS Access to make them come in properly. The import functions that are used are MS Access commands and I have not been able to find good examples of subtitutes to work in Visual Studio. The queries are another whole issue, If I could get the code down for the imports first hopefully I will be able to figure out the rest.

Any help anyone could provide would be GREATLY appreciated!

If you made it this far thanks for taking the time to read all this and thanks in advance if you can give me any help!

Best Regards,

Dan


Private Sub ImportPreData_Click()
 
Dim VBAnsw As String
Dim PreAzPath As String
Dim PreActionPath As String
Dim fs, fs2, Fn, Fn2, FLength, Flength2, Fext, Fext2, FDot, Fdot2, FOrig, Forig2
Set fs = CreateObject("Scripting.FileSystemObject")
Set fs2 = CreateObject("Scripting.FileSystemObject")
VBAnsw = MsgBox("You are about to import all Pre Azimuth and Pre Action Points," & _
            " Do you want to continue?", vbYesNo, "Import Survey Data")
If VBAnsw = vbYes Then
DoCmd.SetWarnings False
PreAzPath = ahtCommonFileOpenSave(OpenFile:=True, DialogTitle:="Please select Pre Azimuth File...", _
Flags:=ahtOFN_HIDEREADONLY)
    Set Fn = fs.getfile("" & [PreAzPath] & "")
     ' This holds the file's original name for rename later.
    FOrig = Fn.Name
    ' Get the length of the file name.
    FLength = Len(Fn.Name)
    ' Set this value = to the last four characters of the file name.
    Fext = Right(Fn.Name, 4)
    ' Set this = to the first character.
    FDot = Left(Fext, 1)
    ' If there is a dot in the fourth from the last position...
    If FDot = "." Then
        '... and the extension is not .txt.
        If Fext <> ".txt" Then
            ' Remove the extension from the file name.
            Fn.Name = Left(Fn.Name, (FLength - 4))
            ' Add the .txt to the file name.
            Fn.Name = Fn.Name & ".txt"
    End If
    Else
        ' If there is not a dot in the fourth position
        ' add the .txt extension.
        Fn.Name = Fn.Name & ".txt"
    End If
PreAzPath = Fn.Name
PreActionPath = ahtCommonFileOpenSave(OpenFile:=True, DialogTitle:="Please select Pre Action File...", _
Flags:=ahtOFN_HIDEREADONLY)
    Set Fn2 = fs2.getfile("" & [PreActionPath] & "")
     ' This holds the file's original name for rename later.
    Forig2 = Fn2.Name
    ' Get the length of the file name.
    Flength2 = Len(Fn2.Name)
    ' Set this value = to the last four characters of the file name.
    Fext2 = Right(Fn2.Name, 4)
    ' Set this = to the first character.
    Fdot2 = Left(Fext2, 1)
    ' If there is a dot in the fourth from the last position...
    If Fdot2 = "." Then
        '... and the extension is not .txt.
        If Fext2 <> ".txt" Then
            ' Remove the extension from the file name.
            Fn2.Name = Left(Fn2.Name, (Flength2 - 4))
            ' Add the .txt to the file name.
            Fn2.Name = Fn2.Name & ".txt"
    End If
    Else
        ' If there is not a dot in the fourth position
        ' add the .txt extension.
        Fn2.Name = Fn2.Name & ".txt"
    End If
PreActionPath = Fn2.Name
Dim dbname As String
    dbname = Left(CurrentProject.Name, (InStr(1, CurrentProject.Name, ".") - 1))
    DoCmd.TransferText acImportFixed, "ImportPreSurvey", "PreAz", "" & [PreAzPath] & "", False, ""
    DoCmd.TransferText acImportFixed, "ImportPreSurvey", "PreAction", "" & [PreActionPath] & "", False, ""
    DoCmd.OpenQuery "Region Calculator Ascending", acViewNormal, acEdit
    DoCmd.OpenQuery "Region Calculator Descending", acViewNormal, acEdit
    DoCmd.OpenQuery "Region Update Ascending", acViewNormal, acEdit
    DoCmd.OpenQuery "Region Update Descending", acViewNormal, acEdit
    ' After the file is transferred, rename it back to
    ' its original name.
    Fn.Name = FOrig
    Fn2.Name = Forig2
MsgBox "Survey data imported successfully!"
DoCmd.SetWarnings True
Exit Sub
Else
MsgBox "You cancelled the import."
DoCmd.SetWarnings True
End If
End Sub

Open in new window

Comment
Watch Question

Commented:
Hi Dan

Indeed a lengthy novel... yet I am still a bit puzzled what you want to do, or better where you want to do it. You obviously got the code working in Access with VBA. You now want to port this code to a different environment? If so, do you stay with an Access .MDB file (backend) or do you switch to SQL server? You mentioned Visual Studio, but not which version? The last few (Version 7+) are using VB.NET.

In straight VB or VB.NET there is no Transfertext function, so instead of massaging the data in a first step and then using this function, you must simply parse all text. VB.NET has got some excellent parsing functions that would help you here and you can use regular expressions as well.

As for the backend DB, you need to look into ADO or ADO.NET (again, depending on your platform).

HTH

Felix

Author

Commented:
Felix,

Thanks for your reply. I am using Visual Studio 2008 now. My first thought was to start from scratch with SQL server, but I think that keeping the existing Access.mdb backend seems to be the best way to go. I did notice that bringing in the mdb kept my select queries as Views and my update / append queries as stored procedures. The only thing that I seem to not have is the code that was attached to the forms.

If I use my existing access.mdb backend for the database file could I write an import function that populates it with the VB.NET parsing tools?

Thanks again for the help, havnt slept much in the last week messing with this.

Dan

Author

Commented:
Felix,

After thought. my access.mdb coding uses ADO to manipulate my recordsets already, so I am familiar with ADO. So far from what I have seen already the commands in the MS Access environment with ADO seem to be a bit diferent in the Visual Studio environment that I have yet to entirely figure out yet.

Dan

Commented:
Hi Dan

Using VS2008 and and a .MDB file is fine. You can use ADO or ADO.NET, there is not a huge difference, but the ADO.NET option is probably more future proof and better supported in the environment. In ADO you have recordsets and in ADO.NET you have datasets. As for the parsing of your text file, have a look at the regex class and the split function, it allows you to read your text file in and have it tokenized in one hit.

There is lots of help and sample code out there to familiarise yourself with the .NET side, even here on EE. It can be daunting when you start, but you will get there!

Good luck

Felix

Author

Commented:
Felix,

Thanks for your time and the direction. I am having trouble finding a good example here on EE and even on the rest of the web. Could you maybe point me towards an example that I could use to update an mdb table in VS2008 using ADO.NET?

Dan
Dirk HaestProject manager
CERTIFIED EXPERT

Commented:
Project manager
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

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.