Uploading CSV files using phpMyadmin?

[Snicklefritz]

I have a csv file that I would like to upload using phpMyAdmin. The data consists of three columns of numbers. The first row indicates the names for each of the columns. I would like to take this information and substitute it for one of the tables in my zencart database. The file contains a lot of numbers which is why I want to upload the data with a csv rather than to input the same stuff manually.

The table I want to upload to has 8 columns. How do I get the data from the file to go into specific columns in the table? when I looked at the import options, I'm asked several questions:

(1) number of records to skip from the start: I'm not sure whether this refers to the file I am uploading, i.e. telling phpMyAdmin how many lines of header materials there is - or does it have something to do with where the file is placed within the table? i.e. which columns to put the information in

(2) fields terminated by ; Why is it asking me this when it's a csv file? the fields are separated by commas not by a semicolon. am I supposed to change this field?

(3) fields enclosed by "". They aren't enclosed by anything, so am I suppose to leave this blank?

(4) fields escaped by \ Not sure what this means

(5) lines terminated by auto
there's nothing at the end, just a return so I assume this field should be left alone.

(6) column names: is this referring to the names of the columns in the file? Or the names of the columns in the table where the numbers should go?


any help would be much appreciated.

[RodG]

I have a csv file that I would like to upload using phpMyAdmin. The data consists of three columns of numbers. The first row indicates the names for each of the columns. I would like to take this information and substitute it for one of the tables in my zencart database. The file contains a lot of numbers which is why I want to upload the data with a csv rather than to input the same stuff manually.

The table I want to upload to has 8 columns. How do I get the data from the file to go into specific columns in the table?

Modify your CSV file so that it also has 8 columns. In other words, make you CSV match the table that you wish to insert too.

Another option would to to create a new table with only 3 columns, insert your data into that table, then use SQL commands to move the data from your new table into where you need it to be.

when I looked at the import options, I'm asked several questions:

(1) number of records to skip from the start: I'm not sure whether this refers to the file I am uploading,

Yes, that is what it is referring too. You'll probably need to skip your 1st row (the names of the columns) because these are meaningless during the import, and if the table you are importing to is expecting numerical data this 1st row will cause it to bomb out.

(2) fields terminated by ; Why is it asking me this when it's a csv file? the fields are separated by commas not by a semicolon. am I supposed to change this field?

Not all CSV files actually use a comma as its separator. Technically speaking *any* character may be used. One reason to not use a comma is if the data itself uses commas and for some reason the field(s) can't be quoted to isolate the field separator from the data.

Yes, you probably do need to change that field (for reasons I'm not sure of phpmyadmin defaults to using the ; as a separator. On our systems we change this by editing the config file to make a comma the default.

(3) fields enclosed by "". They aren't enclosed by anything, so am I suppose to leave this blank?

Yes.

(4) fields escaped by \ Not sure what this means

Some characters have special meanings, for example, the quote character - If for some reason you need to use these as your field delimiters you'll need to 'escape' them so the lose their special meanings, the \ is generally (but not always) used for this purpose.

(5) lines terminated by auto
there's nothing at the end, just a return so I assume this field should be left alone.

Generally speaking, yes, leave it alone. Depending on the origin of the import file, the field terminator will usually be one of the following, a line feed, a carriage return, or both.
The 'auto' will usually make the correct determination for you.

(6) column names: is this referring to the names of the columns in the file? Or the names of the columns in the table where the numbers should go?

I really can't answer this one :-) I never import CVS files with field names - I suspect it is referring to the field names in the CSV file, but this is just a wild guess. I have a 50/50 chance of being right :-)

Finally, I rarely, if ever import CVS data into a pre-existing table (too much can go wrong). I've always found it best to create a new (temporary) table to import too. If things don't go right it is easy to delete the table and start again.

After the data is correctly inserted into the new table it is a trivial task to manipulate and/or move it into the tables where it is really needed.

CVS isn't what I'd consider an 'exact science' - it is very flexible though, and will often take a bit of experimenting to get it correct for any given dataset.

Cheers
Rod