What If a CSV File Is Empty? RRS feed

  • Question

  • Good morning, all -

    I have a snippet of code that daily imports data from a .csv file. This imported data overlays the imported data from the previous day and any extra rows - because the current dataset is smaller than the prior - are trimmed. There is also a formula for each row in a cell just outside of the imported data that checks for conditions in the data and sets a flag depending upon what it is. I have a routine that autofills this formula, in case there is more data in the current import, down to cover each new row.

    But, it struck me today that it is possible for the import data file to be empty. My procedure to trim rows should still work, but because of the way I autofill the formula cells, I'm concerned that the imported data for the day after an empty day will not have the formula populate properly because, well, there is no preexisting cell to autofill down.

    So, I'm thinking, if I can test the .csv file for whether or not it's empty, I can load in a separate Excel file designed to handle empty sets, so that the formula cells are maintained.

    Clear as mud?

    What I'm using to import the data is;

    ActiveWorkbook.Queries.Add Name:="Import Data", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""c:\Import Data.csv""),[Delimiter="","", Columns=24, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {" & _
        """Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type date}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type date}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Import Data"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Import Data]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Import Data"
        .Refresh BackgroundQuery:=False
    End With

    Is there a way to test the .csv for being empty before importing it - the best time, I suspect - or after importing?

    As I'm writing this, I'm thinking that a post-import test would be just to check if cell A2 is empty. It will always have content, if there is valid import data. But, that's after having dumped the empty file into my template and I'm thinking that may cause issues I don't want to have.

    Any assistance would be greatly appreciated! Oh, and, if I need to clarify anything, please let me know.


    Hmm. Another thought just struck me as I was about to hit Submit. If I didn't use Autofill to populate the formula into those cells that need it; if I could directly enter the code, via VBA, where it's needed, that might be best and may obviate the need to check for an empty import file. Is that possible? Meanwhile, I'll start searching along those lines after I post this.

    Wednesday, June 26, 2019 3:28 PM


All replies