CSV import to SQL

[Japri]

Hello all, nice to know this helpfull forum,

I'm a totally newbie and I need advice,

I want to import a CSV to a table in sql, but the problem is the CSV has very strange format.

==================================

Telephone,=,'88213839
Name,=,John Doe
Currency,=,USD

Date,Information,Source,Total,,Balance
'30/04,ATM 28/04 ,'0000,000000000500000.00,DB,1.171892966E7
'30/04,DEBIT 28/04 LAWSON,'0000,000000000092100.00,DB,1.162682966E7
'30/04,DEBIT 29/04 7-ELEVEN,'0000,000000000068000.00,DB,1.155882966E7
'30/04,DEBIT 30/04 CARREFOUR,'0000,000000000254150.00,DB,1.130467966E7
Begin,=,12218929.66
Income,=,3.6101013456E8
Outcome,=,1.080887041E7
Balance,=,3.6242019381E8

======================================

All i need is to import the middle section
"

'30/04,KARTU DEBIT 30/04 CARREFOURCENTRAL,'0000,000000000254150.00,DB,1.130467966E7

"
and append all the value to the SQL table. and i don't want to add same field again to the database.

I have use this code

$row = 1;
  if (($handle = fopen("4.txt", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        //echo "<p> $num fields in line $row: <br /></p>\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            if ($row > 6){
          echo $data[$c] . "<br />\n";}
        }
    }
    fclose($handle);

=============================

But what i don't know how to do is do delete the last 4 lines

Begin,=,12218929.66
Income,=,3.6101013456E8
Outcome,=,1.080887041E7
Balance,=,3.6242019381E8

============

and how to append then to a sql table?

sorry for super totally novice questions. try to solve it but still meet the death end.

:)

[diafol]

This type of question has been asked to death of late. Not a common theme for a university assessment?

ANyway, search for 'csv database' in the daniweb search (top right) and you'll get LOADS of hits.

[Japri]

OK diafol I'll search.

[diafol]

Great, by all means, if you don't get what you want or can't understand some of the solutions, post back.

[Japri]

hah, i have solve it. :D, but the problem is how to append only new data not so the old data don't get duplicated.

is there any tricks fot this?

[diafol]

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

If you have the primary key. If you don't it's more complicated and you may need to use subqueries.

[Japri]

I have made the unique key into collum one.

then i used this

$dup = mysql_query("SELECT SERIALNO FROM BANKA WHERE SERIALNO=".mysql_real_escape_string($row[0]));
        if(mysql_num_rows($dup) > 0){
         echo '<b>username Already Used.</b>';
    };

but It won't works. Pardon me I'm totally blank about sql. I have Sort the string that i want to insert into array.

the array row[0] is the values i want to check if it's exist in the table.

I read the article you have given but understand nothing. hahahaha (I'm a plumber not a coder).

[Japri]

Aw.. i have solve it, thanks to you diafol, i have all I need so far, since i'm doing a simple application.

mysql_query("INSERT INTO BANKA (SERIALNO, STATUS, CHEKSUM) VALUES ({$row}) ON DUPLICATE KEY UPDATE SERIALNO=SERIALNO")or die(mysql_error());

Please check my mysql query is correct? i have check it out it's working just need a comfirmation.

[diafol]

Well I looks ok, I just wonder about the $row variable, I'm assuming this is a string list of values for the 3 fields noted.

[Japri]

$row is a array of three other variables.

another question

does the SERIALNO=SERIALNO means the SERIALNO is replaced by the new one? or it stay intact? :)

[diafol]

Have you tried it? That's the best way to find out :)