Import a list of CSV files into their appropriate tables and email the results when finished.

By : Keegan

<?php
# Author: Keegan
# Email: keegan@sifizm.com
# Web Site: www.sifizm.com

# I run this script from a cron job every night to update
# the mysql database I use with my employee web site
# so it matches my local database every day. Feel free to
# modify it to meet your specific needs. If you find it
# usefull, drop me an email and let me know.

# edit the follow six items to use the script

# first connect to your mysql database
# i have my connection settings in a diferent file
# so i just include that file in all my scripts
include("db.php");

# assign the tables that you want to import to to the table array
$table = array(
'table1',
'table2',
'table3',
'table4',
'table5',
);

# if the first row of your csv file contains column headings:
# $columnheadings=1
# if the first row does not contain column headings and should be imported:
# $columnheadings=0
$columnheadings = 0;

# contains the email address you want the results sent to
$emailaddress = "user@domain.com";

# contains the subject you want the message to have
$subject = "Enter Subject Here";

# contains the email address that will show in the from line
$emailfrom = "user@domain.com";

# you should not have to edit anything below this line



# perform the required operations for every table listed in the table array
foreach ($table as $tablename) {

# empty the table of its current records
$deleterecords = "TRUNCATE TABLE `$tablename`";
mysql_query($deleterecords);

# intialize your counters for successful and failed record imports
$pass = 0;
$fail = 0;

# the csv file needs to be the same name as the table,
# comma seperated with the columns in the same order as the table,
# and in the same dir as this script
$filecontents = file ("$tablename.csv"); # .csv is added to the table name to get the name of the csv file

# every record in the csv file will be inserted into the table unless an error occurs with that record
for($i=$columnheadings; $i<sizeof($filecontents); $i++) {
$insertrecord = "Insert Into `$tablename` Values ($filecontents[$i])";
mysql_query($insertrecord);
if(mysql_error()) {
$fail += 1; # increments if there was an error importing the record
}
else
{
$pass += 1; # increments if the record was successfully imported
}
}

# adds a line to the email message we will send stating how many records were imported
# and how many records failed for each table
$message .= "Table $tablename: Success=$pass Failure=$fail \n";
}

# set to the date and time the script was run
$runtime = (date("d M Y H:i"));

# add the run time to the body of the email message
$message .= "\nTime of the message: $runtime (server time zone)\n\n";

# Send the email message
mail($emailaddress, $subject, $message, "From: '$emailfrom'");

?>

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

More Database Code Articles
More By Codewalkers

 

developerWorks - FREE Tools!

NEW! Don't wait! Try the Rational Application Developer (RAD) v7.5 open beta code today Download the Rational Application Developer (RAD) v7.5 open beta code and start developing applications for the JEE5 standard which features EJB3.0, JPA, JSF 1.2, JSP 2.1 and Servlet 2.5 standards. When you use this beta you will see how you can increase developer productivity for already existing applications with improved support for refactoring, as well as adding new features to existing applications. In addition, the beta provides tooling for JD Edwards, Oracle, SAP, Siebel and PeopleSoft to improve the developer productivity with these enterprise systems. FREE! Go There Now!

NEW! Successful Change and Release Management for .NET Join this webcast to discover the key requirements for successful change and release management. Learn how to extend your .NET environment to improve productivity and collaboration, and address core problems afflicting team development. In this webcast, we’ll review typical challenges faced by customers and how to resolve them with the IBM Rational Change and Release Management solution, including Rational ClearCase, Rational ClearQuest and Rational Build Forge. Replay is available for 9 months. FREE! Go There Now!

NEW! Best Practices: The Integrated Project and Portfolio Management Platform. Hear how IBM Rational Project and Portfolio Management integrated solutions help teams put the right tools and processes in place to maximize the effectiveness and efficiency of project teams and ensure that the business vision is being executed correctly. Learn how to automate and integrate requirements prioritization, top-down project planning, communications and controls, and methodology deployment to keep your scope, costs, and schedules under control. Tackle with an end-to-end approach the management of scope and scope changes, usage of methodology to control and empower project teams, and optimization of resources to align activity costs with the overall project plan. FREE! Go There Now!

NEW! Innovate don't duplicate! Asset reuse strategies for success Asset Reuse is a key strategy for companies looking to create innovative solutions to solve complex software development problems. Searching for, identifying, updating, using and deploying software assets can be a difficult challenge. Listen to this webcast, to learn about strategies and tools that you can leverage for a successful project, including Rational Asset Manager, Rational Software Architect and WebSphere Service Registry and Repository. FREE! Go There Now!

NEW! Webcast: WebSphere Process Server WebSphere Process Server delivers a unique integration framework that simplifies existing IT resources. Often, as IT assets grow to support business demand, so too does their complexity and manageability. In this webcast, we’ll discuss how WebSphere Process Server helps deliver an SOA infrastructure that provides a common model to orchestrate, mediate, connect, map, and execute the underlying IT functions. Discover how WebSphere Process Server simplifies integration of business processes by leveraging existing IT assets as reusable services without the complexities of traditional integration methodologies. FREE! Go There Now!

NEW! Rational Build Forge Express eKit Rational Build Forge Express Edition is an automation framework that packages the latest enterprise-grade technologies into a reliable, flexible and robust configuration designed and priced specifically for small to midsize businesses. The new Rational Build Forge Express eKit provides you with valuable resources – including a case study, podcast, demo, and articles – to help you increase staff productivity, compress development cycles and deliver better software, fast. FREE! Go There Now!

NEW! Maintaining QoS and Process Integrity in an SOA Environment This webcast outlines the best practices that must be instituted to gain the maximum benefit from SOA while maintaining high quality of service. Whether you are deploying new applications or managing and monitoring your existing infrastructure, learn how you can ensure high quality of services with SOA based solutions from IBM. All registrants who attend this live Web Seminar will receive complimentary access to a white paper titled “Maintaining QoS in an SOA Environment”. FREE! Go There Now!

NEW! Test terminal-based applications with Rational Functional Tester Regression testing -- in which code is thoroughly tested to ensure that changes have not produced unexpected results -- is an important part of any development process. But many testing environments neglect the terminal-based applications that still form the backbone of many industries. In this tutorial, you'll learn how the Rational Functional Tester Extension for Terminal-Based Applications works with other Rational Functional Tester to help test terminal-based applications quickly and easily. FREE! Go There Now!

NEW! IBM Enterprise Modernization Sandbox for System z: Architecture Analysts, architects, and developers who have existing COBOL or PL/I skills and want to extend those skills to deploy new workloads on the mainframe can use the IBM Enterprise Modernization Sandbox for System z to find hands-on walkthroughs of common real world scenarios. The scenarios provide examples of how to rapidly design, create, assemble, test, and deploy high-quality Web, Web services, portal, and SOA applications for IBM CICS, IBM IMS, and IBM WebSphere Application Server. FREE! Go There Now!

Role of Integrated Requirements Management in Software Delivery As organizations integrate software into every aspect of business, they are constantly pressured to deliver faster, better, and cheaper results. Unfortunately, a “dis-integrated” software delivery approach reduces returns while increasing costs. This IBM Rational White Paper shows how Integrated Requirements Management aligns organizations around maximizing value and keeping pace with change. FREE! Go There Now!

All FREE IBM® developerWorks Tools!