Visit Our UNIX and Linux User Community


How to insert data into MYSql database from a text file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to insert data into MYSql database from a text file
# 1  
Old 10-16-2007
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 number of record will be different in each file.

How can I do this in shell script? What command I can use? Hope all expertists can help me on this. Thanks a million. Smilie
# 2  
Old 10-16-2007
Login into mysql with "mysql -uroot -pyourpass" and then execute this command, but change it to match your requirement.
Code:
mysql> LOAD DATA LOCAL INFILE '/path/to/file.txt' INTO TABLE your-table-name;

# 3  
Old 01-08-2008
All the data become NULL after load into database

Hi sysgate,

Sorry that I just have time tested on this.

After I load the data from text file, all the value become NULL in the database. I created the datatype to be VARCHAR(32) and also INT(20) but all show me the same NULL result. For the date, I declared the datatype as DATE, it also show the NULL value.

I really dont know what is happening. When I execute this command, it didnt show any error. Really dont know how to debug on this. Smilie Please advise. Thank you so much.

Br,
Shirley
# 4  
Old 01-09-2008
How to insert date in string format into MYSQL table as DATE data type

Hi all,

Let says I have date in string format like 01/01/08, how can I insert it into MYSQL table as DATE data type?

I created the table and declare data type for Date column as DATE, when I insert this 01/01/08 value into table, it become 00:00:00. But if I declare the Date column as VARCHAR, then the 01/01/08 value can be inserted into the table. Is it OK for me to declared Date coulmn as VARCHAR? Will it cause any problem or issue later on? Please advise.

Thank you so much.

Br,
Shirley
# 5  
Old 01-11-2008
Date conversion command in shell script

Hi all,

Is there any command that can be used to convert the date from String to Date datatype by using shell script?

Let say I have the date like 01/01/07 from an input file, before I able to LAOD the file into database, I need to convert the date to Date datatype first so that the date value can be accepted.

Please advise.

Thank you so much.

Br,
Shirley

Previous Thread | Next Thread
Test Your Knowledge in Computers #227
Difficulty: Easy
According to NetMarketShare, in September 2019 BSD had a 0.01% global market share of the desktop computing market.
True or False?

10 More Discussions You Might Find Interesting

1. 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.... (2 Replies)
Discussion started by: JolietJake
2 Replies

2. 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.... (1 Reply)
Discussion started by: aoiregion
1 Replies

3. Shell Programming and Scripting

Need help on Insert data to phpMyAdmin mySQL database from Shell Script

Sorry to disturb you, I would like to seek help on inserting data whenever the switch is on or off to my phpMyAdmin mySQL database from my Shell Script. I'm using Raspberry PI as my hardware and I have follow this LINK: instructables.com/id/Web-Control-of-Raspberry-Pi-GPIO/?ALLSTEPS to create my... (4 Replies)
Discussion started by: aoiregion
4 Replies

4. Shell Programming and Scripting

Scrape 10 million pages and save the raw html data in mysql database

I have a list of 10 million page urls. I want those pages scraped and saved in the mysql database as raw html. I own a Linux VPS server with 1GB RAM and WHM/cPanel. I would like to scrape at least 100,000 urls in 24 hours. So can anyone give me some sample shell scripting code? (4 Replies)
Discussion started by: Viruthagiri
4 Replies

5. 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. (15 Replies)
Discussion started by: shellguy
15 Replies

6. Web Development

INSERT data to a Database Table from a text file

If you have a text file and if you want to Insert data to your Database Table, You can do it with these queries LOAD DATA LOCAL INFILE '/path/yourTextFile.txt' INTO TABLE yourTableName FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' (0 Replies)
Discussion started by: sitex
0 Replies

7. Shell Programming and Scripting

shell script to insert data from gps.txt to mysql database

Hi, I have gps receiver, by using gpsd data i can read gps log data to my database(my sql). Steps: 1. telenet localhost 2947 > gps.txt (press enter) 2. r (press enter) //then i will get the data like below in gps.txt file Trying 127.0.0.1... Connected to localhost.... (1 Reply)
Discussion started by: gudivada213
1 Replies

8. 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... (4 Replies)
Discussion started by: pallavishetty
4 Replies

9. Shell Programming and Scripting

Need Shell Script to upload data from Text file to Oracle database

Hi Does any one have any idea on uploading the data using Unix Shell script from text file to Oracle database. Requirement:- 1. Need to connect to Oracle database from Unix Shell script. 2. Need to pick Text file from some location on Unix Box. 3. Need to upload the data from text file to... (6 Replies)
Discussion started by: chandrashekharj
6 Replies

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 ... (9 Replies)
Discussion started by: forevercalz
9 Replies

Featured Tech Videos