Jun 25 2021 03:00 AM
Jun 25 2021 03:00 AM
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)?
Jun 25 2021 03:25 AM - edited Jun 25 2021 03:25 AMSolution
@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:
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.
Jun 25 2021 03:59 AM
Jun 25 2021 04:07 AM
@ChrizK Okay! Thought you were referring to PQ when you wrote about "Get Data" and "transforming".
The attached link would be a good starting point to learn about PQ.
Jun 25 2021 04:18 AM
@ChrizK ....... provided you're not a Mac. If so, PQ will not (yet) work for you.
Jun 25 2021 05:32 AM
Thank you. As you would expect, your suggestion works perfectly ...the simplest solution is often the best.
Not knowing that Get Data actually is Power Query (on PC).
I am however at a bit of a loss as to how PQ can be used for validation.
Perhaps this is just a basic understanding of what I mean by validation.
As an example, I want to validate 'field one' is the correct date format.
I use =IF(COUNTIF(C2,B2),"PASS","FAIL"), with my expect result being ????-??-??
This results in PASS which I can easily see, especially with conditional formatting.
As PQ appears to be Extraction>Transform>Load, how could I validate the content of fields within a CSV?
Jun 25 2021 10:06 AM
@ChrizK Can't tell unless you disclose what you are validating. The picture you uploaded doesn't mean much to me. My gut feeling was that connecting to a CSV and then do some clever steps in PQ could do much of the validation. But, again, perhaps I was wrong.