Solved

Merging delimited text files

Posted on 2014-10-17
4
111 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Logon script fails 23 70
Find unused columns in a table 12 92
Running Scripts (per users) after OS deployment 2 45
Python Scrip in Windows Task Schedule 2 39
This article will show the steps for installing Python on Ubuntu Operating System. I have created a virtual machine with Ubuntu Operating system 8.10 and this installing process also works with upgraded version of Ubuntu OS. For installing Py…
Sequence is something that used to store data in it in very simple words. Let us just create a list first. To create a list first of all we need to give a name to our list which I have taken as “COURSE” followed by equals sign and finally enclosed …
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

739 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