CSV files

What are they, and how to use in database

[expat]

I have had to bite the bullet and try to learn php,and mysql
I am 60 + and have lost a few million brain cells on the way here.
However, I need to know about CSV and I have searched this site, and also several php sites but with no success. can someone point me to a tutorial that will explain CSV and how to use.

Many thanks in advance

expat

[justageek]

You'll be happy you learning php and mysql. Even with a few missing brain cells it'll be easy :-) Mysql is just like anything else that uses sql and the only thing easier than php is maybe html tags.

csv is nothing more than a comma seperated values file.

Looks like this:

'John','Doe','exap'

This basically says that there would be 3 columns and if the db had the structure First_Name, Last_Name, Where_did_he_go as column names then when you did an import First_name would get the value John, Last_Name would get the value Doe and Where_did_he_go would get the value expat. Each element in the csv is a column in the db. Each row of a csv is a row in the db. It's that simple :-)

[bcolflesh]

Might be useful for you:

codewalkers.com/seecode/175.html

[Friday]

FWIW: You can use BBEdit (For Mac, I don't know what the Windows or Linux equivalent would be) and smply do a search and replace on your CSV file saving a tab-delimited file to export into your MySQL database.

Using: 'John','Doe','exap' as an example:

1) search for ',' and replace with \t
2) using grep, search for ^' and replace it with nothing
(alternately, if grep isn't available, replace \r' with \r and manually check your first line)
3) search for '\r and replace it with \r
4) escape all ' and " with \' and \" respectively
5) save to your server as a txt file; e.g.: file_name.txt
6) telnet to your MySQL server and issue the command:

DELETE FROM TableName;
LOAD DATA INFILE "/FULL/PATH/TO/file_name.txt" INTO TABLE TableName;

NOTE: The first command is assuming you already have information in the table you want to replace it (say for monthly updates).IT WILL DELETE all files in the table.

If it's an empty table you want to populate, or a table withinfo you want to add to, just say:

LOAD DATA INFILE "/FULL/PATH/TO/file_name.txt" INTO TABLE TableName;

[jatar_k]

[mysql.com...]

1) search for ',' and replace with \t

no need you can set the "FIELDS TERMINATED BY" in the load data statement

at any rate that is fairly OT for the moment.

What exactly did you want to know about csv files?

what are they?
They are files that contain comma seperated values. Each line in a csv file represents on full record of data broken down into individual fields.

I may have a contacts csv file

jatar_k,123 some street,vancouver,canada
expat,123 some high st,Oxford,UK

so what the heck is all that? Each row, or line, has all of the data I need for 1 contact. Name, address, city, country. Each record or line is terminated (ends) with a new line character \n.

If you don't have the newline char at the end you can get some weird results when you grab the last line out of the file.

csv files can be used instead of a database(ie mysql). Making what is often termed a flatfile database. csv files can be used to store data so it can be imported into another software program such as excel.

as far as storing data in csv, it is as easy as using fopen (for append) [ca.php.net] to open a file handle. Then to format the data you want to store into a string with commas between each value and ending with \n. Then use fwrite [ca.php.net] to write the data and fclose [ca.php.net].

For importing take a look at fgetcsv [ca.php.net].

Does that help? Any more specific questions.

[Friday]

Thanks for the link, jatar_k.
Good reading.
Probably in one of these damn manuals on my bookshelf somewhere. Don't know how I missed it.
;)

[expat]

Thanks for all the help, much appreciated.

expat