27

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
martineau
  • 119,623
  • 25
  • 170
  • 301
GFL
  • 1,278
  • 2
  • 15
  • 24
  • 1
    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 – RomanPerekhrest Oct 06 '17 at 22:05

4 Answers4

46

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, also adding skipinitialspace=True to csv.reader call if there are multiple space symbols between columns:

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

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

Alex Martian
  • 3,423
  • 7
  • 36
  • 71
martineau
  • 119,623
  • 25
  • 170
  • 301
  • 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? – GFL Oct 07 '17 at 01:15
  • 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()`](http://xlsxwriter.readthedocs.io/worksheet.html#worksheet-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. – martineau Oct 07 '17 at 01:47
  • @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'` – Cappittall Oct 21 '18 at 11:03
  • @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. – martineau Oct 21 '18 at 14:07
  • 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 `['info@z.com','y@-y.com.','...']` – Cappittall Oct 21 '18 at 19:04
  • @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. – martineau Oct 21 '18 at 19:29
  • If file encoding is in UTF-8 then open('testfile.csv', encoding="utf-8") – Isuru Dilshan Jan 11 '20 at 04:46
  • 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] – NicoKowe Sep 29 '21 at 15:30
  • @NicoKowe: [`csv.reader`](https://docs.python.org/3/library/csv.html#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. – martineau Jun 17 '22 at 17:48
14

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=',')
feetwet
  • 3,248
  • 7
  • 46
  • 84
Humi
  • 609
  • 5
  • 15
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.

Yehuda
  • 1,787
  • 2
  • 15
  • 49
0

This method also works for me. Example: Having random data, and each data point starting on a newline like below:

 'dog',5,2
 'cat',5,7,1
 'man',5,7,3,'banana'
 'food',5,8,9,4,'girl'

import csv
with open('filePath.csv', 'r') as readData:
readCsv = csv.reader(readData)
data = list(readCsv)
Anurag A S
  • 725
  • 10
  • 23
RaySun
  • 139
  • 1
  • 5