In an excel sheet I have column where there dates in the format as below :
12/26/2022
11/12/2021
5/5/2021
10/10/2020……. etc.
I want to use select query (excel as database) where I want to filter records using the year only. Basically, I want to build a query something like this : select * from fSheet1$$] where ‘Date’ <> 2021.
In the above query what I am trying to do is filter the records where year is 2021 and leave rest of the records.
Need some help on how to achieve this. Thanks in advance :)
Page 1 / 1
Hi @Abhi25 ,
You can try with LIKE command within the SELECT query for this requirement.
For e.g. SELECT * from rsheet1$$] WHERE $Date$ LIKE %2021%
Date is a variable here to which the data must be saved from the Date column of your sheet.
Thanks for the reply, in my previous experiences I have been using the column name directly in queries like the one you have mentioned.
Can i not use the column name in this case also?
Thanks for the reply, in my previous experiences I have been using the column name directly in queries like the one you have mentioned.
Can i not use the column name in this case also?
Yes, you can use. Then, the query will change as below:
SELECT * from sheet1$$] WHERE WDate] LIKE %2021%
Hi @Abhi25 ,
I can see that @Padmakumar has answered your question. If you find it satisfactory then we’d appreciate it if you could mark his answer as the solution as it increase engagement here on the community. . . . Also if you’d like to explore more on database operations using Excel and CSV(omg csv?!) then I’ve written an article that you may find useful.
Remember, the excel database operations follow MS Access conventions. I usually go here anytime I get stuck.
Kind Regards, Ashwin A.K
Thanks for the reply, in my previous experiences I have been using the column name directly in queries like the one you have mentioned.
Can i not use the column name in this case also?
Yes, you can use. Then, the query will change as below:
SELECT * from sheet1$$] WHERE WDate] LIKE %2021%
I tried using the above query & it ran for sometime and ended without giving any errors but the result was inconsistent. The no of rows obtained from the query were far less than the actual number of rows that should have been there
Hi @Abhi25 ,
I can see that @Padmakumar has answered your question. If you find it satisfactory then we’d appreciate it if you could mark his answer as the solution as it increase engagement here on the community. . . . Also if you’d like to explore more on database operations using Excel and CSV(omg csv?!) then I’ve written an article that you may find useful.
Remember, the excel database operations follow MS Access conventions. I usually go here anytime I get stuck.
Kind Regards, Ashwin A.K
Thanks for the reply Ashwin, as I have already mentioned in my reply above that it is giving me inconsistent results. Since there are more then 6 lakh records in an excel that I am dealing with and I want to get data from the required that's having year as 2022 .
Thanks for the reply, in my previous experiences I have been using the column name directly in queries like the one you have mentioned.
Can i not use the column name in this case also?
Yes, you can use. Then, the query will change as below:
SELECT * from sheet1$$] WHERE WDate] LIKE %2021%
I tried using the above query & it ran for sometime and ended without giving any errors but the result was inconsistent. The no of rows obtained from the query were far less than the actual number of rows that should have been there
Does the received output contain all 2021 data?
Hi @Abhi25 ,
I can see that @Padmakumar has answered your question. If you find it satisfactory then we’d appreciate it if you could mark his answer as the solution as it increase engagement here on the community. . . . Also if you’d like to explore more on database operations using Excel and CSV(omg csv?!) then I’ve written an article that you may find useful.
Remember, the excel database operations follow MS Access conventions. I usually go here anytime I get stuck.
Kind Regards, Ashwin A.K
Thanks for the reply Ashwin, as I have already mentioned in my reply above that it is giving me inconsistent results. Since there are more then 6 lakh records in an excel that I am dealing with and I want to get data from the required that's having year as 2022 . The other time when i ran the same query, it gave me an error saying - "No value given for one or more required parameters."
Hi @Abhi25 ,
I gave it a try from my end, with test data that I’ve prepared and this seems to work well:
SELECT * FROM "Sheet1$$] WHERE YEAR(bDate]) = 2022
I’m not sure if this is exactly what you were looking for, but if you wish to drill down deeper then here are some MS docs that can help you with that.
Kind Regards, Ashwin A.K
How can I fetch data between two date ranges using select query?
For eg I want the records that lie in the month of Oct 2022-Dec 2022. I am using below select query :
select * from Sheet1$$] where Date] BETWEEN #10/1/2022# AND #12/31/2022#
the query is giving me records of every year for the months of Oct-Dec instead I want the records for just 3 months (Oct,Nov,Dec) of the year 2022.
