INSERT data to a Database Table from a text file

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Intelligent Script to Insert Records in Database Table

Hello All Members, I am new to this forum and to the shell scripting. I want to write a shell script to do the following: Scenario: I have a pipe delimited .txt file with multiple fields in it. The very first row of the file contains the name of the column which resembles the name of the...

2. Shell Programming and Scripting

How to read a text file line by line and insert into a database table?

I have a test file that I want to read and insert only certain lines into the the table based on a filter. 1. Rread the log file 12 Hours back Getdate() -12 Hours 2. Extract the following information on for lines that say "DUMP is complete" A. Date B. Database Name C....

3. Programming

Need help on Insert data to mySQL database

Hi guys, I would like to seek help on inserting data whenever the switch is on or off to my sensor mySQL database in phpMyAdmin from my control.php. I'm using Raspberry PI as my hardware and follow a few tutorials to create my own Web Control Interface, it works perfectly without insert method....

4. Shell Programming and Scripting

Korn shell program to parse CSV text file and insert values into Oracle database

Enclosed is comma separated text file. I need to write a korn shell program that will parse the text file and insert the values into Oracle database. I need to write the korn shell program on Red Hat Enterprise Linux server. Oracle database is 10g.

5. UNIX for Dummies Questions & Answers

Bash script to insert data into an html table

hi, I need to create a bash shell script which picks up data from a text file and in the output file puts it into an html made table. I have to use sed and awk utilties to do this the input text file will contain data in the format: job name para1 para2 para3 para4 para4 1 ...

6. Shell Programming and Scripting

load a data from text file into a oracle table

Hi all, I have a data like, 0,R001,2,D this wants to be loaded into a oracle database table. Pl let me know how this has to be done. Thanks in advance

7. Shell Programming and Scripting

how to insert data into database by reading it from a text file??

Hi....can you guys help me out in this script?? Below is a text file and it contains these: GEF001 000093625 MKL002510 000001 000000 000000 000000 000000 000000 000001 GEF001 000093625 MKL003604 000001 000000 000000 000000 000000 000000 000001 GEF001 000093625 MKL005675 000001...

8. Shell Programming and Scripting

How to insert data into MYSql database from a text file

Hi, Need to get help from you guys about this issue. I need to insert data into MySql database from a text file which is located in other server. The text file is something look like below: Date | SubscriberNo | Call Duration 20/7/07 | 123456788 | 20 20/7/07 | 123412344 | 30 The...

9. Shell Programming and Scripting

ccall database and collect data from one table

I want to connect to one database and collect data from any table using shell script.

10. Shell Programming and Scripting

how to insert data in database based on text file?

Hi....can you guys help me out in this script?? Below is a text file script....called Bukom.txt and it contains these: BUKOM 20060101 2.5 2.6 2.7 2.8 2.9 2.3 2.1 BUKOM 20060102 2.4 2.5 2.6 2.7 2.7 2.6 2.4 BUKOM 20060103 2.1 ...

LEARN ABOUT DEBIAN

gammu-smsd-mysql

GAMMU-SMSD-MYSQL(7)						       Gammu						       GAMMU-SMSD-MYSQL(7)

NAME

       gammu-smsd-mysql - gammu-smsd(1) backend using MySQL database server as a message storage

DESCRIPTION

       MYSQL  backend  stores all data in a MySQL database server, which parameters are defined by configuration (see gammu-smsdrc for description
       of configuration options).

       For tables description see gammu-smsd-tables.

       This backend is based on gammu-smsd-sql.

CONFIGURATION

       Before running gammu-smsd you need to create necessary tables in the database, which is described bellow.

       The configuration file then can look like:

       [smsd]
       service = sql
       driver = native_mysql
       host = localhost

       See also

	      gammu-smsdrc

PRIVILEGES

       The user accessing the database does not need much privileges, the following privleges should be enough:

       GRANT USAGE ON *.* TO 'smsd'@'localhost' IDENTIFIED BY 'password';

       GRANT SELECT, INSERT, UPDATE, DELETE ON `smsd`.* TO 'smsd'@'localhost';

       Note   For creating the SQL tables you need more privileges, especially for creating triggers, which are used for some functionality.

CREATING TABLES

       SQL script for creating tables in MySQL database:

       -- phpMyAdmin SQL Dump
       -- version 2.8.0.3
       -- http://www.phpmyadmin.net
       --
       -- Host: localhost
       -- Generation Time: Jun 10, 2006 at 11:08 PM
       -- Server version: 5.0.18
       -- PHP Version: 5.1.3
       --
       -- Database: `smsd`
       --

       -- --------------------------------------------------------

       --
       -- Table structure for table `daemons`
       --

       CREATE TABLE `daemons` (
	 `Start` text NOT NULL,
	 `Info` text NOT NULL
       ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

       --
       -- Dumping data for table `daemons`
       --

       -- --------------------------------------------------------

       --
       -- Table structure for table `gammu`
       --

       CREATE TABLE `gammu` (
	 `Version` integer NOT NULL default '0'
       ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

       --
       -- Dumping data for table `gammu`
       --

       INSERT INTO `gammu` (`Version`) VALUES(13);

       -- --------------------------------------------------------

       --
       -- Table structure for table `inbox`
       --

       CREATE TABLE `inbox` (
	 `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
	 `ReceivingDateTime` timestamp NOT NULL default '0000-00-00 00:00:00',
	 `Text` text NOT NULL,
	 `SenderNumber` varchar(20) NOT NULL default '',
	 `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
	 `UDH` text NOT NULL,
	 `SMSCNumber` varchar(20) NOT NULL default '',
	 `Class` integer NOT NULL default '-1',
	 `TextDecoded` text NOT NULL default '',
	 `ID` integer unsigned NOT NULL auto_increment,
	 `RecipientID` text NOT NULL,
	 `Processed` enum('false','true') NOT NULL default 'false',
	 PRIMARY KEY `ID` (`ID`)
       ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

       --
       -- Dumping data for table `inbox`
       --

       -- --------------------------------------------------------

       --
       -- Table structure for table `outbox`
       --

       CREATE TABLE `outbox` (
	 `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
	 `InsertIntoDB` timestamp NOT NULL default '0000-00-00 00:00:00',
	 `SendingDateTime` timestamp NOT NULL default '0000-00-00 00:00:00',
	 `SendBefore` time NOT NULL DEFAULT '23:59:59',
	 `SendAfter` time NOT NULL DEFAULT '00:00:00',
	 `Text` text,
	 `DestinationNumber` varchar(20) NOT NULL default '',
	 `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
	 `UDH` text,
	 `Class` integer default '-1',
	 `TextDecoded` text NOT NULL default '',
	 `ID` integer unsigned NOT NULL auto_increment,
	 `MultiPart` enum('false','true') default 'false',
	 `RelativeValidity` integer default '-1',
	 `SenderID` varchar(255),
	 `SendingTimeOut` timestamp NULL default '0000-00-00 00:00:00',
	 `DeliveryReport` enum('default','yes','no') default 'default',
	 `CreatorID` text NOT NULL,
	 PRIMARY KEY `ID` (`ID`)
       ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

       CREATE INDEX outbox_date ON outbox(SendingDateTime, SendingTimeOut);
       CREATE INDEX outbox_sender ON outbox(SenderID);

       --
       -- Dumping data for table `outbox`
       --

       -- --------------------------------------------------------

       --
       -- Table structure for table `outbox_multipart`
       --

       CREATE TABLE `outbox_multipart` (
	 `Text` text,
	 `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
	 `UDH` text,
	 `Class` integer default '-1',
	 `TextDecoded` text default NULL,
	 `ID` integer unsigned NOT NULL default '0',
	 `SequencePosition` integer NOT NULL default '1',
	 PRIMARY KEY (`ID`, `SequencePosition`)
       ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

       --
       -- Dumping data for table `outbox_multipart`
       --

       -- --------------------------------------------------------

       --
       -- Table structure for table `pbk`
       --

       CREATE TABLE `pbk` (
	 `ID` integer NOT NULL auto_increment,
	 `GroupID` integer NOT NULL default '-1',
	 `Name` text NOT NULL,
	 `Number` text NOT NULL,
	 PRIMARY KEY (`ID`)
       ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

       --
       -- Dumping data for table `pbk`
       --

       -- --------------------------------------------------------

       --
       -- Table structure for table `pbk_groups`
       --

       CREATE TABLE `pbk_groups` (
	 `Name` text NOT NULL,
	 `ID` integer NOT NULL auto_increment,
	 PRIMARY KEY `ID` (`ID`)
       ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

       --
       -- Dumping data for table `pbk_groups`
       --

       -- --------------------------------------------------------

       --
       -- Table structure for table `phones`
       --

       CREATE TABLE `phones` (
	 `ID` text NOT NULL,
	 `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
	 `InsertIntoDB` timestamp NOT NULL default '0000-00-00 00:00:00',
	 `TimeOut` timestamp NOT NULL default '0000-00-00 00:00:00',
	 `Send` enum('yes','no') NOT NULL default 'no',
	 `Receive` enum('yes','no') NOT NULL default 'no',
	 `IMEI` varchar(35) NOT NULL,
	 `Client` text NOT NULL,
	 `Battery` integer NOT NULL DEFAULT -1,
	 `Signal` integer NOT NULL DEFAULT -1,
	 `Sent` int NOT NULL DEFAULT 0,
	 `Received` int NOT NULL DEFAULT 0,
	 PRIMARY KEY (`IMEI`)
       ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

       --
       -- Dumping data for table `phones`
       --

       -- --------------------------------------------------------

       --
       -- Table structure for table `sentitems`
       --

       CREATE TABLE `sentitems` (
	 `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
	 `InsertIntoDB` timestamp NOT NULL default '0000-00-00 00:00:00',
	 `SendingDateTime` timestamp NOT NULL default '0000-00-00 00:00:00',
	 `DeliveryDateTime` timestamp NULL,
	 `Text` text NOT NULL,
	 `DestinationNumber` varchar(20) NOT NULL default '',
	 `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
	 `UDH` text NOT NULL,
	 `SMSCNumber` varchar(20) NOT NULL default '',
	 `Class` integer NOT NULL default '-1',
	 `TextDecoded` text NOT NULL default '',
	 `ID` integer unsigned NOT NULL default '0',
	 `SenderID` varchar(255) NOT NULL,
	 `SequencePosition` integer NOT NULL default '1',
	 `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error') NOT NULL default 'SendingOK',
	 `StatusError` integer NOT NULL default '-1',
	 `TPMR` integer NOT NULL default '-1',
	 `RelativeValidity` integer NOT NULL default '-1',
	 `CreatorID` text NOT NULL,
	 PRIMARY KEY (`ID`, `SequencePosition`)
       ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

       CREATE INDEX sentitems_date ON sentitems(DeliveryDateTime);
       CREATE INDEX sentitems_tpmr ON sentitems(TPMR);
       CREATE INDEX sentitems_dest ON sentitems(DestinationNumber);
       CREATE INDEX sentitems_sender ON sentitems(SenderID);

       --
       -- Dumping data for table `sentitems`
       --

       --
       -- Triggers for setting default timestamps
       --

       DELIMITER //

       CREATE TRIGGER inbox_timestamp BEFORE INSERT ON inbox
       FOR EACH ROW
       BEGIN
	   IF NEW.ReceivingDateTime = '0000-00-00 00:00:00' THEN
	       SET NEW.ReceivingDateTime = CURRENT_TIMESTAMP();
	   END IF;
       END;//

       CREATE TRIGGER outbox_timestamp BEFORE INSERT ON outbox
       FOR EACH ROW
       BEGIN
	   IF NEW.InsertIntoDB = '0000-00-00 00:00:00' THEN
	       SET NEW.InsertIntoDB = CURRENT_TIMESTAMP();
	   END IF;
	   IF NEW.SendingDateTime = '0000-00-00 00:00:00' THEN
	       SET NEW.SendingDateTime = CURRENT_TIMESTAMP();
	   END IF;
	   IF NEW.SendingTimeOut = '0000-00-00 00:00:00' THEN
	       SET NEW.SendingTimeOut = CURRENT_TIMESTAMP();
	   END IF;
       END;//

       CREATE TRIGGER phones_timestamp BEFORE INSERT ON phones
       FOR EACH ROW
       BEGIN
	   IF NEW.InsertIntoDB = '0000-00-00 00:00:00' THEN
	       SET NEW.InsertIntoDB = CURRENT_TIMESTAMP();
	   END IF;
	   IF NEW.TimeOut = '0000-00-00 00:00:00' THEN
	       SET NEW.TimeOut = CURRENT_TIMESTAMP();
	   END IF;
       END;//

       CREATE TRIGGER sentitems_timestamp BEFORE INSERT ON sentitems
       FOR EACH ROW
       BEGIN
	   IF NEW.InsertIntoDB = '0000-00-00 00:00:00' THEN
	       SET NEW.InsertIntoDB = CURRENT_TIMESTAMP();
	   END IF;
	   IF NEW.SendingDateTime = '0000-00-00 00:00:00' THEN
	       SET NEW.SendingDateTime = CURRENT_TIMESTAMP();
	   END IF;
       END;//

       DELIMITER ;

       Note   You can find the script in docs/sql/mysql.sql as well.

UPGRADING TABLES

       The easiest way to upgrade database structure is to backup old one and start with creating new one based on example above.

       For upgrading existing database, you can use changes described in smsd-tables-history and then manually update Version field in	gammu  ta-
       ble.

AUTHOR

       Michal iha <michal@cihar.com>

COPYRIGHT

       2009-2012, Michal iha <michal@cihar.com>

1.31.90 							 February 24, 2012					       GAMMU-SMSD-MYSQL(7)

Web Development