Excel – 255 Max character size in Excel cell when cell data is linked/fetch from external data source

microsoft excelsql

I have one bug tracking system which export bug data as Excel file. I would like to create a report based on this exported excel file. what I did is

  1. create a office data connection file which link to the export excel file.

DBQ=\myfileserver\01_Team_Weekly\Raw\Raw.xlsx;DefaultDir=\myfileserver\01_Team_Weekly\Raw\;Driver={Microsoft
Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;FIL=excel
12.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes; SELECT * FROM
\\myfileserver\01_Team_Weekly\Raw\Raw.xlsx.general_report$

  1. then I import the external data via Excel data connection feature.

The problem here is "I found the imported data has 255 characters limitation in single cell."

Is there a way to modify the max 255 character limitation? I am not sure the issue is caused by "Office data connection" feature or Excel itself.

thank for help first.

Best Answer

The root cause of this issue is the field type of csv file. The Jet 4.0LEDB Source will guess the csv field datatype with first 8 rows. So if the first 8 rows are all data less then 255 characters, the corresponding column will be treated as String – Unicode string, length 255 (DT_WSTR) (http://msdn.microsoft.com/en-us/library/ms141683.aspx).

Workaround: 1. put some dummy data in the first row with data length over 255. or 2. Update register ==> http://support.microsoft.com/kb/281517

btw, isn't there a way to define the data type of csv fields?

Related Question