Hi!
I have a text file over 70000 lines, some lines are long some are short.
I tried to import this text file into SQL, but all it did was to take the
shortest line as the width and truncated the long times. Though I have in
the "transform" to change the field to varchar and length to 1000, it still
truncated the long lines. Very frustrating. Any idea?

Sincerely,

Hi pelican,

"pelican" <pelican (AT) discussions (DOT) microsoft.com> schrieb im Newsbeitrag
news:B934365A-55D4-4006-9793-EC4983F10977 (AT) microsoft (DOT) com...
the provider tries to guess the needed size for its buffers, which are AFAIK
allocated in 255 byte chunks. To decide this it scans some rows depending on
a regkey with a default of 25.
When the long lines are below 25, the data would be truncated.

Solution 1: change the registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Text\MaxScanRows
and set the value to 0
This should force the provider to scan the hole file. But this would be a
general performace loss.

Solution 2: Insert a dummy row at the beginning, iwth max length values for
all columns > 255

Please let me know if this worked for you

Helge

Hello Helge,
Thank you so much. I added a dummy row at the beginning of the text file
that was the longest string in the file. And that works! First I tried
fixed length of 1000, it filled in every lines with 1000 characters, pulling
several lines on one line. Then I used deliminted by row delimiter
({CR}{LF}), which worked out fine. I really appreciate your help!!!
I used the second method you suggested since it did not require me to
touch the registry...

"Helge C. Rutz" wrote: