JackDanIce
Well-known Member
- Joined
- Feb 3, 2010
- Messages
- 9,922
- Office Version
- 365
- Platform
- Windows
Hi,
I've found the following pieces of code to read data from a CSV file into Excel (PowerQuery is far too slow) but both are returning the correct number of rows but row 1 repeated instead of the actual data.
It suggests I need to loop, but from these two links I can't see what's missing
Version1
Version2
Both return the same results of the header row repeating for all data rows and the data file definitely has distinct table data to header row.
Any suggestions?
TIA,
Jack
I've found the following pieces of code to read data from a CSV file into Excel (PowerQuery is far too slow) but both are returning the correct number of rows but row 1 repeated instead of the actual data.
It suggests I need to loop, but from these two links I can't see what's missing
Load CSV File faster
I load around 75 CSV files containing one line. For dataRow = 8 To Worksheets("Liste").Range("B65535").End(xlUp).Row If Dir(FilePath & Worksheets("List").Cells(dataRow, 2) & ".csv...
stackoverflow.com
Fastest way to read large CSV files as text.
So, I already have something set up that loops through all csv files in a given folder and applies some code to them. The bit i'd like to try and speed up is the import as text that im using. Below is the code im using to import the CSV file as text (must be text as one file is a 20 digit number...
www.mrexcel.com
Version1
VBA Code:
Private Sub Read_Data1()
Dim LineItems As Variant
Dim LineFromFile As String
Dim s As Double: s = Timer
Open wSettings.Range("Data_Path").Value For Input As #1
Line Input #1, LineFromFile
LineFromFile = Replace(LineFromFile, Chr(34), vbNullString)
LineItems = Split(LineFromFile, ";")
wData.Cells(1, 1).Resize(UBound(LineItems) + 1).Value = LineItems
Debug.Print "Run Time 1 : " & Round(Timer - s, 2)
End Sub
VBA Code:
Private Sub Read_Data2()
Dim data As Variant
Dim s As Double: s = Timer
data = Split(CreateObject("Scripting.FileSystemObject").getFile(wSettings.Range("Data_Path").Value).openastextstream.readall, vbCrLf)
With wData.Cells(1, 1).Resize(UBound(data) + 1)
.Value = data
.TextToColumns , xlDelimited, xlTextQualifierNone, False, False, False, True, False, False
End With
Debug.Print "Run Time 2 : " & Round(Timer - s, 2)
End Sub
Any suggestions?
TIA,
Jack