Solved

Merging delimited text files

Posted on 2014-10-17
4
106 Views
Last Modified: 2014-11-06
I have a whole bunch of files in a folder, and want to merge them to a csv file. The one I want to merge have a name format of Proc_Account.txt or Proc_Sales.txt etc  They are all text files delimited by |.  Example of file.

Frank | Albert    | M | 01/05/1974 12:00 | 808 Genstone Ave  | 23UYTLY

What I want is to append all the files in the folder to 1 csv, but I want to process each line where, I change the date format from 01/05/1974 12:00 to 19740501.
0
Comment
Question by:Qazzim Dungey
  • 2
4 Comments
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40388651
What OS is this?
0
 

Author Comment

by:Qazzim Dungey
ID: 40389026
it is windows
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40389332
I would suggest that you copy them using a cmd copy command, import the new file into Excel and then export again, setting the date column the way you want it, copy files into one new file:

copy proc_*.txt newfile.txt

Open a new Excel document (blank), go to Data->From Text and import your newfile.txt, choose Delimited, select Space delimiter and Other where you enter the | pipe character, Finish. Format the date column the way you want it and SaveAs a csv file.
0
 
LVL 29

Accepted Solution

by:
pepr earned 500 total points
ID: 40390441
Here is the working skeleton of the solution. You should clarify, what does it mean merge for you, and whether the output should also use the bar delimiter or how exactly the output should look like:
#!python3

import csv
import datetime
import glob

# I assume you want the real CSV -- here the output file
# open as it should be done in Python 3.
with open('output.csv', 'w', newline='') as fout:

    # Wrap the output file object by the csv writer
    # so that it does the necessary things for you.
    writer = csv.writer(fout)

    # Loop through the list of filenames -- here simple globbing 
    # the files with the .txt extension.
    for fname in glob.glob('*.txt'):
        with open(fname) as f:
            # Extract the info from each line and write
            # the row into the output.
            for line in f:
                row = [e.strip() for e in line.split('|')]
                
                # Extract the datetime from the fourth element
                # and convert it to the wanted form.
                d = datetime.datetime.strptime(row[3], '%d/%m/%Y %H:%M')
                row[3] = d.strftime('%Y%m%d')
                
                writer.writerow(row)

Open in new window

With the single line stored in a.txt, the script located at the same directory produces output.csv that contains:
Frank,Albert,M,19740501,808 Genstone Ave,23UYTLY

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Dictionaries contain key:value pairs. Which means a collection of tuples with an attribute name and an assigned value to it. The semicolon present in between each key and values and attribute with values are delimited with a comma.  In python we can…
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question