so in this video we'll upload this CSV
file and update the data in this table
posit table okay so we'll update this
data over here into our database table
okay so okay so if you are wondering
this that is from The Notebook app you
can watch that videos so for this video
I define these two routes also I could
get this controller budget controller
and also budget model so just create
this thing one budget controller budget
model and migrations okay so you can
download the code from github and set
these things up so our database has
these cheap month okay so whatever is
here okay so whatever is here is our
column in database okay
so way to find that these ID n times
stamps by default and this chip okay
corresponding to this
also month lodging meal housing so
generally this is the budget okay
according to zip code location okay so
for each month so I've just reduced it
out to this for month
okay so these are the some costs so so
let's jump into cold so let me open the
budget controller so first create the
budget controller and after you define
the routers in the web dot PHP just open
the budget okay so you need one method
to so form okay so return view upload so
this upload view is already created okay
in the source here in side views upload
okay
so this is the view to populate the form
like so so this is another simple form
okay so the action is it will post this
data to upload URL okay which we already
defined here it will go to a store
method inside posit okay so all the
logic we are going to write inside this
okay so main thing is this method so
this is a set up so this is just simple
form which is posting data to upload so
this is all about setup so now let's
something to code so define method calls
to okay
so inside this method first
so let me write some pseudocode so first
we'll take we'll get the file okay we'll
get that CSV file we'll read that file
will validate
okay so we'll validate for example so
this is this capital case okay but in
our database these are lower case okay
so we have to validate that data also
there might be some dollar sign or we
have to escape these dollar signs okay
so there is the validation part in order
to get file so I will define the upload
okay fine and name our file so part is
the key so you have to type this name
okay upload file so now we are getting
the file okay that csv file which we
uploaded from which we will upload it
from here okay so inside this data
okay this file
so now we got the file and we need the
file part in order for PSP to read it so
we'll define the file path okay so
upload will call method call get real
path so it will get the real part of
that file in my computer okay so now
we'll open it and read okay so open and
read so we have the PHP function called
F open okay so it will take the file
path the file path and mode will be R so
R is for read mode okay so you can read
right so so we'll validate in a moment
so first get the header
so this error so that we can construct
the array of this with this key and the
value
so there is so this cat header so there
is a function called F cat CSP okay so
what this will do is this will just read
the CSV file so we have to supply the
file here which we have already opened
so now this will read the file so you
can taste this by dumping the diatom
ping this header refresh that and we'll
use that file and we'll upload that so
this is giving us header in this format
okay but we want this to be as a key and
this to be value okay so we'll do that
now that we are getting the header next
we need to do we need to escape that for
any so these are uppercase we need to
make that lowercase so now here comes
the validation part so we'll evaluate
the header first so for that we have to
look through these each each that data
array okay area item so now let's do
that
so for each so variable will be header
so we'll first escape for any special
character or case so it skipped items so
we'll escape each items will assign so
we'll use crack replace yes
so pattern will be
this okay so let me explain first let me
dive okay
II to said
so
this is patron
and we'll replace that with empty string
so
so lower keys header
so let me just skip al header so a lever
means lower kissed error okay so first
we need to make this lowercase okay so
STR to lower is the function that will
make this header lowercase so doing this
is fine okay so in some case we might
have some special character here okay
underscores and we need to skip that so
if you want don't want to validate for
them string to the place will do fine
okay
i sting to lower will define so we are
going further we will replace the
specific actors there so this subject
will be this el header okay
so generally what we are doing is we're
work making this lowercase ever
okay and we are replacing all the
character except lowercase a to Z okay
so this is the res X for that so this
means except a to z lowercase a to z
okay so it will replace all the
characters except lowercase a to z with
empty string okay so which one this
Laura door in Laura okay so we can taste
that item here escaped item okay so the
state says save this go to and there is
uppercase and now if I will do that okay
where's the problem
so here's the problem okay so it expects
to be string but we are passing the
array here okay so the string will be
value remember so value will be that zip
okay so that is the value here so this
one value okay so now that's our to work
we load that and let's see so it is
returning jeep lower case okay so we are
dying and dumping for first value so it
is doing so okay okay so it is escaping
the character so if I supply here on
this course it will escape for title so
I'll save that and the Lord it will
again so zip okay so now it's working
okay so next so now we'll just open that
so this contains the item so now we need
to push this item to to this escaped
adder variable area okay so we need to
define other area
okay so let's define that
so escaped
and so this will be the array like this
so
now we'll push each item each escaped
item to this other area okay so we'll
use a rapist every pose
okay so name up area will be escaped
ever and the variable we want to push is
escaped item
so each item we want to push to that
array
all right so that's to do the work so we
can check that dying and dumping this
escaped header like so so let's check
that
yeah so now we are getting that array
with escaped item
so next we need to get data okay so this
is for header part this is for header
part so now next for looping through
each other columns to get the data okay
so looping
so in this case I'm using the while loop
to look through so while we assigned
columns so I'm using this variable
columns so same thing
I've got CSV and name of file will be
find the same as before so here so what
this will do is as we have already done
this okay we pull the header out of this
now this columns contain all the data
except this other one okay so that is
what I've got CSP - it will just read
that and take that out okay so now
remaining one is this okay so
now first we'll check for if columns so
you can die and um that column there and
check okay so I'll leave that for you if
you don't understand you can do that
okay so if first column is empty okay if
first column is empty we'll continue to
loop will will not process further and
we'll move to next loop okay okay so if
the first column or the first one is
empty like so okay if that zip is empty
we will not store that into our database
okay so that is the validation
so now we'll trim other details okay for
example we have to trim the dollar sign
okay
so dream
okay so here also I will use same as a
disappearance one okay so for each
so in this case variable will be columns
prank replace so in this case because we
have the normal data okay so this
decimal what I will do is just escape so
what this will do is this will escape
all the characters except the numbers
okay so we will escape all other value
except numbers with empty string and for
value okay so yeah so you can just now
the what this will do is this will get
the value okay so at the same so it will
replace this value with this and you can
do the same as a rapist okay
the same as previous but in this case I
don't want to do that so I want to
replace that value with the escaped
value okay so there is we can use the
pointer you can do there so what this
will do is this which replace this value
with this one okay you don't need to do
a rapist okay so now we get that so if
you don't understand this you can source
for that okay so it will just replace
the original value okay next we'll
construct the array so now we are
getting the data you can title value
here yeah so we are getting this data
okay scape data so
now
I want to combine this array okay so
this data array combined
so I will combine the header escaped
error with this value okay so each value
I'll just combine the respective data
with the header so I escaped earlier
case oh yeah here with this value here
so inside the while loop next we'll look
through is data and set type as integer
and flood okay so here same thing for
each we'll go through each data okay as
key and value so here also out in the
original value so next
so I will assign to fail you
so if key calls to deep in that case if
he calls to cheap are
Archy calls to
month
so if key cost achieve here okay so now
as you already know data contains that
key value pair and if that key chip are
month in that case what we want to do is
we want to make that value two integer
okay so in other case we want to make
them float
we also value
okay so this is the setting the right
data type okay so setting type
okay because our this contains integer
here these Kalama integer or decimal
thought okay so what data we are getting
from here might not be that in that
format so we're making that type next
now we will update the table so database
power so to table update okay so now
now are all validation part is finished
so we'll update the table so if I come
is what this data is okay just ID on
that and what so next what we'll do is
we'll was let's get the value jeep will
be inside data Jeep
similarly duplicate them similarly cat
month
okay
similarly for lodging
so make sure this key housing mean
lodging month budget so fast we need to
find the budget which way we want to
object so budget okay
so you need to use that posit here okay
like so so if you install that PHP
companion plug-in okay you can hit f5
okay to popular that I can power 5 is
the shortcut for that so now here we'll
use new larval methods
okay first our new okay so what this
will do is this will let me come let me
complete this tip
so what we are generally doing is we're
finding the data with these two value
okay if jeep and month
okay so zip is common for both many
cases of it like here yeah zippy is same
but we find the particular data using
zip and month combination okay so we're
so we're finding the data okay buzzer so
what first our new will do is if data is
already present it will get us that that
data okay so that we can update or do
whatever if that data is not present
there it will instantiate the new
instance so that we can save that okay
so it was this equivalent of clear doing
new posit okay so if data is not present
it will do new budget it will
instantiate a new object okay so that we
can see so let me - you can you
understand in a moment so now we'll
insert the data buzz it buzzing buzzing
similarly for meal and housing okay
so meal
and house housing okay so now we are
inserting this data in our database in
corresponding fields next we'll say tab
so say okay so we are saving data
so now that should shape that data so
this is all what we need to do so make
sure you created that budget model so
let me go to posit model okay so this
ignore this for a moment so this is the
budget model okay yeah so you need to
feel level these thing okay just okay so
now that's it okay the problem is here
I'm just passing the value it expects to
Eddie okay so we are combining Eddie not
so the arrays columns
okay so columns so that should fix the
problem let's see yeah so now there's
sort of load here so yeah now you can
see more data okay so now you can taste
that and you can detect somewhere here
okay if that is successful you can taste
for you can play around okay validator
so I'm posting the code in github okay
you can check there so this is generally
what you do to upload any CSV file
inside your database okay
so I'll see you next video bye