Access 2016: Import Data into Access

<!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <meta name="keywords" content="import data into access, ms, csv, excel, datasheet view, insert, enter, microsoft access 2016, free tutorial"> <meta name="Description" content="Import data into Access with this Microsoft Access 2016 tutorial."> <link rel="canonical" href="https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access.cfm"> <title>Access 2016: Import Data into Access</title> <script src="//ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script> <link rel="shortcut icon" href="/pix/favicon96.png"> <link rel="apple-touch-icon" href="/pix/apple-touch-icon.png"> <link rel="preconnect" href="https://fonts.googleapis.com"> <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin> <link href="https://fonts.googleapis.com/css2?family=Lato:ital,wght@0,400;0,700;1,400&display=swap" rel="stylesheet"> <link href="/common/css/master.45.min.css" rel="stylesheet"> <script async src="https://cdn.fuseplatform.net/publift/tags/2/3499/fuse.js"></script>  <script async src="https://www.googletagmanager.com/gtag/js?id=G-Q3H025ZKLN"></script> <script> window.dataLayer = window.dataLayer || []; function gtag(){dataLayer.push(arguments);} gtag('js', new Date()); gtag('config', 'G-Q3H025ZKLN'); </script> </head> <body> <header class="site-header"> <div class="site-header-base"> <div class="site-logo"> <a title="Quackit Homepage" target="_top" href="/"><img src="/pix/quackit_logo_watermark.png" width="87" height="33" alt="Quackit Logo"></a> </div> <button id="site-nav-toggler" class="site-nav-toggler" aria-expanded="false" aria-controls="site-nav"> <span class="sr-only">Toggle navigation</span> ☰ </button> </div> <nav id="site-nav" class="site-nav"> <div class="site-links"> <ul> <li><a href="/"><i class="fa fa-home"></i> <span class="sr-only">Home</span></a></li> <li><a href="/html/">HTML</a></li> <li><a href="/css/">CSS</a></li> <li><a href="/scripting/">Scripting</a></li> <li><a href="/database/">Database</a></li> </ul> </div> <div class="site-search-top"> <form action="/search/" id="cse-search-box-bottom" class="site-search"> <div> <input type="hidden" name="cx" value="partner-pub-6331358926293806:98x0fk-bbgi"> <input type="hidden" name="cof" value="FORID:10"> <input type="hidden" name="ie" value="ISO-8859-1"> <input type="text" name="q" size="20" class="site-search-input"> <button type="submit" name="sa" class="site-search-button"><i class="fa fa-search"></i></button> </div> </form> </div> </nav> </header> <div class="main"> <article class="content"> <h1 class="page-title">Access 2016: Import Data into Access</h1> <div class="ad ad-top">  <div data-fuse="23059883623"></div> </div> <ul class="pager"> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/add_data_to_a_table_in_microsoft_access.cfm"><i class="fa fa-long-arrow-left" aria-hidden="true"></i> Add Data to a Table</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/create_a_relationship_in_microsoft_access.cfm">Create a Relationship <i class="fa fa-long-arrow-right" aria-hidden="true"></i></a></li> </ul> <p class="lead">Access provides an easy way to import data from external files.</p> <p>You can easily import data from a variety of different sources, including text, Excel, XML, HTML, ODBC datasources and more.</p> <p>You can also link to external data sources, so that changes in the source file are reflected in your Access database.</p> <p>Generally, when importing data, you have these options:</p> <ul> <li>Import the data into an existing table;</li> <li>Have Access create a new table based on the data; or</li> <li>Establish a link to the external file, so that future updates are reflected automatically.</li> </ul> <p>Today, we'll import data into an existing table.</p> <div class="ad">  <div data-fuse="23059883629"></div> </div> <h2>Import a CSV File into an Existing Table</h2> <p>We will import the following <abbr title="Comma Separated Values">CSV</abbr> file into the <samp>Artists</samp> table.</p> <p>Source File: <samp>artists.csv</samp></p> <script src="/common/js/codemirror/lib/codemirror.js"></script> <div class="code-only"> <textarea id="example1" autocomplete="off" spellcheck="false">1, AC/DC, 1973
2, Louis Armstrong, 1914
3, Iron Maiden, 1975
4, Miles Davis, 1944
5, Pat Benetar, 1972
6, Stevie Ray Vaughan, 1965
7, Avenged Sevenfold, 1999
8, Destiny's Child, 1990
9, Snoop Dogg, 1992</textarea> </div> <script> var exampleCode1 = CodeMirror.fromTextArea(document.getElementById("example1"), { mode: "", tabMode: "indent", styleActiveLine: false, lineNumbers: false, lineWrapping: true, theme: "q-dark" }); </script> <p>Destination table: <samp>Artists</samp></p> <table class="tabular"> <tr> <th>ArtistId</th> <th>ArtistName</th> </tr> <tr> <td> </td> <td> </td> </tr> </table> <p>You can download the CSV file here: <a href="/microsoft_access/microsoft_access_2016/tutorial/artists.csv" target="_blank"><samp>artists.csv</samp></a></p> <p>Note that this file includes an extra field that's not in our destination table. Not a problem. Access will allow us to remove the extra field.</p> <p>Also, the CSV file doesn't contain a header row, but that's fine. Access can deal with this too.</p> <ol class="steps"> <li> <figure> <a target="_blank" data-lightbox="import_data" href="/pix/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access_1.png"><img src="/pix/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access_1.png" class="screenshot steps" alt="Screenshot of the Import option on the Ribbon"></a> <figcaption> <h3>Launch the Import Wizard</h3> <p>Before starting, ensure that both the source file, and the destination table are closed.</p> <p>To launch the wizard for a CSV file, click <kbd>Text File</kbd> from the <kbd>External Data</kbd> tab on the Ribbon.</p> <div class="tip sm"> <p>If you don't see the <kbd>Text File</kbd> option on the Ribbon like in the screenshot, but you do see a <kbd>New Data Source</kbd> option, select <kbd>New Data Source > From File > Text File</kbd>.</p> </div> <div class="tip sm"> <p>You can also launch the wizard by right-clicking on a table and selecting an option from the <samp>Import</samp> option.</p> </div> <div class="tip sm"> <p>Access needs the destination table to be closed before importing data. If you don't close it before starting the import, Access will warn you later in the process, and offer to save and close the table for you.</p> </div> </figcaption> </figure> </li> <li> <figure> <a target="_blank" data-lightbox="import_data" href="/pix/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access_2.png"><img src="/pix/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access_2.png" class="screenshot steps" alt="Screenshot of selecting the source file"></a> <figcaption> <h3>Select the Source File & Destination Table</h3> <p>Use the <kbd>Browse...</kbd> button to navigate to and select the import file (in our case, <samp>artists.csv</samp>).</p> <p>Select <kbd>Append a copy of the records to the table</kbd> and then select the <kbd>Artists</kbd> table from the drop down list of tables.</p> <p>Click <kbd>OK</kbd> to continue.</p> <div class="tip sm"> <p>You can just type the path directly into the <samp>File name</samp> field if you know the full path to the file (i.e. instead of using the <kbd>Browse...</kbd> button).</p> </div> </figcaption> </figure> </li> <li> <figure> <a target="_blank" data-lightbox="import_data" href="/pix/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access_3.png"><img src="/pix/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access_3.png" class="screenshot steps" alt="Screenshot of reviewing the file format"></a> <figcaption> <h3>Review the Format Options</h3> <p>This part of the Import Wizard will ask you to confirm the file's format. In our case, Access has (correctly) detected that our file is in a delimited format.</p> <p>If all looks OK, click <kbd>Next ></kbd>.</p> </figcaption> </figure> </li> <li> <figure> <a target="_blank" data-lightbox="import_data" href="/pix/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access_4.png"><img src="/pix/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access_4.png" class="screenshot steps" alt="Screenshot of selecting the delimiter"></a> <figcaption> <h3>Select/Review the Delimiter</h3> <p>Now Access will ask you to select the file's delimiter. In our case, Access has (correctly) detected that our file uses a comma as its delimiter. If it was wrong, you can click another delimiter and see how the data updates to reflect the new delimiter.</p> <p>You can also select whether or not the file contains header rows. If the first row of your file contains headers, click <kbd>First Row Contains Field Names</kbd>. Otherwise, leave it unchecked.</p> <p>However, before continuing, click the <kbd>Advanced...</kbd> button, as we need to tell Access to skip the extra field.</p> </figcaption> </figure> </li> <li> <figure> <a target="_blank" data-lightbox="import_data" href="/pix/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access_5.png"><img src="/pix/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access_5.png" class="screenshot steps" alt="Screenshot of selecting a field to skip"></a> <figcaption> <h3>Skip the Extra Field</h3> <p>Access won't import the CSV file the way it is because it contains an extra field. The file contains 3 fields but our table only has two. Therefore we can only import two fields. We need to tell Access which field to skip.</p> <p>Check the box next to <kbd>Field3</kbd> so that doesn't get imported.</p> <p>Then click <kbd>OK</kbd>.</p> </figcaption> </figure> </li> <li> <figure> <a target="_blank" data-lightbox="import_data" href="/pix/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access_4.png"><img src="/pix/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access_4.png" class="screenshot steps" alt="Screenshot of delmiter screen"></a> <figcaption> <h3>Continue the Wizard</h3> <p>After skipping the extra field, you'll be back on the delimiter screen. Click <kbd>Next ></kbd>.</p> </figcaption> </figure> </li> <li> <figure> <a target="_blank" data-lightbox="import_data" href="/pix/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access_6.png"><img src="/pix/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access_6.png" class="screenshot steps" alt="Screenshot of final screen before running the import"></a> <figcaption> <h3>Run the Import</h3> <p>Click <kbd>Finish</kbd> to run the import.</p> </figcaption> </figure> </li> <li> <figure> <a target="_blank" data-lightbox="import_data" href="/pix/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access_7.png"><img src="/pix/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access_7.png" class="screenshot steps" alt="Screenshot of confirmation"></a> <figcaption> <h3>Close the Wizard</h3> <p>Once the import operation has completed, Access will ask you if you want to save the import steps for another time. Check the box if you'd like to, otherwise leave it unchecked, and click <kbd>Close</kbd>.</p> </figcaption> </figure> </li> <li> <figure> <a target="_blank" data-lightbox="import_data" href="/pix/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access_8.png"><img src="/pix/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access_8.png" class="screenshot steps" alt="Screenshot of table containing the imported data"></a> <figcaption> <h3>Check that the data has been Imported</h3> <p>Now that the import has run, you should check the table to make sure that all data is in the correct fields and it looks OK.</p> <p>Double click on the <kbd>Artists</kbd> table to view its contents.</p> </figcaption> </figure> </li> </ol> <ul class="pager"> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/add_data_to_a_table_in_microsoft_access.cfm"><i class="fa fa-long-arrow-left" aria-hidden="true"></i> Add Data to a Table</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/create_a_relationship_in_microsoft_access.cfm">Create a Relationship <i class="fa fa-long-arrow-right" aria-hidden="true"></i></a></li> </ul> </article> <div class="sidebar"> <nav> <ul> <li> <h3><a href="/microsoft_access/tutorial/"><i class="fa fa-database"></i> MS Access 2016 Tutorial</a></h3> <ul> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/access_2016_introduction.cfm">Introduction to Access</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/create_a_database_in_microsoft_access.cfm">Create a Database</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/create_a_table_in_microsoft_access.cfm">Create a Table</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/create_a_table_in_design_view_in_microsoft_access.cfm">Create a Table in Design View</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/create_an_input_mask_in_microsoft_access.cfm">Create an Input Mask</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/add_data_to_a_table_in_microsoft_access.cfm">Add Data to Table</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/import_data_into_microsoft_access.cfm">Import Data from a CSV File</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/create_a_relationship_in_microsoft_access.cfm">Create a Relationship</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/create_a_lookup_table_in_microsoft_access.cfm">Create a Lookup Table</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/create_a_query_in_microsoft_access.cfm">Create a Query</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/create_a_query_with_user_input_in_microsoft_access.cfm">Create a Query with User Input</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/use_the_expression_builder_in_microsoft_access.cfm">How to use the Expression Builder</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/export_data_from_microsoft_access.cfm">Export Data to Excel (and XML)</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/create_a_form_in_microsoft_access.cfm">Create a Form</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/add_a_combo_box_to_a_form_in_microsoft_access.cfm">Add a Combo Box to a Form</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/create_a_report_in_microsoft_access.cfm">Create a Report</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/create_a_macro_in_microsoft_access.cfm">Create a Macro</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/backup_a_database_in_microsoft_access.cfm">Backup a Database</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/split_a_database_in_microsoft_access.cfm">Split a Database</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/make_an_executable_database_in_microsoft_access.cfm">Make an Executable Database</a></li> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/install_the_northwind_database_in_microsoft_access.cfm">Install the Northwind Database</a></li> </ul> </li> <li> <h3><a href="/microsoft_access/howto/"><i class="fa fa-question-circle"></i> How To...</a></h3> <ul> <li><a href="/microsoft_access/microsoft_access_2016/howto/how_to_create_a_totals_row_in_access_2016.cfm">Create a Totals Row</a></li> <li><a href="/microsoft_access/microsoft_access_2016/howto/how_to_create_a_calculated_field_in_access_2016.cfm">Create a Calculated Field</a></li> <li><a href="/microsoft_access/microsoft_access_2016/howto/how_to_create_a_parameter_query_in_access_2016.cfm">Create a Parameter Query</a></li> <li><a href="/microsoft_access/microsoft_access_2016/howto/how_to_find_duplicate_records_in_access_2016.cfm">Find Duplicate Records</a></li> <li><a href="/microsoft_access/microsoft_access_2016/howto/how_to_use_the_crosstab_query_wizard_in_access_2016.cfm">How to use the Crosstab Query Wizard</a></li> <li><a href="/microsoft_access/microsoft_access_2016/howto/how_to_password_protect_a_database_in_microsoft_access_2016.cfm">Password Protect a Database</a></li> <li><a href="/microsoft_access/microsoft_access_2016/howto/how_to_create_a_database_diagram_in_access_2016.cfm">Create a Database Diagram</a></li> <li><a href="/microsoft_access/microsoft_access_2016/howto/how_to_add_a_subform_to_a_form_in_access_2016.cfm">Create a Subform</a></li> <li><a href="/microsoft_access/microsoft_access_2016/howto/how_to_do_a_mail_merge_in_access_2016.cfm">Do a Mail Merge</a></li> <li><a href="/microsoft_access/microsoft_access_2016/howto/how_to_link_an_access_database_to_sql_server_in_microsoft_access_2016.cfm">Link Access to SQL Server</a></li> <li><a style="font-style:italic;" href="/microsoft_access/howto/">More...</a></li> </ul> </li> <li> <h3 class="heading-only"><i class="fa fa-book"></i> Access Versions</h3> <ul> <li><a href="/microsoft_access/microsoft_access_2016/tutorial/">MS Access 2016</a></li> <li><a href="/microsoft_access/microsoft_access_2013/tutorial/">MS Access 2013</a></li> <li><a href="/microsoft_access/microsoft_access_2003/tutorial/">MS Access 2003</a></li> </ul> </li> <li> <h3><a href="/database/"><i class="fa fa-database"></i> Other DB Tutorials</a></h3> <ul> <li><a href="/database/tutorial/" title="Into to databases">Database Tutorial</a></li> <li><a href="/sql/tutorial/" title="Structured Query Language">SQL Tutorial</a></li> <li><a href="/sqlite/tutorial/" title="">SQLite Tutorial</a></li> <li><a href="/mysql/tutorial/" title="Open source DB management system">MySQL Tutorial</a></li> <li><a href="/sql_server/tutorial/" title="Microsoft SQL Server">SQL Server Tutorial</a></li> <li><a href="/mongodb/tutorial/" title="Document-store database for big data.">MongoDB Tutorial</a></li> <li><a href="/neo4j/tutorial/" title="Graph database management system for big data.">Neo4j Tutorial</a></li> </ul> </li> </ul> </nav> <div class="ad ad-left">  <div data-fuse="23059511712"></div> </div> </div> <div class="ads"> <div class="ad ad-right">  <div data-fuse="23059883626"></div> </div> </div> </div> <div class="searchbox-bottom"> <form action="/search/" id="cse-search-box-bottom" class="site-search"> <div> <input type="hidden" name="cx" value="partner-pub-6331358926293806:npmuvy-i8kk"> <input type="hidden" name="cof" value="FORID:10"> <input type="hidden" name="ie" value="ISO-8859-1"> <input type="text" name="q" size="30" class="site-search-input"> <button type="submit" name="sa" class="site-search-button"><i class="fa fa-search"></i></button> </div> </form> <script src="//cse.google.com/cse/brand?form=cse-search-box-bottom&lang=en"></script> </div> <footer> <p class="about"><a href="/"><i class="fa fa-home"></i> Home</a> | <a href="/about.cfm" rel="nofollow">About</a> | <a href="/contact.cfm" rel="nofollow">Contact</a> | <a href="/terms_of_use.cfm" rel="nofollow">Terms of Use</a> | <a href="/privacy_policy.cfm" rel="nofollow">Privacy Policy</a></p> <p>© Copyright 2000 - 2025 Quackit.com  </p> </footer> <script src="/common/js/spectrum/spectrum.js"></script> <script src="/common/js/lightbox2-master/dist/js/lightbox.min.js" charset="utf-8"></script> <script> $(document).ready(function(){ $( "#site-nav-toggler" ).click(function() { $( "#site-nav" ).toggle( "slow" ); }); }); </script> <script> $(function(){var a=window.location.href;$(".sidebar nav a").each(function(){a==this.href&&$(this).closest("li").addClass("selected")})}); </script> </body> </html>

CINXE.COM

Access 2016: Import Data into Access