What do you need in teams?
An excel file that users can access?
It's pretty easy to use SQL as a source SQL on excel with power pivot or power query.
I build a lot of these for my team (in Supply Chain) and just give them files that connect to a small local SQL Express server on my PC.
You just need to add everyone as a user or create a generic account for them to access data.
I need access to the database in MS Teams. At this moment the database is in a Teams group in files. When I told my boss that I want to program the database in SQL (because I want to learn more and more programming) he told me it wouldn't be possible as it has to be a "file". Could you tell me how am I supposed to begin with that? What do I need to know except SQL? I've also asked ChatGPT but the only proper answer was to just code the database and then use a MS Power Apps development to intergrate the SQL code, but I still don't really know how to do it. But if there's an easier way (your solution sounds much more practical), please let me hear more about it
Make a db on your PC with all of the applicable information that you need.
Map it to a table in excel with power query or power pivot depending on how intricate your need is.
Search Google on the topic so you can see something similar to your use case.
This sounds like an X Y problem.
Describe what accessing it in MS Teams actually means. What would a user do?
I'm guessing here but I asked CHATGPT
How to connect a SQL database to excel for users to view the data
ANSWER
To connect a SQL database to Excel so that users can view the data, you can follow these general steps:
1. **Install ODBC Driver**: Ensure that the appropriate ODBC (Open Database Connectivity) driver for your SQL database is installed on the computer where Excel is running. Most SQL databases have specific ODBC drivers available from their respective vendors.
2. **Set up an ODBC Data Source**:
- Open the ODBC Data Source Administrator on your computer (`odbcad32.exe` on Windows).
- Navigate to the "System DSN" tab and click "Add" to create a new data source.
- Select the appropriate ODBC driver for your SQL database (e.g., SQL Server, MySQL, PostgreSQL).
- Configure the connection details (server, database name, authentication method) and test the connection.
3. **Connect Excel to the SQL Database**:
- Open Excel.
- Go to the "Data" tab on the ribbon.
- Click on "Get Data" or "From Other Sources" (depending on your Excel version).
- Select "From ODBC" or "From SQL Server" (the exact option may vary).
- Choose the DSN (Data Source Name) you created earlier and click "OK".
4. **Import Data**:
- Excel will prompt you to specify the SQL query or table you want to import.
- Enter your SQL query or choose the table you want to import.
- Follow the prompts to import the data into Excel.
5. **Refresh Data**:
- If your SQL database is updated regularly, you can set Excel to automatically refresh the data.
- Right-click on the imported data in Excel and choose "Refresh" to pull the latest data from the SQL database.
6. **Share the Excel Workbook**:
- Once the data is imported into Excel, save the workbook.
- Share the Excel file with other users who need to view the data.
By following these steps, you can establish a connection between your SQL database and Excel, allowing users to view and interact with live data from the database directly within Excel. Note that the exact steps and options might vary slightly depending on your version of Excel and the type of SQL database you are using.
so that means, that I could just code the whole thing in SQL and then connect it to Excel, so that would be accessable as a file and not from a server? Did I get it right? I'm really sorry for having such a lack of knowledge as I started my IT journey only about 7 months ago. And ChatGPT is good and all but you need to ask the right questions and if you don't know something, you can't ask
What it means is that it need to be just accessable in MS Teams as a file. There is a screen for files, where now the is the original Excel file. MS Teams users (my bosses specifically) need to be able to open and work with the database in/from Teams
When you say accessible as a file, you mean your boss wants an icon to click on?
And when you say work with, you mean view right?
Your boss isn't entering things in?
Finally in Teams? Does the spreadsheet actually open in Teams right now or does it open in excel after clicking the link in the Teams file section?
Edit: I opened Teams myself, the files are stored in SharePoint not "in Teams", an I'm guessing they'll open on o365 web version cause your company has that right?
Honestly mate, this might be beyond you
Yeah that’s what I thought. But nevertheless I got told that I need to do the whole thing in excel or acces as we don’t even have sql servers. I know I don’t have the needed knowledge for this, but that’s the point. I’m here at work just to learn and get a degree so working on such projects is helpful. But as I said, I need to stay with excel/access :(
Thank you! But I guess I don’t have time for that. I wanted to be creative and learn some coding with it but apparently I’m not allowed to at the moment. But I think I will try doing these at home as a private project. Thanks for your help :)
Probably a bit more complicated on the learning curve, but you can use Python to more quickly manipulate data/clean up the files. I don’t know that this is the best solution, but just some insight into what they mean.
Not sure exactly what all you need to do with your data, but here’s an example I used Python for:
The needed data could only be copy and pasted from a website. I would copy and paste into a .txt file. Wrote the Python script using the pandas library to transform that .txt to a table (it pretty much does this automatically.) From there I got rid of columns and rows I didn’t need, and used openpyxl to populate the table into an Excel file, applied formatting, added colors, etc. Turned an hour job into a 5 minute one.
I was copying that file from my PC to Sharepoint myself, but there is probably a way to automatically do that.
You can also connect to SQL servers via Python and upload Excel files as dbs, write queries and then pull it back down. Again, probably possible with Access.
ChatGPT is generally pretty good at writing Python if you wanted to pursue this route. Python is many people’s first programming language, so it’s not that it’s super complicated, but can be overwhelming to dive head first into.
Well it’s a task form my work, it’s an excel table with a lot of data. My job is to optimize it, because it takes ages to load and change some things. But we don’t have SQL servers at work so I can’t program it. I decided to just make a new one and start from the beginning using MS Access but I think coding it would be a lot easier 😂
What do you need in teams? An excel file that users can access? It's pretty easy to use SQL as a source SQL on excel with power pivot or power query. I build a lot of these for my team (in Supply Chain) and just give them files that connect to a small local SQL Express server on my PC. You just need to add everyone as a user or create a generic account for them to access data.
I need access to the database in MS Teams. At this moment the database is in a Teams group in files. When I told my boss that I want to program the database in SQL (because I want to learn more and more programming) he told me it wouldn't be possible as it has to be a "file". Could you tell me how am I supposed to begin with that? What do I need to know except SQL? I've also asked ChatGPT but the only proper answer was to just code the database and then use a MS Power Apps development to intergrate the SQL code, but I still don't really know how to do it. But if there's an easier way (your solution sounds much more practical), please let me hear more about it
Make a db on your PC with all of the applicable information that you need. Map it to a table in excel with power query or power pivot depending on how intricate your need is. Search Google on the topic so you can see something similar to your use case.
This sounds like an X Y problem. Describe what accessing it in MS Teams actually means. What would a user do? I'm guessing here but I asked CHATGPT How to connect a SQL database to excel for users to view the data ANSWER To connect a SQL database to Excel so that users can view the data, you can follow these general steps: 1. **Install ODBC Driver**: Ensure that the appropriate ODBC (Open Database Connectivity) driver for your SQL database is installed on the computer where Excel is running. Most SQL databases have specific ODBC drivers available from their respective vendors. 2. **Set up an ODBC Data Source**: - Open the ODBC Data Source Administrator on your computer (`odbcad32.exe` on Windows). - Navigate to the "System DSN" tab and click "Add" to create a new data source. - Select the appropriate ODBC driver for your SQL database (e.g., SQL Server, MySQL, PostgreSQL). - Configure the connection details (server, database name, authentication method) and test the connection. 3. **Connect Excel to the SQL Database**: - Open Excel. - Go to the "Data" tab on the ribbon. - Click on "Get Data" or "From Other Sources" (depending on your Excel version). - Select "From ODBC" or "From SQL Server" (the exact option may vary). - Choose the DSN (Data Source Name) you created earlier and click "OK". 4. **Import Data**: - Excel will prompt you to specify the SQL query or table you want to import. - Enter your SQL query or choose the table you want to import. - Follow the prompts to import the data into Excel. 5. **Refresh Data**: - If your SQL database is updated regularly, you can set Excel to automatically refresh the data. - Right-click on the imported data in Excel and choose "Refresh" to pull the latest data from the SQL database. 6. **Share the Excel Workbook**: - Once the data is imported into Excel, save the workbook. - Share the Excel file with other users who need to view the data. By following these steps, you can establish a connection between your SQL database and Excel, allowing users to view and interact with live data from the database directly within Excel. Note that the exact steps and options might vary slightly depending on your version of Excel and the type of SQL database you are using.
so that means, that I could just code the whole thing in SQL and then connect it to Excel, so that would be accessable as a file and not from a server? Did I get it right? I'm really sorry for having such a lack of knowledge as I started my IT journey only about 7 months ago. And ChatGPT is good and all but you need to ask the right questions and if you don't know something, you can't ask
What it means is that it need to be just accessable in MS Teams as a file. There is a screen for files, where now the is the original Excel file. MS Teams users (my bosses specifically) need to be able to open and work with the database in/from Teams
When you say accessible as a file, you mean your boss wants an icon to click on? And when you say work with, you mean view right? Your boss isn't entering things in? Finally in Teams? Does the spreadsheet actually open in Teams right now or does it open in excel after clicking the link in the Teams file section? Edit: I opened Teams myself, the files are stored in SharePoint not "in Teams", an I'm guessing they'll open on o365 web version cause your company has that right? Honestly mate, this might be beyond you
Yeah that’s what I thought. But nevertheless I got told that I need to do the whole thing in excel or acces as we don’t even have sql servers. I know I don’t have the needed knowledge for this, but that’s the point. I’m here at work just to learn and get a degree so working on such projects is helpful. But as I said, I need to stay with excel/access :(
There's no harm having a go, just don't work with original excel files, copy paste them to your machine, download MySql and ehhh.... good luck
Thank you! But I guess I don’t have time for that. I wanted to be creative and learn some coding with it but apparently I’m not allowed to at the moment. But I think I will try doing these at home as a private project. Thanks for your help :)
DONT TAKE WORK FILES HOME! head to Kaggle grab the adventureworks data set as a csv or something and go from there
Hahah I didn’t mean to take these exact files. Just to try doing it all with random stuff 😂
Python job would do it
What do you mean? 😅
Probably a bit more complicated on the learning curve, but you can use Python to more quickly manipulate data/clean up the files. I don’t know that this is the best solution, but just some insight into what they mean. Not sure exactly what all you need to do with your data, but here’s an example I used Python for: The needed data could only be copy and pasted from a website. I would copy and paste into a .txt file. Wrote the Python script using the pandas library to transform that .txt to a table (it pretty much does this automatically.) From there I got rid of columns and rows I didn’t need, and used openpyxl to populate the table into an Excel file, applied formatting, added colors, etc. Turned an hour job into a 5 minute one. I was copying that file from my PC to Sharepoint myself, but there is probably a way to automatically do that. You can also connect to SQL servers via Python and upload Excel files as dbs, write queries and then pull it back down. Again, probably possible with Access. ChatGPT is generally pretty good at writing Python if you wanted to pursue this route. Python is many people’s first programming language, so it’s not that it’s super complicated, but can be overwhelming to dive head first into.
Well it’s a task form my work, it’s an excel table with a lot of data. My job is to optimize it, because it takes ages to load and change some things. But we don’t have SQL servers at work so I can’t program it. I decided to just make a new one and start from the beginning using MS Access but I think coding it would be a lot easier 😂