sql server - Importing/Pasting Excel data with changing fields into SQL table -

i have table called animals. pull data table populate system.

i excel data lists of animals need go in animals table.

the excel data have other identifiers, breed, color, age, favorite toy, veterinarian, etc.

these identifiers change each new excel file. may repeat, others brand new.

because fields change, , never know new fields come each new excel file, animals table has animal id , animal name.

i've created values table hold other identifier fields. table structured this:

animalid value fieldid datafileid 

and have fields table holds key each fieldid in values table.

i because alternative keep big table fields may not used each time need add data. big table lot of null columns.

i'm not sure way way either. can seem overly complex.

but, assuming way, best way excel data values table? list of animals easy add animals table. each identifier (breed, color, etc.) have copy or import values , update table assign matching fieldid (or create new fieldid in fields table if doesn't exist yet).

it's huge pain load new data if there lot of identifiers. i'm struggling , use better system.

any advice, help, or pointing me in better direction appreciated.

thanks.

depending on client (eg, use sequelpro on mac), might able import csvs. pretty shaky, can export excel document csv... how convenient.

however, doesn't database structure. granted, using foreign keys idea, importing data unobtrusively (and easily) need done row @ time.

however, try modifying suit needs, first exporting excel document csv, removing header row (the first one), , using regular expressions on change big chunk of sql. example:

your csv:

myval1.1,myval1.2,myval1.3,myval1.4 myval2.1,myval2.2,myval2.3,myval2.4 ... 

at point, like:

mycsvtext.replace(/^(.+),(.+),(.+)$/mg, 'insert table_name(col1, col2, col3) values($1, $2, $3)') 

where know number of columns, names, , how values organized (via regular expression & replacement).

might place start.