/    Sign up×
Community /Pin to ProfileBookmark

csv into mysql

Copy linkTweet thisAlerts:
Jun 12.2008

Hi, I’m still new, but I have learned here in the past so I’m back to ask more questions ?

I’m trying to make an upload script where a CSV file will be imported into a database. to make this simple I’m going to use two fields, Title,Description

so I have a csv file like:

[CODE]Title,Description

Test title,Test description
Test title,Test description[/CODE]

being the header and fields. I searched here and online and found the following script:

[code=php]$columns = “`c1` , `c2`”;
$handle = fopen(“test.csv”, “r”);
while (($data = fgetcsv($handle, 1000, “,”)) !== FALSE) {
foreach( $data as $v ) {
$insertValues=”‘”.addslashes(trim($v)).”‘”;
}
$values=inplode(‘,’,$insertValues);
$sql = “INSERT INTO `tableName` ( $columns ) VALUES ( $values )”;
mysql_query($sql) or die(‘SQL ERROR:’.mysql_error());
}
fclose($handle);[/code]

I’m still new but I take it the “inplode” above is “implode”….In the test example above would the $columns c1, c2 be the header fields(title, description) or the values test title, test description? I take it they are the header….so I would take the database name, ie database_title and replace “c1” with it, correct? I’m sure I’ll have more questions but I want to understand what’s going on step by step here.

to post a comment
PHP

5 Comments(s) ↴

Copy linkTweet thisAlerts:
@Phill_PaffordJun 12.2008 — I know you can load a CVS into MySQL with just a query like this:

MySQL Query:

[CODE]
LOAD DATA LOCAL INFILE 'C:\path\to\your\file.CSV'
INTO TABLE tablename
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'n'
(Title,Description,etc...);
[/CODE]

I think if you are on a Linux box you can set the path as :

[CODE]
LOAD DATA LOCAL INFILE '/path/to/your/file.CSV'
[/CODE]

This will load all the data line by line into your database
Copy linkTweet thisAlerts:
@72newbieauthorJun 12.2008 — Thanks for the comment but this might need to be a little more complex as I should have noted in my first post. The idea is for users to be able to upload their own csv files. I'm going to need to filter javascript and urls, not to mention the number of items that can be uploaded. I hope i'm not in over my head, I think it can be done rather simply once I understand all the commands.

I will need to check that the title is between 7 and 35 characters, the description is between 50 and 5000 characters, the price is a number etc....then that data will be passed to the uploader where it will be entered into the database like:

[code=php]$sql = "INSERT INTO tableName ( $columns ) VALUES ( $values ) WHERE id='$user_id' "; [/code]..I think that will work?
Copy linkTweet thisAlerts:
@SyCoJun 12.2008 — That's pretty cool Phil . I've not used SQL file handling before.

Heres the PHP way

example.csv
[CODE]foo1,bar1
foo2,bar2
foo3,bar3
foo4,bar4
foo5,bar5
foo6,bar6[/CODE]

csvinsert.php
[code=php]<?
$lines = file('example.csv', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);

$q="INSERT into table ('c1','c2') VALUES ";

foreach($lines as $csv){
$parts=explode(',',$csv);//create array of csv items
$q.= " ('$parts[0]','$parts[1]') ";
}

//echo to check it
echo $q;

//uncomment to run
//mysql_query($q);
?>[/code]

This is a very basic example. If you can be 100% sure there are no commas int he data then this is fine, otherwise you'll have to expand upon it. You can use fgetcsv() to open the csv file too. I didn't here as it depends on what version of PHP you're using to use correctly. The query is built dynamically and only a single insert statement is needed. In your version 72newbie you hit the SQL server once per iteration. Generally hitting the SQL server in a loop is bad news and is often easily avoided by building the query dynaically.
Copy linkTweet thisAlerts:
@72newbieauthorJun 12.2008 — How will i handle commas? I can see that as being a major problem....hmmm...starting to think this maybe more advanced that I need to to be into...I also need image uploads..
Copy linkTweet thisAlerts:
@SyCoJun 12.2008 — Open excel or calc and enter some text into the cells, adding commas to some of them. Save it as a CSV and open it in a text editor. Then you'll see the way a CSV should handle data with commas.

Read up on fgetcsv() too.
×

Success!

Help @72newbie spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 4.27,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,
)...