Date: prev next · Thread: first prev next last
2016 Archives by date, by thread · List index

Am 24.01.2016 um 09:04 schrieb Hank Alper:
Hi Ian,
I frequently import Calc Spreadsheets into my MySQL database using
phpMyAdmin.. My version of phpMyAdmin asks to locate the File you wish to
import.Specifically, it offers a Browse option. Locate the Calc file you
wish to browsing to it in your file system.
It also asks for the type of file you wish to import. One of the options is
ODF spreadsheet.That's the format of Calc, isn't it ?
No need to convert to CSV File.

Open the spreadsheet document. Ensure that your data are organized as a
normalized list where numbers are numbers, dates are dates and times are
times. Quite often and for no reason, Excel sheets are poisoned with
text data which makes them rather useless. If you can not distinguish
the data types in a spreadsheet, there is no hope anyway.

Copy the normalized list of valid data including any header row.
In the Base document which is connected to your MySQLDB click the _icon_
of a target table where you want to append the spreadsheet data and paste.
A wizard pops up where you can map the fields of the clipboard data to
the fields of your database table.

This may raise 3 types of errors:
-- referencial integrity errors
-- not nullable errors
-- wrong data type errors
Each of these error types can be answered with "Cancel" or "Ignore". The
ignore option will try to import as much as possible without raising the
same error again.

Some helpful spreadsheet formulas to test spreadsheet data for consisteency:

=MAX(LEN(column))[Ctrl+Shift+Enter] returns the max. length of a text column.
=COUNTBLANK(column) counts blanks.
=COUNTA(column) counts any values
=COUNT(column) counts numbers
=COUNTA(column)=COUNT(column) TRUE if there are only numbers.
=AND(ROWS(COLUMN)=COUNT(column);COUNTA(column)=COUNT(column) TRUE if there are only numbers and 
now blanks.
=MIN/MAX(column) min and max values of a column
=N(MATCH(value;column;0)) returns 0 if value does not occur in some other table's column (test 
referencial integrity).
=COUNTIF(value;same_column)>1 returns TRUE if there are duplicates in the same column.

=MATCH(value ; other_list ; 0) tests referencial integrity against
another table.

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.