In urgent need of help csv please

[adamnichols45]

HI Ladies and Gents,

Can some one look at this please then answer the following 5 questions.

'orderID','CountyLocation','Postcode','MakeofCar','ModelofCar', 'Trim','Price','RegYear','RegLetter','Mileage','BodyStyle', 'Transmission','FuelType','VehicleCC','Colour','NoDoors', 'CarDescription','ImageLink','ContactName','PhoneNumber', 'EmailAddress'

i would like to know.............

Fields terminated by == what should i put here?

Fields enclosed by == what should i put here?

Fields escaped by == what should i put here?

Lines terminated by == what should i put here?

Column names == what should i put here?

If someone could tell me that please because what i have been using is just not working fullstop i would be most grateful you couldnt even understand

Thanks so much for anyone that can help

Best regards
Adam

[edited by: jatar_k at 9:14 pm (utc) on Feb. 15, 2005]
[edit reason] fixed sidescroll [/edit]

[curlykarl]

Hi,

Fields terminated by >> ,

Fields enclosed by >> Leave blank

Fields escaped by >> \

Lines terminated by >> \r\n

Column names >> Leave blank

I'm no expert but to upload a csv file using PHPMyadmin I use the above and it works for me

Karl :)

[adamnichols45]

Thanks for your reply but this is still only doing what it always has! just adds 3 lines and in the csv file there is about 1000 to be updated!

Any more help please

[curlykarl]

Hi,

The Row ID field in your database, what is it set to?

[adamnichols45]

It is set at

int
AUTO_INCREMENT
NOT NULL

hmmmm thats about it

[curlykarl]

Sorry :(

I've no more ideas, I only learned enough to get this far.

Karl

Just a thought, but I've just checked my database and all the values for INT are set at 11, No idea if its relevant or not.

I had a similar problem before and it was due to the fact that I had INT set as TINYINT, it would only accept a couple of hundred when I was trying to add 1000's

[curlykarl]

Sorry, meant to add the extra info here :)

[Timotheos]

Hi Adam,

Still havin' problems eh? I've got a couple more thoughts.

1) Maybe there's something wrong with the csv file. Open it up in Notepad and see if everything looks OK. Check the line where it's stopping and see if anything looks weird or maybe it's missing a comma.

2) If there's all ready rows in the database that have a unique id or autoincrement then I don't think LOADDATA will overwrite them automatically. You have to select Replace to update existing records.

Tim

[adamnichols45]

Hi,

Right now things are getting intersting - i have managed to upload using the settings posted by karl
but i am seeing that all the values in the fields are sorrounded with '' ie 'adam' when as you know it should be just adam can anybosy tell me why this is please regards adam

[hakre]

the option (as karl uses it)

Fields enclosed by >> Leave blank

should be for you ' (the single quote). the other one:

Column names >> Leave blank

i don't know what to set to but "Leave blank" is defenetly wrong because your first line contains them, isn't it?

[adamnichols45]

thats rite yes - Still getting them poxy quotes round me data in the database though this is stupid

[adamnichols45]

by the way im uploading a file called car.txt i do believe this is correct yes just so it is nothing really silly?

[curlykarl]

I'd of thought it should be car.csv?

Never tried uploading a txt file via phpmyadmin.

Hope you get it sorted soon :)

Karl

[gettopreacherman]

'orderID' = (Depending on the format of your orderid's if they are numeric then INT 25, or VarChar 30)
'CountyLocation' = (Most like VarChar 10)
'Postcode' = (INT 9)
'MakeofCar' = (VarChar 15)
'ModelofCar' = (VarChar 30)
'Trim' = (VarChar 15)
'Price' = (INT 20) or (Decimal 20)
'RegYear' = (TinyINT 4)
'RegLetter' = (VarChar 20)
'Mileage' = (INT 15)
'BodyStyle' = (VarChar 15)
'Transmission' = (VarChar 10)
'FuelType' = (VarChar 10)
'VehicleCC' = (VarChar 10)
'Colour' = (VarChar 15)
'NoDoors' = (TinyIT 1)
'CarDescription' = (VarChar 255)
'ImageLink' = (VarChar 255)
'ContactName' = (VarChar 45)
'PhoneNumber' = (INT 10)
'EmailAddress' = (VarChar 60)

Bascially, you need to look at your Columns and the data within. Above you are defining the type of Field and it's Character Limitations. If everything is INT it is an Integer and won't accept Alpha Characters when you have letters...and if the length, the number, is not long enough, it will error out.

Not everyone of the stipulations needs to be met when looking below like Fields end with or terminated by. If you have a csv...I would suggest you use that as it is preformatted and works great. If you have a *.txt file open it with Excel and save it as a *.csv.

Next tell it that they are terminated with a comma, because a *.csv is saved as a "comma separated" file. If you have any comma's in the names or any rows, I would suggest you replace them with "_" or + or some other sign other than the "," as when it uploads you will notice weird line breaks and data.

Hope it helps. :)

[Timotheos]

Welcome to Webmasterworld, gettopreacherman!

Just a note that by default Excel doesn't enclose the fields with anything. So it would be like... one,two,three,etc

Tim

[adamnichols45]

Thanks for everyones help - In the end i managed to get it all uploaded by changing my orderid field from

INT to VARCHAR

Thanks again

[Timotheos]

Phew! I'm glad you got it figured out. Funny how little things like that can trip you up for so long.