Read excel data and insert into database

@holy24Jun 20.2018

Hi,

I have an excel saved in .xls which has column A to J.

I have also created the necessary fields in the database. (eg. name, age, address)

I need all the data from column C4 onward in excel to insert to the name field. Column E4 onward in excel to insert to the age field.

Can anyone kindly advise how can I proceed with this?

Thanks.

to post a comment

PHP

11 Comments(s) _↴

@rootJun 20.2018 — #Simplest way is to export your sheet as a CSV format file and then use PHP to import the file and read the lines for the information you want and then insert it in to the fields you require filling in the database.

PHP has a suite of CSV functions to make accessing the individual lines of code and data.

@holy24authorJun 25.2018 — #Hi,

I have used the following code to read from excel and import the value directly to database.

<?php

$fileName2="test.csv";

 $query = "
   LOAD DATA LOCAL INFILE '$fileName2'
   INTO TABLE test2
   FIELDS TERMINATED BY ','
   ENCLOSED BY '"'
   LINES TERMINATED BY 'rn'
   IGNORE 4 LINES
   (date,due_date,paid)
   ";
 
   $db->query($query);

?>

Somehow, the excel as per attached which is saved as .csv, when the value is imported into the databse, it shows "0000-00-00" and "0.00" for the date and amount. The datatype in database for date (date) and amount (decimal (10,2)). Is it because the datatype is not formatted correctly in the excel.

Can I format in the datatype for the excel in the coding, if so how can I do it? I do not want to amend anything from the excel(manually format the cell).

[upl-image-preview url=https://www.webdeveloper.com/forum/assets/files/2018-06-25/1529908140-98804-picture.png]

@holy24authorJun 25.2018 — #Hi,

For the date, I figured out as below:

$query = "

LOAD DATA LOCAL INFILE '$fileName2'

INTO TABLE test2

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY 'rn'

IGNORE 4 LINES

(@date,due_date,paid)

SET date=STR_TO_DATE(@date,'%d/%m/%Y')

";

However, can anyone advise how to convert the number? In the excel, when I click format cell, it shows Currency with 2 decimal and symbol $ but in database is decimal (10,2).

@holy24authorJun 25.2018 — #Another thing I noted when I tried open the csv file using a notepad, the amount shown as below:

3/05/2018,3/05/2018,,,"$2,222.93",$0.00,"$2,222.93"

@rootJun 25.2018 — #You have no evidence of any of the PHP CSV functions to read the CSV file.

You can't just take a CSV file and expect it to get dumped in to a database in the fashion its set out.

You have to write a routine to suit your table needs.

Suggest that you read the PHP website and see the examples on how to read a CSV file.

@ginerjmJun 25.2018 — #Usually a date type field in MySQL is in the form of yyyymmdd. And for the numeric fields you will want something that looks exactly as you described your intended format "10.2". This means that you need to massage your csv data as already suggested to you.

Write some code to read the csv data file and to produce another filed containing the corrected data fields that you want to then import using the above code. Try doing some preliminary work and output the results to be sure you have successfully converted your input properly.

@rootJun 26.2018 — #@ginerjm#1593295 OR... just use a database to start with.

When I worked for the local authority, the general consensus was that if the database has fewer than 100 records, then use a spread sheet...

When I was told that, I said, no sorry, if its a database, then it needs to be a table in a database AND NOT A SPREADSHEET, a spread sheet is not a database YET people do and still produce them and then have to hoop jump and convert it but find there is NO EASY ROUTE because of VERY BAD ADVISE to start with.

Simple rule...

If its MONEY DATA, then yes, a sp[readsheet, thats what they are for.

If its INFORMATION DATA like Names, addresses, contact, etc... DATABASE the stuff, DON'T use a spread sheet.

The person that thought that was going to save time and effort is really stupid, a spread sheet can not be searched HOWEVER if its a database, a search will pull data from inside the database without having to load it in to the reader program.

Anyone who aspires to this ideal of use a spreadsheet for a database really needs to assess their future career and what they have been taught, maybe more than one or more BAD standard practices being used.

@ginerjmJun 26.2018 — #@root - I hope you don't think that my post was meant to be supportive of the writer's use of Excel as a 'database'. I certainly see the error of his ways but..... Perhaps you should have made all of your valid points in your earlier post....

My post was simply an attempt at helping get a result to alleviate the situation. I've learned in the last few years of being here that sometimes you can't fix s.... but merely try and help and educate. That's what I posted.

@rootJun 26.2018 — #@ginerjm#1593338 nope.

@rootJun 26.2018 — #Also this subject is too similar to another post asking as similar type question where a database is a natural choice for this and instead the OP is wanting a long winded coded effort that could easily be done in maybe three main routines and deliver the results needed with minimal fuss.

@shahroznawazAug 01.2018 — #Hi, I've written a simple tutorial on importing/exporting CSV data to the database. You can follow if it might work for you. https://www.cloudways.com/blog/import-export-csv-using-php-and-mysql/

Also in #PHP _↴

Performance Optimization - Variables VS Accessing Array 2 submit buttons?Query Two Columns

Success!

Help @holy24 spread the word by sharing this article on Twitter...

Tweet This

Read excel data and insert into database

11 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Read excel data and insert into database

11 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

11 Comments(s) _↴

Also in #PHP _↴