What would cause column data to be spread across two lines?
I've imported metadata from audio files and written same to a SQLite table. A number of columns have entries like this "text1\\text2\\text3" where the \\ serves as a delimiter, meaning that the column actually contains multiple distinct entries and should ideally be split into a separate table ... which is what I'm planning to do. I'm using Python to load the column into a Pandas DataFrame, following which I'm converting all records into a Python list which is then split into individual entries, de-duped and written to a new list, which forms the basis of the separate table. In doing this I've encountered a number of items in the list that split over two lines. This led me down the path of running update queries to remove any occurrences of CR and LF in the column in question: update alib set artist = [REPLACE](artist, char(10), '') WHERE artist != [REPLACE](artist, char(10), ''); and update alib set artist = [REPLACE](artist, char(13), '') WHERE artist != [REPLACE](artist, char(13), ''); Whilst this reduced the number of occurrences of items in the list that split over two lines, some still remain and I've no idea where to go from here. Are there any other characters I should be testing for?
Have you tried examining the raw data (before you start any of your processing) of the affected rows to see what characters they contain? Use the SQLite shell to find out what the stored data is for those rows, so you can be sure if the problem is in the data itself or the way you're processing it.
Many database engines, including SQLite, allow returns as part of text strings. This isn't weird, or even unusual. It happens all the time.
If you are converting the data into a one-row-per-record format then you will have to detect this and deal with it according to however your format copes with expressing returns.