Uploading a .CSV file to my MYSQL database

[jefbea]

Does anyone know how I upload a pre-made database into my MySQL database.[nbsp][nbsp]I would like to know the prep I would have to make for the .CSV file and the linux command to store it.

Thanks!

Jeff

[jefbea]

I am still having problems... I have a text file you can see here (my database):

http://www.lease2purchase.com/validate.txt

All of the fields are separated and named correctly.

I type this line:

[nbsp][nbsp] mysqlimport -uxlease2purchase -p -hMyHost --fields-terminated-by=',' xlease2purchase ./validate.txt

it prompts me for the password... it imports it and gives me like 500 errors and tells me that 45 records were successful... the 45 successful records are all gibberish..

Any help would be very much appreciated... Maybe my command line in linux is wrong.

Jeff

[Terra]

From what I can see, it appears you are not escaping embedded commas...

To MySQL:
one,two,three,four,five,six,seven eight nine,ten
**8 columns

but let's say that 'seven eight nine' have commas in their prose: 'seven eight, nine'
one,two,three,four,five,six,seven eight, nine,ten
**9 columns and sure to error out

You need to figure out how to escape embedded commas so they are not seen as delimiters...

You should end up with something similar to:
one,two,three,four,five,six,seven eight\, nine,ten

--
Terra
--A strategic comma by another name could bring about world peace--
FutureQuest

[jefbea]

Thanks for the reply.

So are you saying that my "validate.txt" file is not in an appropriate format?[nbsp][nbsp]If so, how do I go about making it correct?

Thanks for the help![nbsp][nbsp]I am utterly confused.

Jeff

[Terra]

You need to research the program that is creating the .CSV file...[nbsp][nbsp]The answer I believe has been given, how the result is derived warrants investigation..

For that you will either need to read it's documentation or contact the manufacturer...

--
Terra
sysAdmin
FutureQuest

[wharris]

A comma-delimited file is especially likely to create import confusion. MySQL would end a column entry containing "XYZ, Inc." after XYZ, pushing "Inc." into the next column and throwing everything that followed out of sync. For the file upload to work properly, the entry would need to go in as "XYZ\, Inc." with the comma "escaped" so as to be read literally as a comma and not as an end-this-column-entry command.

See if you can export or save your database as a tab-delimited file. If your database app won't do that and your database is not too large, you might be able to clean the file up by opening it in a spreadsheet or word processor and doing a manual search "," replace "\,", replacing any embedded commas with a backslash and skipping over the ones that actually end a field.

Wayne