How to Read Access File in Stata
Annotation: This folio is written using Stata 11 and Windows XP.
Stata command odbc allows Stata to load, write, or view data from ODBC sources. ODBC, an acronym for Open DataBase Connectivity, is a standardized ready of function calls that tin can exist used to access data stored in database management systems. A dBASE file, an Excel file, or an Admission file are all examples of information files created by database systems.
Why might I utilize odbc?
- Need to read in Office files merely do not have Function on machine.
- Have an Excel file with many tabs.
- Have an Access database with many tables or queries.
- Easy manner to do data management on database in Stata setting that can exist exported hands to database.
On this page, nosotros volition go through loading a multi-sheet Excel file and inserting columns from within Stata and an ACCESS database with linked tables and a query that can be modified from within Stata.
Setting Up a Data Source in Windows for Reading Excel and Admission files via odbc in Stata
To read an Excel or Access file into Stata, nosotros must found the file as a via Data Source. The central footstep hither is to register your database with your calculator's ODBC organization. Hither is an example showing how to do it for Windows XP. You lot may also desire to read Stata manual on Data Direction for more details. In this example, nosotros accept one Access file, /stata/faq/hsbdb.mdb. The aforementioned process works for both Excel and Access files.
Step 1: From the Starting time Bill of fare, select Settings and and then the Command Console.
Step 2: Select Administrator Tools and then select Data Sources (ODBC). This will bring up a window like the following window:
Step three: Click on Add… and this will bring up the following window.
Step 4: Select Microsoft Access Commuter (*.mdb) and then click on Cease.
Stride 5: The proper noun for the Information Source tin can be capricious. Here we will use testdb as our information source name for the Access database.
Pace 6: Now, the last step is to select all the Access files that you lot want to be associated with this data source. This is done by "Select". Click on "Select", you volition come across something similar to the following:
Pace seven: Now you can select your .mdb file to be in the Information Source testdb that we are creating. After select your file, yous can and then click on OK to close this window and click on OK again to close previous window.
Discover that we have a new entry in the window higher up, that is our testdb Data Source. We will practice the same for a multisheet Excel file, https://stats.idre.ucla.edu/wp-content/uploads/2016/02/multiplesheets.xls, assigning it the Information Source name testxl. If your computer has multiple versions of Microsoft Office, you will need to be pickier about the commuter y'all choose. Yous should select the driver that supports all of the file extensions. For Excel files, look for Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb). After we have added our Excel file as a data source, our window list includes both of our additions.
Reading and Altering Excel files via odbc in Stata
Afterwards setting upwardly our Data Sources, we are fix to get admission to our Excel file via odbc in Stata. Afterwards typing "odbc list", you can just follow the links to admission a given canvass in our Excel data source testxl: click on testxl, then from the query list, then click on scores$, and and so from the draw output for that sheet, click on load. Alternatively, you lot tin can enter the equivalent commands following the example below. If at that place is a dataset loaded in Stata prior to these steps, you lot must enter clear before loading the dataset from the Data Source.
odbc listData Source Name Driver ------------------------------------------------------------------------------- MS Access Database Microsoft Admission Driver (*.mdb) Excel Files Microsoft Excel Driver (*.xls) dBASE Files Microsoft dBase Driver (*.dbf) testdb Microsoft Access Driver (*.mdb) testxl Microsoft Excel Driver (*.xls, *.xlsx, *.forty -------------------------------------------------------------------------------odbc query "testxl"DataSource: testxl Path : D:datamultiplesheets ------------------------------------------------------------------------------- demo$ scores$ demo$_FilterDatabase scores$_FilterDatabase ------------------------------------------------------------------------------- odbc desc "scores$"DataSource: testxl (query) Table: scores$ (load) ------------------------------------------------------------------------------- Variable Name Variable Type ------------------------------------------------------------------------------- id NUMBER read NUMBER write NUMBER math NUMBER science NUMBER socst NUMBER ------------------------------------------------------------------------------- odbc load, tabular array("scores$")
With this new dataset loaded into Stata, we tin now brand changes to the dataset and output them to Excel using odbc.
Adding observations: Nosotros can add together a new ascertainment to the dataset by creating a new dataset with 1 observation, defining some values in the observation, and and so inserting the observation into our scores tab in the Excel file:
articulate set obs ane gen id =201 gen write = 100 odbc insert id write , table("[scores$]") dsn("testxl") We tin can await at the scores tab in Excel to run into this added observation.
Creating a new data tab: Nosotros tin make changes to our data and output the altered dataset to a new tab in our Excel file.
odbc load, table("scores$") dsn("testxl") supervene upon write = 100 if write >=60 odbc insert id read write math science socst, table("[demonew]") dsn("testxl") create We tin can see that a new tab has been added to our Excel file in which write values of threescore or greater have been replaced with 100.
Reading and Altering Access Database files via odbc in Stata
Our instance Access file has two tables–i containing demographic information for each educatee and i containing scores for each student–and 1 query linking these ii students into a single table with both demographics and scores.
We tin can view the full dataset past selecting the Hsb2_all query.
Post-obit the same steps used for the Excel file, y'all can load the Hsb2_scores table into Stata as a dataset (remember to articulate any existing datasets offset).
articulate odbc load, table("Hsb2_scores") dsn("testdb") Altering a table: We can make changes to the data in this tabular array in Stata and so consign the altered dataset dorsum to its database. We do this with odbc insert, listing all of the variables to send to the database. We then indicate the table and Information Source and, finally, say overwrite to replace the information currently stored in the given table.
replace write = 100 if write >=60 odbc insert id read write math scientific discipline socst, table(Hsb2_scores) dsn("testdb") overwrite We can look at the updated tabular array in Access to run across the changes. For id = 3, the write score had previously been 65 and it is at present 100.
Not only has this table been updated, just the query linking this tabular array to the demographic information also reflects the changes.
Creating a new table: Similarly, we tin make changes and so create a new table in the database.
clear odbc load, table("Hsb2_scores") dsn("testdb") gen totscore = read + write + math + scientific discipline + socst odbc insert id totscore, tabular array(Hsb2_totals) dsn("testdb") create We can see this new tabular array in our Access database.
Source: https://stats.oarc.ucla.edu/stata/faq/how-can-i-load-write-or-view-a-dbase-file-excel-file-or-access-file-using-odbc/
0 Response to "How to Read Access File in Stata"
Post a Comment