[euringroy]

Quote:

Actually I get 150 warnings when importing your data to MySQL 5.  See attached.  It is the same no matter if I use your (tab-delimited) txt.file, the .csv or whether I produce my own csv. or text from your .xls-file.

Thank you for doing this – the messages shown in your screen image confirm my suspicion (see previous posting) that the warnings were related to missing column data.

Quote:

But I cannot reproduce any sort of import problem with the data.  You wrote you have had a lot of trouble and have had to try various formats.  I can import the data with no problem in the first try no matter which text-format I use.

Sorry if I confused you. The only issue I was trying to solve was the invisible warnings – I was trying to get a “clean” import with no error messages. Even with the warnings, the data was still transferred into the table.

😎

[euringroy]

ANSWER: The facility to SHOW WARNINGS is provided by MySQL from version 4.1.0 onwards. Since I am using version 4.0.23 – this feature is not available and I shall therefore have to accept that I need to function without any help from it.

The pointers in the previous replies from peterlaursen provide some hints and tips on how to avoid the warnings in the first place.

[euringroy]

Peter, Thanks for your time and helpful suggestions.

Quote:

My point was that you build some SQL using OPTIONALLY ENCLOSED BY , but you don't use ENCLOSE in your data! 

As I tried to explain in my last reply, I did re-try the import without the OPTIONALLY ENCLOSED BY and got the same results – all imported OK but still the 96 warnings.

Quote:

But still I don't understand all your problems with other CSV-formats/styles.

Is it possible to zip the .xls-file as well as the .csv-file and attach it here ?

I am really not concerned about the choice of formats – I was just experimenting with a new facility I had not used before to see which format worked best. However, in view of your kind offer to look at the files, I am attaching them.

Quote:

Also not the error 1064, unless the show warnings statement is 'mixed with some garbage' from a non-empty buffer of some kind.

Yes, my real interest is in having an import process where I can see the warnings and sort out my own problems.

Quote:

Also: is there any special server setting like ANSI-mode?  What does 'Select sql_mode;' return ?

I am using a default installation with no settings overridden. The SQL statement you suggest gives the following error message “Error Code : 1054 Unknown column 'sql_mode' in 'field list'”.

[euringroy]

Quote:

I think first you should ensure that your data and your use of ENCLOSED correspond!

Good point. I've rerun with this option removed. I got identical results of 54 rows correctly imported and 96 warnings which I still cannot access!

P.S. I think

Quote:

has to be

Quote:

?

[euringroy]

peterlaursen wrote on Dec 27 2005, 10:46 PM:

did you try to execute simply “show warnings”

Yes, after reading through all the related forum postings in search of a solution, I spotted that this should be possible and tried it only to receive the exact same 1064 message!

In the course of a couple of hours, I have tried all the various ways of exporting from Excel including comma separated, strings delimited with double quuotes etc. None of these resulted in a warning-free import, and the present option of tab separated text file seems to work the best. Were there commas or other types of punctation in the fields, then quote delimited would of course have been essential.

Quote:

Local news Local news and notices is this one. two three four five or six variables?

The tabs between fields do not render very well in the forum. This is two fields, and the complete line has two less fields than there are columns in the table.

As an aside, I have also tried this import with phpMyAdmin where the same file import works OK without any warnings (using the same SQL as SQLyog).

[euringroy]

peterlaursen wrote on Dec 27 2005, 10:05 PM:

With an error 1064 you don't get any warnings!  You get an error!  Warnings occur with MySQL if the statement executes, but not a 'clean' way.  Here nothing executes and thus no warnings!  The MySQL error 1064 is a 'garbage error' that occurs when no other and better error message is available.  It is a MySQL server error – not a SQLyog error.  Read http://www.webyog.com/faq/5_8_en.html

Thank you for your reply. The data does import all 54 rows correctly but reports 96 warnings. The SQL error is not reported in response to the import process – but only on clicking the “Show warnings” button. I defer to your superior knowledge that this is reporting an SQL error but would still wish to know what these 96 warnings are! When, after the reported SQL error, the warnings panel is shown – it is blank.

This is the relevant section from the history pane:

Code:

/*[21:13:20][   0 ms]*/ load data local infile 'C:/Documents and Settings/eric/Desktop/abmenu.txt' replace into table `web2930unit`.`menu` fields escaped by '\' terminated by 't' optionally enclosed by '”' lines terminated by 'rn' ( `menu_id`, `parent_id`, `active`, `menu_name`, `description`, `url`, `image`, `role`, `domain` )
/*[21:13:31][   0 ms]*/ show warnings

Here are some lines from the file – inspection of the file with vim editor shows that there are no blanks before or after the data lines. File format is tab separated fields with newline record terminator.

Code:

1 0 Y Welcome Return to our home page default/index
100 1 Y Information Abbots Bromley Information page/information
110 100 Y Local news Local news and notices manage/news_view.php
120 100 Y Visiting Visiting Abbots Bromley page/visiting

My suspicion is that the import process is complaining that the trailing two fields on the first 48 lines are missing – since they are blank in the Excel model from which the data was exported. It would be comforting to be able to confirm this from display of the warnings if this is possible?

[Author]

Posts