0

I am writing some code for a project and saving this code to .csv format. It then will be opened in excel to further inspect the data. There are 8 floats then 1 string in each row. How do I get excel to display each value in different cells? The code for the saving 

def save_array():
    t = utctime.t()
    temp = data.get_data()
    atime = np.append([temp],[t])
    atime = ' '.join(atime)
    return atime

with open( path2, 'w') as save_data:
    save_data_write = csv.writer(save_data, delimiter = ' ' , dialect = 'excel')
    save_data_write.writerow([save_array()])
save_data.close()

while i:
    time.sleep(1)
    with open( path2,'a') as save_data:
    save_data_append = csv.writer(save_data, delimiter = ' ', dialect = 'excel')
    save_data_append.writerow([save_array()])
save_data.close()

The saved data in excel looks like this.

0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 Apr 03 2017 20:24:59

0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 Apr 03 2017 20:25:00

0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 Apr 03 2017 20:25:01

0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 Apr 03 2017 20:25:02

0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 Apr 03 2017 20:25:03

0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 Apr 03 2017 20:25:04

0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 Apr 03 2017 20:25:05

This didn't copy very well but in excel this is all in the first cell and it also skips a line between rows. Does anybody know how to format this correctly?

Thanks

Improve this question
6
  • 1
    You can open a .csv file with Excel just fine without anything fancy. First, use a comma for delimiting rather than a space. However, I don't understand save_data_write.writerow([save_array()]), specifically save_array(). Commented Apr 3, 2017 at 21:06
  • Also, if you want to write to csv files in Windows, use wb instead of w; this will get rid of your unwanted newlines between rows. Commented Apr 3, 2017 at 21:09
  • save_array() is a function call that calls the array to be saved Commented Apr 3, 2017 at 21:26
  • thanks wb and ab got rid of the extra lines Commented Apr 3, 2017 at 21:26
  • here is the save_array function if your curious put that function in the post Commented Apr 3, 2017 at 21:29

2 Answers 2

Reset to default
2

Quick answer (easiest way):

import numpy
#open the file in append, binary mode
savefile=open('filename.csv','ab')

#write to it the first time
save_data = save_array()
numpy.savetxt(savefile, save_data, fmt='%s', delimiter=',', newline='\n')

#do your loop here
time.sleep(1)
save_data = save_array()
numpy.savetxt(savefile, save_data, fmt='%s', delimiter=',', newline='\n')

#once done
savefile.close()

This will save everything as strings (because of the %s option), but that should work for your application. Because we first opened the file in 'ab' mode (append, binary) it will not write over the file, but keep adding to the bottom. If you want to start fresh each time you run the code, open the file with 'wb' instead.

More bulky way (but perhaps more traditional, and better for some specific applications):

#open the file in append, binary mode
savefile=open('filename.csv','ab')
csvwriter = csv.writer(savefile, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)

save_data = save_array()
for i in range(len(save_data)):
    csvwriter.writerow(save_data[i])

#do your loop here
time.sleep(1)
save_data = save_array()
for i in range(len(save_data)):
    csvwriter.writerow(save_data[i])

#once done
savefile.close()

Hope that helps, and make sure to mark this as a correct answer if it solved your problem.

More details for the interested reader:

CSV files are pretty simple when you understand how they are read.

First, you should ideally use delimiters (characters used to separate columns) that are not contained in any of your columns. Since your strings have spaces in them, you should not use a space delimiter. A comma is a common choice, but semicolons and tabs (you can get these using \t as the delimiter) are other common choices. Generally, one uses tabs with a .txt file, while commas are used in .csv files.

Second, you need to tell Excel what the format is. Excel expects .csv files to be delimited by commas (understandably, since the name literally means "comma separated values"). For .txt files, it should open a wizard that lets you choose how to read in the data.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks helps alot
0 
file_path = 'name.csv'
with open(file_path, 'w', newline='') as result_file:
    csv_writer = csv.writer(result_file, delimiter=',', quotechar='"',
                               quoting=csv.QUOTE_MINIMAL)
    csv_writer.writerow([1, 2.2, '33'])
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.