Lesson 31. Convert Comma Delmited Files To Pipe Delimited

This is a simple example and is really just here to build on concepts.

When you are dealing with flat files, you are going to run into all kinds of issues. One of the issues you are going to deal with is having commas in data.

You might have to work with a file that has text fields containing user input. The problem will be that the file has not been prepared in such a way that software interprets these commas as text and not column delimiters. When this happens, you are going to have to do some clean up work to the file before you can load it into a database, otherwise those commas are going to cause problems on import.

Once you have cleaned up the file or even if you do not have to do any cleanup work at all, it is in your best interest to take the file and convert it to pipe delimited instead of comma delimited.

Pipe is the name for that thing over the backlash on your keyboard. Pipe is RARELY used in everyday typing, so there is a low probably that pipe will show up in text fields.

It does not matter what the source is. If you have to write a delimited flat file, write it out as pipe delimited.

Examples

Example #1: Convert File To Pipe Delimited

This example downloads the comma delimited flights dataset.

For simplicity, we will not write the comma delimited version of the file as it is a relatively small file and fits in memory. Instead, we will read it into memory and then write the pipe delimited version to the PipeDelimitedExample folder.

In order to write a file as pipe delimited with the to_csv function, you use the sep argument and set it to |.

import urllib.request
import os
import pandas as pd

url = 'https://query.data.world/s/paxejv4t6pn3el4ttskmx7rhxhz5ho'
script_dir = os.getcwd()
data_directory = 'data\\'
example_directory = 'PipeDelimitedExample\\'
file_name = 'flights.csv'
file_path = os.path.join(script_dir,data_directory,example_directory,file_name)

# Read csv data into pandas and write | delimited txt file
df = pd.read_csv(url,index_col=False, dtype=str)
df.to_csv(file_path, sep="|",index=False)