CODEWITHSUNDEEP
pythonpython-3.xcsvexport-to-csv
Vicot34
Vicot34

Reputation: 1

Writing csv file from list but not all elements follow up

I have to format data from a text book to csv file. In the text book my data are already separated by space so i make a list of string (some contains multiple data separated by space).

When I try to write my list into text file it works well, but when I try to write it into a CSV file, in the middle of a string the writing stops and goes to the next element in my list. Don't know why more that half of my data don't follow up. There is no end line character or whatever.

Here is my simple code

# importing libraries
import os

# defining location of parent folder
BASE_DIRECTORY = r'C:\Users\CAVC071777\Documents\1_Projet\Riverstart\Intrant EDPR\6-Background Harmonics Data at POI\test'

output_file = open('output.csv', 'w')
output = []
outputString = ""
file_list = []
i = 0
# scanning through sub folders
for (dirpath, dirnames, filenames) in os.walk(BASE_DIRECTORY):
    for f in filenames:
        if 'txt' in str(f):
            e = os.path.join(str(dirpath), str(f))
            file_list.append(e)
for f in file_list: 
    txtfile = open(f, 'r')
    i = 0
    for line in txtfile:
        if i == 3:
            outputString = "=Date(""{0}"",""{1}"",""{2}"")+TEMPS(""{3}"",""{4}"",""{5}"")".format(line[46:48],line[40:42],line[43:45],line[58:60],line[61:63],line[64:66])
        if i > 8 and i < 71:
            outputString += line[9:71]
        i = i + 1
    output.append(outputString)
    outputString = ""

for row in output:
    print(row)
    output_file.write(row + "\n")

When I open it in my csv file all the data after 0.830% didn't follow up: screenshot of string printed in terminal

When I print my list of string containing my data in the terminal it's well formatted and all my data is there: screenshot of opened csv file

The text files that i try to read on is like this :

ET H

WHM1 SEL-735 Date: 09/17/19 Time: 11:46:03.726 HDW Time Source: ext

Fundamental Frequency = 60.0

Harmonic IA IB IC IN VA VB VC

2 0.166% 0.137% 0.166% 0.000% 0.000% 0.020% 0.010%

3 ...... ......

And so forth till 60

image of the text file i try to read on

Upvotes: 0

Views: 186

Answers (1)

Serge Ballesta
Serge Ballesta

Reputation: 149095

You have two problems here:

  1. You are building a space separated file
  2. you are using Excel

Excel is known to have a very poor support for csv files. Long story made short in you read a csv file build from Excel on the same system, it will work smoothly. I you read a csv file specifically build for your system, it should work. In any other use case it may or not load correctly...

Here Excel expects the delimiter to be a ; as it is the default delimiter for a French locale, or , if you managed to tell it that. As there are none in the rows, it just tries to put everything in the first cell, and visibly limits the length of a single field.

How to fix:

  1. use LibreOffice or OpenOffice. Both are far beyond Excel for almost all features except csv. You could declare at load time that the separator is a space and control that the lines are correctly parsed
  2. Change the rows in th csv file to use the separator that your version of Excel expects

Upvotes: 1

Related Questions