Import data from text file to sql server via field by field

John, ste5fan,,
Found this in my older questions:

Looking to do this..
The issue is reading the text file and import correctly sql server table..
I just want to be able to set a format if i have to on a field...

similar to this code:

Option Compare Database

Private Sub Command0_Click()
Dim path As String, i As Long
path = "C:\Program Files\Crs Enterprise\Vendor Kits\Import iQ exports\"
i = read_files(path)
MsgBox i & " files imported", vbOKOnly
End Sub



Option Compare Database
Option Explicit

Function read_files(pathd As String) As Long
Dim smask As String
Dim objFSO As FileSystemObject
Dim objFolder As Folder
Dim objFile As file
smask = ".txt"
read_files = 0
Set objFSO = New FileSystemObject
Set objFolder = objFSO.GetFolder(pathd)
For Each objFile In objFolder.Files
  If MatchSpec(objFile.Name, smask) Then
        read_f (objFile)
        read_files = read_files + 1
  End If
Next objFile
End Function
Private Function MatchSpec(FileName As String, FileSpec As String) As Boolean
MatchSpec = False
If Right(FileName, Len(FileSpec)) = FileSpec Then MatchSpec = True
End Function
Sub read_f(filen As String)
Dim Str As String, FileNum As Integer
Dim Arr() As String
Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("ImportedTextFiles")
FileNum = FreeFile
Open filen For Input As #FileNum
Do While Not EOF(FileNum)
    Line Input #FileNum, Str
    If Len(Str) > 0 Then
        Arr = Split(Str, "|")
        rst.AddNew
        rst![fldMfgname] = Arr(0)
        rst![fldDescription] = Arr(1)
        rst![fldkitNumber] = Arr(2)
        rst![fldLocation1] = Arr(3)
        rst![fldLocation2] = Arr(4)
        rst![fldMfnum] = Arr(5)
        rst![fldAwwg] = Arr(6)
        rst.Update
    End If
Loop
rst.Close
Close #FileNum
Set rst = Nothing
Set dbs = Nothing
End Sub

Select allOpen in new window

This may work...
Going to try and revise this code.

fordraiders

pat, Can't Link the file...will always be a different place on each persons pc..I have a dialog search box already created.

Yes you can link he file.  That isn't a problem.  You just give the user the file dialog so the user can navigate to the file.  Do NOT use the newfangled link.  Do it the old way where you can actually change the source of the file.


Use the file dialog box to locate the file.  Then in your code, use TransferText to link to the file.  As long as you give it the same "table" name each time you link, all your queries will continue to function correctly.

I wouldn't go with the text linking...just code to select the file and perform what is needed...probably your application is already too cluttered to add one more linked object

I  ended up using my previous code and it worked fine.

With xlw
     
      Set xls = xlw.Worksheets("Template")
  
        With xls
            .Columns.AutoFit
         
            lastColumn = 19
            
            While Len(.Cells(lastRow, 11)) > 0
                lastRow = lastRow + 1
            Wend
            lastRow = lastRow - 1
            For i = 5 To lastRow
                
                For j = 11 To lastColumn
                    CellDataOrig = CellDataOrig + Trim(.Cells(i, j).Value) + "|"
                Next
                 CellData = ID_RESPONSE + "|" + CellDataOrig + Trim(.Cells(i, j).Value) + "|" + "1" + "|"
                
                ' remove any characters  % $
                CellData = Replace(CellData, "%", "")
                CellData = Replace(CellData, "$", "")
                CellData = Replace(CellData, "#", "")
                
                oFile.WriteLine (CellData)
                CellData = vbNullString
                CellDataOrig = vbNullString
            Next

        End With
    End With
    oFile.Close
    Set fso = Nothing
    Set oFile = Nothing
    
    
xlw.Close SaveChanges:=False
xlx.Quit

Select allOpen in new window

i did not mean to close it

Thanks all