Import Excel Data into MySQL in 5 Easy Steps

This is probably nothing new to many, but I spent quite some time to figure it out so I thought I’d post my notes on it.

To import data from Excel (or any other program that can produce a text file) is very simple using the LOAD DATA command from the MySQL Command prompt.

  1. Save your Excel data as a csv file (In Excel 2007 using Save As)
  2. Check the saved file using a text editor such as Notepad to see what it actually looks like, i.e. what delimiter was used etc.
  3. Start the MySQL Command Prompt (I’m lazy so I usually do this from the MySQL Query Browser – Tools – MySQL Command Line Client to avoid having to enter username and password etc.)
  4. Enter this command:

    [code]LOAD DATA LOCAL INFILE ‘C:\\temp\\yourfile.csv’ INTO TABLE database.table FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\r\n’ (field1, field2);[/code]

  5. Done!

Very quick and simple once you know it 🙂

Some notes from my own import – may not apply to you if you run a different language version, MySQL version, Excel version etc…

  • TERMINATED BY – this is why I included step 2. I thought a csv would default to comma separated but at least in my case semicolon was the deafult
  • ENCLOSED BY – my data was not enclosed by anything so I left this as empty string ”
  • LINES TERMINATED BY – at first I tried with only ‘\n’ but had to add the ‘\r’ to get rid of a carriage return character being imported into the database
  • Also make sure that if you do not import into the primary key field/column that it has auto increment on, otherwhise only the first row will be imported

[Update: I had some trouble getting double quotation marks etc when exporting some Excel data and found a few old but useful Excel export macros in VBA]

[Update: To change the list separator in Excel:
1. First search your Excel file for the character you plan to use so it does not exist somewhere in your data.
2. Go into Windows Control Panel + Regional and Language + Formats Tab click Additional Settings + List separator (Windows 7)]

Published By
Categorized as MySQL

146 comments

  1. hi
    If the contents of the file is english, these 5 stepts are well.

    Not working when the Excel sheet is having chinese characters & english characters.

    i am using 5.0.45 DOS based.

    i tried to import Excel(2003) file to MySql table. (Excel is having English letters & Chinese letters)

    1. save excel file as .csv( comma delimeter)
    2. in MySql, I typed :
    set names utf8;

    3.LOAD DATA LOCAL INFILE ‘c:/test.csv’INTO TABLE test_table
    FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’;

    After that i used “select * from test_table”. The data are displayed as “????”.(English char OK)

    is it possible to display chinese characters in DOS mode.

    what is the wrong? or any other method is there? Pl help me.

    thanks
    [email protected]

    Reply

  2. Even in English, the above will fail if your data has a comma in it. It will throw the column count off.

    The key would be to get Excel to actually enclose the data in “”.

    Anyone know how to do that?

    Reply

  3. Hey,

    I’ve another question: my datas have many columns and I’m too lazy to create a Table with the matching columns. Is there a way that MySQL automatically reads the columns? (the first line of the csv File has the column names)

    Thanks and excuse my bad english 😉

    Reply

  4. hi, do someone know if i can import more than 50000 records in a csv with this fuction “LOAD DATA LOCAL INFILE”? i`d really appreciate if someone reply me to [email protected], thanks

    Reply

  5. Yes, you should be able to load more than 50k records into a mysql table, I’ve been using it to load 100s of 1000s of reords into mysql tables with no issues

    Reply

  6. Does anyone have an answer to the question posted on “Tue Apr 29, 12:22:00 PM CEST”

    “Hey,

    I’ve another question: my datas have many columns and I’m too lazy to create a Table with the matching columns. Is there a way that MySQL automatically reads the columns? (the first line of the csv File has the column names)

    Thanks and excuse my bad english ;)”

    Reply

  7. ello,

    When i am trying to run this query

    LOAD DATA LOCAL INFILE '\taxonomy.csv' INTO TABLE taxonomy FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (Tax_Id,Family,Genus,SpecificEpithet,InfraSpecificEpithet,InfraSpecificRank,AuthorYearOfScientificName);

    i am getting this error.

    File ' axonomy.csv' not found (Errcode: 22)

    I have placed the taxonomy.csv file in the Mysql\bin folder and also set the class path….

    Can any one tell me how to fix this????

    Thanks&Regards,
    PinazKale.

    Reply

  10. Hey hi ,

    I tried using \\ but its still giving me the same error..
    I have also placed the .csv file in mysql\bin folder…
    I tried also giving the entire file path….
    Does this have to do with some thing like local_infile mode enabled???

    Reply

  11. Thanks a lot for the straight-forward approach – I’ve been looking around for tools that would do the same and was pleasantly surprised to find such an easy way out – thanks a bunch!

    Reply

  12. Does it work to run this mysql script in a php file and execute the sql-statement just like i normal select-statement? Or did I must be connected thought the mysql-query browser on my computer?

    Reply

  14. Hi,

    I had the same problem with \\ and \ not working in windows…use the other use the / it worked for me and I’m also in a windows environment.

    thanks,
    Parag

    Reply

  15. Error

    There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

    ERROR: Unknown Punctuation String @ 26
    STR: :\\
    SQL: LOAD DATA LOCAL INFILE ‘C:\\Book1.csv’ INTO TABLE exceldbase.exceltable FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\r\n’ (Fname, Mname, Lname, Age)

    SQL query:

    LOAD DATA LOCAL INFILE ‘C:\\Book1.csv’ INTO TABLE exceldbase.exceltable FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\r\n’ (Fname, Mname, Lname, Age)

    MySQL said: Documentation
    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘‘C:\\Book1.csv’ INTO TABLE exceldbase.exceltable FIELDS TERMINATED BY ‘,‒ at line 1

    Reply

  17. Una cosa sólo, si eres español, para que te coja eñe (ñ), tildes y acentos, asegúrate de que el archivo CSV está en el mismo collate que la tabla destino, yo por ejemplo en la tabla los campos son utf8_general_ci, y el archivo CSV con el notepad++ lo he pasado a UTF8.

    saludos y gran post, me has salvado!

    Reply

  18. This works fine from the above poster.

    1. save excel file as .csv( comma delimeter)
    2. in MySql, I typed :
    set names utf8;

    3.LOAD DATA LOCAL INFILE ‘c:/test.csv’INTO TABLE test_table
    FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’;

    Reply

  19. Fred,

    I initially had the same problem as yours, make sure you use the correct single quote. Do not just copy paste from this page.

    LOAD DATA LOCAL INFILE ‘C:/filename.csv’
    INTO TABLE tablename
    FIELDS TERMINATED BY ‘,’
    ENCLOSED BY ‘”‘
    LINES TERMINATED BY ‘\r\n’ (field1, field2, etc);

    Reply

  22. Hey Hi,

    i. i m creating excel file and save in csv(comma delimate format)

    But when i type the load data function in mysql command prompt then fallowing problem occure please resolve my problem

    Tanks in advance

    mysql> LOAD DATA LOCAL INFILE ‘C:\\sample.csv’ INTO TABLE amit.click_records FIE
    LDS TERMINATED BY ‘;’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’ (id,keyword,adver
    tiser_id,advertisement_id,campaign_id,clicks,click_date);
    Query OK, 1 row affected, 21 warnings (0.01 sec)
    Records: 3 Deleted: 0 Skipped: 2 Warnings: 20

    The message is that 1 row affected but when i m checking the table records by using select query then thre will no records are added in table.

    With Regards,
    Sandip Khairnar

    Reply

  26. When the Excel file is distributed into multiple Sheets then you have to Save each sheet as an CSV, and import each of them to get all the Sheets in table.

    Reply

  28. i’m working on website using ASP.NET 2.0 (C# ) & mysql combination
    my mysql command LOAD DATA LOCAL INFILE ‘C:/Documents and Settings/User/Desktop/MSN.csv’ IGNORE INTO TABLE m1 FIELDS TERMINATED BY ‘|’ LINES STARTING BY ‘’ TERMINATED BY ‘\r\n’ IGNORE 1 LINES; “;
    works fine on my system,
    when i upload this page to the server
    file path will become ‘C:\Documents and Settings\User\Desktop\MSN.csv’ & file not found error will come, if anybody know’s the answer please help me

    Reply

  31. Another method.
    Another way you can do this, if you don’t want to create the table from scratch or have many columns like Robert.
    Create a Access database.
    Import CSV into access, then export to ODBC.
    I’ve done this successfully in the past, currently it is crashing access for me.. 🙁
    Its worth a try I guess.

    Reply

  32. Hi ,
    I had data in Excel.
    Opend Access. Imported data from Excel.
    Used MySQL Migration Toolkit , where u chose to migrate from access to mysql. Worked for me.

    Reply

  33. i have problem with import csv into mysql table . while i import csv column

    name only imported into mysql table. i can’t get the value . how to resolve.

    could anyone help me.

    Reply

  34. Be aware too that sometimes MySQL doesn’t like empty cells. In other words, you’ll probably have to find and replace out all the empty values with NULL values. To complicate things a little further, NULL isn’t actually a string, so will have to exist in the CSV separated by the commas but not encased in the double quote marks.

    Reply

  35. i have two tables in my c: 1)filesandpath.csv 2)report_2.csv . and also i have two empty tables . and i can able to import filesandpath.csv into first empty table(filesandpath table) both has three field. but wht my problem is

    i can’t import report_2.csv into onemore mysql table report_2.csv contains 9 rows i want to import first three rows only . because i create three fields in report_2 table. but my problem is only row name is imported into mysql table. i can’t get the value could anyone reply i strugled long time.

    Advance thanks for your reply

    Reply

  38. Hi

    I tried out alll the above given test querieload excel data to my table but nothing worked for my caseI .Finally I tried out the below steps to achieve.

    copy past the excel data as it is to a text file save your file to c: \\ eg c:\\test.text and run the below query

    LOAD DATA LOCAL INFILE “c:\\test.txt” INTO TABLE mysqltable FIELDS TERMINATED BY “\t” LINES TERMINATED BY “\n”;

    Reply
  41. Pingback: Import Excel Data into MySQL in 5 Easy Steps « Easycoder

  42. Worked successfully. Just have to figure out what should be used in TERMINATED BY TAG and other tags.. Thanks

    Reply

  43. Hi,

    Very IMPORTANT :

    The steps were simple and when i executed the command in Mysql query browser it gave me an error on invalid syntax….

    So remember if you copy the code and paste it…it wont work. as the Single quotes are changed to something else….just re-type the single quote 😉

    Reply

  45. Thanks for the steps/tips! It helped me upload information quickly and painlessly (plus I didn’t need to do any more research). You’ve been a big help!

    Reply

  46. Also, if you are importing dat make sure not to have other characters. like sally’s, you must put it as sallys or you will get errors

    Reply

  47. Dear,

    If you want update two different tables (e.g. foreign key relation) you can
    use dbTube.org. The tool can read native Excel Files and it is an
    PHP script. No coding required.

    @iza: maybe this solves your problem.

    Mark

    Reply

  48. Can anybody help me out wit this problem???
    Given:
    1) Two LAN located at physically different location
    2) First LAN (LAN1)
    3) Second LAN (LAN2)
    4) On LAN2 Server, there is an excel File xyz.xls.
    5) On LAN1, there is an SQL server. (OS Windows Server 2003).

    Problem:
    How do I import data from the excel file (on LAN2) to the SQL Server (on LAN1) without converting the excel file into csv file???

    Reply

  49. Thanks for sharing this info, I need some help, can someone please tell me how to import data from mysql to excel.

    I need it very urgently.

    Regards,
    sonia

    Reply

  51. @imz,
    ->answer to the last problem.
    Try creating a ODBC datasource to connect to excel workbook. Windows comes with its own ODBC driver for Excel allowing you to use your excel workbook as DBMS

    Reply

  52. Very Well done!
    But,1 more point I want to include!

    If the contents contain ‘,’ then replace all to something like ‘ ZZZZZ ‘ or anything similar
    and then import it
    After importing,using php codes,replace the ‘ ZZZZZ’ with “,” again.
    Thankk You,
    Have a nice Day!

    Reply

  53. Dear sonia,
    To import data to Excel
    Open Phpmyadmin
    Click Export and then Select Excel 2000
    and you r done!

    Reply

  55. Goodam/pm can i import more than 50000 records in a csv with this fuction “LOAD DATA LOCAL INFILE”? i`d really appreciate if someone reply to me, thanks…

    Reply

    1. TYABO, you can use any of them depending on what separator you want to use. You then need to specify the separator when you import as described in post.

      Reply

  62. How can I use this command to import a multivalue field from excel to mysql? The excel field is made of numbers with a comma between (i.e. 1,5,6).

    Reply

  63. Here is what I use:

    LOAD DATA INFILE ‘C:/FILE.CSV INTO TABLE table_name FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’ ;

    Reply

  65. HI

    I am trying load csv files which has around 60,000 records into mysql , when I ran the query as mentioned in your post ,it seems like I am missing most of the records(around 50000 records).what do you think can be the reason for this?

    Reply

  67. Please can some one help me out? i am to export a 2007 excel csv file to mysql 4.0 n3.2 using wamp server it will start importing but later on the server will stop working an error messege will be displayed.
    can some one please help me out?

    Reply

  68. Thanks,
    I use this command and successfully transfer my 538 lines of excel sheet into table.
    excellent command.

    Reply

  71. En mi caso particular:

    LINES TERMINATED BY ‘\r\n’

    Debí eliminar esto pues solo insertaba la primera línea.

    Además al hacerlo desde Linux:

    LOAD DATA LOCAL INFILE ‘/home/mortiz/Escritorio/archivo_ejemplo.csv’

    No se aceptan abreviaciones del tipo ~/, es decir nada de path relativo, toca escribir toda la ruta.

    Gracias y muy útil.

    Reply

  72. hi all
    is there any one who could tell me how to fetch data from multiple sheets in excel as
    i have total 5 sheets and each sheet have different data. what should i do

    Reply

  73. am trying to import a csv file into mysql every time i attempt the server will generate an error :
    Error
    SQL query:

    LOAD DATA INFILE ‘c:/orphanrecorddb.csv’ INTO TABLE orphanrecorddb FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n'(

    id, fID, fStatus, pArea, photo, oName, oSex, phyStatus, oDOB, bPlace, resAdd, oLGA, oState, oNation, rDate, rLocation, rReason, referee, relation, refAdd, refPNO, refEdu, oNeeds, needEdu, needSubs, needMed, needShelt, fName, fHome, fLGA, fState, fNation, fOccup, mName, mHome, mLGA, mState, mNation, mOccup, dataOfficer, cDate
    )
    MySQL said:

    #1062 – Duplicate entry ‘0’ for key 1

    Every one please to help me out?

    Reply

  74. Usman – see my last point about autoincrement, I think this may be your problem. Either don’t import the col that has autoincrement or alter the column in db before import to turn off autoincrement. Then import and turn autoincrement on again.

    Hope this helps.

    Reply

  75. Hi,
    I need a script for querying mysql database and read the data from it and write it into a excel file on daily basis.. For example, No of users joined, no of files uploaded and downloaded.. I need to update all these informations into an excel sheet daily..
    Can anyone help to solve it…?

    Reply

  77. Thanks for this post. I had non-ASCII data to import. So I followed John Larsen’s method (sort of).
    – imported the CSV into Access, specifying UTF-8 as the encoding of the input data.
    – saved the Access db as Access 2003 (*.mdb), so that MySQL Migration Toolkit would be able to read it.
    – installed the deprecated MySQL Migration Toolkit (http://dev.mysql.com/downloads/gui-tools/5.0.html), since there is no replacement for it yet in MySQL Workbench
    – used the Migration Toolkit to import the mdb. Success! Special characters were imported correctly.

    Reply

  78. Lars – I think you should be able to do this without having to use access and the migration toolkit.
    Make sure the db is using the same character set (UTF-8) as your file and also you may need to specify character set by adding it to LOAD DATA, for example:

    LOAD DATA LOCAL INFILE ‘C:\\temp\\yourfile.csv’ INTO TABLE database.table CHARACTER SET UTF-8 FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\r\n’ (field1, field2);

    More info in the reference manual http://dev.mysql.com/doc/refman/5.1/en/load-data.html

    Have not tested it yet so if someone does please confirm if it works or not.

    Reply

  79. i tried it and got this error. please help

    938 row(s) affected, 64 warning(s):
    1265 Data truncated for column ‘Address’ at row 1
    1261 Row 1 doesn’t contain data for all columns.

    thanks.

    Reply

  81. Dear All,

    I really stuck in loading data from escel to MY SQL query browser ,every time i tried i got below error ,thnx alot to help in this

    mysql> LOAD DATA LOCAL INFILE ‘C:\\akzo_test.csv’ INTO TABLE akzonobel.akzotable
    FIELDS TERMINATED BY ‘;’ ENCLOSED BY”” LINES TERMINATED BY \r\n? (`CaseID`,`Cas
    eTile`,`AccountID`,`AccountName`,`Severity`,`MTTR`,`OpenDuration`,`CreationTime`
    ,`ActualFaultTime`,`LastActName`,`LastActTime`,`NextEventTime`,`OrginatingWrkGrp
    `,`Orginator`,`Owner`,`Queue`,`WorkGrp`)
    ERROR 2005 (HY000): Unknown MySQL server host ‘(`CaseID`,`CaseTile`,`AccountID`,
    `AccountName`,`Severity`,`MTTR`,`OpenDuration`,`CreationTime`,`Actu’ (11004)
    mysql>

    Reply

  82. I am thankful for your five steps procedure for importing data from Excel to MySql. I have successfully imported data. Only problem is with Date data type in MySql. In Excel, it is in the Medium Date Type (example 15 March 2012 instead of 15-03-2012).
    While importing , My Sql is showing all 0000-00-00.
    I am a beginner in MySQL., working as Scientist in prestigious R & D laboratory in New Delhi, INDIA
    God Bless You,

    Reply
  83. Pingback: Import and Export

  84. LOAD DATA LOCAL INFILE ‘C:\\temp\\yourfile.csv’ INTO TABLE database.table FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\r\n’ (field1, field2); plz suggest me by using d above command how to retrieve my excel file as it was in ‘D’ drive and doesnt contains any terminations and enclosements and there were 13 columns with the names like sno, organism, refrence number, accession id etc… with 450 rows i need to convert dis excel data into sql database in the form of rows and columns…..please suggest me with appropriate solution.

    Reply

  85. in phpmyadmin ru nquery as:

    load data local infile ‘http://localhost/onlinetest/hello.csv’ into table tab_hello fields terminated by ‘,’ lines terminated by ‘\n’ (Name, Age)

    Reply

  86. hi..
    The above code is working…that,s great and thanks for that….
    But i need to give where condition in it. How can I do that?
    And one More thing..I am using file name as date…like – ‘z:\\home\\dev4\\Desktop\1-06-2012.txt’ .I want to give the current date on the date potion….how can i do that…?

    Reply

  87. its great without any ODBC its work its really easy to develop serverside scripting.

    thanx dear

    it there any suggesions plz post it i regularly read this page.

    Reply

  89. Hi,
    I am very much new to this one. ” I have a table named ‘members’ in that i have two fields namely ID(auto increment) EMPID and EMPNAME.” My query is – i have created one admin panel, through this panel i need to update my table with CSV file which i downloaded from my server. Can you please help me.

    Reply

  90. Hi, i have some doubts
    after importing the data in the data table null values are coming even though i filled all the columns.

    can any one explain the region for this

    Thanks in advance

    Reply

  92. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
    corresponds to your MySQL server version for the right syntax to use near ” ENC
    LOSED BY `”` LINES TERMINATED BY `rn'(field1, field2)’ at line 1
    mysql>
    I am getting this error. Can anyone please help.
    Also I did not get what I have to enter in (field1, field2). Please help

    Reply

  97. I tried to import data from my machine with this query

    (LOAD DATA LOCAL INFILE ‘C:\Users\user\Documents\Book2.csv’
    INTO TABLE table1
    FIELDS TERMINATED BY ‘,’
    ENCLOSED BY ‘”‘
    LINES TERMINATED BY ‘\r\n’ (id, name, age);)

    unfortunately i got this error.
    (#1148 – The used command is not allowed with this MySQL version),
    please help me to fix this problem.

    Reply
  102. Pingback: MySQL import from Excel « Glen's Blog

  103. LOAD DATA LOCAL INFILE “C:\\Users\\Arun\\Desktop\\test.csv” INTO TABLE login FIELDS TERMINATED BY “, ” LINES TERMINATED BY “\r\n” ignore 1 lines (username, password, role);

    Reply

  104. I think this is nice post;
    but i have two questions
    first:
    how will mysql understand the different datatypes of the excel file?

    second:
    If I have a large data in my excel file around 100,000 records, and I want to change it to Mysql but store instead of “Male” in the excel column Sex a number e.g. 2 in a look-up table in Mysql and so for other 10 columns ?

    It took me days to develop a C# to do this, but I did it finally
    I can share with any one who is need to this code or algorithm – very simple
    send me an email to

    [email protected]

    hope to get a reply for my two questions if any one knows them how to do them in pure mysql syntax

    Thanks

    Best to all

    Reply

  106. Great Blog, those 5 steps works nicely….
    I have 20 columns with 30k data in the excel sheet but the problem is there a mismatch data in the columns.

    Hope to get a solution to this problem
    thanks

    Reply

  107. Really Great,

    but when i fetched data from excel sheet to mysql database ,along with string it also fetched some special character looking like ‘|’ automatically not exact, i don’t know what is that,
    plz someone help me ,how to prevent that character to insert in database

    thaks in advance,,,,,,

    Reply

  108. Thanks for this post, but I only can insert the first row from my CSV file. I’ve tried all the possible ways, with no success.

    Any sugestion?

    Reply
  109. Pingback: Import Excel Data into MySQL | Development Stuff

  111. Give my utility a try. It’s free and stand-alone (nothing to install). I am confident you will find it easier. It’s on my website under excel2mysql. Let me know what you think!

    Reply

  113. Hi,

    My excel file have many sheets(tabs). How can I inport each sheet into the MySQL database?
    Please do guide me, urgently required.

    Thanks in advance.

    Reply

  116. this post was very helpful to me..i resolved the matter i am bothered about..thank you very much for this post!!!

    Reply

  119. There’s a new online tool called databaseimportscriptor.com, which makes this much easier. You can just upload your spreadsheet, enter a sheet name and cell range and click convert. It will turn the data into a CREATE TABLE command and a set of INSERTs.

    Reply
  120. Pingback: Menambahkan Data MySql dari Data Excel | a husband, a father, a family man

  121. Is there any way where the data in mysql gets updated if we edit the excel sheet using this load infile method ? Also entries are duplicated every time i execute this command in mysql. I tried the mysql for excel plugin it is not showing up in the toolbar.

    Reply

  122. @Neelakshi: I’m not sure that is possible to do, since the Excel sheet doesn’t link to the database once you upload the file. The most obvious way is to re-upload any time you make a change, but I might be wrong.
    If that is possible, I’d like to know how, too.

    Reply

  123. You could use DocChow. It’s free on most platforms including linux. Here is the link for Downloads docchow dot com

    Reply

  125. Here’s a gist for the sql i used to load data from an excel file into mysql

    gist github com/mrgcohen/4bb5bef68f943e2e81dc

    Reply

  126. LOAD DATA LOCAL INFILE ‘C:\\Users\\user\\dbip-city.csv’ INTO TABLE lnw_15724513_test_project.test_ip2country FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\r\n’ (id,ip_from, ip_to,country,state,region);

    but all data insert in one field pls help

    Reply

  130. Thanks a lot, this command saved me 2 days effort.

    One quick tip, adding switch is safe to ensure all languages are loaded appropriately

    Reply

  131. hey. i m using localhost and i m done with the import. bt the prob is that that about 50 extra rows are imported with value 0 in each field. how to limit the no of rows to be imported?

    Reply

  133. Thanks ):

    when u execute this query in php

    in url ‘/upload / file.txt ‘

    chage with ‘ upload / file.txt’

    remove first ‘/ ‘

    Reply

  134. can somebody tell me how to load more than 19 records in mysql using same query. because same query is loads only 19 records.
    I really need it.
    Thanks in advance…!!

    Reply

  135. Kindly help me debug my code. I want to read data from a csv file and store in a temporary table. Then I use the data in temp table to update my main table after 10 minutes. The reason why I want this is because the CSV file runs in a different server and it keeps on changing. here is my code. The error I get is ERROR 1314 (0A000): LOAD DATA is not allowed in stored procedures
    Here is my code

    DELIMITER $$

    CREATE EVENT ev_update_from_London_Stock_exchange_csv

    ON SCHEDULE EVERY 10 MINUTE

    DO BEGIN

    LOAD DATA LOCAL INFILE ‘https://www.quandl.com/api/v3/…

    INTO TABLE temptable (Date,Open,High,Low,Close,Volume,Ex_Dividend,Split_Ratio,Adj_open,Adj_High,Adj_Low,Adj_Close,Adj_Volume)

    FIELDS TERMINETED BY ‘,’

    LINES TERMINETRD BY ‘n’

    IGNORE 1 LINES;

    INSERT INTO LondonStockExchange (Date,Open,High,Low,Close,Volume,Ex_Dividend,Split_Ratio,Adj_open,Adj_High,Adj_Low,Adj_Close,Adj_Volume)

    SELECT (Date,Open,High,Low,Close,Volume,Ex_Dividend,Split_Ratio,Adj_open,Adj_High,Adj_Low,Adj_Close,Adj_Volume)

    From temptable

    TRUNCATE temptable

    END $$

    DELIMITER;

    Reply

  137. when i type the code as:

    load data local infile ‘c:\mydatabase\postalcode.csv’
    into table postal
    fields terminated by ‘,’
    lines terminated by ‘\n’;

    it displays error like :
    ERROR 1300 (HY000): Invalid utf8 character string: ‘ ‘

    how can i solve this problem.

    Reply

  139. Cdata Software has developed The MySQL Excel Add-In, which is a powerful tool that allows you to connect with live data from MySQL Server databases, directly from Microsoft Excel. Use Excel to read, write, and update MySQL data. Perfect for mass imports / exports / updates, data cleansing & de-duplication, Excel based data analysis, and more!

    Reply

  140. Here is a very detailed article that i found for Import excel csv to mysql database.
    phpcodify dot com/import-excel-to-mysql-using-php-bootstrap

    Reply

  141. How to upload data from Excel to MySQL using MyPHP
    (using Windows 10 and Excel 2016)
    It should be easy! You can save an Excel spreadsheet as a CSV file. And there is an option on PHPMyAdmin to import a CSV file into a MySQL database. But…
    The result of saving an Excel file as CSV is like:
    1,Fred,Bloggs,22,Acacia Avenue
    But PHPMyAdmin expects:
    “1”,”Fred”,”Bloggs”,”22”,”Acacia Avenue”
    So, after creating the CSV file you need to open it with a text editor (such as Windows Notepad – free, find it in Windows Accessories) and edit it to get the required format. You can speed this process up by using Find/Replace.
    First, though, it is essential to ensure that your CSV file has the right number of columns. Your spreadsheet table should have exactly the same number of columns as your database table (you need to create and format your MySQL table before you start). Obviously, the spreadsheet columns should match the MySQL columns so that your data ends up in the right place. And CRUCIALLY there must not be any blank cells in your spreadsheet table. When the CSV file is created, Excel skips any blank cells – this is good because all the unused cells at the side or below your table are ignored. But any blank cells within the table will also be ignored and therefore any row in the spreadsheet table that has a blank cell in it will have the wrong number of fields in the CSV file, and the import will fail (usually it will import the rows that are OK starting at the top and will abort at the mismatch row). So you MUST put something in every cell of the table. One option is to enter something like * and ensure that your program which extracts data from your MySQL table treats * as an empty string. Another option is to put something distinctive like XX in the cell and then to delete this later with the text editor. Whichever option you use, make sure that what you enter is something unique to the blank cells!
    Secondly, the cells in your Excel table must NOT contain the comma (,) or double quote (“) symbols. You can see from the earlier example that MyPHP uses double quote to define the start and end of column entries and comma to show breaks between entries. (In fact, you can have commas in your CSV fields as long as they are between the start and end quote symbols – but if you really want them you will have to edit them in after the CSV file has been formatted correctly).
    Note that you do NOT need to have column headings as the first row of your CSV table.
    My suggestion to make life easier.
    Insert an additional column filled with (say) ZZ before your first spreadsheet table column and another one after the last. Then create your CSV table which will look like:
    ZZ,1,Fred,Bloggs,22,Acacia Avenue,ZZ
    Open the file with the text editor. Use Find/Replace to change the comma symbol into “,”
    Your CSV will show
    ZZ”,”1”,”Fred”,”Bloggs”,”22”,”Acacia Avenue”,”ZZ
    Use Find/Replace to change ZZ”,” into “
    Then change “,”ZZ into “
    Your file is now correctly formatted and should import into PHPMyAdmin without errors. (It’s always a good idea to make a backup of the MySQL table first, though.)

    Reply

  142. hello Sir/Mam
    i want to insert on daily basis my excel file data in to my sql database.

    can this happen automatically?
    exel file data get updated on every day

    plz rpl to me

    mail_id:[email protected]

    Reply

Leave a comment

Your email address will not be published. Required fields are marked *