Line break and carriage return

Nish's profile image Nish posted 4 years ago in General Permalink

Hello,

So I have a file that I want to import o HeidiSQL. The file is a .txt delimited by "|" with enclosing '"' in the start and end.

I've imported files successfully to HeidiSQL but this one is giving me a headache. I believe it is related to the characters associated with the line break and carriage return.

To explain what happens, if I attempt to import it with LINES TERMINATED BY '\r\n' I get the error message that "No rows were imported", using the Import Wizard. After this I decided to mess with the line break and carriage return and decided to define '\n' after trying close to every other combination ('\r', '\\r\\n', '\n\r', etc.). The '\n' result is that the file gets imported by half the lines, meaning , the file has 13 rows (original file is much larger) and only 7 get imported. The remaining lines the HeidiSQL tries to import in the same line, but gets truncated since there are not enough columns.

As a test I tried to import it in MSSQL Management Studio and MySQL Workbench. MSSQL worked without any problem, MySQL had precisely the same struggle as HeidiSQL. For all of them I used the Import Wizard.

I attached two images of the outputs that summarize the problem.

Thanks in advance for the help!

2 attachment(s):
  • img01f
  • img02f
ansgar's profile image ansgar posted 4 years ago Permalink

Apparently your file has Linux linebreaks \n, which is fine. You should not mess too much with the linebreaks. Mostly it's Linux \n or Windows \r\n linebreaks. But the table you selected does not have enough columns, or the field control characters are wrong. For example, if the file has semicolons between fields, and you tell HeidiSQL it has the pipe (| as in your screenshots), all rows are seen as a single field, while your table has more than one field.

Nish's profile image Nish posted 4 years ago Permalink

Thank you for the help ansgar!

I'll try to give one of the best example I can.

Imagine I have an old typewriter, and every two lines I don't press the new line key, what happens? The text will overflow the page and theoretically start writing outside the paper.

Well the same happens here, since the software doesn't recognize the new line it doesn't "go down" and attempts to continue to write on the same line, giving the error displayed on the screenshots where there are not enough columns, when in fact it corresponds to a new line.

The pipe "|" is the correct separator.

I reinforce that this works correctly on the MS Management Studio import wizard, which someway shows that the file is ok.

ansgar's profile image ansgar posted 4 years ago Permalink

If MSMS works, you should also post the control character settings you used there.

Apart from that, you are not only comparing two different clients (HeidiSQL vs MSMS), but also two different database servers (MySQL vs. MSSQL). I hope you have at least identical tables in MySQL and MSSQL, otherwise your comparison is totally broken.

The log messages from your second screenshot clearly say your control characters do not fit to the file, or the table does not fit to the columns in the file.

Nish's profile image Nish posted 3 years ago Permalink

I've been able to resolve the issue. I found a post in the forum of something similar which helped me solve my issue:

(can't post link, but the forum question is "Bug: importing UTF-8 CSV")

Basically I was using the option "Fields escaped by" to define '"' and this was leading to errors. I removed it (leaving it blank) and the import was smooth.

Thank you for the help, hope this post can help on future situations.

Please login to leave a reply, or register at first.