• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Import file .txt into table SQL Server

Good afternoon,

First of all, thank you for the time and help you can give me with a TXT file load (delimiter "|") a table created in SQL Server.

the problem is that when I load the file it inserts all nulls ..

I appreciate your time.

Best regards,
 

Attachments

  • ImportarTXT_SQL.rar
    25.4 KB · Views: 2
Hi,​
no issue on my side to import the text file so just well answer to the Import Assistant …​
 
hi @vletm

I want to import a .txt file (rar attachment) using a macro to choose the file and insert it to a table in sql server in the attached .rar is the excel and an example txt file.
 
pakilprogramador
Did You answer to my question
... Why did You upload ... .rar-file?
... ... especially, if You don't use ... .rar-file anywhere.
and
if You have understood based my second question,
... eg I do not open any .rar-files
... ... that's why I cannot know, what have You uploaded.
 
I have to import data into existing table in SQLServer from modulo.txt file. The delimiter must be "|" ”. I need through the .xlsm file to load the file modulo.txt and this is loaded to the existing sql server table.
 
What should come out?
... if moduloTxt.txt is entered (or not?)

answer:Nothing should come out, just insert the data from module.txt into sqlserver table.

Your .xlsm file's MODULO-sheet looks empty...
... is that Your mentioned 'all nulls'?

the .xlsm file should load the txt file, but when I try to load it, it inserts nulls into the sql server table.
Code:
Do While Len(Range("A" & r).Formula) > 0
            ' repeat until the first empty cell in column A
            With rs
                .AddNew ' create a new record
                ' add values to each field in the record
             
                For f = 1 To .Fields.Count
                         Cells(r, f + 1).Value = .Fields(f - 1).Name

                Next f
                        .Update ' stores the new record
            End With
            r = r + 1 ' next row
         
        Loop
 
Last edited:
@vletm This my code vba:

Code:
Sub ModuloCargarFotoGestion()
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim fn As Variant, f As Integer
    fn = Application.GetOpenFilename("Text Files (*.txt),*.txt", _
        1, "Seleccione Data", , True)
  
    If TypeName(fn) = "Boolean" Then Exit Sub
     Call Conectar_Sql
    On Error GoTo 0
    Application.ScreenUpdating = False

   For f = LBound(fn) To UBound(fn)
        Debug.Print "Seleccionar Base a Cargar #" & f & ": " & fn(f)
    ImportFromTxtToSQL cnn, CStr(fn(f))
    Next f
     Application.ScreenUpdating = True
    Set cnn = Nothing
       Exit Sub
DisplayErrorMessage:
    MsgBox err.Description, vbExclamation, ThisWorkbook.Name
    Resume Next
End Sub
Sub ImportFromTxtToSQL(con As ADODB.Connection, strFullFileName As String)
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim rngTargetCell As Range
Call Conectar_Sql
     'con.Open
Dim wb As Workbook, r As Long, f As Integer
    If cnn Is Nothing Then Exit Sub
    If cnn.State <> adStateOpen Then Exit Sub
    ' open the source workbook
    On Error GoTo DisplayErrorMessage
    Set wb = Workbooks.Open(strFullFileName, False, False)
    On Error GoTo 0
    If wb Is Nothing Then Exit Sub ' failed to open the workbook
     Set rs = New ADODB.Recordset
    ' open a recordset, all records in a table
    On Error GoTo DisplayErrorMessage
    Rem query for connect database
        rs.Open "SELECT  ID,TIPO,MARCACION,FECHA,PRECIO FROM dbo.tabla", cnn, adOpenKeyset, adLockOptimistic, adCmdText
        '------------------------------------------------------------------
     On Error GoTo 0

            If rs.State = adStateOpen Then ' successfully opened the recordset
            r = 2 ' the first row containing data in the worksheet
          
           Do While Len(Range("A" & r).Formula) > 0
          
            With rs
                .AddNew ' create a new record
                ' add values to each field in the record
                 For f = 1 To .Fields.Count
                              
                                  Cells.Offset(r, f).Value = .Fields(f - 1).Name

                Next f
            
          Rem -------------------------------------------------------------------------
                .Update ' stores the new record
            End With
            r = r + 1 ' next row
          
        Loop
    
        rs.Close
    End If
    Set rs = Nothing
    wb.Close False
    Exit Sub
DisplayErrorMessage:
    MsgBox err.Description, vbExclamation, ThisWorkbook.Name
    Resume Next
End Sub

this is my conexion database SQL SERVER :

Code:
Function Conectar_Sql()
On Error GoTo err
Application.ScreenUpdating = False
Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
cnn.Open "Provider=SQLOLEDB;Data Source=DESKTOP-H580ID\SERVER;Initial Catalog=bbdd;User Id=SA;Password=123;"
cmd.ActiveConnection = cnn
Exit Function
err:
    MsgBox "Nº de error: " & err.Number & " | " & err.Description, vbCritical, "Control de errores"
    err.Clear
End Function
 
Last edited:
pakilprogramador
Did You skip last two questions for some reason?
Has those codes ever work?
Has someone after that did any even minor modifications?

Have You run Your code step-by-step and
verify ... how do it work?
( I cannot run it! )
 
pakilprogramador
I see ...
Your code worked well before and after Your modifications not!
That's Your challenge! ... You have done something which Excel won't understand.
Have You tested Your code step-by-step?
... because first You 'like get Your txt-file'
... next Your code will use it someway row-by-row with ImportFromTxtToSQL,
... but ... seems Your ImportFromTxtToSQL-code uses Your previous version data somewhere.
= It seems not working as You 'think'.
If You have not test it step-by-step then You don't know - what do it do?
 
pakilprogramador
hmm? ... You've done it step-by-step and it seemed look okay, but still not work - something not match!
I did a sample (named Do_It).
You can test to run it ... okay?
You could see each value which are in Your text-file - okay?
After that, You should add those details, which need to use this Your SQL (msg is that output for SQL) ... okay?
... and test again
... and after that You could delete one line (below) from my sample code
MsgBox msg, vbInformation, "line: " & c & " - value: " & x
 

Attachments

  • DemoImportarTxt_SQL.xlsm
    27 KB · Views: 6
Back
Top