how to store text file information into my sql database

Question

this is my text file

StudentId Name  Dept address     city
1         Chellappa   CSE 22 xx-colony 2nd street   coimbatore
2         Vijay        IT 23 yy colony  coimbatore

In this file (22 xx-colony 2nd street) and (23 yy colony) is address to store in database address column

use DBI;
use strict;

my $driver = "mysql"; 
my $database = "TESTDB";
my $dsn = "DBI:$driver:database=$database";
my $userid = "root";
my $password = "1234";

my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

my $query = 'INSERT INTO student (StudentId,Name,Dept,address,city) VALUES (?,?,?,?,?)';
my $sth = $dbh->prepare($query) or die "Prepare failed: " . $dbh->errstr();

open my $fh, "<", "text.txt" or die $!;
 <$fh>;  #skip header

  while (<$fh>)
 {
    chomp;
    my @vals = split; 

    $sth->execute(@vals);
 }
close $fh;

this code is doesn't work properly for that text file anyone can help me

Answer 1

DBD::mysql::st execute failed: called with 8 bind variables when 4 are needed

That error is not generated by the code that you have shown us. Your code contains this:

my $query = 'INSERT INTO student (StudentId,Name,Dept,address,city) VALUES (?,?,?,?,?)';

That SQL query contains five bind points, not four. It's always important to be accurate when reporting these things.

Anyway, the important thing is that the number of bind points is different to the number of values you pass to execute(). And it's pretty simple to see where that problem comes from.

Your first data line is this:

1         Chellappa   CSE 22 xx-colony 2nd street   coimbatore

And you split that data into the @vals array with this code:

my @vals = split; 

With no arguments, split() splits $_ on whitespace (you can read the documentation online).

So, after running that code on that data, you'll end up with eight values in @vals. Those values are:

1, Chellappa, CSE, 22, xx-colony, 2nd, street, coimbatore

So it's clear that your simple split() isn't processing your data in the way that you expect. You'll need to come up with a more sophisticated way to extract your five expected data items from your input record.

Unfortunately, you don't give us any detail on how your input file is structured, so we can't give you any more help on how to fix your problem.

Update: Guessing at what you might want here, the update statement could become something like:

$sth->update(@vals[0 .. 2], join ' ', @vals[3..6], $vals[7]);

But I have no idea how well that will work with other lines of data that might have the whitespace in other places.