Import txt data into SQL using VB in Visual Studio

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

Select allOpen in new window