PDA

View Full Version : Text Becomes Decimal on Export to Excel



eed
07-08-2004, 12:27 PM
Ooookay, another little question. Maybe someone has seen this before and can explain it to me.

I have a text-type field with alphanumeric values like "16P" and "7A." ALL of them appear correctly in Access itself (table and forms).

When the table is exported to Excel, 99% of these values are fine. But some of them (right now it's 16P, but it's been a different value in the past) are converted into decimals!!

It does not do this every time you export, only sometimes, it's completely unpredictable. What on earth could be causing Excel to sometimes spontaneously interpret a text field as a decimal? <<see additional info added below!>>

Any and all thoughts are more than welcome and DEEPLY appreciated, because I don't even have guesses on this one. THANKS!!!! :help

-----> Additional Information To Add <-----

Okay, I have examined this bizarre event a little more. It's not entirely random: it is only happening on values that are #P, and by comparing the decimals produced, it definitely appears to be a constant formula of (# * P), with P having a value that rounds to about 0.0417.

If there is additional text in the cell besides #P -- even if it is just "#P and ##P" -- the text displays fine and this mysterious calculation doesn't occur.

This only happens on certain computers, meaning that whatever is going on must be a default setting somewhere in that computer's Excel load, but I don't know what or where.

tommy bak
07-08-2004, 01:28 PM
This is maybe a wild guess, but cuold it be that p is interpreted as PM and that excel then convert it to a value. You value 0.0417 is exactly 1/24 part of a day = one hour

If this happens again, try checking the Regional settings and see if PM is set to PM

BR
Tommy Bak

eed
07-08-2004, 01:34 PM
This is maybe a wild guess, but cuold it be that p is interpreted as PM and that excel then convert it to a value. You value 0.0417 is exactly 1/24 part of a day = one hour

If this happens again, try checking the Regional settings and see if PM is set to PM

BR
Tommy Bak
Are you thinking about regional settings for the Excel file? the Excel application? the computer itself? I'm not having much luck locating regional settings in Excel, but if it were something in the regional settings of the computer itself, would Excel really decide that 16P = 0.666666666666667? I'm more than willing to check out your suggestion, just tell me where I should start looking for this possible fix... thanks!!!!

tommy bak
07-08-2004, 02:00 PM
If it happens on only some computers I would have a closer look at Windows control panel. Look under Regional Settings (International, if win98 ) on the tab with date/time-format a see if it differs from one of the computers that works allright.
Then again, I'm not sure, but 0,66666 equal 16 hours in excel (24 hours = 1)
and the fact that your problem is only with P (PM) and A (AM) suggests to me that excel is considering it as a timevalue.


Tommy Bak

eed
07-09-2004, 12:56 PM
I'm not having any luck with identifying *why* Excel thinks this is a time function or how to eliminate that time function. If I could maybe approach this problem from a different angle...

Does anyone know how to explicitly identify a field as Text when exporting from Access to Excel? Right now Excel classifies everything on the sheet as data type "General." If I could specify that Access should export everything to Excel as data type "Text", I think that would prevent the misinterpretation of "16P" as a function of "16 * 0.0417".

Any ideas on that approach to the problem...? :confused:

parry
07-11-2004, 11:09 PM
Hi, this is odd. When I export a table from Access into an Excel file, my field which has a Text data type but contains numbers is exported as text (ie isnt converted to #'s). When you select the table/query then choose File|Export & choose a filetype you will see a box that has Save As Formatted. Tick this and see if it makes any difference.

If still not a goer you can choose to export to a Text file and you will have the option (under Advanced button) to choose an export spcification whereby you can control the data type of each field.

hth

mark007
07-12-2004, 01:11 AM
Format the column as text before importing. Excel will not reformat it then.

:)

eed
07-12-2004, 05:04 AM
But if I am exporting from Access to Excel (never importing in Excel from Access) and performing the output through VBA, is there a way that I can force the data type of the exported field to be "Text" instead of "General"? Once it is in Excel, it is too late to select the column and set it to Text, because the decimals are not converted back into the proper alphanumeric string.

If necessary, since the decimals have been found to follow a predictable pattern, I could program the VBA to find these decimals and replace them with the appropriate "##P" text string. However, this seems like a bandaid to cover up the problem, as opposed to a solution. I would rather understand why some of my network's Excel loads interpret "##P" as a request to perform a calculation and how to have the misinterpretation not happen at all.

kenhigg
07-12-2004, 05:20 AM
Are you using the OutputTo macro?

eed
07-12-2004, 05:57 AM
Are you using the OutputTo macro?I'm using the DoCmd.OutputTo method in VBA, like this:


DoCmd.OutputTo acReport, "rptLIS", "MicrosoftExcel(*.xls)", strExcelPath, False

mark007
07-12-2004, 08:20 AM
I don't know access too well but can you specify directly a worksheet to use in some way? If so then you can format the relevant column as text before exporting the data.

CBrine
07-12-2004, 08:43 AM
FYI,
I remember running into formating problems in the past. It seemed as though the output to command would default the excel format based on the data in the first row(ie. titles, or 1st row of data if no titles we exported). If you are not exporting the column headings, try changing it so that they do get exported. Also make sure your column headings are all alpha characters.

eed
07-12-2004, 08:56 AM
CBrine - Yes, I export my column headers along with the data, and they are all alphabet characters, not numerics. In fact, ALL my fields are defined as text in Access, but Excel doesn't pay any attention to my Access data type definitions. It defines everything as "General" no matter what.

mark007 - Normally I just create the spreadsheet during the OutputTo so that I don't have to worry about my users moving/deleting a specified sheet. But your idea still might work... I can check for a specific sheet, and if it exists, I can open it, format the column, then export... if it doesn't exist, I can create it, open it, format the column, THEN export... hey, that might do it!

I'll check it out and post whatever solution I might find. Thanks all for the insightful thoughts and suggestions!

eed
07-12-2004, 10:41 AM
Okay, that was a good idea, but no. If I format the cell data type to "Text" in Excel first, then export data from Access to Excel, I just write over the type that I set and everything becomes "General" again. Excel basically doesn't hold any formatting when it's receiving data from Access (unless possibly if you use a template, which I have been trying to avoid for this procedure but may soon have to try).

This is awful, I am so confused. This is something going on with the Excel application itself, not my document, because otherwise it would happen consistently on any machine using my document.

Is there a place in Excel to define custom functions or custom constants or something (I don't know, like the way that you can define custom Auto-Correct items in Word)?? If I could find a place like that, it would give me a place to start looking for settings to Undo.

Thank you sooo much to everyone for their continued thought and assistance...

parry
07-13-2004, 03:25 AM
Hi, try the TransferSpreadsheet method.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "table1", "C:\Text.xls", False

eed
08-09-2004, 07:54 AM
Hi, try the TransferSpreadsheet method.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "table1", "C:\Text.xls", False
Sorry it's taken me so long to come back to this post. The TransferSpreadsheet method didn't prevent the decimal change either. Since I have so far failed to find and treat the cause of this problem, I have temporarily set up a treatment for the symptom: after exporting to Excel, my database is running a procedure to locate all decimals in the problem field and replace them with the proper value (they're all predictable since they're constant). Thanks for everyone's help. I'm going to continue to consider this problem if I have some spare time in the future, but for now I suppose I have a temporary patch...

sgrant
08-23-2004, 08:45 AM
As a work around you may want to export your Access data to a prn or text file, then import it into excel. This should allow you to use the Excel import parser to specify the field types. You can also automate this using a macro if you need to.

jamescol
08-23-2004, 09:10 AM
This is a known issue for Excel. In some instances, a number followed by a P is converted to a time format. There is no fix for the issue, but a workaround is to insert an apostrophe in front of the value.

sgrant
08-23-2004, 09:36 AM
That is correct! But consider that the apostrophe would be visible in Access as part of the text, but not shown in Excel as part of the Formatting for the cell in deciding on this option.

eed
08-23-2004, 01:33 PM
This is a known issue for Excel. In some instances, a number followed by a P is converted to a time format. There is no fix for the issue, but a workaround is to insert an apostrophe in front of the value.
Jamescol, thank you for mentioning this -- I couldn't believe I would be the only person who ever experienced this problem. Now I know I'm not crazy.

sgrant -- thanks for the suggestions. For my purposes, the apostrophe in Access is not a convenient solution. I will try writing a macro to export through a prn or txt file and parse the field types. Hopefully that will solve my problem.

Thank you both very much!

eed
09-03-2004, 06:49 AM
Okay, exporting through a text file seemed to work great -- at first. But then I realized it was truncating all of my fields, but not at a particular number of characters... Each field only has around 46 or 50 characters in it once I pull the text file data into Excel.

Could this have something to do with the way that I am defining the fields when I'm asking Excel to import the text file into a workbook? Would it help if I posted a zip file including my Access table, my text file, and my code?

I was really hoping I could find a way to get my data into Excel while getting rid of the decimal bug -- and preferably not truncating the heck out of my data! The truncation is the only problem I found with exporting through a text file... otherwise, it was beautiful, and I had total control over formatting the data once I got it into Excel. Any ideas would be great. Thanks so much. I know this topic has dragged on for a long time, but I appreciate all the great help!!!

Anne Troy
09-03-2004, 09:08 AM
Uploading files ALWAYS helps. It keeps coders from having to create sample files. When they can spend less time, they're more likely to help, and they can help more people in less time. :)

Paleo
01-11-2005, 06:25 PM
eed,

try to import data to excel from access, because in this case you may define the data type. For example when I import data from a text file I use:


.TextFileColumnDataTypes = Array(1, 2, 4, 9)


Where 1 sets to data to 'General', 2 to 'Text', 4 to 'Date' and 9 to 'Not to Import'. I think this might help you out.

ALaRiva
01-11-2005, 07:18 PM
eed,
Don't know if you are still looking for a solution, but I have a module that I created where you can pass in a SQL Statement, and then be prompted with a Save Dialog and you can choose where to save the Excel File.

In the module I get around the issue that you have been having. It's something I had put together a long time ago, but have recently refined it as I have a client who wanted all reports in Excel Format.

Let me know.

- Anthony

dhutch75
03-05-2009, 04:14 PM
I would like to re-open this discussion. I'm having the same problem, but doubt that this issue has anything to do with date formatting. I'm using the following VBA:


stDataFileName = "C:\CSTS\JobData.xls"
DoCmd.TransferSpreadsheet acExport, , "Sheet1", stDataFileName


The export process works, but I'm also having issues with a decimal point that appears in the output data.

I'm exporting query data from DataBase1 to Excel. The .xls file is used as input to DataBase2. Column heading in the spreadsheet are mandated to me by the fieldnames in DataBase2. The first column should be named Serial # . When I export to .xls, the column heading is corrupted to 'Serial . which will not be acceptable. (Note the leading apostrophe that designates the data as text in the cell and the decimal point that has replaced the # symbol.)

There is no letter 'P' anywhere near my '#' symbol, so the discussion about date formats might be totally off. The whole file never has more than a single '#' in it. Does this offer any new insight to the issue that was never resolved in '04?

The whole purpose of this little module is to eliminate the need for user intervention in the output file. Opening the file and editing the cell is not really a solution in this instance.

Deborah