CODEWITHSUNDEEP
phpmysqlcsv
soft genic
soft genic

Reputation: 2056

CSV file And Database

Following is the sample of my CSV file:

Name  | USD
------------
Ifone | 500
Ufone | 600

I want to read my csv file where my php script reads the first row and then (How to) create table accordingly as per the elements of the first row being returned like from the above example the row elements will be Name and USD.

Create table csv
{
$column1 varchar (50)
$column2 varchar (50)
}

Then (How to) store the elements starting from the 2nd row and so on in the created table. Kindly let me know what is an appropriate way to do that.

Thanks,

Following function I used to get data from my csv file:

$row = 1;
if (($handle = fopen("mycsv.csv", "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++) {
            echo $data[$c] . "<br />\n";
        }
    }
    fclose($handle);
}

Upvotes: 0

Views: 537

Answers (1)

LSerni
LSerni

Reputation: 57453

A better approach would be to:

1) Study the CSV table format and decide on fields names and type, in your case e.g.

fonemodel varchar(50);
cost      integer;      -- or a monetary field

2) Create the table manually from the fields above

CREATE TABLE fones ...

3) Import directly the file:

LOAD DATA LOCAL INFILE '/path/to/mycsv.csv'
INTO TABLE fones
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES

The termination and enclosure depends on the CSV structure (the sample you supplied is actually pipe delimited).

Once you have the two tables stored, you can run comparisons between the appropriate data types (varchar might have given you troubles - 599 could be considered to be less than 60, since the character '5' comes before the '6')

Update

Suppose that the real CSV is something like

 UFone,500,useless data,1234,other useless info,blah blah

and you only wanted model, cost, and stock, i.e. columns 1, 2 and 4. Then you'd specify the input as:

 ...INTO TABLE fones (model, cost, @ignore, stock, @ignore, @ignore)...

instead of simply INTO TABLE.

More information on MySQL's manual page.

Upvotes: 2

Related Questions