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

Removing [] from imported data

User, date Message
Written by jabucket
2 years ago
Category: General
18 posts since Mon, 14 Jan 13
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?
Written by kalvaro
2 years ago
594 posts since Thu, 29 Nov 07
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

Written by jabucket
2 years ago
18 posts since Mon, 14 Jan 13
Here is what I get, but I am not sure what I should see

'0A3135343736'
'0A3136383234'
'0A3138313734'
'0A35303434'
'0A3130313431'
'0A31333732'
Written by jabucket
2 years ago
18 posts since Mon, 14 Jan 13
There are thousands of rows, but those are the first few
Written by ansgar
2 years ago
4988 posts since Fri, 07 Apr 06
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.
Written by jabucket
2 years ago
18 posts since Mon, 14 Jan 13
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.
Written by ansgar
2 years ago
4988 posts since Fri, 07 Apr 06
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?
Written by kalvaro
2 years ago
594 posts since Thu, 29 Nov 07
@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.