VBA - Append CSV data to current file

[Flyuphigh]

Thank you all in advance. I have a daily data import (DailyData.csv) drop into my downloads file that needs to append all new data into another csv data file (SourceData.csv) that feeds a power pivot table in another excel file. I managed to utilize the following VBA code (Append_Headers) to create and import the first data set with headers. VBA2 (Append_NoHeaders) appends subsequent data. Unfortunately VBA2 fails with high volume of rows. I use csv file as each data drop is approximately 500K rows. I need the following help:

1. Revise VBA1 if there is an easier way more efficient way
2. VBA code 2 to -append- subsequent imports which are dropped automatically with the same file name and next version number i.e. ReportData(1), ReportData(2) etc.
3. Appended rows must exclude any empty rows or headers.

Ideally, it would be great to have one VBA code in the same file where the data is appended from the daily drop file. Thank you again for any help.

 

[Domenic]

Oh, in that case, it might be that the last line actually contains non-printable characters. Assuming that the last line will always be "blank", we can just amend my first code so that the last line is always removed...

Sub Append_Noheaders()
 
    Dim sourceFile As String
    Dim destFile As String
    Dim fileContents As String
    Dim linesOfText() As String
    Dim linesOfTextNoHeader() As String
    Dim fileContentsNoHeader As String
    Dim fileNum1 As Long
    Dim fileNum2 As Long
    Dim numOfLines As Long
    Dim lineCount As Long
    Dim i As Long
 
    sourceFile = "C:\Users\willa\Downloads\DailyData.csv"
    destFile = "C:\Users\willa\Downloads\SourceData.csv"
 
    fileNum1 = FreeFile()
    Open sourceFile For Input As #fileNum1
        fileContents = Input(LOF(fileNum1), #fileNum1)
    Close #fileNum1
 
    linesOfText() = Split(fileContents, vbCrLf) 'or vbLf
 
    numOfLines = UBound(linesOfText) + 1
 
    If numOfLines > 1 Then
 
        ReDim linesOfTextNoHeader(0 To UBound(linesOfText) - 2)
     
        i = 0
        For lineCount = 1 To UBound(linesOfText) - 1
            linesOfTextNoHeader(i) = linesOfText(lineCount)
            i = i + 1
        Next lineCount
     
        fileContentsNoHeader = VBA.Join(linesOfTextNoHeader, vbCrLf) 'or vbLf
     
        fileNum2 = FreeFile()
        Open destFile For Append As #fileNum2
            Print #fileNum2, fileContentsNoHeader
        Close #fileNum2
     
    End If
 
End Sub

Hope this helps!

 

[Flyuphigh]

Yazaa! This works perfect...Thank you so much for your help!

 

[Domenic]

That's great, I'm glad I could help. And thanks for your feedback.

Cheers!