Import from Excel

[Jonathan58]

Hello everyone,

I am trying to import Excel worksheet into Access 2003 table. The problem I have is with fields that have mixed data types. It is trying to guess the data type based on the first group of records (depending on how it is set in the registry - in my case the default is 25). For example in ZIP code column the first 25 records may be numeric so it will assign "Double" data type to it, however records further down may be something like
10001-9999 and import of those values would fail.
I set TypeGuessRows in[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] to 0 and it works - now it scans all records to determine the data type. However, each time I invoke import data dialog in Access and select excel file, the default of 25 for TypeGuessRows returns and I have to change it again in the registry.
Is there a way to set it permanently to 0?
Any help with this will be appreciated.

[slave138]

Never tried it that way. Usually I have a table already defined to import it to so it just follows the datatypes of the target table (or fails if an invalid type is used). If you can't find a way to permanently set the registry key you could always create a small script to change it before importing.

This page includes some functions to read/write registry keys from Access:
http://vba-corner.livejournal.com/3054.html

[TheShabz]

what I do with all numerical fields that i will not be running calculations on is, before import, i create a row up top that has cells containing "test" in it. this automatically assigns a text datatype and i wont have to worry about it again.

My rule of thumb is that if its numerical and no calculations will be done on it, it's going in as text.

[Jonathan58]

Thank you for your responses.
I ended up setting another excel registry key - FirstRowHasNames to No (00) - the default was Yes (01) - this setting stays as I set it, does not go back to default every time I start import. This way it always considers the first row where I have column names to be a data row and treats every column as text. It does not affect how data is imported into Access - first row can still be specified as "column headings" when going through Access import wizard.
Just have to remember that this is how I set it for all other Excel uses on this machine.

[pkstormy]

I would definately avoid any registry modifications, even if scripted! That opens up a whole new can of worms (especially for computers where the registry hack isn't implemented). You can easily save the excel file as a *.csv or *.txt file and then design an import specification in MSAccess.

Excel importing is problematic where MSAccess has a tendancy to interpret the first record of data as the field type for that column being imported.

If that doesn't work, you could also create a 'dummy' row (as TheShabz stated) as the 1st or 2nd row (under the header row) and fill that row for each column with text characters (or numbers) so that it would then import as the correct field type.

I would just stay far, far away from registry type hacks. This isn't a good practice to get into.

[peter_lawton]

I used the top row as text trick to ensure all fields not to be used for calcualtions are text. When I look at table design it has assigned the text or number data type exactly as I wished.

HOWEVER I was doing this as 12 digit barcode 809651411124 was importing as 8.0965141112e+011. I thought this would force the format but it doesn't. I need to link this field to one in another table so text is fine.

I'm aware of the =UPPER() formula but I have about a nine hundred sheets to import... Want to finish before Christmas

Help Please Please

Peter

[TheShabz]

its importing as the scientific notation and you want it to regular whole number, correct?

Let them all go in as scientific notation then multiple that entire field by 100000000000. There's probably some uber-cool programming way to do it but I'm a simple person.

Although, I should say, I've never had that happen to me.

[peter_lawton]

I tried it (using a calculation in an Access query) but 8.0965141112e+011 x 100000000000 gave the answer 8.0965141112E+22

thanks though

peter

[TheShabz]

try outputting it into something, see if it is just showing up that way in the table or it's actually stored like that. It may be as simple as expanding the field it's in, like in excel

[peter_lawton]

I've been playing around with all sorts of excel cell formats, importing to preformatted columns in excel, csv imports and exports, various field properties in Access and so on for a couple of days now. I've scoured the web (and my goodness I'm not the first) but found nothing to help.

If convert the excel number column to text within excel it turns into the scientific format. If I use the =UPPER() formula in excel it displays the 12 digits for both columns in exactly the same way in the cell and in the formula bar. The format of cells in both cases is number 0 decimal. However when I import into Access the original 12 digit number column converts to scientific but the UPPER() column comes out as a 12 digit number as I want. The field data type for both these fields is text.

If I could understand what is going on I am desperately hoping I can come up with a way of avoiding having to convert 900 columns using the formula and tell the damn column to do what it's told to do when it arrives in Access.

peter

[TheShabz]

I dont see how a text datatype can convert a 12 digit "number" to scientific notation. that's beyond me. Sorry man, hope you find an answer.

[pkstormy]

I know this sounds basic but have you tried expanding the column on the ones with scientific notation. Usually this displays when the column of the field isn't wide enough. It's caught me a few times. You can also do a msgbox "My Variable = " & myVariable in code (or the debug watch) to see what the actual value is. Otherwise what I've often done is import the file and then run an append query to append it to a data table that has the correct field types. Only thing to watch for when doing this is for fields that have text beyond 255 characters since it will cut these off at 255 characters.

[peter_lawton]

thanks but that not the problem. As I say above I can force the format change on an import by import basis using =UPPER() but I have 900 files to import. I'm trying to understand why it is defaulting to scientific instead of text

[pkstormy]

thanks but that not the problem. As I say above I can force the format change on an import by import basis using =UPPER() but I have 900 files to import. I'm trying to understand why it is defaulting to scientific instead of text

To import a mass set of xls files, see this example:
https://www.accessforums.net/code-re...html#post39320

In regards to the scientific notation, why not just import it as a text field? You can link to another table on text fields if need be or after the import, change the field type to Number (as DOUBLE, NOT Long Integer!!)

The problem you're having is most likely due to the LONG INTEGER that MSAccess is trying to import that field as instead of a DOUBLE. I'd again, recommend importing as a text field and then change it to a Number type field (again as a DOUBLE, not Long Integer). You could also try formatting the column in Excel (as Double). I'm not sure if this would work but it seems to me that Excel is giving you the scientific notation because the column is formatted as a Long Integer instead of a Double (in which again, I'd probably change it to a text column field type in Excel and then import as a text column, then change the field type in the MSAccess table after importing.)

You could also simply save the xls file as a txt file and then design an import specification (you could also modify the above link example to mass import *.txt files as well).

[peter_lawton]

The discussion has got a bit fractured so I shall try and sum up.

The basic problem is the field type not staying the same when importing from excel into Access 2003. The problem seems to be related (if not directly caused by) excel or access converting everything 12 digits or over to scientific notation. I am trying to import barcodes (so no calculation required) but need all digits so I can join fields.

Tried solutions:

I was importing other information as will as the barcodes. In all following examples Field Options were greyed out so I couldn’t force Access data type.

1. Format Excel Column as text. This immediately displays the cells in scientific notation e.g 809651400326 as 8.09652E+11 (12 digit) 5018766996371 as 5.01877E+12 (13 digit) although in the formula bar it shows as all digits.

Result: Access imports original barcode in scientific notation format. In Table Design view both columns are shown as data type text.

2. Insert a dummy line beneath column header with format for that column (hoping Access will use first cell to determine field format):

Result: Access imports original barcode in scientific notation format. In Table Design view both columns are shown as data type text.

3. Create extra column in excel sheet using =UPPER() formula. Both columns formatted in Excel as Number with 0 decimal. In new column Formula Bar shows =UPPER(E2) etc Cell displays all digits.

Result: Access imports original barcode column in scientific notation but new column using formula shows all digits. In Table Design view both columns are shown as data type text.


pkstormy – as you can see from above I have not tried to import numbers. I have consistently tried to import as text as I do not need to calculate. I really don’t think the integer type is an issue. (I do appreciate double is required for numbers longer than ten digits).

The import problem also occurs if I convert excel to csv

Conclusion: Using the =UPPER() formula in excel is the only way I have found to have Access import the field as I wish it to be displayed (note all imports are as text but only this avoids displaying as scientific notation).

Inserting this new formula column to 900 excel sheets of varying length is not a possibility. I am trying to understand what is going on so I can find another way to get Access to display the way I want.


thanks everyone for help

peter