Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
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

2 Answers 2

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?

share|improve this answer

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

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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