-2

I have an excel file that looks like this -

enter image description here

I am trying to add it into the database but it's not getting inserted properly. This is what I have tried so far -

LOAD DATA INFILE 'F://blood.xls' 
INTO TABLE blood_bank
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'

and 

LOAD DATA INFILE 'F://blood.xls' 
INTO TABLE blood_bank

This is what it is giving

enter image description here

I am not able to figure out where I am doing wrong. Please help. Thanks in advance.

Koushik Das
  • 9,678
  • 3
  • 51
  • 50
  • 4
    That's because LOAD DATA INFILE works from a __csv__ file, not from a BIFF-format __xls__ file.... you'd need a library capable of reading native BIFF-format files to import them to a database – Mark Baker Sep 05 '16 at 16:33
  • 2
    I would save it as a comma separated file with fields enclosed by double quotes for starters – Drew Sep 05 '16 at 16:36
  • You can save as .XLS file in .CSV format and easily import it using "LOAD DATA". – Rahul Patel Sep 05 '16 at 16:39
  • @Drew I downloaded this file – Koushik Das Sep 05 '16 at 16:44
  • @RahulPatel Hi, can you please tell me the code if the exact table would be in csv? Is it right? LOAD DATA INFILE 'F://blood.csv' INTO TABLE blood_bank FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' – Koushik Das Sep 05 '16 at 16:44
  • That depends what the separators and line endings are in the file – Mark Baker Sep 05 '16 at 16:47
  • It is a 2 or 3 part harmony. The format of the csv, the load data infile statement, (and the 3rd is your table structure). There are dozens of online examples showing the blocks well defined on a page (such as the text with separators etc) and the load data infile. Also the main manual page from mysql. If you need to email it to someone to have them re-save it then do that. Otherwise look into PHPExcel (sp?) – Drew Sep 05 '16 at 16:49
  • @Drew Thanks a lot for PHPExcel. – Koushik Das Sep 06 '16 at 03:00
  • May I know the reason behind downvoting my question? I am ok with criticism if I made any mistake and I am willing to accept my mistake as well since it's a chance for me to learn or is it because some people are too impatient and think that everybody must know everything and think of themselves as the next Mark Zuckerberg? Thanks in advance, guys. – Koushik Das Sep 06 '16 at 03:03

1 Answers1

2

The following query will be helpful to import CSV data in your mysql DB table.

LOAD DATA INFILE 'F://blood.csv' 
INTO TABLE blood_bank
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

Please follow this link : How to import CSV file to MySQL table

Community
  • 1
  • 1
Rahul Patel
  • 5,248
  • 2
  • 14
  • 26