Solved

Merging delimited text files

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
The purpose of this article is to demonstrate how we can use conditional statements using Python.
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

705 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