CSV file And Database

Question

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);
}

Is there a question somewhere? An error you're getting or an issue you're having? — Dave, Nov 15 '12 at 16:37
don't create tables automatically. you should never import data that you don't know the format of. once you elminate that, mysql can directly import csv data: http://dev.mysql.com/doc/refman/5.1/en/load-data.html — Marc B, Nov 15 '12 at 16:38
Does this question help? http://stackoverflow.com/questions/11077801/import-csv-to-mysql-table — Grim..., Nov 15 '12 at 16:39
@MarcB Actually my real task is to compare two csv file based on their minimum cost in asc order so i thought it would be better if i get the csv file store them to my db and filter result via sql queries. — soft genic, Nov 15 '12 at 16:44
@softgenic: that's fine, just don't auto-create tables from arbitary csv data. what if you get a file that has a field with 51 chars? — Marc B, Nov 15 '12 at 16:45
@Dave Not any errors yet above code is working fine and returning all the fileds row wise present in my csv file. I myself not able to narrow down how to create tables dynamically and start storing from the 2nd row and so on. The reason I asked question to get help from experts here — soft genic, Nov 15 '12 at 16:47
@MarcB in your point of view storing in sql table to filter result is a good and easy approach right or it can be done by any other way? — soft genic, Nov 15 '12 at 16:54
no. putting your data into a real database is the best way, even if it's just for a short period. csv files are highly inefficient work with for 'arbitary' methods, such as filtering/querying. — Marc B, Nov 15 '12 at 17:18
@MarcB Thanks alot I'll keep that in mind its really a good method to dump all data in mysql i was going for for loop but thanks for giving me a good advice.. — soft genic, Nov 15 '12 at 17:27
You can convert your csv file into database and expose apis from that by https://themockapis.in/documentation#csvdatabase — nkkumawat, May 12 '21 at 06:52

LSerni · Accepted Answer · 2012-11-15T17:36:27.597

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.

Okay The current solution is working to dump all the rows , Now one last question lets suppose i have 6 coulumns in the .csv file and i want to dump only three respective columns in my db table how would i do that in that case? — soft genic, Nov 15 '12 at 17:25

CSV file And Database

1 Answers1

Update