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 (
'\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!