Solved

Merging delimited text files

Posted on 2014-10-17
4
98 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 37

Expert Comment

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

Author Comment

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

Expert Comment

by:Gerwin Jansen
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 28

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Less strange, but still introduction This introduction was added (1st August, 2011) to reflect some reactions.  Firstly, the term basics in the title of the article...  As any other word, it is a symbol with meaning attached to the word by some a…
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…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now