distal-attribute
distal-attribute
distal-attribute
distal-attribute

Removing [] from imported data

jabucket posted 2 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:

"
stringvalue"

instead of

"stringvalue"

When I view the column data in Crystal reports, it shows as []stringvalue. Any ideas?
kalvaro posted 2 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 2 years ago
Here is what I get, but I am not sure what I should see

'0A3135343736'
'0A3136383234'
'0A3138313734'
'0A35303434'
'0A3130313431'
'0A31333732'
jabucket posted 2 years ago
There are thousands of rows, but those are the first few
ansgar posted 2 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 2 years ago
That was it. I found an article on removing line feeds in MySQL columns here:

http://www.it-iss.com/mysql/mysql-removing-linefeeds-from-text-in-a-database/

That did the trick. Thank you for your help.
ansgar posted 2 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 2 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.