How to import a csv-file into a data array?

Question

I have a line of code in a script that imports data from a text file with lots of spaces between values into an array for use later.

textfile = open('file.txt')
data = []
for line in textfile:
    row_data = line.strip("\n").split()
    for i, item in enumerate(row_data):
        try:
            row_data[i] = float(item)
        except ValueError:
            pass
    data.append(row_data)

I need to change this from a text file to a csv file. I don't want to just change this text to split on commas (since some values can have commas if they're in quotes). Luckily I saw there is a csv library I can import that can handle this.

import csv
with open('file.csv', 'rb') as csvfile:
    ???

How can I load the csv file into the data array?

If it makes a difference, this is how the data will be used:

row = 0
for row_data in (data):
    worksheet.write_row(row, 0, row_data)
    row += 1

Solution 1

Assuming the CSV file is delimited with commas, the simplest way using the csv module in Python 3 would probably be:

import csv

with open('testfile.csv', newline='') as csvfile:
    data = list(csv.reader(csvfile))

print(data)

You can specify other delimiters, such as tab characters, by specifying them when creating the csv.reader:

    data = list(csv.reader(csvfile, delimiter='\t'))

For Python 2, use open('testfile.csv', 'rb') to open the file.

Solution 2

You can use pandas library or numpy to read the CSV file. If your file is tab-separated then use '\t' in place of comma in both sep and delimiter arguments below.

import pandas as pd 
myFile = pd.read_csv('filepath', sep=',')

Or

 import numpy as np
 myFile = np.genfromtxt('filepath', delimiter=',')

Solution 3

I think the simplest way to do this is via Pandas:

import pandas as pd
data = pd.read_csv(FILE).values

This returns a Numpy array of values from a DataFrame created from the CSV. See the documentation here.

Comments

[RomanPerekhrest]

you have 2 tasks: 1) I need to change this from a text file to a csv file; 2) How can I load the csv file into the data array? Start with posting your initial file.txt content

[GFL]

Thank you! I have Python 2 so I modified it as suggested. Does the data = line reiterate itself for each line in the csv? Would I be able to put the data into the worksheet.write_row(row, 0, row_data) line directly, instead of having to put it first into an array and then read each line of the array?

[martineau]

Yes, it looks like the loop at the end of your question would work (although you don't need the parentheses around data). From the xlsxwriter.write_row() documentation it appears that it could also be done more efficiently in a single call: i.e. worksheet.write_row(row, 0, data) instead of using the loop and making multiple calls that each do one row-a-time.

[Cappittall]

@martineau, I am trying to import email list in CSV file into a python list. Array list length is 1 only ` len(data)=1` but I have over 100 emails in CSV file. (Print shows all emails, but the length is =1) When iterating gives error: TypeError: unhashable type: 'list'

[martineau]

@HakanC: From the TypeError it sounds like you may not be passing an open file object to csv.reader()—but without more information and being able to see your code, I can only make guesses. Post a question.

[Cappittall]

I have a list of emails in excel. And I wanted to use this list. First I converted to a CSV file, separated with a comma. But with the above code data = list(csv.reader(csvfile)) and len(data) = 1 and print(data) is ['[email protected]','[email protected].','...']

[martineau]

@HakanC: That doesn't tell me what's in the CSV file, just what csv.reader() is returning. Please don't post any further questions here as comments because I won't be responding to any more of them.

[Isuru Dilshan]

If file encoding is in UTF-8 then open('testfile.csv', encoding="utf-8")

[NicoKowe]

I know this is old now, but... This returns a list of lists... Check when you print that the list its not the first item of a list. in this case you see [[a,b,c]], try to print the data[0] you may see now only one braket= [a,b,c]

[martineau]

@NicoKowe: csv.reader objects are iterable amd return each row read as a list of strings, so it should be no surprise that list(csv.reader(csvfile)) creates a list of lists.