CODEWITHSUNDEEP
sqlexcelvba
HugoLny
HugoLny

Reputation: 11

Import .txt file to excel with VBA/SQL

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?

Upvotes: 0

Views: 440

Answers (1)

ChrisFerreira
ChrisFerreira

Reputation: 172

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

Upvotes: 1

Related Questions