Importing data to ProLaw from Excel

Thursday, February 7th, 2013 | ProLaw Case Management, Technical | Mike Smith

We recently had an issue were we were importing data from Excel sheets into a staging table in SQL, then importing that data into ProLaw.  Some of the fields in excel were getting truncated and causing issues on the imports.

The SSIS package did the imports fine with all our test data, but our test data was not big enough, once deployed we found users were imputing more data into the fields that we had anticipated.

Some Constraints:

Excel data would be varied, but the sheet would be static. We could format the sheet how we needed.

The table where the data was imported needed to be TEXT type.

The Excel file was 2007 formated.

SQL 2005

What we found:

Altering the field formatting on Excel did not alter the data that was imported to SQL.  We tried general, special, and text all with the same results.

Changing the table structure from TEXT, NTEXT, or VARCHAR affected how much (all or a truncated section of data) was imported.

The Solution:

NTEXT (non-unicode) was the format that would always import all the data from Excel regardless of cell formatting, we had to alter our staging table structures to match this.

You cannot insert NXTEXT into TEXT.  Thus we needed to cast the NTEXT as VARCHAR(MAX) when running the insert command into the TEXT field that existed in the ProLaw database.

 

After making those modifications, the schedule tasks correctly imported all of the data in the cells into ProLaw for the users to access.

 

 

Tags: , , , , , ,

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Page 1 of 11

Leave a Reply