svn commit - mysqldoc@docsrva: r5510 - trunk/userguide

[plavin]

Author: plavin
Date: 2007-03-22 22:45:04 +0100 (Thu, 22 Mar 2007)
New Revision: 5510

Log:
Add another section

Modified:
   trunk/userguide/excel-mysql.xml


Modified: trunk/userguide/excel-mysql.xml
===================================================================
--- trunk/userguide/excel-mysql.xml	2007-03-22 20:57:44 UTC (rev 5509)
+++ trunk/userguide/excel-mysql.xml	2007-03-22 21:45:04 UTC (rev 5510)
Changed blocks: 3, Lines Added: 163, Lines Deleted: 9; 7757 bytes

@@ -323,11 +323,11 @@
       <guilabel>Columns and Indices</guilabel> tab active. Enter the
       table name <literal>alldata</literal> in the text box at the top
       of the table editor. Refer to the values shown in
-      <xref linkend="designing-table"/>, enter a name for each
-      column, choose <literal>VARCHAR</literal> as the data type, and
-      specify a field length. You needn't worry about making any other
-      changes at this point. After all the <literal>alldata</literal>
-      table is only temporary.
+      <xref linkend="designing-table"/>, enter a name for each column,
+      choose <literal>VARCHAR</literal> as the data type, and specify a
+      field length. You needn't worry about making any other changes at
+      this point. After all the <literal>alldata</literal> table is only
+      temporary.
     </para>
 
     <para>

@@ -351,9 +351,10 @@
 </programlisting>
 
       Right click the <literal>spreadsheet</literal> database in the
-      <guilabel>Schemata</guilabel> pane and chooseRefresh Schemata the
<guimenu>Refresh
-      Schemata</guimenu> option. The newly created table should appear
-      beneath the <literal>spreadsheet</literal> database, ready for imported
data.
+      <guilabel>Schemata</guilabel> pane and chooseRefresh Schemata the
+      <guimenu>Refresh Schemata</guimenu> option. The newly created
+      table should appear beneath the <literal>spreadsheet</literal>
+      database, ready for imported data.
     </para>
 
   </section>

@@ -362,8 +363,161 @@
 
     <title>Loading the Data into a MySQL Database Table</title>
 
-    <para></para>
+    <para>
+      To import the spreadsheet data we'll use the <literal>LOAD DATA
+      INFILE</literal> syntax. Security considerations can sometimes
+      make this a frustrating exercise, so as we go, we'll try to
+      anticipate any problems that may arise.
+    </para>
 
+    <para>
+      Click on the <literal>Resultset1</literal> tab and enter the
+      following statement into the query text box:
+
+<programlisting>
+LOAD DATA INFILE
<quote><replaceable>/home/peter/Documents/spreadsheet/feb23.tsv</replaceable></quote>
+  INTO TABLE alldata;  
+</programlisting>
+
+      Windows pathnames are also specified using forward slashes rather
+      than backslashes. If you do use backslashes, you must double them.
+    </para>
+
+    <para>
+      To import a comma separated file on the Windows platform use the
+      following syntax:
+
+<programlisting>
+LOAD DATA INFILE <quote><replaceable>C:/Documents and Settings/peter/My
Documents/spreadsheet/feb23.tsv</replaceable></quote> 
+  INTO TABLE alldata 
+  FIELDS TERMINATED BY <quote>,</quote>;  
+</programlisting>
+
+      The default field terminator is a tab character so if you use a
+      different terminator you must specify it as shown in the preceding
+      statement. 
+    </para>
+    <para>
+      There are other possible pitfalls when executing a
+      <literal>LOAD DATA INFILE</literal> statement. The rules for using
+      a relative path are a bit tricky so always specify the complete
+      path to the file. Also, a data file must be readable by all. This
+      is usually not an issue under Windows and on UNIX operating
+      systems, if you need to adjust the file permissions, you can
+      readily do this using the GUI. To make a file world-readable from
+      the command prompt type:
+
+<programlisting>
+shell&gt; <command>chmod</command> 755
<replaceable>feb23.tsv</replaceable>
+</programlisting>
+
+      Finally, the user who is executing the <literal>LOAD
+      DATA</literal> statement must have the <literal>FILE</literal>
+      privilege. If you need to grant this privilege, log in as root and
+      execute the command:
+
+<programlisting>
+GRANT FILE ON *.* 
+  TO
'<replaceable>user</replaceable>'@'<replaceable>hostname</replaceable>'
+  IDENTIFIED BY '<replaceable>password</replaceable>';  
+</programlisting>
+
+      You can do this from the command line or from within Query
+      Browser.
+    </para>
+
+    <para>
+      <emphasis>Note</emphasis>: The <literal>FILE</literal>
privilege
+      is a global privilege and cannot be restricted to a specific
+      database.
+    </para>
+
+    <para>
+      So far so good, but the syntax shown to this point only works if
+      the text file is located on same system as the server. If your
+      MySQL server is remote, you must add the keyword
+      <literal>LOCAL</literal> to the <literal>LOAD DATA
+      INFILE</literal> syntax as in the following example:
+
+<programlisting>
+LOAD DATA LOCAL INFILE
<quote><replaceable>/home/peter/Documents/spreadsheet/feb23.tsv</replaceable></quote>
+  INTO TABLE alldata; 
+</programlisting>
+
+      This is not much different syntactically but servers are sometimes
+      started up with the ability to <literal>LOAD DATA LOCAL
+      </literal>disabled. If the server supports
+      <literal>LOCAL</literal>, you can start up the MySQL client with
+      the <option>--local-infile</option> option. Another approach is to
+      copy the text file to the server before executing the
+      <literal>LOAD DATA</literal> statement.
+    </para>
+
+    <note>
+      <para>
+        Further complications can ensue. For files created on a Windows
+        system, you might have to add <literal>LINES TERMINATED BY
+        '\r\n'</literal> to read the file properly, because Windows
+        programs typically use these two characters as a line
+        terminator. If you need to add this clause, it follows
+        immediately after the table name or, if a <literal>FIELD
+        TERMINTED BY </literal> clause is present, immediately after
+        this clause.
+      </para>
+    </note>
+
+    <para>
+      If you run into problems and require more information about
+      <literal>LOAD DATA INFILE</literal> refer to the manual
+      <ulink url="&base-url-refman;5.0/en/sql-syntax.html"/>.
+    </para>
+
+    <para>
+      Before you continue, paste the appropriate version of the
+      <literal>LOAD DATA INFILE</literal> statement into the script
+      window below the <literal>alldata</literal> table definition.
+    </para>
+
+    <para>
+      After executing this statement and loading the data you can check
+      that it has been copied to the <literal>alldata</literal> table
+      using Query Browser. To inspect the data, double click the
+      <literal>alldata</literal> table and find the following statement
+      in the query text box:
+
+      <programlisting>
+SELECT * FROM alldata LIMIT 0,1000
+</programlisting>
+
+      <emphasis>Note</emphasis>: A <literal>LIMIT</literal>
clause may
+      not appear when using Query Browser under Windows.
+    </para>
+
+    <para>
+      Click the <guibutton>Execute</guibutton> button and you should be
+      able to view the data in the query window.
+    </para>
+
+    <para>
+      You might want to review the integrity of the data again at this
+      point. A visual inspection is fine but you might also want to
+      automate the process with an SQL statement such as the following:
+
+<programlisting>
+SELECT * FROM alldata PROCEDURE ANALYSE(); 
+</programlisting>
+
+      (Note the spelling of <literal>ANALYSE</literal>.)
+    </para>
+
+    <para>
+      Among other things, this query shows actual minimum and maximum
+      values for data in the various fields. It any of the maximum field
+      length values equal the field length, then you have probably
+      truncated data. Empty or <literal>NULL</literal> values in some
+      fields may also indicate problems.
+    </para>
+
   </section>
 
   <section id="temporary-members-table">