Thanks so much to everyone who responded to my help call. All replies were tremendously helpful to me!
The reason why I used sniffer was because I couldn't get the delimiter to work properly to parse out the original input file. Didn't think of checking dialect itself and thanks Adam for pointing that out!
I tried Joshua's suggestion, which worked perfectly in the output file. I found that all \r s in the input disappeared and only \n s were left in the output though but that's easy to fix.
Dan's suggestion also worked great and this one actually ended both \n and \r in the same way as the original input file and mysql didn't complain at all about importing the whole thing. So that was a winner for me. I just never thought to try 'excel-tab' and now I know excel can mean something good to sometimes. :)
Big thanks again to everyone!
From: Code for Libraries [[log in to unmask]] on behalf of Kyle Banerjee [[log in to unmask]]
Sent: Monday, November 25, 2013 4:16 PM
To: [log in to unmask]
Subject: Re: [CODE4LIB] Tab delimited file with Python CSV
> I found the CSV module in Python to be surprisingly confusing when I
> first encountered it, given Python's elegance in many other cases. The
> Dialect thing drove me nuts at first!
What's nuts is that something as simple as delimited data still causes
headaches at this point in our history. But then again, so does label
I'm trying to understand why all software that reads/writes delimited data
doesn't let you define field and record separators of any length/pattern so
it doesn't matter if newlines or other normally problematic character
sequences occur in your data. Heck, awk has allowed people to do this in a
single line since the 70's.