Questions about your Knack app? Let's Chat. https://www.knackbuilders.com. You have built your app, and how you want to get your data into it. Customers, historical transactions, or whatever. You are going to have to get your CSV data in the same structure as your app, and correctly match up values such as connections and multiple choices. Here I show some approaches to some common issues when preparing your spreadsheet for import.
Get a free Knack trial account here: https://www.knack.com/r/you-tube
If you are a fan of building great apps with Knack, please click the Subscribe button to join my channel, and ring the bell to receive notices on all my new instructional videos. Thanks!
You can check out my Knack database services here: Knack!
Full Transcript
hey folks uh dave parish here at knack
builders
um
if you like this video please you know
subscribe to my site
uh or my youtube channel but today's
topic i'm gonna jump right into it
uh prepping a spreadsheet
for uh import into your nac database
uh so you built your app
and you have data maybe historical data
or lists of customers or something
that you want to
bulk import so you don't have to do one
by one
i'm going to show you some
how to prep a spreadsheet for that i
have a few topics here
names and addresses how to deal with
connections multiple choices a few of
these and i'm going to get into that in
just a minute thank you
[Music]
okay
i'm back
um let's talk about
prepping
or issues with
getting a spreadsheet ready to turn into
a csv and then import i'm going to talk
about name and address fields first
um
these are special fields they have
multiple parts in it it's likely
that
you're going to use a name field instead
of a text field somewhere in there in
your app
and but what i find i'm just going to
give you an example here
that when i'm
clients are sending me their
spreadsheets to import
takes all this takes a lot of
sometimes it's simple sometimes it's
complex sometimes it's really hairy uh
but
this is a fairly simple thing you get
your name
names of people to be a column in one of
your spreadsheets and they're together
last name first name
um
that's going to cause a problem if
you're trying to do
a two-part first name last name
field which is more robust you can do
more stuff with it so you're likely
going to have it
to deal with this
you select
your
data
and you do something in excel called
text to column
and
in this case we want to deliminate it
meaning we're going to tell it when to
split these up and in this case it works
with a space i'm going to go to next
you can sometimes there's other
deliminators but in this case it's a
space and if i do that
it breaks them up so now you might have
to copy these whole columns put in
another spreadsheet
perform this function and split it up
add another column to your main
spreadsheet and put it back in there
but that's what this does and it's
helpful sometimes you have names maybe
your last name has two last names or
there's in the middle initial in there
you'll have to deal with some of that
static but this this helps a lot with
that
now addresses
let's jump onto that
and follow my addresses
i get this a lot too they're
not
they're all in one
on field
street
state
zip city whatever and they're not in a
real eliminated fashion meaning they're
in all kinds of formats and if you're
trying to do it by space or whatever
let's just do it real quick let's do
this tell it to deliminate by space
and you can see here it just makes a
mess out of it
chances are i mean if you're lucky you
can get you can break this up in the
fields you need
but um
rather there's services out there let me
go find it right here
uh there's a bunch of them
it basic they're called parsing parsing
means it's taking something and
splitting up
so
if you uploaded
this
which is just like i had in that
spreadsheet
it can figure out i don't know how it
does it what should be what and we'll
put it in the city state postal address
street address
and then you can go
put that
into
back into your main spreadsheet and
hopefully that works
another one here
look at my list
unique ids
it's always best to have a unique id
this may merging with uh
with some of the other topics here
this is a list of clients
and you may wanna i've hidden some of
the stuff here
um
you can use their first name last name
and also how this is can be displayed
your display field if you if you know
what that is
we have first name last name but how
this would have could appear
um
you can make a setting to make it a
let's let's just do one uh let's do
s t a r k e r comma
t i timmy
this is how you can set it to be
displayed
now names aren't good unique identifiers
rather
you might want
when you're dealing with your import
i have this blocked out because they're
real numbers uh a unique id
and
that's easier to match on
um
let me
let me find one here
okay this is a list of clients
and this is a list of orders
client orders
so
in this case if you were matching it on
this you'd said starker
comma timmy
client
this is a
order
and a client over here so one to many
one client can have many orders
uh and this is listing all these orders
this is the connected field
and
if you're matching based on this what
you can do
uh
you're gonna have to make sure that
they're spelled correctly um
meaning there's no
the exact same spelling has to be there
there isn't a trailing space let me do
an example like right here trailing
space
if that wasn't the same in the list of
your actual clients it wouldn't match um
rather in this one if it's possible
sometimes this just isn't possible
because you don't have this data out of
outside of that formatted correctly
you'd rather
have each of these be
the unique id number for that client
it's a better way to match it
and all you have to have a present here
and when you're doing the import and
i'll have another video on how to do the
import this is just how to prep your
spreadsheets
uh but when you're doing the import and
it's a connected field you can choose
which ones to say here's how i want to
connect it and if you had this unique id
number here you'd say okay i want to
connect it to here
um so that's that
let me get onto another one
i've sort of covered a few of them
the other thing order of imports i just
showed you those two spreadsheets one
with the client
and one with client orders and they're
connected one client can have many
orders
well
it's a parent-child relationship the
order of you uploading your stuff you
should always do the parent
first
uh in any of those connections
obviously because if you try to import
the or client orders prior to ordering
or uploading the clients it won't be
able to go find them so that's what you
have to do it in that order
um
also
i'm going to just touch on some advanced
topics here
one of them is
files
that's a hard one uh
files don't exist in a spreadsheet uh
data in a cell does and i haven't
figured out a way actually how to do
files
images knack how knack handles images
is you upload an image but immediately
turns it it puts it on a server on
amazon and turns it into a url
uh you can upload a url
but most people don't have urls of their
various images like if you had a client
you wanted a photo of them
um
i think there's some services out there
that you can sort of like that parsing
thing i just showed you if you had
um
a bunch of you'd have to have a unique
id a unique id number for your
client and then a list
and somewhere on the cloud google drive
or wherever you have pictures and each
of those pictures has the unique id of
that and i think there's some services
out there
that will go do that
it will
go
you will tell where to go look at the
pictures you will tell how to match up
with the list of clients
and it will go and one after another
make a url out of that
then return all those urls and i'll
match the client and then you can do it
like that um
images another one is
if you have
many too many relationships or you can
choose more than one value like in a
checkbox situation
um
those are put in the cell with
the value
then a comma then a space and value and
if you continue they're eliminated with
commas
you have to make sure you prep if you're
gonna upload they have to be in that
format the comma separated
uh stuff but that's what i got today um
i hope you enjoyed appreciate it thanks