Creating a table from .csv with lots of fields

Community Support
1

I’ve got a .csv file (table t’s data) with hundreds of fields, and I
also have a .csv containing the metadata for table t. What I’d like to
do is be able to create an empty table with the fieldnames (without
typing them all out!), and let the data set the types accordingly from
the read (using their types as defined by the meta).

For example:
metat: ( fn:(); typ:(); fk:(); att:())
metat insert (("sccc";",") 0: :metat.csv)
delete from metat where fn=c / don’t need col names

fns: string raze flip select fn from meta / get the fieldnames
fns:fns,:“:();”
fns:-1_ssr[raze fns;“"”;" "]

BUT… fns looks like this:
“fld1:();fld2:();fld3:();…fldn:()” / includes those pesky quotes
because this is a char string
How do I use this to effectively do:
myt:( fns) ???

I was thinking something like value fns, but that didn’t work. Is
there a way to do this?

Then…
types: raze flip select typ from metat
types:ssr[types;“C”;“*”] / have to do this for character strings
myt insert (types;",") 0: :t.csv / would work like a charm, right?

I’m really new to this, so any help (and patience with me) is greatly
appreciated!

2

The problem with your use of value is that you are not building thefull expression needed to define a table. That could be done as:q)a:“fld1:();fld2:()” / field definitionsq)b:value “(”,a,“)” / define table bq)type b98hq)bfld1 fld2---------On Jan 17, 3:46?am, QNewbie <cara.bracamon…> wrote:> I’ve got a .csv file (table t’s data) with hundreds of fields, and I> also have a .csv containing the metadata for table t. What I’d like to> do is be able to create an empty table with the fieldnames (without> typing them all out!), and let the data set the types accordingly from> the read (using their types as defined by the meta).>> For example:> metat: ( fn:(); typ:(); fk:(); att:())> metat insert (("sccc";",") 0: :metat.csv)> delete from metat where fn=c ? ?/ don’t need col names>> fns: string raze flip select fn from meta ? / get the fieldnames> fns:fns,:“:();”> fns:-1_ssr[raze fns;“"”;" “]>> BUT… fns looks like this:> “fld1:();fld2:();fld3:();…fldn:()” ? ?/ includes those pesky quotes> because this is a char string> How do I use this to effectively do:> myt:( ?fns) ? ???>> I was thinking something like value fns, but that didn’t work. Is> there a way to do this?>> Then…> types: raze flip select typ from metat> types:ssr[types;“C”;”*"] ? / have to do this for character strings> myt insert (types;",") 0: :t.csv ? / would work like a charm, right?>> I’m really new to this, so any help (and patience with me) is greatly> appreciated!</cara.bracamon…>

3

Thanks so much, Chris! I guess I was close, but not quite there…

4

To: personal-kdbplus@googlegroups.com
X-Mailer: Apple Mail (2.1077)

if you=E2=80=99ve got hundreds of fields it=E2=80=99ll probably be =
easier to use csvguess.q

http://kx.com/q/e/csvguess.q

q csvguess.q yourfile.csv

look at, and adjust the column mapping table (change the types in =
the column)

then generate yourself a loadscript

q)savescript=E2=8E=95

5

Thanks, Simon. My file, while very wide, doesn’t have tons of records(just a sampling to play with), but I can see that your alternativewould definitely be the way to go for larger files. I’m just barelygetting started, so it’s been a learning experience for me just toattempt this.