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.

  • 1
    Correct me if I'm wrong, but is it really necessary to put all of that data into one cell? – zeldarulez Sep 18 '13 at 2:56
  • Also, specificying which columns you want from your select statement might help you eliminate some bytes.. – zundarz Sep 18 '13 at 20:13
  • I exported the data from JIRA(a bug tracking system). Of course, I don't need all of those fields in JIRA. However, I did need some Free Text Block data which is a unlimited text data. Like Status Whiteboard, Description and some personal customized fields. – user255184 Sep 24 '13 at 1:16
  • I think my issue is similar to this support.microsoft.com/kb/281517/en-us – user255184 Sep 24 '13 at 1:31

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?

  • I investigated workaround option 2 shown above and if I read it right, it just gives the option of increasing the number of rows that the engine looks at before deciding which data type to use. In my case the data didn't start getting larger until much later in the sheet. I chose to just add an extended explantion in the two fields I was having problems with in cell A2 and it solved my problem. Excellent suggestion. – phipywr Mar 4 '16 at 16:42

Export the file as CSV, then open it as Excel for reporting.

  • yes. Manually export as CSV did work. The goal of my ideal report system is automatic report generation and refresh every few hours. So the CSV as datasource way is the only workable solution I can complete in short time. I don't plan to use real SQL data. – user255184 Sep 24 '13 at 1:18
  • @user255184 I am glad this is working for you. If you are not still looking for a more complex answer, you can check the accepted answer button and others will know you are no longer looking for an answer. – James Jenkins Sep 24 '13 at 11:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.