Hi, I found and modified the below code and corresponding custom function hoping it will work faster than Workbooks.OpenText on large csv file imports (500K rows+ in some cases). It works great but writes all the data to just one cell in the worksheet which obviously won't work for large files.
How do I modify it so it writes it to the sheet more like a proper comma delimited across man rows/columns? I found threads on split function but not sure if that's the way to go or how to incorporate it if it is. Hoping there's a few simple lines I can add to the below script or custom function. If its more complicated than that could anyone at least point me towards a potential solution? Any hep truly appreciated....
Here's the script...
Here's the corresponding function...
How do I modify it so it writes it to the sheet more like a proper comma delimited across man rows/columns? I found threads on split function but not sure if that's the way to go or how to incorporate it if it is. Hoping there's a few simple lines I can add to the below script or custom function. If its more complicated than that could anyone at least point me towards a potential solution? Any hep truly appreciated....
Here's the script...
Code:
Sub TxtImporter2()
Dim f As String, flPath As String
Dim i As Long, j As Long
Dim sPath As String
Dim ws As Worksheet
Application.DisplayAlerts = False
Application.ScreenUpdating = False
flPath = Sheets("START").Cells(9, 1).Value & Application.PathSeparator
i = ThisWorkbook.Worksheets.Count
j = Application.Workbooks.Count
f = Dir(flPath & "*.csv")
Do Until f = ""
Workbooks.Add
Cells(1, 1) = LoadTextFile2(flPath & f)
Workbooks(j + 1).Worksheets(1).Copy After:=ThisWorkbook.Worksheets(i)
ThisWorkbook.Worksheets(i + 1).Name = Left(f, Len(f) - 4)
Workbooks(j + 1).Close SaveChanges:=False
i = i + 1
f = Dir
Loop
Application.DisplayAlerts = True
End SubHere's the corresponding function...
Code:
' \\ Function to return the full content of a text file as a string
Public Function LoadTextFile2(sFile As String) As String
Dim iFile As Integer
Dim lineSplit As Variant
' \\ Use FreeFile to supply a file number that is not already in use
iFile = FreeFile
' \\ ' Open file for input.
Open sFile For Input As #iFile
' \\ Return (Read) the whole content of the file to the function
LoadTextFile2 = Input$(LOF(iFile), iFile)
'Split(Symbols, ".")
Close #iFile
End Function
