Forum Discussion

ChrizK's avatar
ChrizK
Brass Contributor
Jun 25, 2021

Please help! Data > Get Data (from File) is driving me insane

I am forced to use Get Data to work around the insane situation with Excel reformatting dates (a centuries old problem that has been discussed to death and still no resolution since the dawn of time ...well, since MS buried Lotus 123).

 

My scenario is importing a csv file which has been exported from a big data source.

I am using Excel to validate each field, using a workbook that has been formatted over a considerable period of time.  Examples being, the validation of date format(!!!!!), number of characters, calculations, pattern matching etc

 

As mentioned, to validate the 'true' date format, I have to use Get Data to Import the CSV, then select fields to Transform to Text (highly annoying, but a workable solution).

 

After the file is Loaded into a worksheet, I cut/copy and paste (Transposing my row into a column) the data into my validation worksheet.

 

The problem is, the most simple validation fails .... =IF(ISBLANK(cell),"PASS","FAIL")

 

If I view the properties of the cell, it is of type General.

All I need do, is to select the cell, and press Enter ...effectively doing nothing, but then the validation PASSes.

At this point, the cell type is still General, and I am not aware of any change to the content.

 

The table has many fields.  Depending on the activity,  I could have a couple of hundred blank cells, and so I have to manually copy an (un)modified blank cell into all the FAILed cells ... all because I want to validate the date!!!!!  This is plain ridiculous.

 

Does anyone know why on earth the blank cells fail ISBLANK, and if there is a simple way to resolve the issue (without having to manually select each cell, or column from Get Data/Transform)?

 

Many thanks

 

  • ChrizK ISBLANK() requires the cell to contain absolutely nothing. I suspect that your transformation to Date and then to Text returns something to the cell that just isn't visible. Try this in stead:

     

    =IF(cell="","PASS","FAIL")

     

     

    On the other hand, you say that you are "forced to used Get Data". I would want to suggest to do all your validations with "Get and Transform Data" (a.k.a. Power Query), in stead of working with all sorts of formulae in Excel. Power Query is extremely powerful in formatting, matching and more.  But since I haven't seen your data, I may be mistaken. 

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    ChrizK ISBLANK() requires the cell to contain absolutely nothing. I suspect that your transformation to Date and then to Text returns something to the cell that just isn't visible. Try this in stead:

     

    =IF(cell="","PASS","FAIL")

     

     

    On the other hand, you say that you are "forced to used Get Data". I would want to suggest to do all your validations with "Get and Transform Data" (a.k.a. Power Query), in stead of working with all sorts of formulae in Excel. Power Query is extremely powerful in formatting, matching and more.  But since I haven't seen your data, I may be mistaken. 

    • ChrizK's avatar
      ChrizK
      Brass Contributor
      TBH I have never touched Power Query (at least, not that I know). Dare I say, I don't even know what it is (will need to do some googling)!
      NB I am only transforming the date column, however, if I view the columns in the Transform operation, I can see that each column with no content is identified as Text (not sure why it is General at the point it is loaded).

      Thank you for your quick reply ...I will try "" 🙂

Resources