Question

SQL Query to filter by Year in Excel as database

  • 26 December 2022
  • 9 replies
  • 578 views
A
Badge +5

Hi Guys,

 

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 [Sheet1$$] 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 :)

9 replies

Padmakumar
Userlevel 7
Badge +13

Hi @Abhi25 ,

 

You can try with LIKE command within the SELECT query for this requirement. 

For e.g. SELECT * from [sheet1$$] WHERE $Date$ LIKE %2021%

 

Date is a variable here to which the data must be saved from the Date column of your sheet.

Padmakumar
A
Badge +5

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?

Padmakumar
Userlevel 7
Badge +13

 

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 [Date] LIKE %2021%

Padmakumar
Ashwin A.K
Userlevel 4
Badge +7

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

Blogs at TheCodingTheory - https://www.thecodingtheory.com
A
Badge +5

 

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 [Date] 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

A
Badge +5

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 .

Padmakumar
Userlevel 7
Badge +13

 

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 [Date] 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?

Padmakumar
Ashwin A.K
Userlevel 4
Badge +7

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([Date]) = 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

Blogs at TheCodingTheory - https://www.thecodingtheory.com
A
Badge +5

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.

P.S. - I am using excel as database.

Reply


Terms & ConditionsCookie settings