Importing from CSV using LOAD DATA

[adamnichols45]

hi guys im having some problems with a csv file.

what attributes should i use for a text coloum should it be binary?

[coopster]

A text column in what ... a database?

[adamnichols45]

yes it is a database.

Can you look at this for me please

im beeing asked by phpmyadmin (when uploading a csv file) for info such as

Fields enclosed by
Fields escaped by etc etc

here is a line from my file
'OrderID','CountyLocation','Postcode','MakeofCar', 'ModelofCar','Trim','Price','RegYear','RegLetter', 'Mileage','BodyStyle','Transmission','FuelType', 'VehicleCC','Colour','NoDoors','CarDescription', 'ImageLink','ContactName','PhoneNumber','EmailAddress'

i was using the following

Fields terminated by = ,
Fields enclosed by = '
Fields escaped by = '
Lines terminated by = \r\n

Is this correct if not what should i be using please? Thanks in advance for any help

[edited by: jatar_k at 8:23 pm (utc) on Feb. 7, 2005]
[edit reason] fixed sidescroll [/edit]

[Timotheos]

Hi adamnichols45,

what attributes should i use for a text coloum should it be binary?

Depends on what you're doing with the data but usually VARCHAR is good enough.

Fields terminated by = ,
Fields enclosed by = '
Fields escaped by = '
Lines terminated by = \r\n

Looks good except I'd make the fields escaped by \. This is for things like a last name of O'Brien which needs to be O\'Brian so it knows to insert the '. Unless you know that these types of things are escaped by a ' then the default is \.

Tim

[adamnichols45]

Hi,

thanks for the last post - The csv is inputting all the info in all the correct fields BUT it should be putting
in about 1000 records but instead it is only putting in about 10 - Can you tell me why this is please?

How can i sort this out?

[Timotheos]

Hi,

If I remember correctly it usually gives a message of success or failure. Do you get anything after inserting the csv?

Tim

[adamnichols45]

no message no it just goes back to the stucture!

DO i need to use \n or \r what is the difference any way?

[adamnichols45]

also in phpmyadmin while im trying to upload my csv file it is just hanging there! gets all the way up ie full blue bar in the browser but juts hangs there for over 5 minutes.

only 1000 records how can i combat this also please?

[jatar_k]

number of rows doesn't really matter, it depends on how much data there is.

I find that phpmyadmin is very crummy for LOAD DATA and also for MYSQLDUMP but that's just me. I prefer to just ssh to the server and get it myself.

[Timotheos]

DO i need to use \n or \r what is the difference any way?

Usually depends on your operating system. If the csv was created with Windows then use \r\n. Unix \n. Mac (I think) \r.

Tim

[adamnichols45]

hi jatar_k when you use ssh what is this?

[jatar_k]

use telnet or ssh to connect to the server using a program such as putty and just interact with mysql via the commmand line, no web or graphical interface.

[adamnichols45]

thanks for that is there a tutorial i could read so i know what to type etc? thanks adam

[jatar_k]

not sure

you can download putty here
[chiark.greenend.org.uk...]

it's nice and free ;)

connecting putty to a server is fairly simple. All you need is to enter the hostname or ip for the server to connect to and then the protocol, I always use ssh

you will get asked for a username and a password once you are connected to the server, which you should have for ftp already. Then you can connect to mysql exactly as you would using your scripts, usually something like

for localhost
mysql -u username -p
then you will be prompted for your mysql password

for remote host
mysql -h hostname -u username -p

then you will be connected to mysql and can type in your commands at the prompt. It takes a while to get used to but it is a much more powerful and intuitive way to interact with mysql (imo) and it will better your sql skills more quickly ;)

see
mysql, the Command-Line Tool [dev.mysql.com]