need way to handle large csv imports that overflow cell size

[Guest]

We are using Outlook 2002 and 2003 to create tasks for many different people.
Each person adds notes to their tasks. To create a weekly report, we export
the tasks from each person into 1 CSV file per person, and then import each
CSV file into a single file in Excel 2003 ver (11.8117.8122) SP2.

When we have a large amount of notes for a task, the import into Excel
overflows the "Notes" cell/column, and the overflow text spews into other
cells like "Subject", "Start Date", etc. This one large task might take up 20
or 30 rows in Excel instead of 1. This make a horrible looking report!

We TRIED to export from Outlook directly into Excel, but that process only
captures about 200-300 characters of the notes in the task, so it is quite
worthless. That was why we went the CSV file route.

Questions are:

1) Excel help indicates "Length of cell contents (text)" as: "32,767
characters. Only 1,024 display in a cell; all 32,767 display in the formula
bar".
Why would a task that has 31283 characters (with spaces) overflow the cell,
if the limit is 32767? One task that has 30038 characters was handled
properly upon import.

2) Is there a way to force Excel to truncate the import so the overflow does
not occur?

3) What is the BEST way to handle large text task imports that have notes
greater than the limit?

My test cases were several tasks created with the following numbers and
letters to get the desired character count, and then saved to different tasks
with different word counts:

---
test3

1234567890 1234567890 1234567890 1234567890 1234567890 1234567890
abcdefg hijklmn opqrstuvwxyz abcdefg hijklmn opqrstuvwxyz abcdefg hijklmn
opqrstuvwxyz

 

[DaveO]

You may know that Outlook is essentially a huge database file, and as
such it readily imports to Access, and from there to Excel if you
still need to go that route.

If you'll open up a blank database file, select the Tables tab along
the left margin;from the menu click ~File ~Get External Data and
select Link or Import as you like. You'll be prompted for a file
location (on my machine my Outlook PST files are in a folder called c:
\mail but yours may be different) and also for "files of type": select
Outlook. From there you'll be prompted to specify Tasks, Mail, etc.
Tasks are located in Personal Folders.

I suspect this will solve many of the problems you've encountered,
although it may still bear some experimentation: importing is
different than linking, and may provide different results. If you
import, for instance, you can select data types for the fields you
import, and use Memo type fields for the Notes to accommodate long
text strings.

Good luck with it- please let us know how it turns out!

 

[Guest]

We are trying to keep the effort to a "minimum" as one person is trying to
keep up with 20 or more people and their tasks, and create a report.

There was an attempt to do what you suggest, but the notes are cut off the
same as just bringing the information directly in to Excel.

Thanks.

:

 

[Guest]

Have done some more testing with no success in getting the issue resolved...

Below is a test task CSV file (with the middle of the file removed). If you
were to duplicate the 50 characters per line until you reach the 30515 total
number of characters for the Notes section (based on MS Word's Word Count),
and then import the csv file into excel, it causes the task to "wrap" to the
next row. If you remove say the last character in the Notes (so the total
number of characters goes to 30514), excel correctly imports the csv file
and the task remains contained on one row.

"Subject","Start Date","Due Date","Reminder On/Off","Reminder
Date","Reminder Time","Date Completed","% Complete","Total Work","Actual
Work","Billing
Information","Categories","Companies","Contacts","Mileage","Notes","Priority","Private","Role","Schedule+ Priority","Sensitivity","Status"
"Test CSV for Excel","11/30/2006","3/19/2007","True","3/19/2007","9:00:00
AM",,"0.050","0","0",,,,,,"12345678901234567890123456789012345678901234567890
12345678901234567890123456789012345678901234567890
12345678901234567890123456789012345678901234567890
12345678901234567890123456789012345678901234567890

:
:
:

12345678901234567890123456789012345678901234567890
12345678901234567890123456789012345678901234567890
12345678901234567890123456789012345678901234567890
12345678901234567890123456789012345678901234567890
123456789012345
","Normal","False","",,"Normal","In Progress"


The above CSV file (when filled in to 30515 characters for "Notes") causes
problems when imported into Excel. It causes the "Notes" cell to overflow and
puts info for the cells following that onto the next line (or multiple lines
if you continue to add more characters).

(from MS Word of JUST the Notes text and NO delimiters or headers)

Word Count
Pages 10
Words 611
characters (no spaces) 30515
characters (with spaces) 30515
Paragraphs 611
Lines 611


The goal is to figure out how to get excel to gracefully TRUNCATE the info
from the Notes field, to say 30000 or even 25000 characters, when importing
the CSV file into excel, so the task remains all on one line. We are not
concerned about losing the "end" of the task notes. We are MORE concerned
about creating readable reports in excel.