Post reply

Issues when outputting to excel

  • September 22, 2003 at 1:01 am

    #76056

    Hi all,

    Currently in the process of elimination with an issue with converting data that comes from SQL server via Access into a new xls file.

    The best way to describe it is that on occassion some text gets "skewed" or jumps into the wrong column. I think it might be what im doing in code, but the only thing is that extensive testing by support dept has not been able to replicate the issue with the same data. however they are running a few different vers of Access at different times. It seems to only happen at our client sites.

    Now for my question. Is there any issues with the text converters etc which may cause unexplained jumping of fields into the following field. I dont believe there is but i just need to rule the possibility out. Hoping that everyone can say they have not heard of this before


    ------------------------------
    Life is far too important to be taken seriously

  • September 22, 2003 at 8:08 am

    #475269

    I think that when exporting to Excel it uses a default export template (or you can use an custom one that you setup in Access). So my guess is that there is something in your data that the export template is reading as a delimiter, thus moving your data over an extra column. I would check for tab characters in particular.

  • September 25, 2003 at 9:15 am

    #475833

    I agree that there is most probably a delimiter within the text being exported. If you are exporting Access text to Excel then you would probably need to create an Access function to replace or handle special values; comma, tab, etc.

    This would replace a single comma with a double comma that would not be seen as a delimiter. Then call it within the Access query to purify the text.

    Public ReplaceCommas(strText as String) As String

    ReplaceCommas = Replace(strText, Chr(34), Chr(34) & Chr(34))

    End Function

    Steve King

  • October 19, 2003 at 11:10 pm

    #478739

    Hi people, thanks for those reponses to my query.. They sounded good and i did try them however they dont solve the problem. I will try to rephrase to make it clearer.

    The text that is output is fine for a few rows and than all of a sudden jumps a column to the right (typically). the silly thing is that when outputting the same data with the same programon a different machine on the network, everything comes out ok. So using the same program, with the same backend but a different computer causes an issue, but for others it is ok.. I thought it might be drivers but it isnt as our office has almost every combination of driver out there on our PC's, as well as the smae drivers as those people who are getting the problem, but to no avail. if it is not driver related and it is not data related what else could it be ?


    ------------------------------
    Life is far too important to be taken seriously

  • October 27, 2003 at 4:17 am

    #479634

    Have you checked all delimiters? Pipe, tab, etc? What about export to a csv, does that always work?

    Andy

    http://qa.sqlservercentral.com/columnists/awarren/

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

  • October 28, 2003 at 4:12 pm

    #479994

    Hi Andy,

    Yes that has been checked, Ithought it could be versioning from ODBC drivers but it is not the case.

    Remember i said that the same program with the same data on a different computer works fine... All references are fine on both computers. I believe them to be identical in almost everyway. It is just a constantly happening error on a computer. i use the following command to do this

    "DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strTable, strFile, chkShowHead" (obviously without the quotes and the last 3 are definately populated.) A thought occurs: We seem to be having issues with certain access 2000 machines but above we are using Excel 97 flag for output, would this have any bearing on the error...?


    ------------------------------
    Life is far too important to be taken seriously

  • December 15, 2003 at 11:00 pm

    #486308

    Hi all,

    just thought i would offer the solution to my problem that i have been able to solve. hopefully no other poor soul will get this brain bending error.

    The solution is to change/upgrade the sql server driver..

    After i upgraded the system to MDAC 2.8 from microsoft. the problem *magically* went away. HTH someone in the future !


    ------------------------------
    Life is far too important to be taken seriously

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply