Import CSV files into Excel files

Question

I have tried several of the suggested ways to do this on Stack Overflow and none have worked so far. I have a few differently formatted CSV files that I want imported into a Excel workbook and to add a new workbook with some formulas written in.

here are the links of what I have tried: Link1 Link2 Link3

I have tried what they suggested and nothing happens in the best case and in the worst I get a Null error. FileName1: YYYY-MM-DD.csv format1:

2019/09/04 00:00  32.85 17.94 21.04 0.00
2019/09/04 00:00  32.98 15.77 21.43 -0.02
2019/09/04 00:00  32.85 15.23 21.21 -0.09
2019/09/04 00:01  32.93 15.51 21.30 0.06
2019/09/04 00:01  32.96 15.54 21.45 0.36
2019/09/04 00:01  33.09 17.49 21.26 0.02
2019/09/04 00:01  34.74 17.34 21.17 0.00
2019/09/04 00:02  35.08 17.87 20.62 -0.06

FileName2:YYYY-MM-DD.csv Formate2:

2019/05/01 09:36  30.8,67.6
2019/05/01 09:37  28.8,57.6
2019/05/01 09:38  27.2,53.6
2019/05/01 09:39  27.3,53.4
2019/05/01 09:40  27.0,50.5
2019/05/01 09:41  27.8,54.8
2019/05/01 09:42  25.7,47.1
2019/05/01 09:43  25.8,49.3
2019/05/01 09:44  25.8,48.4
2019/05/01 09:45  26.0,50.0

FileName3:YYYYMMDD.csv Formate3:

2019/09/16 08:00 ,001896100BE3,26C,79RH,2,4,8
2019/09/16 08:01 ,001896100BE3,27C,77RH,2,4,7
2019/09/16 08:01 ,001896100BE3,27C,75RH,3,5,5
2019/09/16 08:02 ,001896100BE3,28C,74RH,2,4,5

Differently formatted you mean different separators and decimals for each file? Do they have a pattern in name -> format? — spYder, Oct 04 '19 at 17:06
Too many questions. Is first file fixed width? tab delimited? space delimited? do you want date/time in one field or two? — Jan, Oct 04 '19 at 19:29
Second file looks like 3 fields (if date/time one field), tab or space delimiter then a comma between two numeric fields — Jan, Oct 04 '19 at 19:30
Third file would work as a comma delimited - next questions would be put 'em on multiple sheets? Basically, what do you need to do with the data? — Jan, Oct 04 '19 at 19:31
Everything is either space delimited for has a comma, and Date needs to be a separate field from time, there is no fixed width to any of the cells/values.I need each csv to be its own excel file. — Michael H., Oct 04 '19 at 19:33

Dmitrii · Accepted Answer · 2020-01-02T22:36:02.423

1

A basic approach:

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

from contextlib import closing
from csv import reader
from xlsxwriter import Workbook

if __name__ == '__main__':

    # file2

    # open workbook
    workbook = Workbook('file2.xlsx')

    # add worksheet
    page1 = workbook.add_worksheet()

    with closing(open('file2.csv', 'r')) as csv_file:
        # read the csv file
        reader_orig = reader(csv_file, delimiter=' ')

        # iterate through the rows
        row = 0
        for el in reader_orig:
            res = dict()
            res['date'] = el[0]
            res['time'] = el[1]
            res['c1'] = el[3].split(',')[0]
            res['c2'] = el[3].split(',')[1]

            # fill in the result of reading to xlsx
            col = 0
            for item in res:
                page1.write(row, col, res[item])
                col += 1

            row += 1

    workbook.close()

You can see other cases here.

edited Jan 02 '20 at 22:36

answered Oct 04 '19 at 23:57

Dmitrii

877
1
6
12

I will have to try this on Monday, I don't have access to the files over the weekend – Michael H. Oct 05 '19 at 00:57
tried running the example you had for the first file and got this error:``` for el in reader_orig: _csv.Error: line contains NULL byte ``` – Michael H. Oct 07 '19 at 15:50
It looks like you have another encoding, not UTF8, or something like that. Try to play with it, like https://stackoverflow.com/questions/7894856/line-contains-null-byte-in-csv-reader-python – Dmitrii Oct 07 '19 at 17:26
Both utf-16 and utf-16le I get: with closing(open('2019-09-04.csv', 'r', 'utf-16le')) as csv_file: TypeError: an integer is required (got type str) – Michael H. Oct 08 '19 at 19:55
Which one OS do you use? Can u send this file via an email to mail@demin.co? – Dmitrii Oct 08 '19 at 20:14
Windows 10 is the os, I'll send you my email through a chat window tomorrow – Michael H. Oct 09 '19 at 01:05

Import CSV files into Excel files

1 Answers1