Access import function converts text field to scientific notation

[Rodeo Clown]

On to the next project. I have a text file that was exported out from our county's assessment office database. It includes all of the properties in the county. I imported this file into Excel and have a spreadsheet with nearly 300,000 rows. Each property has an identifier called ParcelNum and is comprised primarily of numbers, but may include a letter or two. So, that column is formatted as text in Excel.

When I try to import that file via the import wizard, Access changes the format of the ParcelNum to scientific notation. For example, ParcelNum 1000002000000 imports as 1.000002e+12.

Here is a screenshot of the spreadsheet:

and here is the input screen in Access:

Attached is a stripped-down spreadsheet and a sample access database. Yes, I know that the table is not normalized so don't chastise me for that. I'll fix that at a later date once I get this import function working correctly.

How do I get Access to import this field without changing it?

ps: seems like I can't upload an xlsx file, so I changed the filename extension to txt. Change it back if you want to open it in Excel.

 

[gemma-the-husky]

In 32 bit access the biggest long integer is 2billion roughly. Your numbers are bigger than that and won't fit, so they represented as exponential approximations. You could show them as text strings, but then not manipulate them as numbers.

In 64 bit access, there's a bigger date type, a long long which may accommodate your numbers, but then all users would need to be using 64 bit access.

 

[Gasman]

 

[LarryE]

On to the next project. I have a text file that was exported out from our county's assessment office database. It includes all of the properties in the county. I imported this file into Excel and have a spreadsheet with nearly 300,000 rows. Each property has an identifier called ParcelNum and is comprised primarily of numbers, but may include a letter or two. So, that column is formatted as text in Excel.

When I try to import that file via the import wizard, Access changes the format of the ParcelNum to scientific notation. For example, ParcelNum 1000002000000 imports as 1.000002e+12.

Here is a screenshot of the spreadsheet:

View attachment 115341

and here is the input screen in Access:

View attachment 115342

Attached is a stripped-down spreadsheet and a sample access database. Yes, I know that the table is not normalized so don't chastise me for that. I'll fix that at a later date once I get this import function working correctly.

How do I get Access to import this field without changing it?

ps: seems like I can't upload an xlsx file, so I changed the filename extension to txt. Change it back if you want to open it in Excel.

Just click the NEXT button and ParcelNum will be highlighted. Then you can format the field as a Double when it is imported as Gasman shows.

 

[Pat Hartman]

The text file is corrupted.

When you have a long text field that is all numbers, Excel thinks it is a number. You can open the spreadsheet and convert the data type to text. NOT General. It must be text. Then you can import it into Access as a text field.

If you save the spreadsheet, while excel thinks the long number is actually a number, it will be saved as scientific notation and you will never be able to recover the actual values.

Do NOT treat these codes as double. They are not true numeric values. They are codes that happen to be composed of all numeric digits.

 

[Isaac]

On to the next project. I have a text file that was exported out from our county's assessment office database. It includes all of the properties in the county. I imported this file into Excel and have a spreadsheet with nearly 300,000 rows. Each property has an identifier called ParcelNum and is comprised primarily of numbers, but may include a letter or two. So, that column is formatted as text in Excel.

When I try to import that file via the import wizard, Access changes the format of the ParcelNum to scientific notation. For example, ParcelNum 1000002000000 imports as 1.000002e+12.

Here is a screenshot of the spreadsheet:

View attachment 115341

and here is the input screen in Access:

View attachment 115342

Attached is a stripped-down spreadsheet and a sample access database. Yes, I know that the table is not normalized so don't chastise me for that. I'll fix that at a later date once I get this import function working correctly.

How do I get Access to import this field without changing it?

ps: seems like I can't upload an xlsx file, so I changed the filename extension to txt. Change it back if you want to open it in Excel.

Import it as Text in the specification.

 

[Rodeo Clown]

Thanks for all your input. As I stated in the original post, some of the records in ParcelNum contain a letter or two - I didn't name the field. Thus, formatting this field with any of the numerical data types is not an option. It has to be text. When the column format is changed from number to text, Excel automatically converts the number to scientific notation.

The work around is as follows:

In Excel, select the entire column. The click the Data tab, then Text to Columns. On the 3rd step of the Text to Columns Wizard, change the column data format to text. Excel will throw a warning on every single row that a number is stored as text. This can be turned off in Excel's Options -> Formulas -> Error checking rules -> "numbers formatted as text or preceded by an apostrophe"

 

[CJ_London]

Why use excel? As you have discovered it can change the datatype of the value.

Why not use sql to import the file directly, or just as a query. Set headers = no and you will get column heading F0 F1 etc, then use criteria to exclude the rows you don’t want (such as isnumeric(F1) =true). If you want you can alias F1 etc back to a recognisable name

 

[Rodeo Clown]

Why use excel? As you have discovered it can change the datatype of the value.

Why not use sql to import the file directly, or just as a query. Set headers = no and you will get column heading F0 F1 etc, then use criteria to exclude the rows you don’t want (such as isnumeric(F1) =true). If you want you can alias F1 etc back to a recognisable name

Thanks, I'll try that but I think that there is an issue with one of the fields used for property description. So, I'll have to address that first.

 

[cheekybuddha]

The text file is corrupted.

From the OP:

ps: seems like I can't upload an xlsx file, so I changed the filename extension to txt. Change it back if you want to open it in Excel.

Change the file extension, Pat, and you will be able to open the file.