Converting Large Spreadsheet to Table

[Arthur Flores]

Hi All,

I’m having trouble trying to get a large spreadsheet converted to a Cloverleaf table.  I used a script to convert the data into the format of a .tbl file.  I keep getting a ‘malformed line’ error when I try to load the entire file.  If I change the file to only contain the first 100 lines or so, the file loads without issue.  I was curious if anybody else has run into this issue?  Or if there is better way or a tool that we can use to convert raw data into a table.  In case it matters, the spreadsheet has about 800 lines to be loaded into the table.

Any guidance would be much appreciated!

Topic

[Jason Russell]

You likely have duplicate lines. Make sure you don’t have duplicates on the first column of your spreadsheet.

Also check there are no encoded ‘invisible’ characters (tabs, nonbreaking spaces, etc). Use Notepad++ or vi -b (on Linux) to check for them.

• This reply was modified 1 month, 2 weeks ago by Jason Russell.

[Jim Kosloskey]

I am assuming you are exporting the spreadsheet to a csv file then running that for conversion. I have a proc that works if you would like that but like Jason says a likely culprit is a duplicate in the left column if the table is one-way or column if it is a 2-way table.

I would see if you have a tool that will check the file first for duplicates and have that let you know where duplicates exist rather than just unilaterally eliminating duplicates (how do you know which dupe should remain otherwise?).

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 61 years IT – old fart.

[James Cobane]

I have a script that I created that reads a CSV file and utilizes an SQLite database to remove duplicates and generates a Cloverleaf lookup table.  Let me know if you would like a copy of it.

Regards,

Jim Cobane, Henry Ford Health

[Arthur Flores]

Thanks so much, guys!  I got so caught up in the weeds, that I forgot about duplicates because I thought there was a more descriptive error message for that scenario.

Crisis averted – thank you all!

[Jason Russell]

Yeah that error is pretty bad. It took us a LONG time to figure out what was going on when I first ran into it.

[Jim Kosloskey]

Ditto – but it is one of those things that is so painful you NEVER forget the experience. 🙂

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 61 years IT – old fart.

[Lonnie Davis]

For future reference, the Cloverleaf API has a function to convert Cloverleaf tables (.tbl) to CSV and back.  Of course the use of CLAPI would require basic or advanced security to use.

[Tony Matsche]

Arthur – Would you mind sharing the script?

[Jason Russell]

Here is one I put together in KSH before we really got to rolling in Cloverleaf (I’d probably do it in TCL now):

A few notes, the comment is ‘eGate to cloverleaf’ but it is a basic two item CSV file. the default was %default%,<value>, so we would grab and set that as necessary. This also created the name as a copy with .tbl as the extension, regardless of the previous extension.  I wrote this rather quickly with limited comments as it was a initially a throwaway script for converting mass tables. I need to update it since we use it for csvs now when updating larger tables. It doesn’t take long even on tables with a few thousand lines, though TCL would probably parse it faster. Note this is on RHEL and I can’t guarantee POSIX compliance on the script, though I don’t think there’s much to be non-compliant on.

#!/bin/ksh
#Table conversion from eGate to cloverleaf.

tableSet=””
clTable=””
clTableName=””
egTableName=””
defVal=””
default=””
userName=”$(who am i | awk -F’ ‘ ‘{print $1}’)”
datetime=”$(date +’%B %d, %Y %r EDT’)”
prologue=”# Translation lookup table\n\
#\n \
prologue\n \
who: ${userName}\n \
date: ${datetime}\n \
outname: output\n \
inname: input\n \
bidir: 0\n \
type: tbl\n \
version: 6.0\n \
end_prologue”

if [[ ! -f $1 ]]; then
echo “$1 is not a valid file”
else
egTableName=$1
clTableName=$(echo $egTableName | sed -E ‘s/(.*\.)(.*)/\1tbl/’)
if grep -q “%default%” $egTableName; then
defVal=$(grep “%default%” $egTableName | awk -F’,’ ‘{print $2}’)
fi
default=”#\ndflt_passthrough=0\ndflt=${defVal}\ndflt_encoded=false”
clTable=”${prologue}\n${default}”
while read line; do
input=”$(echo ${line} | cut -d’,’ -f1)”
output=”$(echo ${line} | cut -d’,’ -f2)”
firstChar=”$(echo ${line} | cut -b 1)”
if [[ ( “${firstChar}” != “#” ) && ( “${firstChar}” != “%” ) ]]; then
clTable=”${clTable}\n#\n${input}\n${output}\nencoded=0,0″
fi

done < “${egTableName}”

printf “${clTable}” > “${clTableName}”
fi

[Author]

Replies