Question Data truncating after 255 characters while inserting into oracle from Excel

[sakthi_velan]

Hi,

I am trying to insert the data from Excel file to oracle database using ADO.NET. Below is the procedure adopted to upload the data

1. First all excel data are loaded into dataset

2. Using stored procedure, data are inserted into oracle tables from dataset and while inserting a field,which contains hyperlink (and more than 255 characters) does inserting only to 255 characters.

3. But during debug mode it inserts all the characters into oracle table, but during release mode it inserts only 255 characters. (I couldn't find where it is truncating)

In our source code there is no code for truncating.

Please help me to over come this problem

Thanks in advance

Regards
Sakthi

 

[keb1965]

What type of field are you trying to put the data into. There is an issue with text fields and a 255 char limit. You may look at changing the field type to memo.

 

[sakthi_velan]

Data Truncating after 255 characters while inserting into oracle from Excel 2003

Actually we are using Excel 2003 and the default data type of the field is general (value with the hyperlink).

As per your comments, i searched the memo datatype in excel 2003. But I couldn't find that one.

I got the same problem when i tried the same issue with the text data type.

Kindly help me to read the values from excel (2003) if it exceeds the character limit 255.

Thanks & Regards,
S. Sakthivel

 

[keb1965]

from your post, I thought you were reading from excel and putting the data into an Oracle database, if that is the case check the database field type

 

[sakthi_velan]

the database field size is 500. I verified the source also. We are not truncating data anywhere.

Some times i can see while inserting the recrods from excel using debugging, it is inserting into database properly (even more than 255 characters).

But it is not inserting if it is in release mode (after deploying)

Kindly help me.

 

[keb1965]

I don't know what to suggest.

The database is probably where your truncation is coming from, have you tried changing it to a memo field rather than text?

 

[Tom]

Check the field sizes in the stored procedure parameter itself. Both in the SP in the database and also in your coding to call the SP.

 

[cjard]

are you doing this as a 2 part staging?

Read from Excel into DataTABLE
Upload from DataTable into Oracle

If so, where does the data truncate?


-

In your program, are you using the jet ole db driver? If so, is there a difference in the schema.ini file being usede between debug and live?

 

[sakthi_velan]

Hi All,

I verified (printed) the data immediately after reading it from Excel 2003 using JET OLEDB driver. It is showing only first 255 characters.

Note: I am not using any INI file for connectivity configuration. We are directly copying the Excel file in application folder and reading it from the application folder.

Also I couldn't find the data format type memo in Excel 2003.

I think the problem is in EXCEL side. i.e. Excel is not allowing to read its value if it is more than 255 characters.

Could you please suggest me how to read the values from Excel 2003 if the value has more than 255 characters?

FYI, I verified the field size in database stored procedure as well source. It is showing the size as 500. So, I am clearly saying that there is no size problem in the source and DB. And I assure that the problem from Excel 2003.

Please help me how to resolve it.

Thanks & Regards,
S. Sakthivel

 

[Tom]

Can you give more detail of what method you are using to read the Excel sheet and what it is being stored in, in the app before being transfered to the database?

 

[sakthi_velan]

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & filePath & fileName & ";" & _
"Extended Properties=Excel 8.0;"

Dim myDataset As New DataSet()
''You must use the $ after the object you reference in the spreadsheet
Dim myData As New OleDbDataAdapter(SQL.ToString() & " FROM [Sheet1]", strConn)
myData.TableMappings.Add("Table", "AttrID_Template")
myData.Fill(myDataset)
myData.Dispose()

For Each dtRow As DataRow In myDataset.Tables(0).Rows
strValue=dtRow(1).ToString()
Next

Response.Write(strValue) ' It is printing only 255 characters even if it is having more than on that.

Please help me how to get the full data from Excel 2003

 

[Tom]

Using the same type of connection as you, I imported an Excel file into a dataset. The cell/field value that I have that has 300 characters is stored in my untyped dataset field and displayed in my datagridview.

If you run your app without the table mapping does the value get truncated?

 

[sakthi_velan]

While executing the source in developer PC, the Excel file import is working properly and it inserted the value even more than 255 characters. If I deployed (copied) the same source in application server, it is truncating the value as 255 character.

I noticed that in my application server, we don't have MS-Office sofware.

Could you please confirm me whether this as a problem? (i.e. Should MS-Office needs to be install in application server or not?)