small problem with importing data into mysql

yahyac

I have an excel sheet that I'm using to add product data to. And for the product descriptions, in a single cell of excel, I'm using bullets and return characters, so the data is somewhat formatted.

From excel, I export as a tab delimited text file, and I then import this file into mysql using the following loadfile command:

load data infile './products.txt' into table products
	fields terminated by '\t'
	optionally enclosed by '"'
	lines terminated by '\r\n';

When the file is imported, the newline (returns) are ignored. Actually, the are read as spaces. Because at the end of one line, where it should have gone to the next line, there is now a space, and then the bullet of the next line, and everything is just "stuck" together.

Any clues on where this mishap is happening? Is there something I need to add to the import to tell mysql that there are newline characters in the file, and that those should be kept? And if so, what would that newline character be?

Thanks.

bradgrafelman

If you open the original tab delimitd text file, does it appear correct?

If it does, how are you viewing the data again? In a web browser? Don't forget that newlines are ignored by browsers (that instead look for the HTML element 'br').

Though it's a Windows system, did you try using "\n" as the line terminator?

yahyac

The original file was an Excel document. Here's a copy/paste from ONE of the cells in the excel document where the problem is occuring.

"• Comfort grip spine for easy carry
• Interior space for PDA and phone
• Deluxe organizer for business accessories
• Center panel has three built-in CD sleeves
• Includes 5"" x 8"" writing pad
• Includes 1-piece gift box
• Made of Neoprene."

As you see, the paste automatically puts these on separate lines, cause there must be an end of line character.

And when i look at the text delimited file that was expoted from Excel, it, too, has the bullet on a new line.

So the problem is happening at the import. And it may not be a problem. Does it have to do with the last command of the import, lines terminated by '\r\n' ?

bradgrafelman

bradgrafelman wrote:
Though it's a Windows system, did you try using "\n" as the line terminator?

Did you try this?

yahyac

I just did, and in doing so, was reminded of why that '/r' was inserted before the '/n'. I am not familiar with the rhyme or reason as to why one needs the /r along with the /n, but in my case, when i do not use the /r, i get foreign key constaint failures with my loadfile.

I kept running into issues with the loadfile, and finally, when someone mentioned that i needed to use that line "lines terminated by '/r/n'", everything was working like clockwork. Just as a result (i'm assuming this is a result), my data is not keeping it's newline characters.

yahyac

btw, those forward slashes should have been backslashes.

bradgrafelman

Yeah... it was a long shot. To explain, though, different operating systems use different character-sequences to end a line:

Mac - \r (CR)
Windows - \r\n (CRLF)
Unix/etc. - \n (LF)

'Fraid this problem is beyond my limited SQL knowledge. Sorry :\

yahyac

You know, and i'm just brainstorming, maybe the line i had originally should have worked:

lines terminated by '\n'

And perhaps the only reason it did not work was because of issues with my data, and by adding the '\r\n', perhaps that allowed the import to work more "liberaly".?.

I guess it would help if i knew what the \r was for in the first place. Off to google.

bradgrafelman

yahyac wrote:
I guess it would help if i knew what the \r was for in the first place.

As I explained above, it's a common line-ending character.

\r = Carriage Return
\n = Line Feed

yahyac

Thanks.

I figured out what \r meant last night. I still can't figure it out.

I will see if it is possible to attach a file later and give a basic example of the problem, and maybe someone can try to duplicate it and see how to avoid the problem...although that is a lot to ask for someone who wants to help.

But we'll see.

Jonathan