Results 1 to 17 of 17

Thread: Insert multiples files txt into table sql server

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2021
    Posts
    7

    Resolved Insert multiples files txt into table sql server

    hello everyone I want to modify this code so that it inserts different txt files to sql your help please very grateful for your time.

    Code:
    Public Function ImportTextFile(cnn As Object, _
      ByVal tblName As String, _
      Optional FieldDelimiter As String = "|", _
      Optional RecordDelimiter As String = vbCrLf) As Boolean
    
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim sFileContents As String
    Dim iFileNum As Long
    Dim sTableSplit() As String
    Dim sRecordSplit() As String
    Dim lCtr As Long
    Dim iCtr As Long
    Dim iFieldCtr As Long
    Dim lRecordCount As Long
    Dim iFieldsToImport As Long
    Dim asFieldNames() As String
    Dim abFieldIsString() As Boolean
    Dim iFieldCount As Long
    Dim sSQL As String
    Dim bQuote As Boolean
    Dim fn As Variant
    
    On Error GoTo errHandler
    If Not TypeOf cnn Is ADODB.Connection Then Exit Function
    'If Dir(FileFullPath) = "" Then Exit Function
    
    If cnn.State <> adStateOpen Then Exit Function
        WB_1 = ThisWorkbook.Name
    #If Mac Then
        FileFullPath = MacScript("(choose file) as string")
    #Else
      With Application.FileDialog(msoFileDialogOpen)
            .AllowMultiSelect = True
            If .Show = -1 Then FileFullPath = .SelectedItems(1)
        End With
     
    #End If
        If FileFullPath = Empty Then
            ans = MsgBox("No se Escogio Archivo!", vbCritical, "Error")
            Exit Function
        End If
    
    
    If cnn.State = 0 Then cnn.Open
    Set cmd.ActiveConnection = cnn
    cmd.CommandText = tblName
    cmd.CommandType = adCmdTable
    Set rs = cmd.Execute
    iFieldCount = rs.Fields.Count
    rs.Close
    
    
    
    ReDim asFieldNames(iFieldCount - 1) As String
    ReDim abFieldIsString(iFieldCount - 1) As Boolean
    
    For iCtr = 0 To iFieldCount - 1
        asFieldNames(iCtr) = "[" & rs.Fields(iCtr).Name & "]"
        abFieldIsString(iCtr) = FieldIsString(rs.Fields(iCtr))
    Next
       
    
    iFileNum = FreeFile
    Open FileFullPath For Input As #iFileNum
    sFileContents = Input(LOF(iFileNum), #iFileNum)
    Close #iFileNum
    'split file contents into rows
    sTableSplit = Split(sFileContents, RecordDelimiter)
    lRecordCount = UBound(sTableSplit)
    'make it "all or nothing: whole text
    'file or none of it
    'cnn.BeginTrans
    
    For lCtr = 0 To lRecordCount - 1
            'split record into field values
        
         'sRecordSplit = Split(sTableSplit(lCtr), FieldDelimiter)
        sRecordSplit = Split(sTableSplit(lCtr + 1), FieldDelimiter)
        iFieldsToImport = IIf(UBound(sRecordSplit) + 1 < _
            iFieldCount, UBound(sRecordSplit) + 1, iFieldCount)
     
       'construct sql
        sSQL = "INSERT INTO " & tblName & " ("
        
        For iCtr = 0 To iFieldsToImport - 1
            bQuote = abFieldIsString(iCtr)
            sSQL = sSQL & asFieldNames(iCtr)
            If iCtr < iFieldsToImport - 1 Then sSQL = sSQL & ","
        Next iCtr
        
        sSQL = sSQL & ") VALUES ("
        
        For iCtr = 0 To iFieldsToImport - 1
            If abFieldIsString(iCtr) Then
                 sSQL = sSQL & prepStringForSQL(sRecordSplit(iCtr))
            Else
                sSQL = sSQL & sRecordSplit(iCtr)
            End If
            
            If iCtr < iFieldsToImport - 1 Then sSQL = sSQL & ","
        Next iCtr
        sSQL = sSQL & ")"
        cnn.Execute sSQL
       
    
    Next lCtr
    rs.Close
    Close #iFileNum
    Set rs = Nothing
    Set cmd = Nothing
    ImportTextFile = True
    Exit Function
    errHandler:
    On Error Resume Next
    If iFileNum > 0 Then Close #iFileNum
    If rs.State <> 0 Then rs.Close
    Set rs = Nothing
    Set cmd = Nothing
    End Function

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Insert multiples files txt into table sql server

    Not enough info, what do you mean by different text files? Are you simply talking about files with the same data layout and different filenames or something completely different?
    Apparently there is a global variable called FileFullPath which contains the filename to be imported. You would need to change the value of that to have it use a different file. It also looks like the code is for Excel given the usage of ThisWorkBook.Name.

    You'll need to provide a better description of what you are trying to do.

  3. #3

    Thread Starter
    New Member
    Join Date
    Apr 2021
    Posts
    7

    Re: Insert multiples files txt into table sql server

    Hi, I explained it wrong, i want insert multiple Txt files(*.TXT) with the same structure.

  4. #4
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,126

    Re: Insert multiples files txt into table sql server

    ~~~:-)~~
    Sam I am (as well as Confused at times).

  5. #5
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Insert multiples files txt into table sql server

    From the looks of it you should be able to call that routine multiple times, once for each file changing the FileFullPath each time to the next file.

    That said if your structure is suitable Bulk Insert would be a better option as it is much faster than insert into.

  6. #6

    Thread Starter
    New Member
    Join Date
    Apr 2021
    Posts
    7

    Re: Insert multiples files txt into table sql server

    It is exactly that, my question would be how to adapt that code to my requirement. where could the code vary?

  7. #7
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Insert multiples files txt into table sql server

    Quote Originally Posted by PACOBJX View Post
    It is exactly that, my question would be how to adapt that code to my requirement. where could the code vary?
    What you have shown is a function. Somewhere else in your code there is a variable that is set with a file path and name. When the function is called it processes that file. So like I said you would need to call that function multiple times changing the filename in that var each time. The changes you would need to make are not to the code you have shown but where that function is called from.

  8. #8

    Thread Starter
    New Member
    Join Date
    Apr 2021
    Posts
    7

    Re: Insert multiples files txt into table sql server

    the function is called with this code
    Code:
    ImportTextFile cnn, "nametable"

  9. #9
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Insert multiples files txt into table sql server

    I thought T-SQL had a bulk insert for just this sort of thing.

  10. #10

    Thread Starter
    New Member
    Join Date
    Apr 2021
    Posts
    7

    Re: Insert multiples files txt into table sql server

    T-SQL has it but in this case I would like to be able to do it using vba code inserting multiple files txt with the same structure modifying the code of the vba function

  11. #11
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Insert multiples files txt into table sql server

    Quote Originally Posted by PACOBJX View Post
    the function is called with this code
    Code:
    ImportTextFile cnn, "nametable"
    Like I said your filename is stored in what appears to be a global variable. You need to change the value in that var and call the function again for each filename.

    The bulk insert is the way to go though, simple and fast.

  12. #12

    Thread Starter
    New Member
    Join Date
    Apr 2021
    Posts
    7

    Re: Insert multiples files txt into table sql server

    I tried but couldn't load multiple text files with my code at the same time.

  13. #13
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Insert multiples files txt into table sql server

    What did you try? You should not even try to load multiple files at the same time. You load one and then load another and so on until you have loaded them all.

  14. #14

    Thread Starter
    New Member
    Join Date
    Apr 2021
    Posts
    7

    Re: Insert multiples files txt into table sql server

    correct one after another, but all need to be selected at the same time to load, and I only load the first.

  15. #15
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Insert multiples files txt into table sql server

    You still haven't given us even a hint at how you are trying to do it so all we can do is guess at what you might be doing wrong.
    Did you understand what I said about changing the value of the variable before calling the routine each time?

  16. #16
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,207

    Re: Insert multiples files txt into table sql server

    Quote Originally Posted by PACOBJX View Post
    the function is called with this code
    Code:
    ImportTextFile cnn, "nametable"
    Currently you have a function-signature with a param-list like this:
    Code:
    Function ImportTextFile(cnn As Object, ByVal tblName As String, _
      Optional FieldDelimiter As String = "|", Optional RecordDelimiter As String = vbCrLf) As Boolean
    And the File(Name) this function imports, is determined *inside* that function
    (by showing a File-Dialog-Popup).

    To make it more universal, you should try to isolate "the GUI-stuff" from your importer-function,
    and give it a proper First-Parameter, which better confirms with the name you gave that function:
    Code:
    Function ImportTextFile(FileFullPath As String, cnn As Object, ByVal tblName As String, _
      Optional FieldDelimiter As String = "|", Optional RecordDelimiter As String = vbCrLf) As Boolean
    The above change should be accompanied, by moving the FileDialog-Popup-stuff outside this routine.

    E.g. in a Button_Click or whatever, you could then:
    - show the FileDialogue (in MultiSelect-Mode)
    - when it returns, enumerate the Dialogue-FileSelection-List
    - and from within that enumeration-loop, call your new "Param-extended function"

    Olaf

  17. #17
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Insert multiples files txt into table sql server

    I missed that bit that opens the file dialog.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width