Re: [libreoffice-users] Re: LibreOffice Base and MySQL

[Hank Alper]

Hi Ian,
If you are importing a spreadsheet into your MySql Database, you should be
able to do this without using Base.
Alexander feels it may be that we are using different versions of
phpMyAdmin. He may be right..
Two years ago I imported my Base database tables to a MySQL Database
running on a remote, headless Centos 6 VPS server. I installed phpMyAdmin
on that server and it is visible
via my browser.  Here are the versions of the software I'm running on the
*remote* Centos 6 server:

Database server
Server: Localhost via UNIX socket
Software: MySQL
Software version: 5.1.69 - Source distribution
Protocol version: 10
User: MyUserName@localhost
Server charset: UTF-8 Unicode (utf8)
phpMyAdmin
Version information: 3.5.8.2, latest stable version: 4.5.4.1
Note:
 Because my server software is on a remote Linux machine, I view phpMyAdmin
via my home computer's
 browser. Here is the Web server on that same remote machine:
Web server
Apache/2.2.15 (CentOS)
Database client version: libmysql - 5.1.69
PHP extension: mysqli Documentation
Spreadsheet
   On my home computer, I'm running Libre Office 5
I suggest you check the versions of your software against mine.

*Have you established a named database in MySQL into which you are
importing this spreadsheet ?* You know of course, you must do this before
you import anything into it.
I would suggest you prepare a small spreadsheet in Calc with perhaps 3 rows
and three named columns with test data..Keep the data simple, Use integers,
decimals and  short strings.
Save the file as a standard Calc file. *Forget CSV.* Open phpMyAdmin and
select the named database you're working on. Click on the Import tab. Enter
the path to the Calc file.or browse to its location.
Select 'Open Document Spreadsheet' from the drop down box.
The input screen will show a check box asking if you want the first row as
headers.Check that box.
Everything should be ready for you to press* GO.*

Hope this helps.
Hank





On Tue, Jan 26, 2016 at 6:42 AM, Ian "Witty" Whitfield <
edi...@federalsaints.net> wrote:

> On 01/24/2016 10:04 AM, Hank Alper wrote:
>
>> 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
>>
>> Thanks Hank
>
> But no luck!! I tried to import my ODS Spreadsheet file from LibraOffice
> and got the following error...
>
> /"You attempted to load file with unsupported compression
> (application/zip).
> Either support for it is not implemented or disabled by your
> configuration"./
>
> The file is NOT compressed or Zipped and opens perfectly in LO Calc!!
>
> So still a problem - but I appreciate the pointer.
>
> Best regards
>
> IanW
> Pretoria RSA
>
>
> --
> 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
>

-- 
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

Re: [libreoffice-users] Re: LibreOffice Base and MySQL

[Ian "Witty" Whitfield]

On 01/24/2016 10:04 AM, Hank Alper wrote:

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


Thanks Hank

But no luck!! I tried to import my ODS Spreadsheet file from LibraOffice 
and got the following error...


/"You attempted to load file with unsupported compression 
(application/zip).
Either support for it is not implemented or disabled by your 
configuration"./


The file is NOT compressed or Zipped and opens perfectly in LO Calc!!

So still a problem - but I appreciate the pointer.

Best regards

IanW
Pretoria RSA

--
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

Re: [libreoffice-users] Re: LibreOffice Base and MySQL

[Jaroslaw Staniek]

On 26 January 2016 at 12:42, Ian "Witty" Whitfield  wrote:

> On 01/24/2016 10:04 AM, Hank Alper wrote:
>
>> 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
>>
>> Thanks Hank
>
> But no luck!! I tried to import my ODS Spreadsheet file from LibraOffice
> and got the following error...
>
> /"You attempted to load file with unsupported compression
> (application/zip).
> Either support for it is not implemented or disabled by your
> configuration"./
>
> The file is NOT compressed or Zipped and opens perfectly in LO Calc!!
>
> So still a problem - but I appreciate the pointer.
>

​ODF files are compressed archives of XML files (that's why they, like
MSOOXML, are not the best containers for larger data, but this is off
topic)​. I guess you're getting the error because the web server handling
the Admin isn't fully set up. The devil is in the detail...

Hank's suggestion looks good -- apps supporting ODS would be much better
than underspecified CSV, so such
​
phpMyAdmin's import may work. However there's possible issue that such
tools need to be supplied with _range information_ of data that has to be
imported from ODS.
In absence of such, the only is guessing or heuristics, like we have in
Kexi's ODS->Table importer, by the way. If there are more possible ranges,
manual intervention may be needed, such as moving data to a simpler,
separate sheet, and that would degrade the whole automation. Sure, for
one-time import this isn't a big deal.

I am writing this to note that we sometimes lack specialized but popular
standards for exchanging data or schema.


> Best regards
>
> IanW
> Pretoria RSA
>
>
> --
> 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
>



-- 
regards, Jaroslaw Staniek

KDE:
: A world-wide network of software engineers, artists, writers, translators
: and facilitators committed to Free Software development - http://kde.org
Calligra Suite:
: A graphic art and office suite - http://calligra.org
Kexi:
: A visual database apps builder - http://calligra.org/kexi
Qt Certified Specialist:
: http://www.linkedin.com/in/jstaniek

-- 
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

[libreoffice-users] Re: LibreOffice Base and MySQL

[Alexander Thurgood]

Le 24/01/2016 09:04, Hank Alper a écrit :

Hi Hank,


> 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.

The "rub" in this useful solution is that it does indeed depend on the
version of phpmyadmin supplied with your distro.

Ian is using PCLinuxOS or PCLOS, and judging from his reply to you, it
would appear that the version supplied with that distro doesn't have the
import from ODS functionality.


Alex



-- 
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

[libreoffice-users] Re: LibreOffice Base and MySQL

[Andreas Säger]

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

Re: [libreoffice-users] Re: LibreOffice Base and MySQL

[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

On Thu, Jan 21, 2016 at 7:43 AM, Andreas Säger  wrote:

> Am 21.01.2016 um 13:20 schrieb Heinrich Stoellinger:
> > Of course these are valid values as for as CSV is concerned, but not ALL
> > are
> > valid as far as MySQL goes. I have no problem restoring MySQL-DATE COLUMN
> > values, but represent them as "2014-12-22", the same as character
> strings.
> > Forget Base at the moment and deal with the stipulations of SQL, in
> > particular
> > MySQL.
> >
> > On Thu, 21 Jan 2016 11:37:51 +0100, Jaroslaw Staniek 
> > wrote:
> >
>
> Of course, ISO dates are the one and only unambiguous standard for date
> strings. It is not too difficult to convert other date strings with 3
> numbers and 2 separators into ISO strings which can be converted to real
> date values.
>
>
> --
> 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
>

-- 
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

[libreoffice-users] Re: LibreOffice Base and MySQL

[Andreas Säger]

Am 21.01.2016 um 13:20 schrieb Heinrich Stoellinger:
> Of course these are valid values as for as CSV is concerned, but not ALL
> are
> valid as far as MySQL goes. I have no problem restoring MySQL-DATE COLUMN
> values, but represent them as "2014-12-22", the same as character strings.
> Forget Base at the moment and deal with the stipulations of SQL, in
> particular
> MySQL.
> 
> On Thu, 21 Jan 2016 11:37:51 +0100, Jaroslaw Staniek 
> wrote:
> 

Of course, ISO dates are the one and only unambiguous standard for date
strings. It is not too difficult to convert other date strings with 3
numbers and 2 separators into ISO strings which can be converted to real
date values.


-- 
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

Re: [libreoffice-users] Re: LibreOffice Base and MySQL

[Heinrich Stoellinger]

Of course these are valid values as for as CSV is concerned, but not ALL are
valid as far as MySQL goes. I have no problem restoring MySQL-DATE COLUMN
values, but represent them as "2014-12-22", the same as character strings.
Forget Base at the moment and deal with the stipulations of SQL, in particular
MySQL.

On Thu, 21 Jan 2016 11:37:51 +0100, Jaroslaw Staniek  wrote:


On 20 January 2016 at 21:34, Andreas Säger  wrote:


How can anybody solve any csv related problem without knowing anything
about
the actual content?

4 different lines of perfectly valid and commonly used csv data:

1234.98;2014-12-22
1.234,98;22 Dez 2014
1234.98;12/22/14
1234.98;22/12/14

Each line carries the exact same information. I could add a thousand more
different lines with the exact same values without leaving the conventions
of valid csv.



​Exactly, this is why I have invested in pretty a intelligent
auto-detection of data types​
​ and representation of values in Kexi​

​CSV importer.​ Yes, dates values are the most tricky.

Sure, the auto-detection has limits, importing has to be _interactive_ at
times, at least until we "know" all the options user wants to use, then the
user would be able to save and re-use an 'import specification'.
For complicated import cases it's not a matter of a custom SQL statement,
it's sometimes a trial-and-error process.

The only thing I would regret is that we have no organized FOSS effort to
share such routines and every app has own. Not yet at least.

(Ian has sent me a rich feedback off the list, work in progress)





--
View this message in context:
http://nabble.documentfoundation.org/LibreOffice-Base-and-MySQL-tp4172396p4172475.html
Sent from the Users mailing list archive at Nabble.com.

--
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








--
Erstellt mit Operas revolutionärem E-Mail-Modul: http://www.opera.com/mail/

--
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

Re: [libreoffice-users] Re: LibreOffice Base and MySQL

[Jaroslaw Staniek]

On 20 January 2016 at 21:34, Andreas Säger  wrote:

> How can anybody solve any csv related problem without knowing anything
> about
> the actual content?
>
> 4 different lines of perfectly valid and commonly used csv data:
>
> 1234.98;2014-12-22
> 1.234,98;22 Dez 2014
> 1234.98;12/22/14
> 1234.98;22/12/14
>
> Each line carries the exact same information. I could add a thousand more
> different lines with the exact same values without leaving the conventions
> of valid csv.
>

​Exactly, this is why I have invested in pretty a intelligent
auto-detection of data types​
​ and representation of values in Kexi​

​CSV importer.​ Yes, dates values are the most tricky.

Sure, the auto-detection has limits, importing has to be _interactive_ at
times, at least until we "know" all the options user wants to use, then the
user would be able to save and re-use an 'import specification'.
For complicated import cases it's not a matter of a custom SQL statement,
it's sometimes a trial-and-error process.

The only thing I would regret is that we have no organized FOSS effort to
share such routines and every app has own. Not yet at least.

(Ian has sent me a rich feedback off the list, work in progress)


>
>
> --
> View this message in context:
> http://nabble.documentfoundation.org/LibreOffice-Base-and-MySQL-tp4172396p4172475.html
> Sent from the Users mailing list archive at Nabble.com.
>
> --
> 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
>
>


-- 
regards, Jaroslaw Staniek

KDE:
: A world-wide network of software engineers, artists, writers, translators
: and facilitators committed to Free Software development - http://kde.org
Calligra Suite:
: A graphic art and office suite - http://calligra.org
Kexi:
: A visual database apps builder - http://calligra.org/kexi
Qt Certified Specialist:
: http://www.linkedin.com/in/jstaniek

-- 
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

[libreoffice-users] Re: LibreOffice Base and MySQL

[Andreas Säger]

When using HSQL (which is less powerful than MySQL) command CREATE TEXT
TABLE "Foo"(...) prepares a table "Foo" with fields and indices just
like a regular data table. A second command SET TABLE "Foo"... links the
text table "Foo" to a particular text file.
If the linked text table and the underlying csv and the target table
share the same structure with same data types in same order of columns,
I can simply drag the text table icon over the icon of the regular data
table and confirmt the import dialog without further adjustments.
If this precondition is not met or when data type conversions are needed
(e.g. text with German comma decimals and D.M.Y dates) then I create a
view to return all fields with the right types in right order and drag
the view over the data table. HSQL only accepts text data with point
decimals and ISO date/times.
With the right setup it is just a simple drag operation with no
spreadsheet involved. The setup is development work because it requires
understanding simple data types. Same problem with csv to spreadsheet
imports which will fail in most cases when you ignore the import options
because you don't understand them.


-- 
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

[libreoffice-users] Re: LibreOffice Base and MySQL

[Andreas Säger]

How can anybody solve any csv related problem without knowing anything about
the actual content?

4 different lines of perfectly valid and commonly used csv data:

1234.98;2014-12-22
1.234,98;22 Dez 2014
1234.98;12/22/14
1234.98;22/12/14

Each line carries the exact same information. I could add a thousand more
different lines with the exact same values without leaving the conventions
of valid csv.



--
View this message in context: 
http://nabble.documentfoundation.org/LibreOffice-Base-and-MySQL-tp4172396p4172475.html
Sent from the Users mailing list archive at Nabble.com.

-- 
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

[libreoffice-users] Re: LibreOffice Base and MySQL

[Andreas Säger]

Am 20.01.2016 um 11:05 schrieb Ian Whitfield:
> Hi All
> 
> I have been trying to import a CSV file from Calc into MySQL to run with
> Base for over a week now and can not believe this is so difficult to do
> so I'm hoping some other Base users might have cracked this one!!
> 
> My OpSys is PClinuxOS - 32-bit and MySQL is ver 5.1.73. Everything is
> running on localhost.
> 

Database admins don't use spreadsheets. MySQL can do this very well. Of
course, csv is not a file format. It is mere convention with millions of
different flavours.



-- 
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

[libreoffice-users] Re: LibreOffice Base and MySQL

[Alexander Thurgood]

Le 20/01/2016 11:05, Ian Whitfield a écrit :

Ian,

I would suggest opening up your Calc created CSV in a normal text
editor, to see what it "really" looks like. Only in this way will you be
able to choose the right options to import the data into mysql.

As Andreas has rightly said, you don't actually need a graphical tool to
import CSV files into mysql - the official mysql technical
documentation, if you care to read it, is really quite good in this
respect :

http://dev.mysql.com/doc/refman/5.7/en/load-data.html

Also, this tutorial gives some very handy examples of how to import CSV
files into mysql :

http://www.mysqltutorial.org/import-csv-file-mysql-table/


The error messages you are seeing seem to indicate that the data you are
trying to input doesn't correspond to the definition of that data which
is set in the database.


Alex


-- 
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