Test string looks strange

I have a field in a table that is a text field.   But when I run a query using the table as a record source the following strange thing happens.

For example, 0101210E103 looks like this in the query result:

1.01E+108

What is going on?
SteveL13Asked:
Who is Participating?
 
PatHartmanCommented:
OMG Excel has completely hosed this process.  Access correctly exports text numeric stringe enclosed in quotes per the .csv standard and Excel, because it is smarter than everyone else, overrides that and assigns a numeric data type.

That means that THERE IS NO GOOD WAY TO OPEN A CSV FILE USING EXCEL AND STILL MAINTAIN THE ORIGINAL DATA TYPES.

I did find that if you opened Excel and then used the Data tab to import from Excel, you get a dialog but the only way to get it to import the long numbers correctly is to change the Data Type Detection to "do not detect".  That of course hoses all the other data types if you had currency and date data requiring you to add them back in.

I believe this is a BUG in Excel and should be reported to Microsoft.  Access exports the data correctly as you can see when you open the .csv file with notepad.  Both my text fields are enclosed in double quotes and that is supposed to define them as text.CSVtoEXCEL.JPG
0
 
SteveL13Author Commented:
I just noticed something.  When I just view the query results it looks fine.  But when I do this vis VBA code it doesn't:

DoCmd.TransferText acExportDelim, , "qryPRODUCTS_PartsMasters", strCSV & Environ("userProfile") & "\Desktop\" & "Product Master " & Format(Now, "mm-dd-yyyy_hh-nn") & ".csv", True
0
 
PatHartmanCommented:
The problem is that you are probably just double clicking on the .csv file to open it and the default viewer is Excel.  Excel is not as smart as it thinks it is.  It takes long numeric STRINGS and assumes they are numbers and then converts them to scientific notation because actual numbers cannot be that long.  Other bad things happen when you have numeric strings that start with leading zeros.  Again, Excel assumes the string is a number and so removes the leading zeros.

To correctly open these files using Excel, you need to open Excel and go to the Data tab and from there import the .csv file.  You will get a dialog similar to what you see in Access where you can specify that these "numeric" fields are actually text and then Excel will display them correctly.

To solve the problem with your export, you need to convince Excel that the long numbers are actually text.  That requires exporting the file ONCE manually so you can create an Export spec.  In the Export spec, you specifically define these fields as text.  That will cause Access to surround them with quotes and that should be enough to convince Excel that they are text.

Right click on the query you want to export.  Go through the dialog and properly define each column.  On the last page of the dialog, press the Advanced button.  Make sure that you choose  quotes to surround text fields and coma separation.  Save the spec with a short name.  Then in the TransferSpreadsheet, reference the spec name to reuse your definition.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
SteveL13Author Commented:
Pat, I must be missing something.  If I right click on the query I don't see the dialog.  ??
0
 
SteveL13Author Commented:
Correction.  I can go through the dialog but I never get to where I can surround text fields with quotes.
0
 
SteveL13Author Commented:
You should win an award for discovering this!!!!  Thank you.
0
 
Ryan ChongCommented:
you should open the csv in Excel using the From Text feature under Data > Get External Data

if necessary, format the type of your field when necessary.

SnapShot.png
Excel will assign a default data type based on the values of that column.
1
 
PatHartmanCommented:
Ryan,
That's the dialog I expected to see.  It's gone!!!!!
CSVtoExcel2.JPGChoosing the  From Text/CSV option, you get the dialog I posted.  Choosing the GetData option and then From File, and then From Text/CSV, you get the same dialog I posted.

I'm using O365 with Office 2016  Version 1709 (build 8528.2139 click-to-run) Monthly channel

The dialog you posted is similar to what you see from Access with the exception that you don't have an option to save your spec which of course means that you have to set the spec EVERY SINGLE TIME you open a file.
0
 
PatHartmanCommented:
I also just noticed something else.  Using the "do not detect" option makes the header rows a data field!!!!!!!!!!!!!!!!!!!!!!!!!!

I just reported this to MS using the Excel back stage option  -  File/Feedback  So PLEASE, if any of you who read this think this is a much of a problem as I do, let MS know.  They will never fix this problem if they don't hear from us.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.