[libreoffice-users] Re: LibreOffice Base and MySQL

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 import.by 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.
> Hank

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: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted