Import Warnings Do Not Display

[euringroy]

I am trying to import some data from Excel and encountering errors which are reported by the “import data from CSV…” process.

However, when I click on the “Show warnings” button in the “CSV Import Result” dialogue, instead of the warnings being displayed, I receive the following error message – “Error No. 1064. You have an error in your SQL syntax….”

I am running SQLyog Pro v 5.0 with MySQL version 4.0.23-nt on Windows XP with service pack 2 and all recent updates applied.

As this is my first attempt at import with SQLyog, there may be configuration issues?

Note that I am not bothered about getting import errors – if I can get the warning messages displayed they can be resolved. The issue is not being able to view the warnings! 😮

Thanks for any help.

[peterlaursen]

Quote:

Error No. 1064. You have an error in your SQL syntax….

and

Quote:

The issue is not being able to view the warnings!

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

Quote:

As this is my first attempt at import with SQLyog, there may be configuration issues?

There are lots of configuration issues with CSV! Read about SQLyog and CSV here: http://www.webyog.com/faq/28_73_en.html

On the HISTORY tab you can see what SQL was actually did send to MySQL. Could you paste that statement in with a few lines of data?

More Q's:

Also are there some 'header-lines' in the file ?

Is comma or semicolon used as seperator ?

If you press the 'Change' button in CSV-dialogue what does it look like?

Did you create the table structure in advance and do filed definitions match the data?

It is much simpler in my opinion to open the .xls file from Access and use ODBC. But that probably is a matter of taste too.

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

[peterlaursen]

OK …

did you try to execute simply “show warnings”

I did not get this point before:

Quote:

The SQL error is not reported in response to the import process – but only on clicking the “Show warnings” button.

When you click the show warning button SQLyog sends simply “show warnings”. you can see that from history tab. This should not trigger an error 1064 … hmmm

however 'optionally enclosed by '”' ' means that strings and only strings in the data are enclosed with ” (doublequote). That is not as your data are, I guess.

Now have a look at

Quote:

Local news Local news and notices

is this one, two three four five or six variables ?! You should at least enclosed string-data, I think like

Quote:

“Local” “news” “Local” “news” “and” “notices”

or

“Local news” “Local news and notices”[/QUOTE].

But still 'show warnings' should not trigger error 1064!

[peterlaursen]

BTW: what happened to the QUOTE tag ?

[peterlaursen]

“Optionally enclosed by” according to MySQL docs:

Code:

FIELDS [OPTIONALLY] ENCLOSED BY controls quoting of fields. For output (SELECT … INTO OUTFILE), if you omit the word OPTIONALLY, all fields are enclosed by the ENCLOSED BY character. An example of such output (using a comma as the field delimiter) is shown here:

“1”,”a string”,”100.20″
“2”,”a string containing a , comma”,”102.20″
“3”,”a string containing a ” quote”,”102.20″
“4”,”a string containing a “, quote and comma”,”102.20″

If you specify OPTIONALLY, the ENCLOSED BY character is used only to enclose values in columns that have a string data type (such as CHAR, BINARY, TEXT, or ENUM):

1,”a string”,100.20
2,”a string containing a , comma”,102.20
3,”a string containing a ” quote”,102.20
4,”a string containing a “, quote and comma”,102.20

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

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

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:

?

[peterlaursen]

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

At least this should correspond! It might also very well remove the warnings, I think.

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'll look into in tomorrow then (it is late night here!)

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

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

[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'”.

[peterlaursen]

What next …

If you execute the SQL simply without using SQLyog GUI, but simply by typing/pasting the SQL. Do you get the same number of warnings? Can you now 'show warnings' ?

Next type (or copy from SQLyog SQL-pane and paste) into MySQL command-line client. What happens here?

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

[peterlaursen]

Sorry about the missing 'show warnings' in 4.0. I should have checked it.

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.

However I think there is a small issue with SQLyog here. It does not show all 150 warnings but only about 50. Some buffer running full? Or is a buffer-setting with the server?

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.

I also can open the .xls in Access and export using ODBC. Actually that was what I did in the first place to be sure to get the columns definitions right. Next I can empty the table and import any .csv/.txt format to that table.

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

😎

[Author]

Posts