CSV Import Prep for Knack Database Apps

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