<jupyter_start><jupyter_text>Examples Example 1: Let’s Pack ‘Em Up And Move ‘Em Out!We are going to drag back out the data that we just got through loading in the last lesson. Since the data is currently sitting in a stage table, we are going query the view. That way, we do not wind up with audit columns in our file.If this were a real world ETL process, we would have cleansed the data after we loaded it in lesson 33. As it turns out, the data got loaded with some hitch hikers. The carriage return line feed character got loaded along with the last column so that needs to be removed. Once that is done, we can proceed with creating the file.Most of the code here should be familiar. The new bits are working with the csv module. That module has functions that allows us to create CSVs files in the format that most the world expects when dealing with CSVs.<jupyter_code>--remove carrage return and line feed characters
UPDATE euro.EurostatData
SET SUP_QUANTITY = REPLACE(REPLACE(SUP_QUANTITY,CHAR(13),''),CHAR(10),'')
WHERE 1 = 1
AND Cleansed = 0
AND ErrorRecord = 0
AND Processed = 0
import pyodbc as db
import os
import time
import csv

if not 'script_dir' in globals():
    script_dir = os.getcwd()
data_directory = 'data\\'
example_directory = 'WriteLargeFilesExample\\'
file_name = 'EurostatDataOut.csv'
target_path = os.path.join(script_dir,data_directory,example_directory,file_name)

sql = 'SELECT * FROM EurostatData'

#Set up the connection.
print('Connecting to SQL Server database' + time.strftime(' %H:%M:%S'))
connection_string = 'DSN=ETL;'
conn = db.connect(connection_string)
print('Preparing database for update' + time.strftime(' %H:%M:%S'))
csr = conn.cursor()
csr.execute(sql)

with open(target_path,'w',newline='') as f:
    writer = csv.writer(f, delimiter = '|', quoting=csv.QUOTE_NONE)
    writer.writerow([x[0] for x in csr.description])
    for row in csr:
        writer.writerow(row)
        
print('Processing file {} complete.'.format(file_name) + time.strftime(' %H:%M:%S'))
conn.commit()
csr.close()
conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09663461538461539, 'fraction_numerical': 0.00625, 'mean_word_length': 4.812849162011173, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '274119', 'license': 'MIT', 'n_tokens_mistral': 659, 'n_tokens_neox': 630, 'n_words': 261, 'path': '04 Solutions/34-efficient-disk-write.ipynb', 'repo_name': 'MassStreetUniversity/tutorial-python', 'size': '2080'}