2

I would like to take values from two csv files and put that in a single CSV file.

Please refer the Data in those two csv files:

CSV 1:

|   | Status   | P  | F | B | IP | NI | NA | CO | U |
|---|----------|----|---|---|----|----|----|----|---|
| 0 | Sanity 1 | 14 |   |   |    |    |    | 1  |   |
| 1 | Sanity 2 | 13 |   | 1 |    |    |    | 1  |   |
|   |          |    |   |   |    |    |    |    |   |

CSV 2:

|   | Status     | P   | F | B | IP | NI | NA | CO | U |
|---|------------|-----|---|---|----|----|----|----|---|
| 0 | P0 Dry Run | 154 | 1 |   |    | 1  |    |    | 5 |
|   |            |     |   |   |    |    |    |    |   |
|   |            |     |   |   |    |    |    |    |   |

Code: I tried with following code:

filenames = glob.glob ("C:\\Users\\gomathis\\Downloads\\To csv\\*.csv")
wf = csv.writer(open("C:\\Users\\gomathis\\Downloads\\To 
csv\\FinalTR.csv",'wb'))

for f in filenames:
    rd = csv.writer(open(f,'r'))
    next(rd)
    for row in rd:
        wf.writerow(row)

Actual result: While trying with above code, I didn't get the values from those above CSV files.

Expected result: I need that two files needs to be added in a single csv file and to be saved locally.

Modified code:

filenames = glob.glob ("C:\\Users\\gomathis\\Downloads\\To csv\\*.csv") wf = csv.writer(open("C:\\Users\\gomathis\\Downloads\\To csv\\FinalTR.csv",'w')) print(filenames)

for f in filenames:

  rd = csv.reader(open(f,'r', newline=''))
  next(rd)
  for row in rd:
      wf.writerow(row)

Latest result: I got the below result after modifying the code. And I didn't get the index like status P, F,B,etc. Please refer the latest result.

| 0 | P0 Dry Run - 15/02/18 | 154 | 1 | | | 1 | | | 5 | |---|--------------------------------|-----|---|---|---|---|---|---|---| | | | | | | | | | | | | 0 | Sanity in FRA Prod - 15/02/18 | 14 | | | | | | 1 | | | | | | | | | | | | | | 1 | Sanity in SYD Gamma - 15/02/18 | 13 | | 1 | | | | 1 | |

gomathi subramanian
  • 39
  • 6
  • 1
    Possible duplicate of [Merging 2 csv files](https://stackoverflow.com/questions/16265831/merging-2-csv-files) – Benjámin Gerván Apr 18 '18 at 12:01
  • Are you using Python 2 or 3? If Python 2 your open csv files for reading should be `open(f, 'rb')`. If Python 3, `open(f, 'r', newline='')`. Writing follows the same pattern. The reason for this special handling for csv files is that the csv module needs to bypass Python's universal newline support. – Steven Rumbalski Apr 18 '18 at 12:16
  • Are you certain your glob is matching your filenames? To check print filenames before your loop. – Steven Rumbalski Apr 18 '18 at 12:22
  • @StevenRumbalski Sorry the late response. I got this result while printing before passing it into loop `['C:\\Users\\gomathis\\Downloads\\To csv\\FinalTR.csv', 'C:\\Users\\gomathis\\Downloads\\To csv\\file1.csv', 'C:\\Users\\gomathis\\Downloads\\To csv\\file2.csv'] ` – gomathi subramanian Apr 18 '18 at 15:11
  • Are you using Python 2 or 3? What ends up in `FinalTR.csv`? Anything? As @Ank points out you do `rd = csv.writer(open(f,'r'))` when you should to `rd = csv.reader(open(f,'r'))`. You need a `csv.reader` not a `csv.writer` in that spot. Also, since your destination file exists in that list you should skip reading it. `if f.endswith('FinalTR.csv'):continue` – Steven Rumbalski Apr 18 '18 at 15:14
  • I'm using Python 3 and In `FinalTR.csv`, i need to store the results of file1 & file2. But now in FinalTR.csv is created with empty data. – gomathi subramanian Apr 18 '18 at 15:16
  • Since you are using Python 3, your open for reading should be `open(filename, 'r', newline='')` and your open for writing should be `open(filename, 'w', newline='')`. No `b` binary flag on the file mode and you need the `newline=''` argument. This `newline=''` oddity is only needed when opening files for use by the `csv` module. – Steven Rumbalski Apr 18 '18 at 15:21
  • @StevenRumbalski Kindly check the modified code and latest result. Now didn't get the status row. – gomathi subramanian Apr 18 '18 at 15:33

2 Answers2

1

You need to call the csv reader method over your csv files in the loop.

rd = csv.reader(open(f,'r'))
Ank
  • 1,704
  • 9
  • 14
0
import csv
import glob

dest_fname = "C:\\Users\\gomathis\\Downloads\\To csv\\FinalTR.csv"
src_fnames = glob.glob("C:\\Users\\gomathis\\Downloads\\To csv\\*.csv")
with open(dest_fname, 'w', newline='') as f_out:
    writer = csv.writer(fout)
    copy_headers = True
    for src_fname in src_fnames:
        # don't want to overwrite destination file
        if src_fname.endswith('FinalTR.csv'):
            continue
        with open(src_fname, 'r', newline='') as f_in:
            reader = csv.reader(f_in)
            # header row is copied from first csv and skipped on the rest
            if copy_headers:
                copy_headers = False
            else:
                next(reader) # skip header 
            for row in reader:
                writer.writerow(row)

Notes:

  • Placed your open() in with-statements for automatic closing of files.
  • Removed the binary flag from file modes and added newline='' which is needed for files passed to csv.reader and csv.writer in Python 3.
  • Changed from csv.writer to csv.reader for the files you were reading from.
  • Added a copy_headers flag to enable copying headers from the first file and skipping copying of headers from any files after that.
  • Check source filename and skip it when it matches the destination filename.
Steven Rumbalski
  • 44,786
  • 9
  • 89
  • 119
  • Thank you. But I have another clarification, what if I want to select only two csv files from a folder which contains more than 3 csv files. – gomathi subramanian Apr 18 '18 at 15:51
  • @gomathisubramanian: What's special about the two names? How do you know the difference manually? – Steven Rumbalski Apr 18 '18 at 15:52
  • week_1 and week_2 and those are the files where I have the data to make it as a single file – gomathi subramanian Apr 18 '18 at 15:54
  • @gomathisubramanian: You could change the end of your `glob()` argument from `'*.csv'` to `'week_[1-2].csv'`. Or `'week_[1-9]'` for weeks one through nine. With a glob that specific you could remove the lines `if src_fname.endswith('FinalTR.csv'): continue` – Steven Rumbalski Apr 18 '18 at 16:01