Import .txt file to excel with VBA/SQL

Question

As highlighted in the title, I would like a macro to import a .txt file rapidly. I've already tried multiple code but everytime you have to open, then copy then close the .txt file and since I have a couple of .txt files it takes somes times:

Sub ImportChange()
ChDir "R:\Desktop\Hugo"
SendKeys "~"
Workbooks.OpenText Filename:="R:\Desktop\Hugo.txt", 
DataType:=xlDelimited, semicolon:=True
Cells.Select
Selection.Copy
Windows("Tableau.xlsm").Activate
Sheets("Change").Select
Cells.Select
ActiveSheet.Paste
Columns("B:B").Select
Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
Columns("A:A").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Range("A1").Select
Windows("Hugo.txt").Activate
Application.CutCopyMode = False
ActiveWorkbook.Close
SendKeys "~"
End Sub

Thus my question is can I import using SQL instead of VBA or can I just import a .txt file without opening then closing it?

You left out the critical bit. Please amend your post to include a description of the structure of your text file, or provide an example. — freeflow
– freeflow, Commented Apr 12, 2022 at 9:55

ChrisFerreira · Accepted Answer · 2022-04-12 10:10:40Z

1

You can just use Bulkinsert to import directly your file to SQL. Once you have it on the database, you only have to load it on Excel once

---- INSERT
        bulk insert [TABLE]
        from 'c:\Filepath.txt'
        with
        (
        firstrow = 2,
        fieldterminator = '\t',
        rowterminator = '\n',
        maxerrors = 0
        )
---- MOVE
        EXEC master..xp_cmdshell 'MOVE filepathfrom.txt filepathTO.txt'

---- RENAME
        DECLARE @Rename VARCHAR(100)
        SET @Rename = 'RENAME "OriginalFilePath.txt" ' + @FilenameRenamed
        EXEC master..xp_cmdshell @Rename

answered Apr 12, 2022 at 10:10

ChrisFerreira

1724 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Import .txt file to excel with VBA/SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related