File transfer to Access

[John778]

Hi,

I tried to transfer Excel file to Access and got null values (lost data) in the Access file. Then I tried to transfer it in text, got it successful but didn't save, so when tried again later to transfer same text files got null values again. Can anyone share ideas please what could this be? many thanks!

[CJ_London]

in excel, what you see is not necessarily what you think you have - e.g. dates are actually numbers (same in Access). a blank cell may be empty (null) or may have a zero length string. perhaps the cell value starts with a linefeed so the text is 'moved down' and no longer visible in excel unless you increase the height of the row.

You have not described your problem clearly - 'transfer excel' - how? copy/paste, transferspreadsheet or some other method?
what do you mean by 'transfer in text'? and 'successful but didn't save'?
and 'got null values'
Are you appending data to an existing table or making a new table?

provide an example of the data you are trying to transfer and what you currently end up with, and clarify are you in Access 'pulling' data from Excel, or in Excel 'pushing' data to Access. If you are using code, provide the code.

[John778]

Thanks very much for replying! I am using 'External data' option to transfer 'the Parking permits' data in Excel format so it has 2 pages - one for People and their IDs and another for Cars and registration numbers. I use Import&Link and then follow a dialogue to transfer data e.g. 'first line are headlines', 'assign a key' etc. In the result I obtain an Access file but the ID numbers are missing in many places, so when I am trying to create relationship between these 2 files in Access it gives an error '..it can't be created because of null values..'.

I read that it is a common problem of Access of transferring Excel files, and the advise was to try transferring data in txt. format which I did. I saved Excel files in 2 separate text files and again through External Data transferred through Import &Link. When I did it first time I got all data transferred, felt happy and closed Access without saving. A day later I opened a blank Access again and tried transferring these txt. files again and now I again got missing files in the ID field! So I don't understand what is happening and what can be done as a quick fix.

Does this error sound familiar? Many thanks!!

[CJ_London]

data in Excel format

importing data does not import format - so as previously mentioned what you are seeing is not necessarily what the underlying data actually is

When data is imported, if you do not specify the datatype or use a previously created import specification, the importer will take a 'best guess' based on the first few rows of data. examples of the type of thing that can happen is if excel is formatted as 0.00 to display two decimal places, unless there are some decimal values in the first few rows, it will assume it is integer or long. If a field is blank it will assume text, etc. So as previously requested

provide an example of the data you are trying to transfer and what you currently end up with

[John778]

Yes, when I transfer the files through the dialogue I set the data types for numbers as Integers for IDs and text for reg numbers. I also assign primary keys to IDs, Index them etc. I am attaching the extract from the Excel file a and the resultant Access which has missing IDs. It doesn't only have missing data in the first rows, it's in random places (records) of the file. Attached now. Many thanks!!

[CJ_London]

Taken a look - the problem is you have specified the datatype as integer in Access - max integer is 32767 - the numbers that are missing are greater than that.

In access, change the number type from integer to long - should solve the problem

[John778]

oh, i see... thanks so much!!