Search code examples
excelcsvis-empty

Excel 2019: CountA is counting empty cells in data imported from CSV

I recently upgraded from Windows 7 to 10 and from Office 2007 to 2019. Today, I imported a CSV file into Excel:

Data > From Text/CSV > Load

The wizard indicated the "File Origin" as "1200: Unicode" and the delimiter as comma. The file does have comma delimiters with double quotes around text.

There was an immediate visual difference from Excel 2007 -- The imported data had table formatting added to it automatically. It was pretty, but I didn't want it, and I found out how to get rid of it by going to "Table Design", unchecking "Header Row" and "Banded Rows" and clicking "Convert to Range" (upon which "Table Design" disappeared from the main menu), and then deleting row 1, which had been added for the unwanted header row. If someone can say how to import the data without the table formatting being added, I'd like to know that, although there's a more important problem.

As in Excel 2007, a connection was set to the CSV file. I don't need that, so I deleted it, as I always have done in Excel 2007.

Now here's the big problem. The entire rectangular range of cells bounded by the number of rows and columns imported is now treated as if every cell has data in it, including those that are empty. This is the case even for rows in which the CSV file has only one data element (i.e., no commas outside of double quotes), so only column A should have data for those rows. When I say that the empty cells are treated as if they have data, what I mean is:

  • len(<cell>) = 0 (meaning that the cell is empty)
  • CountA(<cell>) = 1 (meaning that Excel thinks it is not empty)
  • CountBlank(<cell>) = 1 (meaning that Excel schizophrenically also thinks it is empty)
  • Ctrl-arrow skips over these cells along with cells that contain data.
  • Sorting A-Z on a column with these cells puts their rows at the top, instead of at the bottom, where they ended up when I did this procedure in Excel 2007.

Edit: Through further testing, I've determined that what is happening is that Excel is incorrectly treating null cells in the import as if they are empty strings.

Is there something I need to do differently when importing the CSV file in Excel 2019 in order to have the empty cells treated correctly?

Solution
    • I can confirm your observations.
    • As suggested by @NewSites and confirmed by me, when a CSV file is imported using the current wizard, the blank cells are given a content of a null string "" (Value = ""), whereas when imported using the legacy wizard, the cell remains, indeed, Empty
    • In addition to what you note, .SpecialCells(xlCellTypeBlanks) executed over the table range will not find any cells.

    3 workarounds

    • Use the "Legacy Wizard" for text importing.
      • This does not demonstrate the problem
      • To enable the legacy wizards, navigate to File > Options > Data and select the legacy wizards you want. They will then show up as an option on the Data > Get & Transform Data > Get Data dropdown.
    • Select the blank cells in the table/range and Del (probably best done using VBA code, if you have a lot of cells to select.
    • In doing the PowerQuery import, when the wizard opens
      • Select Transform Data
      • Replace Values
        • Value to Find: leave this blank, empty
        • Replace with: null (yes typed just like that. It is a "keyword")
        • Then Close and Load the file