[SOLVED] Writing CSV File Data Into MYSQL Database

[winmastergames]

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.

[jonsjava]

<?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);
}

[winmastergames]

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

[winmastergames]

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,[email protected]
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`
--

[digitalgod]

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.

[winmastergames]

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

[winmastergames]

Sorry Ive Found a way to do this using the comma separated way