Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 121
  • Last Modified:

Merging delimited text files

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
Qazzim Dungey
Asked:
Qazzim Dungey
  • 2
1 Solution
 
Gerwin Jansen, EE MVETopic Advisor Commented:
What OS is this?
0
 
Qazzim DungeyAuthor Commented:
it is windows
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
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
 
peprCommented:
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now