Good day!
In VBA I have to write into a CSV file a large array. To increase speed of writing I want to write a line at once to avoid looping over each field. The solution with looping works. The solution with line at once inserts more or less quotes (") then it is required.
Dim WrdArray() As String
Dim ArrayOut(10) As String 'Required for 10000000 rows
Dim line As String
line = "A|B|C|D|E|F|Z|H|I|K|L|M|N|O|P|Q|R|S|T|V|X"
'Simulate content
For i = 1 To 10
ArrayOut(i) = line
Next
'->SOLUTION 1 line in loop
Filename = "C:" & "\Temp\line_in_loop.csv"
Open Filename For Output As #1
For r = LBound(ArrayOut) To UBound(ArrayOut)
WrdArray() = Split(ArrayOut(r), "|")
For i = LBound(WrdArray) To UBound(WrdArray) 'This loop is time consuming
If i < UBound(WrdArray) Then
Write #1, WrdArray(i),
Else
Write #1, WrdArray(i)
End If
Next
Next
Close #1
'line_in_loop.csv: "A","B","C","D" ...
'That is correct
'<-SOLUTION 1 line in loop
'->SOLUTION 2 line at once
Filename = "C:" & "\Temp\line_at_once.csv"
Open Filename For Output As #1
For i = 1 To UBound(ArrayOut)
line = Replace(ArrayOut(i), "|", Chr(34) & "," & Chr(34))
Write #1, "V1:", line
line = Replace(ArrayOut(i), "|", ",")
Write #1, "V2:", line
Next
Close #1
'line_at_once.csv: "V1:","A"",""B"",""C"",""D"",""E""...
'line_at_once.csv: "V2:","A,B,C,D,E ...
'How to provide write line_at_once.csv in format?: "A","B","C","D" ...
'<-SOLUTION 2 line at once
The code is provided here, CSV_line_at_once.accdb, Module1.
How to write line at once in line_at_once.csv in following CSV format?: "A","B","C","D" ...
Please help.
Thanks.
'->SOLUTION 3 line at once - SOLVED as answered below!
Filename = "C:" & "\Temp\line_at_once.csv"
Open Filename For Output As #1
For i = 1 To UBound(ArrayOut)
line = Replace(ArrayOut(i), "|", Chr(34) & "," & Chr(34))
line = Chr(34) & line & Chr(34)
Print #1, line
Next
Close #1
'<-SOLUTION 3 line at once - SOLVED as answered below!
I found also an additional solution with FSO and looping by lines.
https://technet.microsoft.com/en-us/library/ee198715.aspx
'->SOLUTION 4 Save CSV, line loop with FSO
'Dim WrdArray() As String
Dim ArrayOut(10) As String 'Required for 10000000 rows
Dim line As String
line = "A|B|C|D|E|F|Z|H|I|K|L|M|N|O|P|Q|R|S|T|V|X"
'Simulate content
For i = 1 To UBound(ArrayOut)
ArrayOut(i) = line
Next
'https://technet.microsoft.com/en-us/library/ee198715.aspx
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("C:\Temp\fso_line_loop.csv")
For i = 1 To UBound(ArrayOut)
line = ArrayOut(i)
line = Replace(line, "|", Chr(34) & "," & Chr(34))
line = Chr(34) & line & Chr(34)
ArrayOut(i) = line
objFile.WriteLine (ArrayOut(i))
Next
objFile.Close
'<-SOLUTION 4 Save CSV, line loop with FSO
I am still looking for the fastest way which I think is saving at once the entire array ArrayOut() without looping.
The below code SOLUTION 5 does not work.
Please help.
'->SOLUTION 5 Does not work. Please help.
Print #1, ArrayOut() 'case A
objFile.WriteLine (ArrayOut()) 'case B
