upload via phpmyadmin

[meanweaver]

I have a problem which i think is a database issue, Using the phpmyadmin panel i can create a table in the database and input info manually no problem, but i have just got my hands on a .csv file, it looks the same as a normal excell file and is 4 cols wide, In my database i have created a table called "cars" and in that table have created 4 fields with names, now all i need to do is upload to this via the myadmin panel, so i went though the insert process but get an sql error returned, now i think the problem is the sql does not know what cols in the .csv file need to go into what field on the table, Things have been slowly sinking into place until i tried this, I know i could put the data in 1 field at a time but for a big database or regular updating that could take hours. Is there a way of nameing the cols in the .csv file so they match up with the online table.

Regards ian

[henry0]

For a large work
there are a few good tools
that allow transforming
Access to MySQL
or XL to MySQL

cvs doc can be changed in XL doc

I do not know about free tools
but those I have used are quite affordable

[meanweaver]

I have been told that i should be able to upload the .csv file direct via the control panel, I just have to build the table first which i have done, As far as i can see i need to give all the cols in the .csv file a unique id, this is the point i start to get lost, so in theroy i shouldn't need any other software.

Regards Ian

[bcolflesh]

Assuming you've created a table with the appropiate fields to match your .csv file, in phpmyadmin, go to the SQL tab, then choose the 'Insert data from a textfile into table' link at the bottom, configure your options and click Submit.

[meanweaver]

I think this is the problem, my table has 4 fields with names, but in the .csv file all i have is 4 cols filled with data, where do i give these cols there name so that the myadmin interface knows which cols to put in what field.

Cheers Ian

[bcolflesh]

It will insert them in the same order as the .csv file - make a test table and try it, then tweak from there.

[meanweaver]

That was what i thought at the start, when i try it i get this error

Error

SQL-query :

LOAD DATA INFILE '/tmp/phpZcDIR7' REPLACE INTO TABLE `cars`
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'

MySQL said:

Access denied for user: 'mydomain@localhost' (Using password: YES)

That is the error message, i have no idea why it says access denied, Anyone have any ideas!
Thanks Again

[Philosopher]

That doesn't sound like an upload problem, but a problem with actually logging into the MySQL itself.

[bcolflesh]

The mydomain@localhost user needs the FILE privilege - see:

[dev.mysql.com...]

[meanweaver]

Yes it was a privilege issue, Left a message with my host they said this:
Please try doing this again now - I believe the MySQL user needed the 'FILE' priviledge to be granted in order for this operation to be successful. I've granted this for your MySQL user (manleynet), so hopefully the problem should now have been fixed.

And now all ok, 3 of the rows in the .csv all went into the 2nd field on the table but i will woory about that another day. thanks for everyones help.

[meanweaver]

ok, now i have stopped jumping up and down at the excitement of getting the data to at least to upload, I now have the issue of getting it into the correct fields, All my 4 cols from the .csv file have all dropped into the first colunm in the first field in the table, when i uploaded it the sql for it looked like this:

SQL-query :
LOAD DATA INFILE '/tmp/phpneVtH7' INTO TABLE `cars` FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

Was i meant to put some info in there somewhere,

Regards Ian