PHP MYSQL IMPORT EXCEL DATA
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??
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??
Greg Alexander
You need to change your cellphone field type to float instead on INT... or let me ask what the field type is now?
ASKER
it is varchar now. ill try it as float
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
using the phpmy admin import tool for excel files.
Untitled.png
Untitled.png
I would try to save the spreed sheet to CSV and then try to import as CSV.
ASKER
ive tried that as well,
all the data ends up in one column
all the data ends up in one column
ASKER
like so...
Untitled.png
Untitled.png
Hmm that it semicolon separated, did you set that when you imported instead of commas
ASKER
here is how it is set out..
Untitled.png
Untitled.png
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
Fields terminated by:
Fields enclosed by:
Fields escaped by:
Lines terminated by:
you get my last message
nvm I see
ASKER
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"
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"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.
10-4. I'll be here.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
I will award points equally thanks again.
ASKER
javascript:void(0);