How to delimit a CSV file with semicolons?

Question

I am trying to open a CSV file after I create it with python. My goal is to be able to read back the file without editing it and my problem has been that I cannot get the delimiter to work. My file is created with python csv writer and then I am attempting to use the reader to read the data from the file. This is where I am getting stuck. My CSV file is saved in the same location that my python program is saved in thus I know it is not an access issue. My file is created with special character delimiter I am using Semicolons; because the raw data already contains commas,, colons;, plus signs+, ampersands&, periods., and possibly underscores_ and/or dashes-. This is the code that I am using to read my CSV file:

with open('Cool.csv') as csv_file:
  csv_reader = csv.reader(csv_file, delimiter=';', dialect=csv.excel_tab)
  for row in csv_reader:
    print row[0]

csv_file.close() 

Now this is my csv file (Cool.csv):

"Sat, 20 Apr 2019 00:17:05 +0000;Need to go to store;Eggs & Milk are needed ;Store: Grocery;Full Name: Safeway;Email: safewayiscool@gmail.com;Safeway <safewayiscool@gmail.com>, ;"
"Tue, 5 Mar 2019 05:54:24 +0000;Need to buy ham;Green eggs and Ham are needed for dinner ;Username: Dr.Seuss;Full Name: Theodor Seuss Geisel;Email: greeneggs+ham@seuss.com;"

So I would expect my output to be the following when I run the code:

Sat, 20 Apr 2019 00:17:05 +0000
Tue, 5 Mar 2019 05:54:24 +0000

I either get a null error of some kind or it will print out the entire line. How can I get it to separate out data into what I want to have defined the columns delimited by the ;?

I am not sure if the issue is that I am trying to use the semicolon or if it is something else. If it is just the semicolon I could change it if necessary but many other characters are already taken in the incoming data.

Also please do not suggest I simply just read it in from the original file. It is a massive file that has a lot of other data and I want to trim it before then executing with this second program.

UPDATE: This is the code that builds the file:

with open('Cool.csv', 'w') as csvFile:
    writer = csv.writer(csvFile, delimiter=';')
    for m in file: 
        message = m['msg']
        message2 = message.replace('\r\n\r\n', ';')
        message3 = message2.replace('\r\n', ';')
        entry = m['date'] + ";" + m['subject'] + ";" + message3
        list = []
        list.append(entry)
        writer.writerow(list)
csvFile.close()

Answer 1

It looks like the file was created incorrectly. The sample data provided shows the whole line double-quoted, which treats it as one long single column. Here's correct code to write and read and semicolon-delimited file:

import csv

with open('Cool.csv','w',newline='',encoding='utf-8-sig') as csv_file:
    csv_writer = csv.writer(csv_file,delimiter=';')
    csv_writer.writerow(['data,data','data;data','data+-":_'])

with open('Cool.csv','r',newline='',encoding='utf-8-sig') as csv_file:
    csv_reader = csv.reader(csv_file,delimiter=';')
    for row in csv_reader:
        print(row)

Output (matches data written):

['data,data', 'data;data', 'data+-":_']

Cool.csv:

data,data;"data;data";"data+-"":_"

Notes:

• utf-8-sig is the most compatible encoding with Excel. Any Unicode character you put in the file will work and look correct when the CSV is opened in Excel.
• newline='' is required per the csv documentation. The csv module handles its own newlines per the dialect used (default 'excel').
• ; delimiter is not needed. The default , will work. Note how the second entry has a semicolon, so the field was quoted. The first field with comma would have been quoted instead if the delimiter was a comma and it would still work.
• csv_writer.writerow takes a sequence containing the column data.
• csv_reader returns each row as a list of the column data.
• A column in the .CSV is double-quoted if it contains the delimiter, and quotes are doubled if present in the data to escape them as well. Note the third field has a double quote.
• csv_writer.close() and csv_reader.close() are not needed if using with.

Answer 2

RTFM.

From help (csv)

    DIALECT REGISTRATION:

    Readers and writers support a dialect argument, which is a convenient
    handle on a group of settings.  When the dialect argument is a string,
    it identifies one of the dialects previously registered with the module.
    If it is a class or instance, the attributes of the argument are used as
    the settings for the reader or writer:

        class excel:
            delimiter = ','
            quotechar = '"'
            escapechar = None
            doublequote = True
            skipinitialspace = False
            lineterminator = '\r\n'
            quoting = QUOTE_MINIMAL

And you use dialect=csv.excel_tab.

You effectively overwrite your delimiter. Just don't use the dialect option.

Sidenote: with handles closing of the file handle for you. Read here

Second sidenote: The whole line of your CSV file is in double quotes. Either get rid of them, or disable the quoting. i.e.

with open('b.txt') as csv_file:
  csv_reader = csv.reader(csv_file, delimiter=';', quoting=csv.QUOTE_NONE)
  for row in csv_reader:
    print (row[0])