Input CSV File and upload it to MySQL DB

Question

Edit2:

Well i found a solution that works for me, the string will be saved in $v[0][0], [1][0], [2][0] ... still not perfekt, but i should work that way.

Code: (Delimiters: ";" and "\n")

$csvData = file_get_contents("data.csv");
$lines = preg_split("/[\n,;]+/", $csvData);
$array = array();
foreach($lines as $line){ $array[]= str_getcsv($line);}

Edit: Save the CSV to a array would solve my problem, i would use the indexes from the array in the sql insert.

I tried following codes, but it doesnt work for me...

$csv = array_map('str_getcsv', file('data.csv'));

.

$csvData = file_get_contents($fileName);
$lines = explode(PHP_EOL, $csvData);
$array = array();
foreach ($lines as $line) {
$array[] = str_getcsv($line);
}
print_r($array);

When i echo $array[0][0] it shows the HOWL line. I really dont get it...the second one is the top answer here: PHP CSV string to array

I have to upload a csv file like this:

category;question;a1;a2;a3;a4
1;question;a1;a2;a3;a4
2;question;a1;a2;a3;a4
2;question;a1;a2;a3;a4

Now i have to add this questions to my questions table, my problem is that every category starts at a specific id: cat 1 start at 1, cat 2 at 1000, cat 3 at 2000 ...

E.g: The last question with category 2 has the id 1012, so the questions with cat 2 from the csv file need to be inserted at 1013 and 1014.

My idea is that before i insert the csv, i count the number of rows from each category an then insert the IDs manually. (e.g: cat 2 has 12 rows, + 1001 would be the id for the first new question)

Has anyone a idea how i could get that work?

fancyPants · Accepted Answer · 2015-03-30 09:34:13Z

1

First just load the CSV to another table.

LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE tmp_table_name
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
;

read more about this command here.

Then calculate the id in the insert statement.

INSERT INTO questions (id, cat, question, a1, a2, a3, a4)
SELECT 
cat - 1 + 1000 + sq.number as id,
cat, question, a1, a2, a3, a4
FROM tmp_table_name t
JOIN (SELECT cat, count(*) as number FROM tmp_table_name GROUP BY cat) sq
ON t.cat = sq.cat

P.S.: Column names like a1, a2, a3 and a4 (I assume a is short for answer?) smells like very bad database design. Put the answers in a separate table and in one column.

Table questions:
id
cat
questionId
question
answers

Table answers:
questionId
answer

The tables are linked via questionId

answered Mar 30, 2015 at 9:34

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Input CSV File and upload it to MySQL DB

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related