Removing [] from imported data

jabucket posted 5 years ago in General
I imported a known good CSV file into a MySQL DB using HeidiSQL, but one column has a blank line return before each string value, making the data look like this:


instead of


When I view the column data in Crystal reports, it shows as []stringvalue. Any ideas?
kalvaro posted 5 years ago
Do you mean that the line feed is not in the original file?
Also, are you sure it's a line feed? You can run this query to find out:

SELECT HEX(column_name) FROM table_name
jabucket posted 5 years ago
Here is what I get, but I am not sure what I should see

jabucket posted 5 years ago
There are thousands of rows, but those are the first few
ansgar posted 5 years ago
0A is a linefeed (#10). You have these linefeeds apparently in your CSV file, don't you? Some editors might show some broken char insteaf of a linefeed, as this is not a Windows linefeed.
jabucket posted 5 years ago
That was it. I found an article on removing line feeds in MySQL columns here:


That did the trick. Thank you for your help.
ansgar posted 5 years ago
I thought the question was where these line feeds came from. Removing them afterwards is what you found out now, but I think you should avoid getting them the next time, or?
kalvaro posted 5 years ago
@jabucket - "0A" is the line feed character that MySQL represents as "\n".

I'm suspect that you imported a file with Windows line feeds ("\r\n") but in "Lines terminated by" you wrote "\r" instead of "\r\n".

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