Inserting CSV into MYSQL

[eluser]Ducky[/eluser]
Hi,

I'm trying to import a CSV file into a Mysql database. To do this i created a controller who reads the CSV file, and i use the following command

Code:

$this->db->set('name', $name);

to add it to the SQL statement.
Everything goes fine until a certain point. Then suddenly the text from field A is partial imported and the second half is imported 2 fields further (Field C). the text didn't split on a space but in the middle of the word "installation".
When this occurs the import will continue but the content isn't of the fields don't match anymore.
When i change the text manually it works fine but then many records further the same problem exists with another record.

You could say change everything manually and then import it. The problem with this method is that the csv will be provided to my application once an hour so it is impossible to do this manually every time

I don't believe it has something to do with UTF-8 because i'm using the active record method which escapes the values.


Can someone help me with this?
Kind regards
ducky

[eluser]überfuzz[/eluser]
What core functions are you using? Some of them have field length as parameter. Have you checked that?

[eluser]Ducky[/eluser]
This is the code i used to read the CSV file

Code:

function countryfiches()
{
$this->db->truncate('_countryfiches');
$this->dbutil->optimize_database();
$this->db->query("ALTER TABLE _countryfiches AUTO_INCREMENT=1");

$counter=0;
$handle = fopen("csv/countryfiches.txt", "r");
    while (($data = fgetcsv($handle, 1000, ";")) !== FALSE)
    {
        $num = count($data);
        for ($c=0; $c < $num; $c++)
            {
            switch($counter)
                {
                //CASE 1 ISN'T Needed
                case 2:$Countryfiche_CDNumber=$data[$c];
                $this->db->set('CD_Number',$Countryfiche_CDNumber);

                case 3:$Countryfiche_CDTtitle=$data[$c];
                $this->db->set('Countryfiche_CdTitle',$Countryfiche_CDTtitle);
                break;
                case 4:$Country_ID=$data[$c];
                $this->db->set('Country_ID',$Country_ID);
                break;
                case 5:$Countryfiche_Release=$data[$c];
                $this->db->set('Countryfiche_Release',$Countryfiche_Release);
                break;
                case 6:$Countryfiche_Factor=$data[$c];
                $this->db->set('Countryfiche_Factor',$Countryfiche_Factor);
                break;
                case 7:$Countryfiche_Memofield=$data[$c];
                $this->db->set('Countryfiche_Memofield',$Countryfiche_Memofield);
                break;
                case 8:$Countryfiche_ForSale=$data[$c];
                if ($Countryfiche_ForSale=="True"){$Countryfiche_ForSale=1;}else{$Countryfiche_ForSale=0;}
                $this->db->set('Countryfiche_ForSale',$Countryfiche_ForSale);
                $this->db->insert('_countryfiches');
                                    
                $counter=0;
                break;
                }
            $counter++;

            }
    }
fclose($handle);
}

While testing the code i changed every field in the database to text except for the Memofield. This one is a longtext in phpmyadmin.

[eluser]überfuzz[/eluser]
Try:

Code:

while (($data = fgetcsv($handle, 0, ";")) !== FALSE)
{
  etc...
}

instead of this:

Code:

while (($data = fgetcsv($handle, 1000, ";")) !== FALSE)
{
  etc...
}

(Just to see if the length is messing things up.)

[eluser]Ducky[/eluser]
Thanks for your response. But when i change it to 0 it doesn't import anything...
So i tried to set it to 10000 and it works perfectly.
I didn't think it would mess up the data if the length would exceed the max length. I thought he would jump to the next record and start from the beginning (as a new record).
But you telling me to change that value really helped me.

I would like to thank you for this.

Kind regards
ducky

[eluser]überfuzz[/eluser]
I seem to recall that the length = 0 is php version 5+. I did a function that read a spreadsheet(csv) and echoed it out as a html-table, some years ago. I think there were some updates to the getcsv-function then. If you're unsure of what php-version you're on, heres a little gift for you.

Code:

echo 'I'm messing about with PHP version: ' . phpversion();

[eluser]überfuzz[/eluser]
LOL
You'd better make it:

Code:

echo "I'm messing about with PHP version: " . phpversion();