Import Tab And Space Delimited Multiple Text Files to excel

[Pradeepmm]

Hi to Evryone,
Appreciate Help from expert Plz!
I found This import text file code on Google but it import only Single text file at a time and
Code Is-

Sub ImportTextFile()
Dim vFileName
On Error GoTo ErrorHandle
vFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
If vFileName = False Or Right(vFileName, 3) <> "txt" Then
GoTo BeforeExit
End If
Application.ScreenUpdating = False

Workbooks.OpenText Filename:=vFileName, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, _
Other:=False, TrailingMinusNumbers:=True, _
local:=True
Columns("A:A").EntireColumn.AutoFit
BeforeExit:
Application.ScreenUpdating = True
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

My Text file Data Format is Like-

LEADMINI16MAYFUT 04-05-2016 10:00:00 116.8500 116.9000 116.8000 116.8500 40
LEADMINI16MAYFUT 04-05-2016 10:01:00 116.9000 116.9000 116.7500 116.8000 63
LEADMINI16MAYFUT 04-05-2016 10:02:00 116.7500 116.7500 116.6500 116.7000 61

This code work well and separate each field in separate column but import only one text file at a time!
And it produce following format in Excel-

LEADMINI16MAYFUT	04-05-2016	10:00:00	116.8500	116.9000	116.8000	116.8500	40
LEADMINI16MAYFUT	04-05-2016	10:01:00	116.9000	116.9000	116.7500	116.8000	63
LEADMINI16MAYFUT	04-05-2016	10:02:00	116.7500	116.7500	116.6500	116.7000	61

<tbody>
</tbody>

I found another Code on Google Which import multiple text files-
Code is-

Sub CombineTextFiles()
Dim FilesToOpen
Dim x As Integer
Dim wkbAll As Workbook
Dim wkbTemp As Workbook

On Error GoTo ErrHandler
Application.ScreenUpdating = False

FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt), *.txt", _
MultiSelect:=True, Title:="Text Files to Open")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If

x = 1
Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
wkbTemp.Sheets(1).Copy
Set wkbAll = ActiveWorkbook
wkbTemp.Close (False)
wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, _
Other:=False
x = x + 1

While x <= UBound(FilesToOpen)
Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
With wkbAll
wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, _
Other:=False
End With
x = x + 1
Wend

ExitHandler:
Application.ScreenUpdating = True
Set wkbAll = Nothing
Set wkbTemp = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

Above code works to import multiple Text files but it doesn't delimit space, though i used Space:=True!
And it Produce Following Format on Excel-

LEADMINI16MAYFUT	04-05-2016 10:00:00	116.8500	116.9000	116.8000	116.8500	40
LEADMINI16MAYFUT	04-05-2016 10:01:00	116.9000	116.9000	116.7500	116.8000	63
LEADMINI16MAYFUT	04-05-2016 10:02:00	116.7500	116.7500	116.6500	116.7000	61

<tbody>
</tbody>

Space between 04-05-2016 10:00:00 is not Delimited! And i want Date(04-05-2016) and Time(10:00:00) in seprate Column As shown in 1st Table.
Plz help to solve this Problem!

 

[snowy01]

HI
Did you solve this problem? cause I have same.
I need to import multiple txt files of different names in same format like you in seprate column but in one sheet.
My Text file Data Format is Like-
2016-01-08 15:35:55;1;195;2016-01-08 15:38:42;20,14,15,;0;2016-01-08 15:38:42;2016-01-08 15:41:19;2016-01-08 15:42:08

i want Date and Time in seprate Column As shown in 1st Table.

thx for help

 

[Pradeepmm]

HI
Did you solve this problem? cause I have same.
I need to import multiple txt files of different names in same format like you in seprate column but in one sheet.
My Text file Data Format is Like-
2016-01-08 15:35:55;1;195;2016-01-08 15:38:42;20,14,15,;0;2016-01-08 15:38:42;2016-01-08 15:41:19;2016-01-08 15:42:08

i want Date and Time in seprate Column As shown in 1st Table.

thx for help

Try This-

Sub Sample()

Dim myfiles
Dim i As Integer

myfiles = Application.GetOpenFilename(filefilter:="txt Files (*.txt), *.txt", MultiSelect:=True)

If Not IsEmpty(myfiles) Then
For i = LBound(myfiles) To UBound(myfiles)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & myfiles(i), Destination:=Range("A" & Rows.Count).End(xlUp).Offset(1, 0))
.Name = "Sample"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Next i
Else
MsgBox "No File Selected"
End If

End Sub

 

[snowy01]

Try This-

Sub Sample()

Dim myfiles
Dim i As Integer

myfiles = Application.GetOpenFilename(filefilter:="txt Files (*.txt), *.txt", MultiSelect:=True)

If Not IsEmpty(myfiles) Then
For i = LBound(myfiles) To UBound(myfiles)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & myfiles(i), Destination:=Range("A" & Rows.Count).End(xlUp).Offset(1, 0))
.Name = "Sample"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

End Sub

it works great!!!
thanks a lot.
Just one more question: Can you make it for .log files too ?

thx