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

I did something similar recently. You might succeed if you change the
CSV options: line separator LF, enclose strings in ", field separator ;

If I remember rightly, it did not cope well with field separator ,

Cheers
Harvey

#3

Hello Ian,
I am somewhat puzzled by the error message you get:
It seems to me that you are defining multiple columns with the name `NULL`.
To MY knowledge column names should be unique withing one table. Also NULL is
used to specify whether the value of a column may be NULL (i.e. "unknown") or
not - with NULL (=yes) as the default. So if you don't allow a value to be
NULL you have to say (e.g.) colName integer not null default 0...
Maybe you could send me your table definition and I will have a look at it.
Regards from snowed in Salzburg to certainly much warmer Pretoria...
H. Stoellinger

#4

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.

#5

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

#6

Hello again,
Can you use the LOAD DATA command under the mysql command client? You should
be able to fire this command also under phpmyadmin.
like so...

#7

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

#8

Hello again, Ian,
FIELDS ENCLOSED BY '"' does of course only apply to character type fields
(CHAR, DATE,...), not to numeric values.
mysql command client should read mysql command line client...
Regards
H.S.

#9

Hello,
Maybe I don't understand your problem, but...
LO lets you store your calc-spreadsheet as a csv-file. I have just tried that out.
The format is just fine for then loading into a table as I described earlier.
Regards
H.S.

#10

Maybe this a really stupid question: but - have you defined the table before trying to
upload the csv-file ? The LOAD DATA procedure I mentioned before assumes that a correctly
defined table (CREATE TABLE tablename (col1,col2,...)) with the correct data types of your
columns exists in the DB.
Regards
H.S.