Reading Excel file - text cut-off

[tsinvr]

I have a utility that needs to read an Excel data file. I have the system working pretty well except that it will not read long notes. Any note that is longer than 255 characters gets cut-off.

I am using VB6 and using an oledb connection to Excel:
cnnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sDataSource & ";" & _
"Extended Properties=""Excel 8.0;IMEX=1;"""

Is this just a limitation with reading Excel files?

Thanks.

Ted

 

[Dashley]

Ted,

I dont know if this will help but I might as well ask.
Are you prulling the "note" into a string data type.

A variable-length string can contain up to approximately 2 billion (2^31) characters.

A fixed-length string can contain 1 to approximately 64K (2^16) characters.

So If you pulling it into a string data typeI dont think
You should be having this problem. Excell may be storing unseen cariage returns or line feeds in the data. I've seen this happen in databases' especially when the data entry folks use a carrage return or auto text wraps. You might want to try a replace function and repalce and vbcrlf or vblf.

(Replace(mystr, vbcrlf,"")

This might cure it for you. Hope this helps


Dan

 

[AndyWatt]

I had a similar problem a few years ago - Excel (at least Excel 97) truncates memo fields, despite claiming to support longer strings.

Andy
--
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux

 

[tsinvr]

Thanks Dan - it turned out that was not the exact problem I was having but you got me thinking in the right direction. I started to scan through the notes field that I was reading in from Excel and it was pulling in hidden fields - some nulls and other lower ascii characters that I did not see in Excel but were giving me trouble in posting. So now I have a function that parses out these unwanted characters and now the same spreadsheet that was giving me trouble in my SQL import is now working properly.

Ted

 

[Dashley]

Ted,

Glad you got it fixed. I have a replace function I should have sent you. It clears out all of the junk.

Take care

dan