-
Apr 16th, 2021, 04:53 PM
#1
Thread Starter
New Member
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
-
Apr 16th, 2021, 05:42 PM
#2
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.
-
Apr 16th, 2021, 06:02 PM
#3
Thread Starter
New Member
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.
-
Apr 16th, 2021, 06:57 PM
#4
Re: Insert multiples files txt into table sql server
Sam I am (as well as Confused at times).
-
Apr 16th, 2021, 08:09 PM
#5
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.
-
Apr 17th, 2021, 12:24 AM
#6
Thread Starter
New Member
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?
-
Apr 17th, 2021, 08:30 AM
#7
Re: Insert multiples files txt into table sql server
Originally Posted by PACOBJX
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.
-
Apr 17th, 2021, 07:32 PM
#8
Thread Starter
New Member
Re: Insert multiples files txt into table sql server
the function is called with this code
Code:
ImportTextFile cnn, "nametable"
-
Apr 17th, 2021, 07:57 PM
#9
Re: Insert multiples files txt into table sql server
I thought T-SQL had a bulk insert for just this sort of thing.
-
Apr 17th, 2021, 08:10 PM
#10
Thread Starter
New Member
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
-
Apr 17th, 2021, 11:11 PM
#11
Re: Insert multiples files txt into table sql server
Originally Posted by PACOBJX
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.
-
Apr 23rd, 2021, 08:39 PM
#12
Thread Starter
New Member
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.
-
Apr 23rd, 2021, 08:51 PM
#13
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.
-
Apr 23rd, 2021, 08:59 PM
#14
Thread Starter
New Member
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.
-
Apr 23rd, 2021, 11:56 PM
#15
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?
-
Apr 24th, 2021, 02:02 AM
#16
Re: Insert multiples files txt into table sql server
Originally Posted by PACOBJX
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
-
Apr 24th, 2021, 07:32 AM
#17
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|