how to insert data in database based on text file?

Login or Register to Ask a Question and Join Our Community

how to insert data in database based on text file?

Tags

Login to Discuss or Reply to this Discussion in Our Community

Top Forums Shell Programming and Scripting how to insert data in database based on text file?

12-11-2005

Registered User

74, 0

Join Date: Sep 2005

Last Activity: 2 July 2008, 10:53 AM EDT

Posts: 74

Thanks Given: 0

Thanked 0 Times in 0 Posts

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 2.3 2.5 2.6 2.7 2.7 2.6

I would like this set of data to be auto read by the script to be insert into the database.
The database table is call bukom_table
and its contains 4 fields..namely:
locn,
date,
hour,
strength..

How to create a script that insert this data into the database?
for example bukom will be in locn...and i do not want anyspace behind.
and for the date will be 20060101 and the strength will be 2.5.

ok for the fields hour it will have to create manually...like for first row..2.5 is the strength and 0000 will be hour..follow by 2.6 will be 0030 hour and 2.7 will be 0100 hour. likewise for the second row...2.4 will be 0000 hour and 2.5 will be 0030 hour and so on...

im having a headache now on working on this scripts....hope you(expertists) can help

a million thanks

forevercalz

View Public Profile for forevercalz

Find all posts by forevercalz

12-12-2005

Registered User

220, 1

Join Date: Feb 2004

Last Activity: 19 November 2015, 4:17 AM EST

Location: Oslo, Norway

Posts: 220

Thanks Given: 0

Thanked 1 Time in 1 Post

Have you thought about SQL Loader??
Invoking SQL Loader through a script can help you.

encrypted

View Public Profile for encrypted

Find all posts by encrypted

12-12-2005

Registered User

74, 0

Join Date: Sep 2005

Last Activity: 2 July 2008, 10:53 AM EDT

Posts: 74

Thanks Given: 0

Thanked 0 Times in 0 Posts

yes...i thought of that too.... however my text file is not formated in a way to be append in and its a very huge set of data..

forevercalz

View Public Profile for forevercalz

Find all posts by forevercalz

12-12-2005

Registered User

158, 0

Join Date: Sep 2004

Last Activity: 2 February 2010, 2:41 PM EST

Location: Mumbai-India

Posts: 158

Thanks Given: 0

Thanked 0 Times in 0 Posts

Make a new formatted file

You can write code to create the formmated data you want and write it to a csv file. The format of the csv files should be:

field1,field2,field3,field4

You should then need a corresponding ctl file containg the column names in a comma separated format. Then you can have a shell script calling the sqlldr which can use the data and ctl files.

Regards,
Rahul.

rahulrathod

View Public Profile for rahulrathod

Find all posts by rahulrathod

12-12-2005

Registered User

158, 0

Join Date: Sep 2004

Last Activity: 2 February 2010, 2:41 PM EST

Location: Mumbai-India

Posts: 158

Thanks Given: 0

Thanked 0 Times in 0 Posts

And however large the mount of data maybe there are many options in sqlldr to load it into the DB in less than a minute. Just take care that the table u r inserting data into does not have any indexes created on them.

rahulrathod

View Public Profile for rahulrathod

Find all posts by rahulrathod

12-12-2005

Registered User

74, 0

Join Date: Sep 2005

Last Activity: 2 July 2008, 10:53 AM EDT

Posts: 74

Thanks Given: 0

Thanked 0 Times in 0 Posts

i got over thousands of data and its hard for me to format all into the correct fields....i need a script to auto read it as there are more than 8 files to read from.......above are my problems....as you see the hour field needed to be inserted manually..i will need days to insert it as the records are too much...

forevercalz

View Public Profile for forevercalz

Find all posts by forevercalz

12-20-2005

Registered User

1, 0

Join Date: Dec 2005

Last Activity: 20 December 2005, 12:33 AM EST

Posts: 1

Thanks Given: 0

Thanked 0 Times in 0 Posts

You can with mysqlimport

Quote:

Originally Posted by forevercalz

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 2.3 2.5 2.6 2.7 2.7 2.6

I would like this set of data to be auto read by the script to be insert into the database.
The database table is call bukom_table
and its contains 4 fields..namely:
locn,
date,
hour,
strength..

How to create a script that insert this data into the database?
for example bukom will be in locn...and i do not want anyspace behind.
and for the date will be 20060101 and the strength will be 2.5.

ok for the fields hour it will have to create manually...like for first row..2.5 is the strength and 0000 will be hour..follow by 2.6 will be 0030 hour and 2.7 will be 0100 hour. likewise for the second row...2.4 will be 0000 hour and 2.5 will be 0030 hour and so on...

im having a headache now on working on this scripts....hope you(expertists) can help Smilie

Smilie

a million thanks Smilie

Smilie

mysqlimport --fields-terminated-by=" " your_database Bukom.txt

a_akmal

View Public Profile for a_akmal

Find all posts by a_akmal

Login or Register to Ask a Question

Previous Thread | Next Thread

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

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. 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. 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'

6. 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....

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

Extracting data from text file based on configuration set in config file

Hi , a:) i have configuration file with pattren <Range start no>,<Range end no>,<type of records to be extracted from the data file>,<name of the file to store output> eg: myfile.confg 9899000000,9899999999,DATA,b.dat 9899000000,9899999999,SMS,a.dat b:) Stucture of my data file is...

10. 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...

Login or Register to Ask a Question