LibreOffice Base and MySQL

users
#1

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.

My CSV file is in Calc and has 2600 rows and 78 cols. I save it as a CSV file which is 717.8Kb in size. Field Names are in row 1.

I have tried many times with phpMyAdmin with no luck and today I installed MySQL Workbench - also no luck.

In all cases I get the error ...

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar(10), `NULL` varchar(10), `NULL` varchar(10), `NULL` varchar(21), `NULL` ' at line 1

MyAdmin seems to stop after only 38 fields and the error does not show the field names in the error. Also it is not MY SQL syntax it is MyAdmin's syntax!!

Workbench says - ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RecordID,Status,StatusGraphic,Title,PrefFirstName,FirstName,LastName,Maiden/Form' at line 1
Operation failed with exitcode 1.

Can anyone help me out please. I'm NOT a Database expert just a user and must get this DB up and running again ASAP!!

Many, many thanks

IanW
Pretoria RSA

#2

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

Hi Ian,
If you send me the file or at least first few rows, I may try it with Kexi
CSV Importer, which imports directly to mysql. In worst case I would send
you a mysql sql dump file as a result.
I'm interested since this helps to develop the best importer on the planet
which is the actual goal :wink:
And also to test differences/compatibility with other software such as Base.

My OpSys is PClinuxOS - 32-bit and MySQL is ver 5.1.73. Everything is

running on localhost.

My CSV file is in Calc and has 2600 rows and 78 cols. I save it as a CSV

file which is 717.8Kb in size. Field Names are in row 1.

I have tried many times with phpMyAdmin with no luck and today I

installed MySQL Workbench - also no luck.

In all cases I get the error ...

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near
'varchar(10), `NULL` varchar(10), `NULL` varchar(10), `NULL` varchar(21),
`NULL` ' at line 1

MyAdmin seems to stop after only 38 fields and the error does not show

the field names in the error. Also it is not MY SQL syntax it is MyAdmin's
syntax!!

Workbench says - ERROR 1064 (42000) at line 1: You have an error in your

SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near
'RecordID,Status,StatusGraphic,Title,PrefFirstName,FirstName,LastName,Maiden/Form'
at line 1

Operation failed with exitcode 1.

Can anyone help me out please. I'm NOT a Database expert just a user and

must get this DB up and running again ASAP!!

Many, many thanks

IanW
Pretoria RSA

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

#3

Hello

It doesn't really look like you are importing into Base as much as MySQL.
Have you tried this...

In phpMyAdmin, click the table, and then click the Import tab at the top of the page.

Browse and open the csv file. Leave the charset as-is. Uncheck partial import unless you have a HUGE dataset (or slow server). The format should already have selected “CSV” after selecting your file, if not then select it (not using LOAD DATA). If you want to clear the whole table before importing, check “Replace table data with file”. Optionally check “Ignore duplicate rows” if you think you have duplicates in the CSV file. Now the important part, set the next four fields to these values:

>Fields terminated by:,Fields enclosed by:“Fields escaped by:\Lines terminated by:auto|

Currently these match the defaults except for “Fields terminated by”, which defaults to a semicolon.

Now click the Go button, and it should run successfully.

From: http://stackoverflow.com/questions/9734486/importing-a-csv-into-phpmyadmin

Open your CSV to be sure that it matches these rules.

I haven't done this myself, hope this helps though.

#4

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.

#5

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)

#6

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.

#7

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.

#8

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

#9

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

#10

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.

#11

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

#12

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.

#13

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