All I want to do. Is just. Import information.
So I have this table with numbers and shit right? I export it as a text file with | separating all the columns. Then I use magic and sacrifice a goat to edit all the information with the intent of re-importing it back into the table. But I cannot do this for some unfathomable reason. Why do people invent things that are retarded and intuitive?
So anyways, does anyone have extensive experience with MySQL? I could sure use some help. :I
Instead of the "|" character, separate the values by commas. Save the text file as a ".csv" file.
Then run the following commands:
load data local infile 'myFile.csv' into table MyTable fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(colName1, colName2, colName3)
Source: http://www.tech-recipes.com/rx/2345/import_csv_file_directly_into_mysql/
Seconded, Beldaran's method is how I do it at work.
Be mindful that Windows is different from everyone else in that it terminates lines with two characters. If this is your case, you'll want to use LINES TERMINATED BY '\r\n' or else you'll inadvertently load the \r character into your database.
My last tip is that if you need to do some kind of on-the-fly transformation to the data as it is loaded into MySQL, you can use the @ symbol like this:
LOAD DATA LOCAL INFILE 'myFile.csv' INTO TABLE MyTable
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(colName1, colName2, @colName3)
SET colName3 = TRIM(@colName3)
Glenn the Great wrote:Seconded, Beldaran's method is how I do it at work.Be mindful that Windows is different from everyone else in that it terminates lines with two characters. If this is your case, you'll want to use LINES TERMINATED BY '\r\n' or else you'll inadvertently load the \r character into your database.
My last tip is that if you need to do some kind of on-the-fly transformation to the data as it is loaded into MySQL, you can use the @ symbol like this:LOAD DATA LOCAL INFILE 'myFile.csv' INTO TABLE MyTable
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(colName1, colName2, @colName3)
SET colName3 = TRIM(@colName3)
Remind me where you work?
It's on my Facebook, I'd rather not make it public information.