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+unsubscr...@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
