import a csv file to a look up table

[Collin Praster]

I have approx. 7000 lines in an excel sheet that I am trying to get into a lookup table. Would anyone have any way or idea for me on how to accomplish this?

Topic

[Jim Kosloskey]

I have a proc you can use.

email me if you would like it.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[James Cobane]

Hi Collin,

There are a few other posts out there regarding this as people have created scripts to take a .csv file and create a Cloverleaf lookup table.  Attached is a script the we created to create a lookup table from a .csv file.  It is pretty basic, but gets the job done.  The usage is:

hfhs_tablebldr inputfile table [delimiter] [debug]

You can put the script in the $HCIROOT/usercmds directory (remove the .txt extension).

Just run the script from the command line and it will put the table into the $HCISITEDIR/Tables directory based on your current site setting.

Hope this helps.

Jim Cobane

Henry Ford Health

[Brent Fenderson]

With that many rows, you might want to consider using an SQLite table.

[Charlie Bursell]

As another said you may want a database especially since 6.1+ allows database lookup in a table.

With that said, here is an easy method.

Create an empty table of the name you want to create.  Assign default if needed.   Then a simple script

package require csv

set fd1 [open test.csv]   ;# Open for read only.  May need full path

set fd2 [open mytbl.tbl a+]  ;# Open for append new table just created

# A list to check for duplicates.  Assumes lookup is left to right

set dupList {}

# Read CSV file one record at a time

while {[gets $fd1 line] >= 0} {

   # Just in case

   if {[string trim $line] eq “”} { continue }

   # Assumes two values in each csv record, adjust as required

   lassign [csv::split $line] fld1 fld2

   # Check for duplicates

   if {[lsearch -exact $dupList $fld1] >= 0} {

       echo $fld1 is duplicate in the record: $line  — Ignorned!!

       continue

   }

   # Put fld1 in list

   lappend dupList $fld1

   # Write to table.  Assumes first csv value left, second right

   puts $fd2 “#n$fld1n$fld2”

}

# close both

close $fd1

close $fd2

# You have to decide if it will be a left to right, right to left or both.  Then you have to check for duplicates.  The engine will puke on duplicates

[Author]

Replies