?
Solved

PHP MYSQL IMPORT EXCEL DATA

Posted on 2011-04-26
23
Medium Priority
?
529 Views
Last Modified: 2012-05-11
Hi Experts,

I have an excel data file with mobile numbers, which have leading zero's when i export this into mysql it takes the leading zeros off..

I have tried the following: Formatting the mobile number to custom and using 00000000000 so it looks like 07777777777 but that did not work still strips off the leading zero.

i also tried changeing the format of the column to text but that still did not work im guessing mysql changes the formatting back to "general" which then strips out the leading zero's.

Do you have any ideas??

0
Comment
Question by:NeoAshura
  • 9
  • 6
  • 4
  • +3
23 Comments
 
LVL 19

Expert Comment

by:Greg Alexander
ID: 35467497
You need to change your cellphone field type to float instead on INT... or let me ask what the field type is now?
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35467507
it is varchar now. ill try it as float
0
 
LVL 1

Accepted Solution

by:
AnuarJamlus earned 400 total points
ID: 35467523
What's the data type for the phone number field in the mysql database?

I'm guessing it must be an integer type data and that's causing the leading zeros to be ignored.

Try changing it to varchar of an appropriate length. That should preserve the formatting of the phone number.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 19

Assisted Solution

by:Greg Alexander
Greg Alexander earned 400 total points
ID: 35467534
Hmm, actually varchar should work... how are you importing it?
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35467558
using the phpmy admin import tool for excel files.
Untitled.png
0
 
LVL 19

Expert Comment

by:Greg Alexander
ID: 35467588
I would try to save the spreed sheet to CSV and then try to import as CSV.
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35467606
ive tried that as well,

all the data ends up in one column
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35467628
like so...
Untitled.png
0
 
LVL 19

Expert Comment

by:Greg Alexander
ID: 35467701
Hmm that it semicolon separated, did you set that when you imported instead of commas
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35467714
here is how it is set out..
Untitled.png
0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 400 total points
ID: 35467727
Can you please post a stripped-down copy of the spreadsheet - we only need a few columns and rows to illustrate the principles.

Just a thought - why not prepend the letter "A" to all of the numbers.  It would dilute any propensity to convert the values to integers!
0
 
LVL 1

Expert Comment

by:AnuarJamlus
ID: 35467731
When you do the file import what are you entering on the following fields?

Fields terminated by:
Fields enclosed by:
Fields escaped by:
Lines terminated by:
0
 
LVL 19

Expert Comment

by:Greg Alexander
ID: 35467733
you get my last message
0
 
LVL 19

Expert Comment

by:Greg Alexander
ID: 35467741
nvm I see
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35467851
what do you mean ray prepend? A?

here is the stripped down datasheet.


Untitled.png
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35467916
prepend:

If the number is 0117033565001, you do something like this:
$text_number = '0117033565001';
$db_number = 'A' . $text_number;

Then when you recover the number out of the data base you use substr() to declop the leading "A"
0
 
LVL 7

Assisted Solution

by:Vimal DM
Vimal DM earned 400 total points
ID: 35473940
Hai,

1) Otherwise,go for Manuel import to Mysql, as they say change the xls file to csv file

2) And just read the content from the csv file and insert them in the DB

this would be very nice in data conversion.

I have done so-many conversion like this.

"Try CSV to MYSQL data import using PHP"
 
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35474661
a stripped-down copy of the spreadsheet - we only need a few columns and rows

Instead of a picture of an empty spreadsheet, can you please post some of the actual data?  You can upload XLS files here at EE.  Thanks.
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35474921
Hi ray, Im not back at work until tomorrow but i will provide you with a stripped down copy and also try saving the XLS and CSV.

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35474929
10-4.  I'll be here.
0
 
LVL 5

Assisted Solution

by:innotionent
innotionent earned 400 total points
ID: 35475025
I'm thinking you are going to have to make your own import script.

There is a php function you can use to make sure you're using the correct padding.

For example you have 01234567. And the leading zero is removed giving you 1234567. Sprintf is probably what you want to use. Then your result should be 01234567.

This is assuming that your variable will always be the same lenghth. Even though you can probably do a count of the string length and adjust your sprintf parameters accordingly.


$value = sprintf( '%08d', 1234567 );

Open in new window

0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35482282
It has been decided by management to remove the 0's from the data, This will make data input every month easier and more automated.

I will award points equally thanks again.
0
 
LVL 6

Author Closing Comment

by:NeoAshura
ID: 35482290
javascript:void(0);
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses
Course of the Month16 days, 19 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question