winmastergames Posted June 6, 2008 Share Posted June 6, 2008 Hi Guys! Well For A School im Creating a script for a certain service and What i need it to do is Well a program on the server outputs a .csv file daily and what i need to do is have it so every day the PHP script Take that data and imports into a MYSQL database for later use Ive looked around on google for Help But Most Doesnt Exactly do what i want to do Would you guys be able to help me with this Thanks Any Suggestions please comment Ill Put Your User name in Credits for Helping Because i can i and come to PHPFreaks Often and yea! Thanks For Your Time. Quote Link to comment Share on other sites More sharing options...
jonsjava Posted June 6, 2008 Share Posted June 6, 2008 <?php $csv_file = "test.csv"; $fh = fopen($csv_file, "r"); $csv_data = fread($fh, filesize($csv_file)); $data_array = explode("\n", $csv_data); foreach ($data_array as $value){ $new_value = str_replace(",", "', '", $value); $sql = "INSERT INTO `table` VALUES('$new_value');"; mysql_query($sql); } Quote Link to comment Share on other sites More sharing options...
winmastergames Posted June 7, 2008 Author Share Posted June 7, 2008 So will this when opened will it get all Columns and put them into a mysql table? ill see if i can get it going but how do i make it automaticly every day Run this script? Thanks alot jonsjava Quote Link to comment Share on other sites More sharing options...
winmastergames Posted June 7, 2008 Author Share Posted June 7, 2008 Ive tried using it This is what im doing: Main Script: <?php $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'pass'; $dbname = 'webmeterread'; $csv_file = "test.csv"; $fh = fopen($csv_file, "r"); $csv_data = fread($fh, filesize($csv_file)); $data_array = explode("\n", $csv_data); mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql'); foreach ($data_array as $value){ $new_value = str_replace(",", "', '", $value); $sql = "INSERT INTO `coredata` VALUES('$new_value');"; mysql_query($sql); } ?> test.csv: First,Last,Email Bob,Joe,bobjoe@example.com Bilgo,Bagins,bilbo.example.com SQL Database: -- phpMyAdmin SQL Dump -- version 2.11.4 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Jun 07, 2008 at 01:12 PM -- Server version: 5.0.51 -- PHP Version: 5.2.5 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; -- -- Database: `webmeterread` -- -- -------------------------------------------------------- -- -- Table structure for table `coredata` -- CREATE TABLE IF NOT EXISTS `coredata` ( `First` text NOT NULL, `Last` text NOT NULL, `Email` text NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `coredata` -- Quote Link to comment Share on other sites More sharing options...
digitalgod Posted June 7, 2008 Share Posted June 7, 2008 and is it working? you need to setup a cron job so that your script runs daily. If you have a panel like cpanel there's an option in there to setup cron jobs. Quote Link to comment Share on other sites More sharing options...
winmastergames Posted June 7, 2008 Author Share Posted June 7, 2008 Sorry my server doesn't have Cron Jobs that doesn't matter though I found out now that the File isn't actually CSV (Comma separated) Its Tab Delimited Does anyone know how to Take the data from this and insert it into a MYSQL database? Here is the type of document im trying to import: 206011569-6.ls2: [LoadSurvey] LastRecord=0000035481 StartTime=10/04/2008 15:49:00 Record No Date/Time Current Ph-A Avg Current Ph-B Avg Current Ph-C Avg Phase Angle Ph-A Avg Phase Angle Ph-B Avg Phase Angle Ph-C Avg Status (0x00009080) (0x00009081) (0x00009082) (0x00009095) (0x00009096) (0x00009097) 14078 20/04/2008 10:27 0.8 1 1.1 49.4 68.6 52.8 ....... 14079 20/04/2008 10:28 0.8 1 1.1 49.7 68.8 52.9 ....... 14080 20/04/2008 10:29 0.8 1 1.1 49.6 68.7 52.8 ....... 14081 20/04/2008 10:30 0.8 1 1.1 49.5 68.6 52.8 ....... 14082 20/04/2008 10:31 0.8 1 1.1 49.5 68.6 52.8 ....... 14083 20/04/2008 10:32 3.3 3.4 3.5 49.2 66.9 52.6 ....... 14084 20/04/2008 10:33 17.2 15.1 16.2 67.1 67.7 74.6 ....... 14085 20/04/2008 10:34 0.8 1 1.1 50.3 69 52.9 ....... 14086 20/04/2008 10:35 0.8 1 1.1 50.3 69 52.9 ....... 14087 20/04/2008 10:36 0.8 1 1.1 50.5 69.3 53.2 ....... 14088 20/04/2008 10:37 20.2 17.5 19.1 60.8 62.7 69.8 ....... 14089 20/04/2008 10:38 6.5 5.4 6.3 56.8 69.3 62.9 ....... 14090 20/04/2008 10:39 0.8 1 1.1 50.3 69.2 53.4 ....... 14091 20/04/2008 10:40 0.8 1 1.1 50.2 69.1 53.3 ....... 14092 20/04/2008 10:41 0.8 1 1.1 50.1 69 53.2 ....... The data im trying to import is this: Record No Date/Time Current Ph-A Avg Current Ph-B Avg Current Ph-C Avg Phase Angle Ph-A Avg Phase Angle Ph-B Avg Phase Angle Ph-C Avg Status 14078 20/04/2008 10:27 0.8 1 1.1 49.4 68.6 52.8 ....... But everything like that the file isnt this small its 1.3mb So i dont want these: (0x00009097) or [LoadSurvey] LastRecord=0000035481 StartTime=10/04/2008 15:49:00 So would anyone have some code that can import Tab Delimited Data into Mysql Quote Link to comment Share on other sites More sharing options...
winmastergames Posted June 7, 2008 Author Share Posted June 7, 2008 Sorry Ive Found a way to do this using the comma separated way Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.