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 day, 23 hours 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.

[Author]

Replies