Fields formatted as text display in datasheet view as Exponential - How to avoid Exponential display

I spent all day researching.  What follows are excerpts from that task.

Tomorrow I'll check out all of what follows and then post my findings.

!!!!!!! NOTE: ActiveCell.PrefixCharacter = "'" will return "True" if an apostrophe in seen in formula bar.

If an Excel cell has ever held a beginning apostrophe (Control-apostrophe) and then a number plus

Alpha is entered then the apostrophe will reappear if the number plus Alpha is re-entered

and can not be removed even with VBA or backspace as long as the cell contains an alpha. (See comments below for reasons)

http://excel.tips.net/T003332_Searching_for_Leading_Apostrophes.html

It is a misnomer to refer to the apostrophe as a "leading character" or mentioning that it is in

the "leftmost position" of a cell. Even though you may be able to look at the Formula bar and see

the apostrophe at the beginning of the formula, that apostrophe is not really a part of the cell's

contents; that is why you can't use Find and Replace to find and replace it. [or Instr()]

The apostrophe is actually considered a "prefix character" for a cell. The possible values of the

prefix character are set by the Transition Navigation Keys setting in Excel, and the value of the

setting is saved on a workbook-by-workbook basis. You can change this setting by using the

Transition tab of the Options dialog box.

If the setting is cleared (the default condition for the setting), then the value of the prefix

character for each cell can either be blank or an apostrophe. If the cell contains text, then the

setting of the prefix character doesn't really matter much. If the cell contents are not text,

then setting the prefix character to an apostrophe forces Excel to treat the cell contents as if

they are text. So, for instance, the number 123 is treated as text—not a number—and shows up in

the Formula bar as '123.

If the Transition Navigation Keys setting is selected (the check box has a check mark in it), then

the value of the prefix character for each cell can have one of five different values. These values

are consistent with the prefixes used in Lotus 1-2-3 and are, oddly enough, supported in Excel only

as a transitional aid to the regular usage in the program. The possible values are an apostrophe

(left-justified), quote mark (right-justified), carat (centered), back slash (repeated), or blank

(non-text item).

Now, back to Richard's original question: how to search and get rid of that leading apostrophe. You

can't use Find and Replace to do the editing because the apostrophe isn't really part of the cell

contents. So, you must do the changing in a macro. The changing is relatively easy. First, you'll

want to make sure that the workbook has the Transition Navigation Keys setting cleared. Why? Because

you probably don't want to mess up the prefix character for the cells if the workbook could be used

at some future point with Lotus 1-2-3 again. You make sure that the setting is correct, in your macro,

with the following line:

  Application.TransitionNavigKeys = False

If you would rather not use a macro to get rid of the apostrophe prefix characters, then you can take

advantage of a strange little quirk of Paste Special. Follow these general steps:

1.Select a blank cell and copy it to the Clipboard (use Ctrl+C).

2.Select the range of cells from which you want to remove the prefix character.

3.Display the Paste Special dialog box.

4.Click the Add radio button.

5.Click OK.                      Comment: The reason this works is because it changes the cell format!

Great comment on this issue:

http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/6d7c0645-3141-4c7c-ac08-b09feae74ea8/

With Access 2003, I get a control apostrophe when I export.  With 2010, no such luck.  So I think you're

right in saying that if you upgrade to 2010 you won't have the control apostrophe in the exported file. 

As I mentioned above, I didn't see any difference between the runtime and the retail. 

That's what I planned to bring to the attention of our developers.  I don't know what the *intended*

behavior is here, but the fact that we see different results between versions is something that should

certainly be investigated further. 

Approaches to force Access to import as Text

NOTE: If a blank row is used to force Access to consider as text then

      be sure to delete that row during the Read/Write import process

http://www.mrexcel.com/forum/microsoft-access/556080-cannot-change-data-type-when-importing-excel-file.html

Cannot change Data Type when importing excel file

When importing into Access from .xls, the Data Type field is greyed out and it won't let me change it

to text! I've tried saving it in .csv format, but that doesn't work either (the columns don't line up,

unfortunately).

The CSV-file import is best, because you can change the data type and much more.

An approach is to insert a blank row in your excel spreadsheet just below the header row. In cell A2,

put a space and save your file. Now import the file and it should import column A as text.

////////////////////////////////////////////////////////////////////////

Insert a new row2, and put some random text at the top of the column - you will get a spurious row

imported, but access will treat the columns as text now

-or-

save it as a csv, then try again - you get more control over csv imports

////////////////////////////////////////////////////////////////////////

http://jamelcato.com/68/microsoft-access-import-errors/

Open your source file in a text editor (or its native environment), find the column causing the error

and then place a single quote in front of the first numeric value in that column. This will force MS

Access to view the entire column as text, even if it contains some numeric values. If you need to

perform numeric calculations on that column once it’s successfully imported into Access, just open the

table in Design View and manually change the data type back to a numeric type.

If you import Excel files into Access on a regular basis, it would behoove you to change the value of

the following Windows Registry setting to 0 (zero), which will permanently force Access to guess each

Column’s data type based on all of its values in instead of just the first 8 or 10

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]

"TypeGuessRows"=dword:00000000

If you’re using the DoCmd.TextTransfer method to import a CSV file and Access is skipping or rounding

your values after the import, then try adding this line just above the line where you call TextTransfer:

[YourSourceFile.txt].[YourColumn].numberformat = "@"

http://support.microsoft.com/kb/119472

How to Use an Import/Export Specification in Another Database

Where is the import file specification file stored?

I built an import file spec a while ago but I can't seem to find where/how it can be edited. I have to

show this to a co-worker and I forgot how I set it up.

 SELECT MSysIMEXSpecs.*

 FROM MSysIMEXSpecs;

To get to file Specifications:

To change either an import or Export, you must choose:

1. File > Get External Data > Import
2. File Type 'Text File'
3. Must pick a sample import file (Even if you desire to modify an Export Spec
4. Press 'IMPORT'

 After you have data on screen

5. Select Advanced Button on Bottom Left
6. Then Pick 'Specs'
7. Pick the Spec you want
8. Make Changes
9. Save As and overwrite

 Then, if all you wanted to do was modify an existing spec, just exit the import

10 ) Exit/Cance/Quit

////////////////////////////////////////////////////////////////////////

SpecificationName  Optional Variant. A string expression that's the name of an import or export

specification you've created and saved in the current database. For a fixed-width text file, you must

either specify an argument or use a schema.ini file, which must be stored in the same folder as the

imported, linked, or exported text file.

To create a schema file, you can use the text import/export wizard to create the file. For delimited

text files and Microsoft Word mail merge data files, you can leave this argument blank to select the

default import/export specifications.

////////////////////////////////////////////////////////////////////////

An import/export specification contains information, such as file format, date order, or number formats,

that Microsoft Access uses to import or export a fixed-width or delimited text file. An import/export

specification is stored with the default name: Filename_ImportSpec or Filename_ExportSpec in the

database that you import to or export from.

You create an import/export specification by using either the Import Text Wizard or the Export Text

Wizard. Use an import/export specification when you want to repeatedly import to the same table, export to the same file, or automate the import or export process.

Alternatively, you can use a Schema.ini file in a Microsoft Visual Basic program to provide even more

control over data in the text file, such as specifying special currency formats or handling floating

point data types. A schema.ini file is a text file containing entries that override default text driver

settings in the Microsoft Windows registry. You store a schema.ini in the same folder as the imported or

exported text file, and it must always be named schema.ini.

////////////////////////////////////////////////////////////////////////

I'm wondering if, even though the receiving field is text, when you import it Access thinks the source field (in the CSV) is numeric, and so first converts it to an internal number, and then converts that internal number into text, somehow deciding that it has to use exponential notation along the way.

In the .csv file that you are importing, are the problem values enclosed in quotes, or are they just liong strings of digits without surrounding quotes?  If the latter, can you modify the input .csv file so that these values are enclosed in quotes.  In that case, I'm pretty sure they would be imported as text, with not attempt to convert them to numbers.

Thanks for your time and knowledge.

This issue opened a real Pandora's box.  I will post back when I resolve for the benefit of others.

Dennis

At this point I would probably process the file in VBA: read the file one line at a time, parse it into the fields, and add the record to a DAO.Recordset object.

Tom,

About 30 of the 3,800 fields display >>>> 4.40E+15 (all of which were about 4398820002113500)

When I place 1234567890123456 in the last row of the field and go to the next row then the display shows 1234567890123456 as you expected.

I am guessing that the value comes into the table via SCHEMA.INI instantaneously before the field is formatted as Text therefore is a number which becomes Text?   Like a bug in the SCHEMA.INI import process???

My workaround (in VB.Net) is:   (Below was Edited 5/14/2013 - better answer)

--

For Each myCell In xlsSheet.UsedRange                                   'Added 5/14/2013

   If IsNumeric(myCell.Formula) And Len(myCell.Formula) > 11 And Mid(myCell.Formula, 1, 1) <> "`" Then

     'Adds the Grave to the beginning of a numeric string that will show

     '  in an Exponential Format unless this code next is performed

     '  After this file's information is imported into Access then

     '  Run a Update query to remove the Grave from all information in Table

          myCell.Formula = "`" & myCell.Formula

   Else

          myCell.Formula = xlWSFunction.Trim(xlWSFunction.Clean(myCell.Formula))

   End If

Next myCell

--

The import command SQL is:

myDbs.Execute("SELECT * INTO PaymentHistory FROM [Text;FMT=CSVDelimited;HDR=Yes;DATABASE=" & dbsPath & ";].[PaymentHistoryDtl#csv];", Dao.RecordsetOptionEnum.dbFailOnError)

--

All of the Data Type = "Text" data is imported with the "Length" and "Column (Field) Headings" as per the SCHEMA.INI file below.

 [PaymentHistoryDtl.csv]

ColNameHeader = False

Format=CSVDelimited

MaxScanRows = 25

CharacterSet = ANSI

CurrencyPosFormat $1

CurrencyNegFormat ($1)

Col1="VendorNumb"      Text      Width 20

Col2="PONumb"          Text      Width 20

Col3="InvoiceNumb"     Text      Width 35

Col4="InvoiceDate"     DateTime  Width 10

Col5="InvoiceAmt"      Currency  Width 16

Col6="InvoiceAbsAmt"   Currency  Width 16

Col7="VoucherNumb"     Text      Width 35

Col8="EntryDate"       DateTime  Width 10

Col9="FiscalYear"      Text      Width 4

Col10="PeriodNumb"     Text      Width 1

Col11="VendorName"     Text      Width 25