I have a problem reading from excel file source in DI. 3.1 version. The MAX string length for strings automatically is assigned to 255 characters. This means strings greater than 255 characters cannot be read into DI excel file format in DI. The string is truncated.
Is there a fix for this? How about 3.2? Is this corrected?
Any help is appreciated.
jgopalan 
(BOB member since 2008-02-25)
I’ve just tested this with Data Services 12.2.2 (XI 3.2 with Service Pack 2 applied).
Using Toad, I took one of our long text description tables (which contains converted CLOB to VARCHAR descriptions), I selected all item descriptions longer than 255 characters and exported the end result (incl. some key columns and varchar attribute columns) to an Excel sheet. (XLS in Excel 2000/2003 format).
I then created a new Excel File Format based on this Excel file in our DS Development area. When retrieving the metadata, DS actually listed this column as VARCHAR with lenght 0 and defaulted all other character columns to VARCHAR 255.
I changed the definition of the description column from VARCHAR (0) in the Excel File Format definition to VARHCAR 4000 which is the max Varchar length in Oracle 10g.
I then created a data flow, using this Excel File Format as source, with a Query transform and a new template table in the middle.
(Excel -> QRY -> Template Table).
There were no errors or warnings running this job and all 5076 rows from the Excel sheet were correctly imported.
I then ran this SQL to group and count records by description length to validate that none of the content had been cut-off at 255 characters:
select length(step_desc_char), count(1) from test_excel
group by length(step_desc_char)
order by 1
LENGTH(STEP_DESC_CHAR) COUNT(1)
256 11
257 19
258 15
259 12
260 18
261 25
262 15
263 11
264 11
265 15
266 8
267 35
268 15
269 16
270 6
271 5
272 10
273 17
274 3
275 8
276 7
277 21
278 6
279 30
280 11
281 8
282 8
283 8
284 8
285 14
286 11
287 11
288 7
289 15
290 7
291 12
292 12
293 6
294 66
295 7
296 7
297 6
298 11
299 25
300 7
301 7
302 7
303 12
304 6
305 10
306 27
307 5
308 21
309 6
310 3
311 3
313 6
314 1
315 4
316 32
317 4
318 11
319 5
320 4
321 12
322 5
323 10
324 43
325 2
326 16
327 9
328 6
329 8
330 6
331 6
332 22
333 7
334 16
335 12
336 8
337 20
338 6
339 4
340 1
341 3
342 10
343 10
344 6
345 16
346 16
347 3
348 4
349 2
350 5
351 10
352 8
353 3
354 9
355 3
356 8
357 8
358 2
359 1
360 14
361 12
362 3
363 5
364 23
365 3
366 16
367 14
368 6
370 3
371 17
372 12
373 6
374 14
375 15
376 1
377 2
378 18
379 3
380 1
381 4
382 8
383 5
384 7
385 3
386 10
388 1
389 6
390 5
391 2
392 1
393 3
394 5
395 4
396 3
397 1
398 12
399 6
400 1
401 2
402 4
403 2
404 29
405 9
407 24
408 63
409 7
410 2
411 3
412 22
413 13
414 6
415 2
416 3
417 2
418 4
419 8
420 18
421 3
422 20
423 1
424 2
425 3
426 2
427 5
428 1
429 7
430 3
431 16
432 7
433 3
434 4
435 6
436 4
437 9
438 3
439 15
440 2
441 23
442 2
443 4
444 7
445 7
446 6
447 160
448 3
449 26
450 1
452 4
453 2
454 1
455 32
456 4
457 7
458 3
459 17
460 27
461 1
462 1
463 1
464 3
465 1
467 1
468 2
469 1
473 1
474 2
475 2
477 6
478 6
480 1
481 3
482 3
483 14
484 2
485 6
486 1
487 1
488 2
490 1
491 1
492 7
493 1
494 1
497 3
498 2
499 12
500 6
501 2
503 13
504 1
505 12
506 2
507 1
508 1
509 3
510 1
511 6
512 1
513 10
516 4
518 1
519 1
520 1
521 1
522 2
523 9
526 1
527 1
528 1
529 2
530 6
531 3
532 1
534 8
535 3
536 5
537 1
538 2
539 1
540 12
541 1
544 1
545 1
547 1
549 2
551 1
552 4
553 3
554 5
556 12
558 4
559 1
560 1
561 1
562 1
564 10
567 2
569 1
571 8
572 1
573 2
575 1
576 1
578 1
579 12
580 1
582 13
583 3
585 6
587 5
588 2
589 1
591 2
592 1
593 6
594 2
595 2
596 2
597 3
599 1
600 1
601 1
602 4
603 1
605 10
606 1
614 16
615 2
616 1
619 11
620 1
622 4
624 2
625 6
628 1
629 2
632 1
633 4
634 1
635 15
636 1
639 1
640 2
641 4
644 3
646 1
650 2
657 2
663 2
666 4
667 4
668 2
670 3
672 1
675 1
676 5
677 4
680 2
681 1
682 6
684 2
687 8
688 57
689 1
694 6
696 1
697 2
700 2
701 2
702 27
705 1
706 1
707 1
708 1
716 1
717 3
719 1
720 1
724 1
725 3
727 1
735 6
737 6
739 1
740 8
742 1
756 1
764 1
769 7
774 6
785 3
786 2
787 2
790 1
791 1
792 1
797 1
798 2
803 5
805 1
809 1
813 1
814 52
819 1
824 2
833 1
834 1
837 1
838 16
839 31
842 10
850 1
853 1
855 1
857 1
858 11
859 2
861 1
862 27
864 1
875 1
876 5
878 2
881 3
887 1
889 2
894 3
897 16
899 1
903 11
904 1
911 4
915 2
916 6
917 1
920 2
922 1
926 1
927 1
929 2
932 29
936 2
940 1
941 3
942 1
950 2
963 1
965 2
968 1
976 1
992 1
996 1
997 1
999 11
1002 3
1003 1
1007 5
1008 2
1009 8
1010 17
1013 22
1014 1
1015 428
1023 1
1025 1
1026 1
1028 1
1029 1
1036 2
1039 1
1049 1
1069 93
1071 1
1072 14
1077 1
1079 22
1089 1
1090 1
1091 1
1092 1
1093 5
1098 4
1102 7
1106 2
1110 1
1116 5
1127 3
1130 4
1135 1
1138 3
1139 2
1145 1
1146 7
1150 1
1152 19
1156 2
1158 31
1164 1
1177 2
1179 2
1180 6
1182 1
1185 19
1192 1
1193 12
1195 1
1202 3
1206 6
1211 19
1222 1
1241 2
1248 1
1250 23
1251 26
1257 20
1258 1
1264 3
1265 2
1270 1
1271 1
1275 1
1277 1
1280 8
1282 1
1293 2
1294 1
1297 2
1306 7
1312 1
1313 1
1316 1
1318 40
1322 7
1324 1
1327 1
1331 1
1345 1
1346 163
1348 1
1349 1
1350 1
1351 43
1353 17
1362 1
1364 1
1366 1
1373 2
1374 294
1376 51
1391 1
1392 2
1393 1
1396 51
1398 1
1405 1
1416 1
1417 1
1420 1
1428 2
1431 1
1432 1
1434 4
1438 1
1439 1
1442 1
1443 1
1445 2
1446 7
1448 1
1450 1
1454 3
1469 1
1478 1
1479 1
1480 1
1496 1
1504 1
1507 1
1514 1
1516 1
1520 1
1528 2
1535 4
1543 36
1546 4
1547 1
1548 1
1556 1
1570 1
1574 1
1580 12
1585 15
1588 1
1594 2
1608 1
1634 1
1638 13
1639 23
1641 5
1648 1
1653 2
1655 25
1692 1
1701 1
1727 1
1739 4
1767 1
1782 1
1795 1
1798 1
1803 4
1804 3
1805 16
1819 5
1824 4
1829 5
1831 1
1857 1
1870 1
1904 3
1911 117
1915 1
1923 5
1927 1
1932 1
1982 1
2001 2
2015 8
2022 1
2025 1
2030 9
2067 1
2111 8
2178 1
2205 3
2222 1
2228 3
2356 4
2363 6
2412 2
2435 10
2455 1
2485 7
2502 3
2694 1
2763 2
2852 1
2931 2
3206 1
3362 10
3481 1
3666 1
3981 3
ErikR 
(BOB member since 2007-01-10)
Recently we had the same problem, but I think I found the clue for this. In the Technical Manual I you can read that if the data integrator can not determinate the type of coloumn it will be set to varchar255. And the another piece of information is that the DI detect the type of the date only on the first 100, or I do not remember exactly on how much column…
So you have to revision the format of the excel, and do not let it solely to the BODI.
Medan (BOB member since 2010-05-31)
Does anyone know if there is a way around this issue?
Changing the formatting in Excel for the column or entire sheet to “Text” doesn’t resolve the issues. I think that’s what Medan was suggesting.
The column type is defined in the BODS XLS template object as VARCHAR 1000.
FreeZey (BOB member since 2007-12-07)
Have you tried to update the VARCHAR lengths manually as per my post from around 7 years ago?
This worked fine for me.
ErikR 
(BOB member since 2007-01-10)
Yes. The spreadsheet file template has been manually created with VARCHAR columns of 1000 and regardless of this configuration it still crops all text strings being loaded to 255.
FreeZey (BOB member since 2007-12-07)
What version of DS are you using? What OS and what Excel ODBC drivers are you using when not using Windows Server?
ErikR (BOB member since 2007-01-10)
[list]
[:87a02f8401]SAP Data Servers version 14.0 (also called 4.0 by some)
[:87a02f8401]Windows Server 2008 R2
[*:87a02f8401]Client and servers are both on the Windows platform.
[/list]
I also tried experimenting with the same file saved as:
[list]
[:87a02f8401]97-2003 xls
[:87a02f8401]2010 xlsx
[/list]
…but it happens with both.
FreeZey (BOB member since 2007-12-07)
I had the issue. There is a simple fix.
Ensure that column in the first row has value (that is more than 255 chars) and run the job.
I think DI reads all columns in the first row to identify whether the columns needs data length greater than 255.
DI should read that sheet perfectly.
bugsbunny (BOB member since 2017-03-23)
Could you make a test file available, so I can have a look and see if I can nor cannot replicate the problem with our own DS environment?
DS 4.0 has been out of mainstream support for a few years now. The most current version is DS 4.2 SP08 so you’re 2 minor product releases (4.1.0 and 4.2.0) and 6 Service Packs behind. (DS 4.2 SP02 was the GA release version).
It could well be that this issue was resolved somewhere in between these many releases? If you have a file available then I can see if the problem actually exists in DS 4.2 SP08. If I can’t replicate the problem, an upgrade may be overdue.
Note that if your company has been paying continuous maintenance fees to SAP, you are always eligible to download & upgrade to the latest version of DS.
ErikR (BOB member since 2007-01-10)
I’m now seeing this issue on DS4.2sp8.1 and sp9.1. I’ve tried all the suggestions on this thread to no avail. So I’ve raised a ticket with SAP to see if they can shed any light on this!
Nemesis 
(BOB member since 2004-06-09)
Has this been fixed?
I am having the same issue where it truncates the data to 255 characters when using Excel source no matter what I set the column to in the FF. The only time it runs correctly is if I put the biggest row in the first row of data.
tibs (BOB member since 2018-12-19)
No. I raised a helpdesk call but was told that this is a known issue with Excel and there is nothing that SAP can do.
Which was nice.
Nemesis 
(BOB member since 2004-06-09)