Removing [] from imported data
| User, date | Message |
|---|---|
|
Written by jabucket
3 months 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
3 months ago 440 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
3 months 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
3 months ago 18 posts since Mon, 14 Jan 13 |
There are thousands of rows, but those are the first few |
|
Written by ansgar
3 months ago 3949 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
3 months 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
3 months ago 3949 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
3 months ago 440 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. |