Problem Importing Csv Data – Error 1265 – Want Null

[marcm]

Hi, I'm new here, but I've been using SQLyog for some localhost development for a little while now. I really enjoy the software, and now that I'm going live soon I plan on upgrading so I can use the more advanced features.

I am having a problem importing CSV data that I've exported locally and I'm trying to import into my Live DB. I can't get the import to recognize my NULL values. It converts them to 0, etc. I get an error 1265 – data truncated error. I've read anothe post here that says it's a MODE issue or something, but I'm still unsure about how to fix this problem. I was using 4.1 of SQLyog, but I upgraded today to 5.2 – although this hasn't fixed this problem. Can anyone give me a clear solution to this problem?

Thanks a bundle…

[peterlaursen]

There are two ways to import CSV:

1) You may import into a table from the table menu

2) or you might use the Migration 'powertool' with the Microsof text-ODBC-driver.

Which one do you use?

You are sure that NULLs are not made ZERO when you generate the CSV? Did you inspect the file?

And are the NULL's quoted like “NULL” or 'NULL' and how are your settings?

From which program do the data come?

You are also sure that the column is not defined as 'NOT NULL' ?

Could you post here:

* a small CSV-file with such NULLs

* the corresponding SQL-dump after import

* a screenshot of the CSV-import settings (if you use the import tool)

(zip it altogether in one file)

BTW: we have a FAQ:

http://www.webyog.com/faq/28_73_en.html

and BBTW: there may be more efficient ways than using CSV-format – but all depends on what is the source.

[marcm]

Wow, hello there, thanks for your reply. Here's what I can tell you:

1. I was trying to import through the table menu.

2. The CSV file was made by SQLyog! I was trying to export the data from my local MySQL and then import it into my Live MySQL database – both through SQLyog.

3. Here is an example of a line from the CSV file generated by SQLyog:

1;1;\N;\N;”Architecture”;2005-09-21;2006-05-26;1;6

I tried many different settings, and some wouldn't import at all. This line is from my last attempt.

Fields terminated by ; Optionally enclosed by “, Escaped By \ , Lines Terminated by n

I used these settings for both export and import.

I tried with column names at the top and without.

So, at this point I switched to using SQL insert statements generated by SQLyog, but I can't 'replace' or 'ignore' rows as far as I know.

Does the free trial of the enterprise version let me experience the data synch functions? I plan on upgrading, but it would be nice to see that working first.

Also of note, since I upgraded to 5.2 from 4.1 I notice that double clicking on a field in my result set (especially if NULL) yields strange and unpredictable behavior. Like the editable part appears to the right or the result set jumps around – it never did that before! Maybe I'll revert back to 4.1…

Thanks for your help!

[Ritesh]

Quote:

Does the free trial of the enterprise version let me experience the data sync functions? I plan on upgrading, but it would be nice to see that working first.

Yes. The trial version is fully functional for 30 days.

Quote:

Also of note, since I upgraded to 5.2 from 4.1 I notice that double clicking on a field in my result set (especially if NULL) yields strange and unpredictable behavior. Like the editable part appears to the right or the result set jumps around – it never did that before! Maybe I'll revert back to 4.1…

This is strange. We have never been reported something like this before. Can you attach your table structure and sample data to reproduce the issue? Screenshot will also be helpful.

[Ritesh]

Regarding your CSV export/import issue, can you attach your table sturcture out here with sample data. We will export and try to import at our end to see if there is any issue.

Also, can you tell me which version of MySQL are you using for exporting/importing?

[peterlaursen]

Regarding the NULL issue with CSV …

This is an example of a line in a CSV file generated by SQLyog

“1”,”Admin”,\N,””,”529″

— note that NULL is written as \N

— even if I check 'optionally' numbers like 529 are enclosed! They should not.

Are they bugs with SQLyog or have the OUTFILE parameters changed with MySQL 5 ??

and “\N” import as the string “N” for instance

the line should simply read like

1,”Admin”,NULL,””,529 — and it import easy

but it is not possible to generate such line, because the 'optional' is ignored and NULL becomes an escaped N.

Why the hell write an escaped N for NULL ???? 😮

This was tested with MySQL 5.0.21.

Something went wrong with the the introduction of 'fill Excel frienly values' of 5.0 ???

[peterlaursen]

Tested with MySQL 4.0 and old SQLyog versions as well.

Same result – NULL becomes an escaped N like \N and imports as 'N' to a char/varchar field whether quoted or not.

When imported to a numerical field it becomes 0 (sero), unless some strict SQL-mode is used .. if so an error will occur.

But actually I am in doubt whether the CSV format is fit for handling NULLs at all ??

This

1,”Admin”,NULL,””,529

and

1,”Admin”,NULL,””,NULL

… would work for 'load data infile' with MySQL, but what happens if you open it in Excel?

Excell does not know about NULL .. it is inserted as a text-string, I believe

And now I CAN make it read the 'optional' checkbox. My head on vacation or a GUI bug that does it does not always check for it – with certain combinations of/sequences of operations? But the 'N' still is a mystery to me!

[marcm]

Here's the image of the edit field error. I'm running 5.2 on Win2K. I didn't have this problem with 4.1. It also inserts a new row without me asking it to at times. Note that I'm using MySQL 4.1 one Win2k. Below the image is a sample table structure for the CSV problem..

Note that the fields that are highlighted with a black border are the fields I double clicked on to edit…

CREATE TABLE `categories` (

`categories_id` int(4) unsigned NOT NULL auto_increment,

`pr_types_id` int(3) default NULL,

`cat_name` varchar(32) NOT NULL default '',

`cat_date_added` date default NULL,

`cat_date_modified` date default NULL,

`cat_status` tinyint(1) unsigned NOT NULL default '0',

`cat_item_count` int(11) unsigned default NULL,

PRIMARY KEY (`categories_id`),

KEY `categories_index2` (`pr_types_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

[peterlaursen]

“I'm running 5.2 on Win2K”

Do you mean that you are running SQLyog 5.12 on Win 2K?

On Win2K we always need more info! SP-level?, a 2K-server or a 2k-'ordinary'? There are so many 2k-variants …

“I didn't have this problem with 4.1.”

Exactly what problem did you not have with (SQLyog) 4.1?

Do you mean that the 'mouse-cursor' and the 'keyboard-cursor' is not synchronzied, so that keyboards writes in another cell than mouse selects?

This is what I understand from your pictures.

No other program does similar things? Spreadsheet programs for instance?

.. my first inpression is that your system must be sick ….

How goes the CSV-thing?

[marcm]

peterlaursen wrote on May 27 2006, 03:25 PM:

“I'm running 5.2 on Win2K”

Do you mean that you are running SQLyog 5.12 on Win 2K?

On Win2K we always need more info! SP-level?, a 2K-server or a 2k-'ordinary'? There are so many 2k-variants …

“I didn't have this problem with 4.1.”

Exactly what problem did you not have with (SQLyog) 4.1?

Do you mean that the 'mouse-cursor' and the 'keyboard-cursor' is not synchronzied, so that keyboards writes in another cell than mouse selects?

This is what I understand from your pictures.

No other program does similar things? Spreadsheet programs for instance?

.. my first inpression is that your system must be sick ….

How goes the CSV-thing?

Uh, no, my system is far from sick. It's actually rock solid. I run Service Pack 2. It's ordinary, not Server.

Yes, you nailed it, I did not have the keyboard curser error in 4.1 or in any other software for that matter. (spread sheets included) I run a variety of software and I have no probs, this is an isolated issue. I've given up on the CSV problem for now. I also have reverted back to 4.1 for now and the cursor prob is gone.

Thanks for your help and interest.

[peterlaursen]

hehe .. nobody readily admits that their system is 'sick' <_< We have two issues to discuss. 1) SQLyog CSV-export with NULL values:

Could that be done better?

Is this a MySQL issue or a SQLyog issue?

Or will it never be possible to get a reasonable CSV-export with NULL values?

I think Ritesh should comment on it. I know exactly what the program does when it imports CSV but not exactly what it does when exporting CSV!

2)

The display issue with mouse-cursor and keyboard-cursor not 'synchronizing'

Is it possible that you could try reinstall your graphics drivers?

Do it the 'tough way:

1) Boot into VGA-mode

2) Uninstall graphics drivers

3) Boot into VGA-mode

4) Install graphics drivers and configure the display

5) Boot normally.

BTW:

What is you graphics system (graphcis card, driver version) etc?

Newer driver available?

When installing 5.x you don't need to overwrite 4.1. Just install to another folder.

[Ritesh]

marcm wrote on May 27 2006, 09:23 PM:

Here's the image of the edit field error. I'm running 5.2 on Win2K. I didn't have this problem with 4.1. It also inserts a new row without me asking it to at times. Note that I'm using MySQL 4.1 one Win2k. Below the image is a sample table structure for the CSV problem..

While we are working diligently to solve the CSV issue, we are not able to reproduce the display issues on any machine in our office ( tried on 10 at least )

Are you using dual monitors by any chance?

[Ritesh]

marcm wrote on May 27 2006, 12:17 AM:

I was using 4.1 of SQLyog, but I upgraded today to 5.2 – although this hasn't fixed this problem. Can anyone give me a clear solution to this problem?

Was your CSV export issue *NOT* present in v4.1?

This bug should be there for v4.x to the latest one.

[marcm]

Ritesh wrote on May 29 2006, 05:50 AM:

Was your CSV export issue *NOT* present in v4.1?

This bug should be there for v4.x to the latest one.

Hi guys,

The CSV problem exists in both versions for me. However, the cursor prob doesn't exist in 4.1. I would think that if it were a problem with my system, then it would show up in 4.1 also.

[peterlaursen]

1)

CSV-issue is fixed in BETA4 (I just tested it 1 hour ago). Just use the latest link from the *News Section*.

And let me explain: to generate the CSV/text fil SQLyog simply executes

“SELECT .. into outfile … (optionally) enclosed by … terminated by .. escaped by …”

If escaped by is '' (empty string) it writes NULL values as 'NULL' (without quotes) to the file.

If escaped by is anything else (try ^ for instance) NULL values are written as 'N' (also without quotes) what is also understood by LOAD DATA INFILE.

You see very little is done by SQLyog itself here – it just executes MySQL standard commands. But there was a bug that is now fixed.

Now a small improvement with beta 4 is that no matter if you enter '' or '\' as escape character, SQLyog now does the same: it uses '' as the escape character. What is sent to MySQL is ” … escaped by '\'” – and as '\' is an escaped '' it will use '' as the escape character. Just as you will have to send “INSERT … 'backs\ash' …. ” to the server to insert the string 'backsash'. 🙂

It has confused 'newbie' users a lot over the years that the escape character '' itself also must be escaped like '\' in the 'Esport as CSV' settings dialogue. As of Beta 4, you may escape it or not – or put another way both '' and '\' in the settings dialogue makes SQLyog use the escape character ''. It sends ” … escaped by '\'” to MySQL.

2)

We have absolutely no reports of similar screen-issues with any 5.x. And 5.0 is soon one year old and has been downloaded by 100.000's of users. I still suggest you try updating/reinstalling graphics drivers. And updating Win2k to at least SP3 is probably a good idea too .. for several reasons – including security.

Are you using some (IMHO crap) hardware like Logitech replacing the original Windows drivers? Do you have the chance to try with the most simple hardware – no bluetooth, no USB, no wireless. Plain cabled PS2 with original Microsoft drivers!

We need a detailed information of graphics card, mouse and keyboards (hardware as well as drivers) if we shall have a chance to go further into this.

[Ritesh]

http://www.webyog.com/forums/index.php?sho…=0&gopid=10336&#038;

[marcm]

Hi again Peter,

I understand why you suggest reinstalling the video drivers, but that still doesn't explain why it works fine in 4.1. I would think that if it were as simple as a video driver, then it would affect all versions. Anyway, I haven't tried reinstalling anything because I'm in production at the moment and I don't have the time or actual need to mess with it right now.

As for my version of Win2k, I do have all the security patches. I haven't upgraded because I have some very expensive 3d software that I run on this box that could have problems if I upgrade (unless I also upgrade the 3d software, which costs big $$ and I'm happy with the system as it is). I've been in computing for a long time and one thing that I've learned is that chasing hardware and software can be an expensive and time consuming thing. I leapfrog technology when necessary, but if I have a stable system that does what I need it to do, then I try to use it as long as I can. I have a second machine that I use for rendering and audio production, and I might be able to try this software on that box.

As for my hardware profile, I am using a Logitech cordless optical mouse. I've been really happy with this mouse, so I wouldn't exactly call it crap. But Logitech does make some crap, and most manufacturers have made crap, Microsoft included. I'm using a GeForce Nvidia card with Nvidia drivers. Now again, I would like to point out that I have all sorts of applications, from high end 3d, animation and graphics software, web developments apps, word, excel type apps, other db design apps, etc. I could go on. I have had no problem with any piece of software whatsoever. So, if I look at that record, and then look at the problem I have with the cursor, my guess is that it's the software – especially since 4.1 works like a charm. Something must have changed in the software.

However, having said all that, if I have some time, I will reinstall the latest version in a new directory and I'll see if I can experiment with some drivers, etc.

[peterlaursen]

I think the information that is Logitech + nvidia is important.

And of course I agree that if no other program does similar things we should at least be able to spot the origins of it. But we could not possibly without information.

Do you have a chance to plug in the most simple mouse (PS2 – wired) , and reboot and see if the problems persists. If not – then we have some 'thread' to follow in our attempt to reproduce (and fix if there is something to fix).

When I used the word 'crap' it was in some humoristic sense. The produts are fine in many respects (hardware quality, ergonomics) – but there are two many driver issues!

I have been a supporter at one of the biggest and most welknown IT-forums in my Country (Denmark) for some years before starting here. And I think that I have about 50 examples of 'weird' display issues with Logitech wireless products. In one situation the order of installation of mouse and keyboard made the difference. It worked if one was installed first – not if the other was installed first.

[marcm]

Hello again!

I finally got around to reinstalling the enterprise trial because I'd like to test the SSH feature before I buy. This time I made sure my system was fresh, with nothing running at all. I installed into a new directory so I still have 4.1 running. Unfortunately, I'm still getting some cursor probs with the new version. Also, I wanted to point out that the general response of the table data window, when clicking on fields, is very slow/sluggish compared to the zip of 4.1. For example, when I click on a field, it sometimes takes a second for the field to become active. Also, I've noted some other interesting behavior that you might find handy. Say if I double click a field in row 3 of a table, if the cursor does jump to the right, I also notice that the scroll bar at the bottom of the table data window moves as if it's adjusting the position of the selected field. But for some odd reason, the rest of the fields don't always move with the scroll bar and the selected field. I've also noticed different behavior if I use the enter button instead of the double click. When I use the enter button, the rest of the window scrolls properly and the field is high-lighted properly.

Here's another strange thing I've found – and that is if I select row 1 and then select a field, when I hit enter, instead of selecting that field, it moves to the next field. Where if I'm on row 2 for example, the same enter key stroke selects and high-lights the proper field. That is two totally different behaviors with the same key stroke on two different rows. That seems to be software related and not video card driver related. Maybe you will be able to repeat that? Also, I think that if you can prevent the window from scrolling when selecting a field, then you would probably eliminate this cursor issue altogether. Something to consider. In fact, that may be the difference with 4.1, because I don't see it jump around much when I select a field. Finally, when selecting fields in the newer version, sometimes it creates a new empty row for no apparent reason, I don't recall having that problem with 4.1.

I hope that this is helpful for you guys. I really love this software, so keep at it…

[peterlaursen]

1) I agree that the DATA tab still is too slow. I think it must be rewritten from scratch. There are more reasons. BTW: What is the exact SQLyog version that you are using?

2) To reproduce the different issues could you provide a sample table? It will be easier to discuss this if we have the same data.

3) I still ask if you to try a very simple mouse! How much of this will you still get then? No chance to borrow one?

[marcm]

I'm using 4.1 and 5.13 Enterprise trial edition. Yes, I'll provide you with a sample table – I'll try to do this in a bit.

As for the mouse, I'll see if I can track one down. My bro might have one. I have several, but they are all in storage right now and I won't be getting there for a while unfortunately. I'll check with my brother tomorrow on the mouse thing.

[Ritesh]

@marcm:

Are you sure that you using 5.13 FINAL?

The speed issue has been fixed in 5.13 FINAL. Check a similar post at:

http://www.webyog.com/forums/index.php?showtopic=2186

We are working on the other issues that you have mentioned.

[peterlaursen]

@Ritesh …

the problem is that there are more versions named 5.13 … so how should marcm be able to tell?

We have been discussing this before, but I will repeat that there should NEVER two not-identical pieces of code having the same ID. Even when inserting a single linebreak or a SPACE-character it is a new version. You can call them 5.13a, 5.13b etc. then. Because this is a mess!

And btw. it is still much faster from RESULT tab than from DATA tab.

[marcm]

Ritesh wrote on Jun 7 2006, 12:24 AM:

@marcm:

Are you sure that you using 5.13 FINAL?

The speed issue has been fixed in 5.13 FINAL. Check a similar post at:

http://www.webyog.com/forums/index.php?showtopic=2186

We are working on the other issues that you have mentioned.

Well, I got it from this link:

Enterprise Trial Edition: http://www.webyog.com/betas/SQLyog513EntTrial.exe

Is this the correct link?

[peterlaursen]

This is from a BETA repository after all.

It may depend on WHEN you downloaded it.

Please try from here:

http://www.webyog.com/sqlyog/download_sqlyogent.html

[Ritesh]

We plan to provide a BUILD info too from later releases. This will help in curbing all the different binary confusion.

[marcm]

I figured that – since the directory is 'beta' 🙂 I downloaded it at 8pm last night.

[peterlaursen]

regarding the speed of inserts from DATA TAB:

Try 5.14 beta2. It is much faster here than was any 5.12 or 5.13 (( don't remember the ones before)

[peterlaursen]

Now look here!

I have a similar thing here with this Forums database itself!

I click the 'created_data' column and data pops up somewhere else!

@Ritesh – it is the 'phpbt_auth_user' table!

@marcm: can we have your database schema?

[marcm]

peterlaursen wrote on Jun 7 2006, 03:48 PM:

Now look here!

I have a similar thing here with this Forums database itself!

I click the 'created_data' column and data pops up somewhere else!

@Ritesh – it is the 'phpbt_auth_user' table!

@marcm: can we have your database schema?

Aha!

Here's a SQL statement for just one of the tables…

CREATE TABLE `sitemembers` (

`mem_id` int(11) unsigned NOT NULL auto_increment,

`mem_gender` char(1) default NULL,

`mem_firstname` varchar(32) NOT NULL default '',

`mem_lastname` varchar(32) NOT NULL default '',

`mem_username` varchar(32) default NULL,

`mem_email` varchar(64) default NULL,

`mem_password` varchar(40) default NULL,

`mem_level` tinyint(1) unsigned NOT NULL default '0',

`mem_notice` tinyint(1) unsigned NOT NULL default '0',

`mem_deleted` tinyint(1) unsigned NOT NULL default '0',

`mem_date_created` datetime default NULL,

`mem_date_modified` datetime default NULL,

`mem_last_logon` datetime default NULL,

`mem_number_logons` int(7) unsigned default '0',

`mem_newsletter` tinyint(1) unsigned NOT NULL default '0',

`mem_email_format` tinyint(1) unsigned NOT NULL default '0',

`mem_active` tinyint(1) unsigned NOT NULL default '0',

`mem_agree` tinyint(1) unsigned NOT NULL default '0',

PRIMARY KEY (`mem_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

Let me know if you need more info/data…

[peterlaursen]

Not required, I think!

Even when inserting first row of data I get this!

When clicking the mem_date_created column the corresponding NULL value pops up 30-40 characters to the right. SQLyog 5.14 beta2.

[marcm]

peterlaursen wrote on Jun 7 2006, 08:45 PM:

Not required, I think!

Even when inserting first row of data I get this!

When clicking the mem_date_created column the corresponding NULL value pops up 30-40 characters to the right. SQLyog 5.14 beta2.

Aha! Well, I'm glad you have been able to recreate it. I'm sorry that it's there, but at least now you guys can target and fix the bug.

[Manoj]

Hello,

Confirmed the display issue!

It is fixed in the development tree.

Regards,

Manoj

[Author]

Posts