"Fossies" - the Fresh Open Source Software Archive

Member "phpMyAdmin-5.1.0-all-languages/doc/html/_sources/import_export.rst.txt" (24 Feb 2021, 10213 Bytes) of package /linux/www/phpMyAdmin-5.1.0-all-languages.zip:


As a special service "Fossies" has tried to format the requested text file into HTML format (style: standard) with prefixed line numbers. Alternatively you can here view or download the uninterpreted source code file. See also the last Fossies "Diffs" side-by-side code changes report for "import_export.rst.txt": 5.0.4-english_vs_5.1.0-english.

    1 Import and export
    2 =================
    3 
    4 Import
    5 ++++++
    6 
    7 To import data, go to the "Import" tab in phpMyAdmin. To import data into a
    8 specific database or table, open the database or table before going to the
    9 "Import" tab.
   10 
   11 In addition to the standard Import and Export tab, you can also import an SQL
   12 file directly by dragging and dropping it from your local file manager to the
   13 phpMyAdmin interface in your web browser.
   14 
   15 If you are having troubles importing big files, please consult :ref:`faq1_16`.
   16 
   17 You can import using following methods:
   18 
   19 Form based upload
   20 
   21     Can be used with any supported format, also (b|g)zipped files, e.g., mydump.sql.gz .
   22 
   23 Form based SQL Query
   24 
   25     Can be used with valid SQL dumps.
   26 
   27 Using upload directory
   28 
   29     You can specify an upload directory on your web server where phpMyAdmin is installed, after uploading your file into this directory you can select this file in the import dialog of phpMyAdmin, see :config:option:`$cfg['UploadDir']`.
   30 
   31 phpMyAdmin can import from several various commonly used formats.
   32 
   33 CSV
   34 ---
   35 
   36 Comma separated values format which is often used by spreadsheets or various other programs for export/import.
   37 
   38 .. note::
   39 
   40     When importing data into a table from a CSV file where the table has an
   41     'auto_increment' field, make the 'auto_increment' value for each record in
   42     the CSV field to be '0' (zero). This allows the 'auto_increment' field to
   43     populate correctly.
   44 
   45 It is now possible to import a CSV file at the server or database level.
   46 Instead of having to create a table to import the CSV file into, a best-fit
   47 structure will be determined for you and the data imported into it, instead.
   48 All other features, requirements, and limitations are as before.
   49 
   50 CSV using LOAD DATA
   51 -------------------
   52 
   53 Similar to CSV, only using the internal MySQL parser and not the phpMyAdmin one.
   54 
   55 ESRI Shape File
   56 ---------------
   57 
   58 The ESRI shapefile or simply a shapefile is a popular geospatial vector data
   59 format for geographic information systems software. It is developed and
   60 regulated by Esri as a (mostly) open specification for data interoperability
   61 among Esri and other software products.
   62 
   63 MediaWiki
   64 ---------
   65 
   66 MediaWiki files, which can be exported by phpMyAdmin (version 4.0 or later),
   67 can now also be imported. This is the format used by Wikipedia to display
   68 tables.
   69 
   70 Open Document Spreadsheet (ODS)
   71 -------------------------------
   72 
   73 OpenDocument workbooks containing one or more spreadsheets can now be directly imported.
   74 
   75 When importing an ODS spreadsheet, the spreadsheet must be named in a specific way in order to make the
   76 import as simple as possible.
   77 
   78 Table name
   79 ~~~~~~~~~~
   80 
   81 During import, phpMyAdmin uses the sheet name as the table name; you should rename the
   82 sheet in your spreadsheet program in order to match your existing table name (or the table you wish to create,
   83 though this is less of a concern since you could quickly rename the new table from the Operations tab).
   84 
   85 Column names
   86 ~~~~~~~~~~~~
   87 
   88 You should also make the first row of your spreadsheet a header with the names of the columns (this can be
   89 accomplished by inserting a new row at the top of your spreadsheet). When on the Import screen, select the
   90 checkbox for "The first line of the file contains the table column names;" this way your newly imported
   91 data will go to the proper columns.
   92 
   93 .. note::
   94 
   95     Formulas and calculations will NOT be evaluated, rather, their value from
   96     the most recent save will be loaded. Please ensure that all values in the
   97     spreadsheet are as needed before importing it.
   98 
   99 SQL
  100 ---
  101 
  102 SQL can be used to make any manipulation on data, it is also useful for restoring backed up data.
  103 
  104 XML
  105 ---
  106 
  107 XML files exported by phpMyAdmin (version 3.3.0 or later) can now be imported.
  108 Structures (databases, tables, views, triggers, etc.) and/or data will be
  109 created depending on the contents of the file.
  110 
  111 The supported xml schemas are not yet documented in this wiki.
  112 
  113 Export
  114 ++++++
  115 
  116 phpMyAdmin can export into text files (even compressed) on your local disk (or
  117 a special the webserver :config:option:`$cfg['SaveDir']` folder) in various
  118 commonly used formats:
  119 
  120 CodeGen
  121 -------
  122 
  123 `NHibernate <https://en.wikipedia.org/wiki/NHibernate>`_ file format. Planned
  124 versions: Java, Hibernate, PHP PDO, JSON, etc. So the preliminary name is
  125 codegen.
  126 
  127 CSV
  128 ---
  129 
  130 Comma separated values format which is often used by spreadsheets or various
  131 other programs for export/import.
  132 
  133 CSV for Microsoft Excel
  134 -----------------------
  135 
  136 This is just preconfigured version of CSV export which can be imported into
  137 most English versions of Microsoft Excel. Some localised versions (like
  138 "Danish") are expecting ";" instead of "," as field separator.
  139 
  140 Microsoft Word 2000
  141 -------------------
  142 
  143 If you're using Microsoft Word 2000 or newer (or compatible such as
  144 OpenOffice.org), you can use this export.
  145 
  146 JSON
  147 ----
  148 
  149 JSON (JavaScript Object Notation) is a lightweight data-interchange format. It
  150 is easy for humans to read and write and it is easy for machines to parse and
  151 generate.
  152 
  153 .. versionchanged:: 4.7.0
  154 
  155     The generated JSON structure has been changed in phpMyAdmin 4.7.0 to
  156     produce valid JSON data.
  157 
  158 The generated JSON is list of objects with following attributes:
  159 
  160 .. js:data:: type
  161 
  162     Type of given object, can be one of:
  163 
  164     ``header``
  165         Export header containing comment and phpMyAdmin version.
  166     ``database``
  167         Start of a database marker, containing name of database.
  168     ``table``
  169         Table data export.
  170 
  171 .. js:data:: version
  172 
  173     Used in ``header`` :js:data:`type` and indicates phpMyAdmin version.
  174 
  175 .. js:data:: comment
  176 
  177     Optional textual comment.
  178 
  179 .. js:data:: name
  180 
  181     Object name - either table or database based on :js:data:`type`.
  182 
  183 .. js:data:: database
  184 
  185     Database name for ``table`` :js:data:`type`.
  186 
  187 .. js:data:: data
  188 
  189     Table content for ``table`` :js:data:`type`.
  190 
  191 Sample output:
  192 
  193 .. code-block:: json
  194 
  195     [
  196         {
  197             "comment": "Export to JSON plugin for PHPMyAdmin",
  198             "type": "header",
  199             "version": "4.7.0-dev"
  200         },
  201         {
  202             "name": "cars",
  203             "type": "database"
  204         },
  205         {
  206             "data": [
  207                 {
  208                     "car_id": "1",
  209                     "description": "Green Chrysler 300",
  210                     "make_id": "5",
  211                     "mileage": "113688",
  212                     "price": "13545.00",
  213                     "transmission": "automatic",
  214                     "yearmade": "2007"
  215                 }
  216             ],
  217             "database": "cars",
  218             "name": "cars",
  219             "type": "table"
  220         },
  221         {
  222             "data": [
  223                 {
  224                     "make": "Chrysler",
  225                     "make_id": "5"
  226                 }
  227             ],
  228             "database": "cars",
  229             "name": "makes",
  230             "type": "table"
  231         }
  232     ]
  233 
  234 LaTeX
  235 -----
  236 
  237 If you want to embed table data or structure in LaTeX, this is right choice for you.
  238 
  239 LaTeX is a typesetting system that is very suitable for producing scientific
  240 and mathematical documents of high typographical quality. It is also suitable
  241 for producing all sorts of other documents, from simple letters to complete
  242 books. LaTeX uses TeX as its formatting engine. Learn more about TeX and
  243 LaTeX on `the Comprehensive TeX Archive Network <https://www.ctan.org/>`_
  244 also see the `short description od TeX <https://www.ctan.org/tex/>`_.
  245 
  246 The output needs to be embedded into a LaTeX document before it can be
  247 rendered, for example in following document:
  248 
  249 .. code-block:: latex
  250 
  251     \documentclass{article}
  252     \title{phpMyAdmin SQL output}
  253     \author{}
  254     \usepackage{longtable,lscape}
  255     \date{}
  256     \setlength{\parindent}{0pt}
  257     \usepackage[left=2cm,top=2cm,right=2cm,nohead,nofoot]{geometry}
  258     \pdfpagewidth 210mm
  259     \pdfpageheight 297mm
  260     \begin{document}
  261     \maketitle
  262 
  263     % insert phpMyAdmin LaTeX Dump here
  264 
  265     \end{document}
  266 
  267 MediaWiki
  268 ---------
  269 
  270 Both tables and databases can be exported in the MediaWiki format, which is
  271 used by Wikipedia to display tables. It can export structure, data or both,
  272 including table names or headers.
  273 
  274 OpenDocument Spreadsheet
  275 ------------------------
  276 
  277 Open standard for spreadsheet data, which is being widely adopted. Many recent
  278 spreadsheet programs, such as LibreOffice, OpenOffice, Microsoft Office or
  279 Google Docs can handle this format.
  280 
  281 OpenDocument Text
  282 -----------------
  283 
  284 New standard for text data which is being widely adopted. Most recent word
  285 processors (such as LibreOffice, OpenOffice, Microsoft Word, AbiWord or KWord)
  286 can handle this.
  287 
  288 PDF
  289 ---
  290 
  291 For presentation purposes, non editable PDF might be best choice for you.
  292 
  293 PHP Array
  294 ---------
  295 
  296 You can generate a php file which will declare a multidimensional array with
  297 the contents of the selected table or database.
  298 
  299 SQL
  300 ---
  301 
  302 Export in SQL can be used to restore your database, thus it is useful for
  303 backing up.
  304 
  305 The option 'Maximal length of created query' seems to be undocumented. But
  306 experiments has shown that it splits large extended INSERTS so each one is no
  307 bigger than the given number of bytes (or characters?). Thus when importing the
  308 file, for large tables you avoid the error "Got a packet bigger than
  309 'max_allowed_packet' bytes".
  310 
  311 .. seealso::
  312 
  313     https://dev.mysql.com/doc/refman/5.7/en/packet-too-large.html
  314 
  315 Data Options
  316 ~~~~~~~~~~~~
  317 
  318 **Complete inserts** adds the column names to the SQL dump. This parameter
  319 improves the readability and reliability of the dump. Adding the column names
  320 increases the size of the dump, but when combined with Extended inserts it's
  321 negligible.
  322 
  323 **Extended inserts** combines multiple rows of data into a single INSERT query.
  324 This will significantly decrease filesize for large SQL dumps, increases the
  325 INSERT speed when imported, and is generally recommended.
  326 
  327 Texy!
  328 -----
  329 
  330 `Texy! <https://texy.info/>`_ markup format. You can see example on `Texy! demo
  331 <https://texy.info/en/try/4q5we>`_.
  332 
  333 XML
  334 ---
  335 
  336 Easily parsable export for use with custom scripts.
  337 
  338 .. versionchanged:: 3.3.0
  339 
  340     The XML schema used has changed as of version 3.3.0
  341 
  342 YAML
  343 ----
  344 
  345 YAML is a data serialization format which is both human readable and
  346 computationally powerful ( <https://yaml.org> ).