In the last post on mail automation I described how I imported mail recipients from an Excel sheet hosted in SharePoint. With a PowerShell script I download the Excel sheet and converted it to a CSV file, FTP it to a Linux environment and imported it to a MySQL database by stating a job in the Data Warehouse. Now it’s time to scrutinize the data Warehouse job.
The first part picks the CSV file from the FTP server and inserts the file into a table:
One job for convert the csv from iso-8859-1 to UTF-8 and one job to import it into MysQL. Now i have all data into one table, but I need some additional information, each recipient works at a location, each location is located in a timezone and all recipients have a preferred language. I create these three tables by select distinct from the first loaded table and add some columns for the extra info I needed for my mail system:
That’s it now I have a database, with four tables, Users which is my master table with all recipients and three support tables, Location, Language and TimeZone. Now I’m ready to tackle the mail templates, that will be the topic for the next post.