How to Convert/Import Excel to SQL

Published 5 months ago by TheBlueDragon

Hello everyone, i have small project and client now ask for importing the old data from excel to sql

and i use this tool https://sqlizer.io to help me out and after some modifications i get the sql code but the issue now with the date format

in the excel sheet the date format is mix like

14-3-17
14/3/2017
17-3-14
2017/03/14

so the phpmyadmin didnt accept it and changing the cell format or pc date format didnt solve it

some rows still have the issue

so is there any package in laravel or any other resources can help me with solving this matter

and could carbon solve this if i could read the excel sheet and replace the old date then make the entry. ?

thanks..

jaydeluca

Pretty sure you could do something like

Carbon::parse($date)

and then use carbon to format them however you need.

jaydeluca

To expand a little bit, I assume that you are using Laravel?

What I would do in your position is import the SQL into sequel pro, and then export the data as json. Then I'd create a seeder that loops through the json data and formats the dates (using Carbon::parse()->format()) and then enters them into the DB

TheBlueDragon

@jaydeluca thanks for the reply and the idea i try to did that

as following

'passport_exp_date' => Carbon::parse($obj->passport_exp_date)->format('d-m-Y'),

my json line is

  "wp_exp_date": "15/01/2013",

but after i run the seed command i got this error

 [Exception]
DateTime::__construct(): Failed to parse time string (15/01/2013) at position 0 (1): Unexpected character

how i can solve this issue ??

ohffs
ohffs
5 months ago (241,840 XP)

If you use something like phpexcel or spout it can read an xls(x) file in and spit out the data in any format you want to make. Both of those also understand if a column in excel is formatted as a date and will turn it into a datetime object for you.

But if the data is in a fairly random free-format style (as is often the case with spreadsheets - don't get me started or I'll be here all day) then you're probably going to have to do some work transforming it with various tests/regex etc.

TheBlueDragon

@ohffs thx for reply

i think it will give same result as the json file i have

ya i was speak with my friend about make some regex for fixing this but dono if it even possible

i found some fields have space in date " 1 / 1 / 1990 " so for this i can do it with regex to remove the spaces but what about flipping the whole thing to be like

1990/1/1

is it possible by regex ?

ohffs
ohffs
5 months ago (241,840 XP)

Yeah - spreadsheets are evil :-/ Down the years I've had to do a lot of 'guesses' for fields - so maybe test for a date that could be :

1990/11/30

11 30 1990

30th Nov 1990

30 November 1990

Nov 30th, 1990

Friday 1990/Nov 30th

On and on :-/

I used to just have a big series of if (preg_match(style1.... if (preg_match(style2.... etc. But more recently I've been doing a try { Carbon::parse($field) catch (... and only if Carbon can't parse it I fall back to manual. Carbon catches about 90% of the cases so there's less to do manually and you can usually fix those up by hand.

But yeah - it's a pain. Embedded tabs, control characters, hidden formatting - all really, really unpleasant to deal with :-/

TheBlueDragon

@ohffs they make ppl crazy really Microsoft need to do something with there software =_= if the date format need to be chosen why its allow for free style =/

i will try the try and catch, and will see

the issue i my json have nearby 900 record and its fail in first record LOL !!

TheBlueDragon

@ohffs

i found some issue with the following date

15/01/2013

Carbon consider the format giving as m/d/y

so its return error bcuz its consider 15 is month !!

now for the

date_parse() its return an array with every thing is good

but i how i can let Carbon use such information to parse every thing correctly

its not only about reversing its about let it know that giving date is in a format of d-m-y

TheBlueDragon

strange issue i got now after trying so many things

i got the following code working

   $date = "1/4/1964";
   echo Carbon::parse(date_format(date_create_from_format('d/m/Y', $date), 'Y/m/d'))->format('d-m-Y');

but when i add it to my loop its not working

foreach ($data as $obj) {
        try {
        \App\EmployeeList::create(
            [
            'account_number'    => $obj->account_number,
            'name'              => $obj->name,
            'passport_number'   => $obj->passport_number,
            'passport_exp_date' => Carbon::parse(date_format(date_create_from_format('d/m/Y', $obj->passport_exp_date), 'Y/m/d'))->format('d-m-Y'),
            'wp_exp_date'       => Carbon::parse(date_format(date_create_from_format('d/m/Y', $obj->wp_exp_date), 'Y/m/d'))->format('d-m-Y'),
            'wp_number'         => $obj->wp_number,
            'visa_number'       => $obj->visa_number,
            'visa_exp_date'     => Carbon::parse(date_format(date_create_from_format('d/m/Y', $obj->visa_exp_date), 'Y/m/d'))->format('d-m-Y'),
            'date_of_birth'     => Carbon::parse(date_format(date_create_from_format('d/m/Y', $obj->date_of_birth), 'Y/m/d'))->format('d-m-Y'),
            'trade'             => $obj->trade,
            'nationality'       => $obj->nationality,
            'uae_id'            => $obj->uae_id,
            'uae_id_exp_date'   => Carbon::parse(date_format(date_create_from_format('d/m/Y', $obj->uae_id_exp_date), 'Y/m/d'))->format('d-m-Y'),
            'start_joing_date'  => Carbon::parse(date_format(date_create_from_format('d/m/Y', $obj->start_joining_date), 'Y/m/d'))->format('d-m-Y'),
            'working'           => $obj->working,
            ]
        );
        } catch (\Exception $e) {
        echo $e->getMessage();
        dd($obj);
        }
    }

i got following error

date_format() expects parameter 1 to be DateTimeInterface, boolean given

but for example its return the first obj

so i try the date manually in the $date variable

and actually return the correct format date but inside the loop its not working !!

Sign In or create a forum account to participate in this discussion.