Hubaal in Excel loo isticmaalo xaashida faafinta, laakiin ma ogtahay inaad ku xidhi karto Excel ilaha xogta dibadda? Maqaalkan waxaan kaga hadli doonaa sida loogu xiro xaashida xaashida Excel iyo miiska xogta ^(Excel)MySQL oo aan isticmaalno xogta ku jirta miiska xogta si aan ugu shubno xaashidayada. Waxaa jira dhawr waxyaalood oo aad u baahan tahay inaad samayso si aad isugu diyaariso xidhiidhkan.

Diyaarinta^{(Preparation)}

Marka hore, waa inaad soo dejisaa darawalkii ugu dambeeyay ee Isku xidhka Database^{(Database Connectivity)} Open ( ODBC ) ee MySQL . Darawalka ODBC ee hadda ee MySQL waxa uu ku yaalaa at

https://dev.mysql.com/downloads/connector/odbc/

Hubi^(Make) ka dib markaad soo dejiso faylka inaad hubiso xashiishka md5 ee faylka ka dhanka ah kan ku qoran bogga soo dejinta.

Marka xigta, waxaad u baahan doontaa inaad ku rakibto darawalka aad hadda soo dejisay.  Laba jeer^(Double) guji faylka si aad u bilowdo habka rakibida. Marka habka rakibidda dhammaato waxaad u baahan doontaa inaad abuurto Database Source Name ( DSN ) si aad ugu isticmaasho Excel .

Abuuritaanka DSN^{(Creating the DSN)}

DSN- ku waxa uu ka koobnaan doonaa dhammaan xogta isku xidhka lagama maarmaanka u ah isticmaalka miiska xogta MySQL . Nidaamka Windows , waxaad u baahan doontaa inaad gujiso Start , ka dibna Control Panel , ka dibna Tools maamulka^{(Administrative Tools)} , ka dibna Data Sources (ODBC) . Waa inaad aragto macluumaadka soo socda:

U fiirso^(Notice) tabsyada sawirka kore. Isticmaale DSN^{(User DSN)} waxa kaliya oo heli kara isticmaalaha abuuray. Nidaamka DSN^{(System DSN)} ayaa diyaar u ah qof kasta oo geli kara mashiinka. Faylka DSN^{(File DSN)} waa faylka .DSN oo loo rari karo loona isticmaali karo nidaamyada kale ee wata OS iyo darawalada la rakibay.

Si aad u sii waddo abuurista DSN , dhagsii badhanka ku dar^(Add) ee u dhow geeska midig ee sare.

Waxay u badan tahay inaad hoos u rogto si aad u aragto MySQL ODBC 5.x Driver . Haddii aysan jirin, waxbaa khaldamay ku rakibida darawalka qaybta Diyaarinta^{(Preparation)} ee boostadan. Si aad u sii waddo abuurista DSN , hubi in MySQL ODBC 5.x Driver la iftiimiyay oo dhagsii badhanka dhame^(Finish) . Hadda waa inaad aragto daaqad la mid ah kan hoose:

Marka xigta waxaad u baahan doontaa inaad keento macluumaadka lagama maarmaanka u ah buuxinta foomka kor ku xusan. Xogta MySQL iyo miiska aanu u isticmaaleyno boostadan waxay saaran yihiin mishiinka horumarinta waxaana isticmaala hal qof oo keliya. Deegaannada “wax soo saarka”, waxaa lagugula talinayaa inaad abuurto isticmaale cusub oo aad siiso isticmaaleha cusub mudnaanta xulashada oo kaliya . ^(SELECT)Mustaqbalka, waxaad siin kartaa mudnaanta dheeraadka ah haddii loo baahdo.

Ka dib markaad keento faahfaahinta qaabaynta isha xogtaada, waa inaad gujisaa badhanka Imtixaanka^(Test) si aad u hubiso in wax walba si habsami ah u shaqaynayaan. Marka xigta, dhagsii badhanka OK . Waa inaad hadda ku aragtaa isha xogta aad ku bixisay foomka qaabkii hore ee ku taxan daaqada Maamulaha Isha Xogta ODBC :^{(ODBC Data Source Administrator)}

Abuuritaanka Xiriirinta Xaashiyaha

Hadda oo aad si guul leh u abuurtay DSN cusub , waxaad xidhi kartaa ODBC Data Source Administrator daaqada oo aad furto Excel . Marka aad furto Excel , dhagsii ribbonka Xogta . ^(Data)Noocyada cusub ee Excel , dhagsii Hel Xogta^{(Get Data)} , ka dib Ilaha Kale^{(From Other Sources)} , ka dibna ka ODBC^{(From ODBC)} .

Noocyadii hore ee Excel , waa xoogaa ka badan oo geeddi-socod ah. Marka hore, waa inaad aragto wax sidan oo kale ah:

Talaabada xigta waa inaad gujiso isku xirka Connections ee ku yaal midigta ereyga Xogta^(Data) ee liiska tab. Goobta isku xirka Connections waxay ku wareegsan tahay casaan sawirka kore. Waa in lagugu soo bandhigaa daaqada Xidhiidhada Buuga Shaqada :^{(Workbook Connections)}

Talaabada xigta waa inaad gujiso badhanka ku dar^(Add) . Tani waxay ku siin doontaa daaqada Xidhiidhada Jira :^{(Existing Connections)}

Sida iska cad ma rabto inaad ka shaqeyso mid ka mid ah xidhiidhada liiska ku jira. Sidaa darteed, dhagsii badhanka Browse for More… ^{(Browse for More…)}Tani waxay ku siin doontaa daaqada Xulashada Xogta :^{(Select Data Source)}

Sida daaqadii hore ee Xidhiidhada Hore^{(Existing Connections)} , ma rabto inaad isticmaasho xidhiidhada ku taxan daaqada Xulashada Xogta^{(Select Data Source)} . Sidaa darteed, waxaad doonaysaa inaad laba jeer gujiso +Connect to New Data Source.odc folder. Markaad sidaas samaynayso, waa inaad hadda aragto daaqada Wizard Connection Data :^{( Data Connection Wizard)}

Marka la eego xulashooyinka isha xogta ee liiska ku jira, waxaad dooneysaa inaad muujiso ODBC DSN oo guji Next . Tallaabada xigta ee Wizard Connection Data^{(Data Connection Wizard)} waxay soo bandhigi doontaa dhammaan ilaha xogta ODBC ee laga heli karo nidaamka aad isticmaalayso.

Waxaan rajeyneynaa, haddii dhammaan sidii qorshuhu ahaa u dhaceen, waa inaad aragto DSN- ka aad ku abuurtay tillaabooyinka hore ee ku taxan ilaha xogta  ODBC . Muuji^(Highlight) oo guji Next .

Tallaabada xigta ee Wizard Connection Data^{(Data Connection Wizard)} waa in la keydiyo oo la dhammeeyo. Meesha magaca faylka waa in si toos ah laguugu buuxiyaa Waxaad bixin kartaa sharaxaad Sharaxaadda tusaalaha lagu isticmaalay waa mid si qurux badan isu sharraxaysa qof kasta oo isticmaali kara. Marka xigta, dhagsii badhanka dhame^(Finish) ee ku yaal midigta hoose ee daaqada.

Hadda waa inaad dib ugu noqotaa daaqada Isku xidhka Buuga Shaqada . ^{(Workbook Connection)}Xidhiidhka xogta ee aad hadda abuurtay waa in lagu taxay:

Soo dejinta Xogta Shaxda^{(Importing the Table Data)}

Waxaad xidhi kartaa daaqada Isku xirka Buuga Shaqada . ^{(Workbook Connection)}Waxaan u baahanahay inaan gujino badhanka Xidhiidhada jira^{(Existing Connections)} ee ku yaal ribbon Data ee ^(Data)Excel . Badhanka Xidhiidhada^{(Connections)} Jira waa inuu ku yaal dhanka bidix ee ribbonka Xogta .^(Data)

Riixitaanka badhanka Xidhiidhada Jira^{(Existing Connections)} waa inay ku tusisaa daaqada Xidhiidhada jira . ^{(Existing Connections)}Waxaad ku aragtay daaqadan talaabooyinkii hore, farqiga hadda waa in isku xirka xogtaada lagu taxay meel u dhow sare:

Hubi^(Make) in xidhiidhka xogta aad ku abuurtay talaabooyinkii hore la iftiimiyay ka dibna dhagsii badhanka Furan^(Open) . Hadda waa inaad aragto daaqadda xogta soo dejinta :^{(Import Data)}

Ujeedooyinka qoraalkan, waxaan u isticmaaleynaa habaynta caadiga ah ee daaqada xogta soo dejinta . ^{(Import Data)}Marka xigta, dhagsii badhanka OK . Haddii wax walba ay kuu shaqeeyeen, waa in hadda lagugu soo bandhigaa xogta miiska MySQL ee xaashidaada shaqo.^(MySQL)

Boostada, miiska aanu la shaqaynaynay wuxuu lahaa laba beerood. Goobta ugu horeysa waa goob toos ah oo INT ah oo cinwaankeedu yahay Aqoonsi. Goobta labaad waa VARCHAR (50) waxaana cinwaan looga dhigay fname. Xaashida faafintayada u dambaysa waxay u egtahay sidan:

Sidaad u badan tahay inaad dareentay, safka kowaad wuxuu ka kooban yahay magacyada tiirarka miiska. Waxa kale oo aad isticmaali kartaa falaadhaha hoos u dhaca ee ku xiga magacyada tiirarka si aad u kala saarto tiirarka.

Duub^(Wrap-Up)

Maqaalkan waxaan ku soo koobnay halka laga helo darawalada ODBC ee ugu dambeeyay ee MySQL , sida loo sameeyo DSN , sida loo sameeyo isku xirka xogta xisaabaadka iyadoo la adeegsanayo DSN iyo sida loo isticmaalo isku xirka xogta xisaabaadka si loogu soo dejiyo xogta Excel . Ku raaxayso!

 

Connecting Excel to MySQL

Sure Excel is used fоr spreadsheets, but did you know you can connect Exсel to external data sources? In this article we’re going to discuss how to connect an Excel spreadsheet to a MySQL database table and use the data in thе databaѕe table to рopulate our spreadsheet.  There are a few things yoυ need to do іn order to prepare for this connection.

Preparation

First, you must download the most recent Open Database Connectivity (ODBC) driver for MySQL.  The current ODBC driver for MySQL can be located at

https://dev.mysql.com/downloads/connector/odbc/

Make sure after you download the file that you check the file’s md5 hash against that listed on the download page.

Next, you will  need to install the driver you just downloaded.  Double click the file to start the install process.  Once the install process is complete you will need to create a Database Source Name (DSN) to use with Excel.

Creating the DSN

The DSN will contain all of the connection information necessary to use the MySQL database table.  On a Windows system, you will need to click on Start, then Control Panel, then Administrative Tools, then Data Sources (ODBC).  You should see the following information:

Notice the tabs in the image above.  A User DSN is only available to the user that created it.  A System DSN is available to anyone that can log into the machine.  A File DSN is a .DSN file that can be transported to and used on other systems that have the same OS and drivers installed.

To continue creating the DSN, click on the Add button near the top right corner.

You will probably have to scroll down to see the MySQL ODBC 5.x Driver.  If it’s not present, something went wrong with installing the driver in the Preparation section of this post.  To continue creating the DSN, make sure MySQL ODBC 5.x Driver is highlighted and click on the Finish button. You should now see a window similar to the one listed below:

Next you will need to supply the information necessary to complete the form shown above.  The MySQL database and table we’re using for this post is on a development machine and is only used by one person. For “production” environments, it is suggested you create a new user and grant the new user SELECT privileges only.  In the future, you can grant additional privileges if necessary.

After you have supplied the details for your data source configuration, you should click on the Test button to make sure everything is in working order.  Next, click on the OK button. You should now see the data source name you supplied on the form in the previous set listed on the ODBC Data Source Administrator window:

Creating the Spreadsheet Connection

Now that you have successfully created a new DSN, you can close the ODBC Data Source Administrator window and open Excel.  Once you have opened Excel, click on the Data ribbon.  For newer versions of Excel, click on Get Data, then From Other Sources, then From ODBC.

In older versions of Excel, it’s a bit more of a process. Firstly, you should see something like this:

The next step is to click on the Connections link located right under the word Data in the tab list. The location of the Connections link is circled in red in the above image. You should be presented with the Workbook Connections window:

The next step is to click on the Add button.  This will present you with the Existing Connections window:

Obviously you don’t want to work on any of the connections listed.  Therefore, click on the Browse for More… button. This will present you with the Select Data Source window:

Just like the previous Existing Connections window, you do not want to use the connections listed in the Select Data Source window.  Therefore, you want to double click on the +Connect to New Data Source.odc folder.  In doing so, you should be now see the Data Connection Wizard window:

Given the data source choices listed, you want to highlight ODBC DSN and click Next.  The next step of the Data Connection Wizard will display all of the ODBC data sources available on the system you are using.

Hopefully, if all as gone according to plan, you should see the DSN that you created in previous steps listed among the ODBC data sources.  Highlight it and click on Next.

The next step in the Data Connection Wizard is to save and finish.  The file name field should be auto filled for you.  You can supply a description.  The description used in the example is pretty self explanatory for anyone that might use it.  Next, click on the Finish button in the lower right of the window.

You should now be back at the Workbook Connection window.  The data connection you just created should be listed:

Importing the Table Data

You can close the Workbook Connection window.  We need to click on the Existing Connections button in the Data ribbon of Excel.  The Existing Connections button should be located to the left on the Data ribbon.

Clicking on the Existing Connections button should present you with the Existing Connections window.  You’ve seen this window in previous steps, the difference now is that your data connection should be listed near the top:

Make sure the data connection you created in the previous steps is highlighted and then click on the Open button.  You should now see the Import Data window:

For the purposes of this post, we are going to use the default settings on the Import Data window.  Next, click on the OK button.  If everything worked out for you, you should now be presented with the MySQL database table data in your worksheet.

For this post, the table we were working with had two fields.  The first field is an auto-increment INT field titled ID.  The second field is VARCHAR(50) and is titled fname.  Our final spreadsheet looks likes like this:

As you’ve probably noticed, the first row contains the table column names.  You can also use the drop down arrows next to the column names to sort the columns.

Wrap-Up

In this post we covered where to find the latest ODBC drivers for MySQL, how to create a DSN, how to create a spreadsheet data connection using the DSN and how to use the spreadsheet data connection to import data into an Excel spreadsheet. Enjoy!

 

Related posts

• Sida loo tirtiro khadadka banaan ee Excel

• Sida loo Isticmaalo Tilmaamaha Unugyada Hadalka ee Excel

• Sida loo Geliyo Xaashida Shaqada ee Excel Word Doc

• Sida loo isticmaalo Excel's What-If Analysis

• Sida Loo Hagaajiyo Safka gudaha Excel

• Google Sheets vs Microsoft Excel - Waa maxay faraqa u dhexeeya?

• Hal-Column iyo Xog-urur badan oo Aasaaska ah oo ku kala soocida xaashida faafinta ee Excel

• Sida Taariikh ahaan loogu kala saaro Excel

• Sida Loo Dhaqaajiyo Columns gudaha Excel

• Hagaha VBA Sare ee MS Excel

• Sida loo xarfo xarfaha Excel

• Sida Loo Sameeyo Liisaska Hoos-u-dhaca ee Isku Xirka Badan ee Excel

• Waa maxay VBA Array ee Excel iyo Sida loo Barnaamijyo Mid

• 5 siyaabood oo loogu badalo qoraalka nambarada gudaha Excel

• Sida loo xisaabiyo Z-Score gudaha Excel

• 3 Siyood Oo Unugyada Excel Loogu Kala Qaybiyo

• U isticmaal Magacyada Range Dynamic ee Excel si aad u Dejiso Dabcida

• 4 siyaabood oo Excel loogu badalo Google Sheets

• Adeegsiga Qalab Raadinta Hadafka ee Excel Maxaa-Haddii Falanqaynta

• Sida loo Isticmaalo If iyo Nsted Haddii Odhaahyada Excel