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! |
Download the IBM WebSphere Portal V6.1 beta code and learn more about the rich features and enhancements in IBM WebSphere Portal V6.1. WebSphere Portal provides a composite application or business mashup framework and the advanced tooling needed to build flexible, SOA-based solutions, and scalability to meet the needs of any size organization. FREE! Go There Now!
|
|
|
|
Listen to this webcast to get an overview of Info 2.0 and a technical demo of how to quickly build an enterprise mashup. IBM's Info 2.0 technology leverages emerging Web 2.0 technologies such as mashups, feeds, AJAX, and JSON in order to simplify assembly of information using feeds and services. Come learn about the technical elements of Info 2.0 including the Feed Generation framework, Mashup Engine, and mashup assembly components. Learn how to pull information from databases, departmental information, and the Web to create mashups critical to your company’s success. We will also discuss best practices to help you get started. FREE! Go There Now!
|
|
|
|
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!
|
|
|
|
Rational Modeling Extension for Microsoft .NET enhances usability for code generation supporting a more intelligent refactoring. The latest enhancements enable organizations with Java and .NET systems and software development maintain architectural integrity across heterogeneous platforms. FREE! Go There Now!
|
|
|
|
Learn the basics of the IBM Customer Information Control System (CICS). With a hands-on exercise, learn how to get your first CICS application up and running on your desktop using TXSeries V6.1 for Windows. The tutorial shows you how to download and install a free trial version of TXSeries V6.1. FREE! Go There Now!
|
|
|
|
You can now evaluate IBM Rational Asset Manager V7.0 online without installing or configuring it on your own system! Rational Asset Manager helps create, modify, govern, find, and reuse any type of development assets, including SOA and systems development assets. Rational Asset Manager helps you reduce software development costs and improve quality by facilitating the reuse of all types of software development-related assets. Visit developerWorks to learn more about this product and register to explore its capabilities online. FREE! Go There Now!
|
|
|
|
Join this Rational Talks to You teleconference, to hear how Enterprise Generation Language (EGL) eliminates the need for tedious and error-prone low level coding, so developers can focus on business requirements. EGL extends the Rational software development platform with a simplified programming language that enables developers who have little or no experience with Java, Web technologies or Service Oriented Architecture, to create enterprise-class applications and services quickly and easily. It also allows developers who may have little or no mainframe programming experience to quickly create traditional mainframe components. FREE! Go There Now!
|
|
|
|
Attend this launch webcast with Scott Hebner, Vice President of IBM Rational Marketing and Strategy, where he will overview Rational’s new offerings and programs to help customers accelerate software innovation on System z. He will discuss how these solutions help organizations extend their core business processes toward modern architectures such as SOA and web technologies to deliver business improvements that stand the test of time. FREE! Go There Now!
|
|
|
|
In this webcast, you'll get an introduction to the eXtreme Transaction Processing (XTP) features of WebSphere Extended Deployment and the common architectural traits required by XTP applications. See how WebSphere Extended Deployment's ObjectGrid feature provides a state-of-the-art infrastructure for hosting XTP applications. FREE! Go There Now!
|
|
|
|
User communities play an important role in communication and collaboration around products, solutions and other areas of special interest to members. Successful communities are able to provide the right mix of content and services to deliver a value proposition that resonates with each audience. Join Tom Inman, VP of Marketing for Information and Platform Solutions as he introduces the new LeverageINFORMATION community. During this webcast, learn about the value provided by the community and how customers and partners derive value from the community in addressing their own technical and business challenges. FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools! |