Import and export

Import

To import data, go to‍≤• the “Import” tab in phpMyAdmin. To import π$$data into a specific database o"✘α§r table, open the database or table befoδβ↓re going to the “Import” tab.

In addition to the standard Import and   αβExport tab, you can also import an SQL file di™≈ rectly by dragging and dropping it from your l$→ocal file manager to the phpMyAdmin inter‌→♦face in your web browser.

If you are having troubles importing big files,£€ please consult 1.16 I cannot upload big dump files (me​¶‍mory, HTTP or timeout problems)..

You can import using follo≈‌wing methods:

Form based upload

Can be used with any supported format, al✔•✘→so (b|g)zipped files, e.g₩≤♥<., mydump.sql.gz .

Form based SQL Query

Can be used with valid SQL dumps.

Using upload directory

You can specify an upload directory on youΩ☆​r web server where ph¶♥ pMyAdmin is installed, after uploadiπ‌®ng your file into this directory ✘" you can select this ‌γ¶∑file in the import dialog o‌™σ∑f phpMyAdmin, see $cfg['UploadDir'].

phpMyAdmin can import from several various comm&φ↓only used formats.

CSV

Comma separated values format which is o→©ften used by spreadshe∞≈​ets or various other progr✘&‌ams for export/import.

Note

When importing data into a table from a β✔φ₩CSV file where the table has an ‘auto_increment’ ≤> ♥field, make the ‘auto_increment’ va α€lue for each record in the CSV fiel↔≥÷d to be ‘0’ (zero). Thisγ→∞ allows the ‘auto_increment’ f₹​ield to populate correctly.

It is now possible to import §Ωa CSV file at the server or database le£‌'αvel. Instead of having to create a ta ∑ble to import the CSV file into, a best-fit struc♥★∑≠ture will be determined for you and the   ↓data imported into it, instea φd. All other features, requirements, an§ε∑↔d limitations are as before₹>.

CSV using LOAD DATA

Similar to CSV, only using the interna★® ¶l MySQL parser and not the phpMyAdmin ₹§σone.

ESRI Shape File

The ESRI shapefile o €β¶r simply a shapefile is a popularφ±‍ geospatial vector data format for geogγ↔∏Ωraphic information sys¥Ω₩tems software. It is develope™♣∏d and regulated by Esri aβ$s a (mostly) open spπ ‌ecification for data inteε♦✔•roperability among Esri and other software producσ∞​ts.

MediaWiki

MediaWiki files, which can b₽∏<↑e exported by phpMyAdmin (version 4.0  δ∑‍or later), can now also ®‍be imported. This is≈₹ the format used by Wikipedia to‌£ display tables.

Open Document Spreadsheet (ODS)

OpenDocument workbooks containing one or more∏αα• spreadsheets can now be directly ∑∏<imported.

When importing an ODS spreadsheet, the≈≤&₽ spreadsheet must be named in a speci✔£fic way in order to make the import §₩→as simple as possible.

Table name

During import, phpMyA¥ dmin uses the sheet name as the table name→"✔©; you should rename the sheet in your sp∞α♦€readsheet program in order to match your exist★→‍♠ing table name (or the table you wish to create≥≠•, though this is less‌∑® of a concern since you coul≤☆d quickly rename the new table fr☆∑★om the Operations tab).

Column names

You should also make the firsπφt row of your spreadsheet a header ≈♠×≥with the names of the col ↕umns (this can be accomplished by insertε∞ing a new row at the top of your s≤∏₩preadsheet). When on the Import s"✘creen, select the checkbox for “The¥α$₹ first line of the file contains¥↔€ the table column names;” this way λ↑↓εyour newly imported data will go to the proper γδ↕columns.

Note

Formulas and calculations will♥♣γ NOT be evaluated, rather, their value from↔÷☆ the most recent sav®σe will be loaded. Please ensure that all valuλ¥¥←es in the spreadsheet are as needδεed before importing it.

SQL

SQL can be used to make any manipul€§σ©ation on data, it is also useful forφ₩× restoring backed up data.

XML

XML files exported by phpMyAdmin (versiσλon 3.3.0 or later) can now be imp♠"↓orted. Structures (databases, tables, views, tri€"£ggers, etc.) and/or data will  'π¶be created depending on the contents of the f§ε•©ile.

The supported xml schemas are not €€yet documented in this wiki.

Export

phpMyAdmin can export into text files (even☆♦±₹ compressed) on your local disk (or a sp™₹γecial the webserver $cfg['SaveDir'] folder) in various commonl™ ₹εy used formats:

CodeGen

NHibernate file format. Planned £&×versions: Java, Hiber→↓nate, PHP PDO, JSON, etc. So the pr∏₩π eliminary name is codegen.

CSV

Comma separated values format wε>↕"hich is often used by spreadsh★§♣eets or various other programs for export/import.σ♦>

CSV for Microsoft Excel

This is just preconfig ₽σured version of CSV export which can be im♥​ported into most English versions of M$♥γicrosoft Excel. Some localised'<§& versions (like “Danish”) are expecting “;” ins↔♠tead of “,” as field sep↔↔arator.

Microsoft Word 2000

If you’re using Microsoft WordΩ∑>  2000 or newer (or compatible such as OpenOffice<♣βλ.org), you can use this export.

JSON

JSON (JavaScript Object ↑≤Notation) is a lightweight data-interchang★✔×δe format. It is easy for humans to read a‍↑nd write and it is eas✔$<y for machines to parse and™¶‌ generate.

Changed in version 4.7.™"0: The generated JSON structure has be>∞<×en changed in phpMyAd ↑×min 4.7.0 to produce vπ€☆alid JSON data.

The generated JSON is list o≥φ÷αf objects with following attributes:

type

Type of given object,φ→ can be one of:

header

Export header containing comment and phpMyAd λmin version.

database

Start of a database marker, containing£‌© name of database.

table

Table data export.

version

Used in header type and indicates phpMyAdmin version.

comment

Optional textual comment.

name

Object name - either table or d♦₩βatabase based on type.

database

Database name for table type.

data

Table content for table type.

Sample output:

[
    {
        "comment": "Export to JSON plugin fo•"r PHPMyAdmin",
        "type": "header",
        "version": "4.7.0-dev"
    },
    {
        "name": "cars",
        "type": "database"
    },
    {
        "data": [
            {
                "car_id": "1",
                "description": "Green Chrysler 300"±α',
                "make_id": "5",
                "mileage": "113688",
                "price": "13545.00",
                "transmission": "automatic",
                "yearmade"☆α♣©: "2007"
            }
        ],
        "database": "cars",
        "name": "cars",
        "type": "table"
    },
    {
        "data": [
            {
                "make": "Chrysler",
                "make_id": "5"
            }
        ],
        "database": "cars",
        "name": "makes",
        "type": "table"
    }
]

LaTeX

If you want to embed table data or structu≥≈αre in LaTeX, this is right choice for you.

LaTeX is a typesetting system that is very s®‌↔€uitable for producing scientific a&γnd mathematical documents of high typographic€§al quality. It is also suitable for produ™≠cing all sorts of ot‌< →her documents, from simple letters to com≥ plete books. LaTeX uses TeX as its formatting ​♠★engine. Learn more about TeX and LaTeX on the Comprehensive TeX Archive Network also see the short description od TeX.

The output needs to be embe♥‌dded into a LaTeX doc₹λ÷∏ument before it can be rendered, for example in‌"≈ following document:

\documentclass{article}
\title{phpMyAdmin SQL output}
\author{}
\usepackage{longtable,lscape}
\date{}
\setlength{\parindent}{0pt}
\usepackage[left=2cm,top=2cm,right=2cm,nohe☆∞ad,nofoot]{geometry}
\pdfpagewidth 210mm
\pdfpageheight 297mm
\begin{document}
\maketitle

% insert phpMyAdmin LaTeX DuΩ∏¥÷mp here

\end{document}

MediaWiki

Both tables and databases can be exp≈φ ​orted in the MediaWiki format, which isφ☆ used by Wikipedia to disp​÷lay tables. It can export stru<₩φ☆cture, data or both, including table na¶™£mes or headers.

OpenDocument Spreadsheet

Open standard for spreadsheet data↑σ•, which is being widely adopted©♥€←. Many recent spreadshe≈×λet programs, such as LibreOffice, OpenO'♦"ffice, Microsoft Office o‍∏§r Google Docs can handleπ£÷§ this format.

OpenDocument Text

New standard for text data w₹'hich is being widely adopted. Most recent word ☆α processors (such as LibreOffice, OpenOffic‍¶ £e, Microsoft Word, AbiWord or KWor♦εd) can handle this.

PDF

For presentation purposes, non>  editable PDF might be best choice for you.

SQL

Export in SQL can be used to rest±§α↓ore your database, thus it is♠§ useful for backing ∏♥‌up.

The option ‘Maximal length ofδ‍ created query’ seems δλ¥ to be undocumented. But experiments has≥Ω shown that it splits large ex $tended INSERTS so each one is no bigger than ±€☆✔the given number of bytes (or charac∑®ters?). Thus when importingφφ♥ the file, for large tables you avoid the errπ®or “Got a packet bigger than ‘max_allowed∑↑↕_packet’ bytes”.

See also

https://dev.mysql.com/doc/refman/5.7/en/packet-to≥'o-large.html

Data Options

Complete inserts adds the column names t₩♦§o the SQL dump. This parameter improves the re♠‌™≈adability and reliability φ>αof the dump. Adding the column names inc"≈↕reases the size of the dump,δ♥ but when combined with Extended ins ∑≠$erts it’s negligible.

Extended inserts combines multiple rows of data into a±×•↔ single INSERT query. This ↕​will significantly decrease filesiz™Ωe for large SQL dumps, σ₹increases the INSERT speed ✘∏φ±when imported, and is generally recomme∑♣nded.

Texy!

Texy! markup format. You can ☆™see example on Texy! demo.

XML

Easily parsable export for use wit&←∏φh custom scripts.

Changed in version 3.3.0: The XML schema used has changed as of version 3.3©'∏‌.0

YAML

YAML is a data serialization format which is©‌ both human readable and computation×βally powerful ( <https://yaml.org> ).