How to insert data into MYSql database from a text file

Shell Programming and Scripting
1

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. :slight_smile:

2

Login into mysql with "mysql -uroot -pyourpass" and then execute this command, but change it to match your requirement.

mysql> LOAD DATA LOCAL INFILE '/path/to/file.txt' INTO TABLE your-table-name;
3

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. :confused: Please advise. Thank you so much.

Br,
Shirley

4

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

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