Problems with MYSQL

Hi all,

I have a flatfile database that has 6 fields, each field is seperated by the ; symbol. I have tried to upload this in to a table whithin phpMyAdmin - version 2.80.3 on a server run by my host.

The format of the fields for the database are like this:

mixtape_ID;mixtape_DJ;mixtape_MC;mixtape_Event;mix tape_Date;mixtape_Style

I get this error:

Invalid parameter for CSV import: Fields enclosed by

Is there a way of getting around this problem? can i change some settings within the phpmyadmin? I have tried to change the Fields enclosed by text box with nothing but this doesn't work, it used to work but for some reason doesn't anymore... I hope i don't have to enclose each value with an enclosed by value...

Any help would be greatly appreciated...

I don't use phpMyAdmin so I don't know what your options are, but...

The first thing you need to try is to change all the ; seperators for , (commas).

CSV does stand for Comma Seperated Value after all.

You might also like to consider using, what I consider to be, a far superior tool.

SQLyog, available form: http://www.webyog.com

They do a free version as well as two paid for versions.

You will have to get your hosting service to grant you external access to your database but any good one should do.

I already have external access to my host, they use phpMyAdmin so there isn't a way i can choose my software... The values can be ; and do not have to be changed to a , as there is a facility with the phpadmin interface that allows me to change this, but it does ask for the data relating to the error i previously posted...

Thanks for your help, i know it can be done with the existing set up as it was working before until the host migrated there servers... it's just finding where the option is and how to implement it!

There are 2 options, import CSV file or import CSV using load data. The Import CSV LOAD DATA give these additional options

Format of imported file CSV

CSV using LOAD DATA

SQL


CSV optionsReplace table data with fileIgnore duplicate rowsFields terminated byFields enclosed byFields escaped byLines terminated byColumn namesAnd the import CSV gives the below options Options for CSV import using LOAD DATAReplace table data with fileIgnore duplicate rowsFields terminated byFields enclosed byFields escaped byLines terminated byColumn namesUse LOCAL keyword SQL optionsThis format has no options



Now i have a different error, great lol... see below... i cannot find anywhere to edit the preferences to change Using Password to "No", i only get this error in the import csv with load data.
Error

SQL query:
LOAD DATA INFILE '/tmp/phpshKPEj' INTO TABLE `mixtapes` FIELDS TERMINATED BY ';'
MySQL said:
#1045 - Access denied for user: 'cdrj1n29t22y@localhost' (Using password: YES)



If i use the import CSV option without the load data facility then i get this error message
Error

Invalid field count in CSV input on line 2.

'1', 'Druid', '', 'Studio Tape', '1992-04-18', 'Oldskool'
Remember my data is structered as follows:


ID;DJ;MC;Eventate;Genre

in line 2 above there is no mc fot the event hence 2 '' together

I have my sql tool running on my own machine and hook up remotely to the server.

In the past, when I've had a message like the one shown, it's because the server is not recognising my right to connect.

Check your database logon data and also if your hosting service has stopped your remote access for some reason, even accidentally.

That sounds familiar to me as well. hence why your upload breaks on line 2 because it doesnt know the first line has ended. Have you tried adding a ; to the end of the first line to see if the error moves to line 3?

The thing is it never did this before...

Since the host migrated the bloody servers it messed all my scripts up, i managed to sort the others out but their not using SQL so weren't so much of a problem...