Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

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

Brass Contributor

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

 

6 Replies
best response confirmed by ChrizK (Brass Contributor)
Solution

@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. 

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 "" :)

@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.

https://exceloffthegrid.com/power-query-introduction/ 

 

Good luck!

 

@ChrizK ....... provided you're not a Mac. If so, PQ will not (yet) work for you.

@Riny_van_Eekelen 

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).
https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a...

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?

 

ChrizK_0-1624624334024.png

 

 

@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.

1 best response

Accepted Solutions
best response confirmed by ChrizK (Brass Contributor)
Solution

@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. 

View solution in original post