How can I load 10,000 rows of test.xls file into mysql db table? -

-

how can load 10,000 rows of test.xls file mysql db table?

when use below query shows error.

load data infile 'd:/test.xls' table karmaasolutions.tbl_candidatedetail (candidate_firstname,candidate_lastname);

enter image description here

my primary key candidateid , has below properties.

enter image description here

the test.xls contains data below. enter image description here

i have added rows starting candidateid 61 because upto 60 there candidates in table.

please suggest solutions.

to import data excel (or other program can produce text file) simple using load data command mysql command prompt.

save excel data csv file (in excel 2007 using save as) check saved file using text editor such notepad see looks like, i.e. delimiter used etc. start mysql command prompt (i’m lazy mysql query browser – tools – mysql command line client avoid having enter username , password etc.) enter command: load data local infile ‘c:\temp\yourfile.csv’ table database.table fields terminated ‘;’ enclosed ‘”‘ lines terminated ‘\r\n’ (field1, field2); [edit: make sure check single quotes (') , double quotes (") if copy , paste code - seems wordpress changing them similar different characters] done! quick , simple once know :)

some notes own import – may not apply if run different language version, mysql version, excel version etc…

terminated – why included step 2. thought csv default comma separated @ least in case semicolon deafult enclosed – data not enclosed left empty string ” lines terminated – @ first tried ‘\n’ had add ‘\r’ rid of carriage return character being imported database make sure if not import primary key field/column has auto increment on, otherwhise first row imported

original author reference


Comments

Post a Comment

Popular posts from this blog

java - activate/deactivate sonar maven plugin by profile? -

-
i use sonar plugin executes if activated in maven profile. sonar runs on every compile. the problem build fail other team members not have sonar application installed locally. pom.xml checked in team members same copy including sonar configuration, don't want them modify purpose of disabling sonar (they might inadvertently check modified copy in). please advise how configure maven (.m2/settings.xml) and/or pom.xml sonar feature can enabled/disabled local profile. here sonar configuration in each project's pom.xml: <plugin> <groupid>org.codehaus.mojo</groupid> <artifactid>sonar-maven-plugin</artifactid> <version>1.0</version> <executions> <execution> <id>make-sonar</id> <phase>compile</phase> <goals> <goal>sonar</goal> </goals> </execution> </executions> ...
Read more

python - TypeError: can only concatenate tuple (not "float") to tuple -

-
i'm new python, question might easy, anyway, patience: as trying call newton-raphson method calculate implied volatility in black-scholes formula call/put option pricing, first thing is, newton method in scipy.optimize seems calculate function's zeros, in black-scholes formula, want function's value option price, not zero. (i'm new here programming, i'm not sure techniques.) should write function things like: def f(sigma, price): return bsformula(s0,k,r,t,q,sigma) - price then, while calling newton method, takes args=() 1 parameter in function, write this: value = newton(bsprice2, 0.5, args=price) but error message: file "bs.py", line 36, in bsimpvol value = newton(bsprice2, 0.5, args=float(price)) file "/usr/lib/python2.7/dist-packages/scipy/optimize/zeros.py", line 143, in newton q0 = func(*((p0,) + args)) typeerror: can concatenate tuple (not "float") tuple could tell me why that? how fix it? apprecia...
Read more

java - What is the difference between String. and String.this. ? -

-
this question has answer here: meaning of .this , .class in java 2 answers i'll straight point. im still learning bit of syntax , want know difference between code is code a: public class buttonz extends jbutton{ public buttonz(){ settext(new string(string.valueof(i))); } } please ignore fact i undeclared, not lost. code b: public class buttonz extends jbutton{ public buttonz(){ settext(new string(string.this.charat(i))); } } what don't yet understand difference in typing string.this , string. i under assumption when use dot operator on class accessing it's static methods(and/or variables if they're not hidden). i have studied little bit , have concluded when using string. accessing string static methods.. when using string.this. i'm accessing methods class buttonz extendi...
Read more