Importing data

  • Thread starter Thread starter H. Martins
  • Start date Start date
H

H. Martins

I am trying to import records (Excel) to a database. It would be nice
if I could import through the all database structure, but it is OK to
import table by table (I will 'connect' table records latter using
regular form). There are no required key fields (pointing to other
tables records).

While trying I got this "an error occurred trying to import file ..."
obnoxiously pale error message.


Notes:
A - column headings are the same as record names

B - Some Access records require unique fields but I checked there are
no collisions between existing data and data being imported.

C - All data being imported is 'clean' data (no formulas - pasted as
value).


Still, I have some questions and need some advise:

1 - Should I provide key field values for 'this' table key field?

2 - Date fields can be imported as serial number? If no, in which
format?

3 - Must I provide all the fields (leaving some of them blank)?

4 - While writing this post I converted some Excel numeric data that
was preceded by an apostrophe (string indeed) using Excel Value()
function and pasting as value. Again, I tested - no luck.

Thanks
Henry
 
H. Martins said:
I am trying to import records (Excel) to a database. It would be nice
if I could import through the all database structure, but it is OK to
import table by table (I will 'connect' table records latter using
regular form). There are no required key fields (pointing to other
tables records).

While trying I got this "an error occurred trying to import file ..."
obnoxiously pale error message.


Notes:
A - column headings are the same as record names

B - Some Access records require unique fields but I checked there are
no collisions between existing data and data being imported.

C - All data being imported is 'clean' data (no formulas - pasted as
value).


Still, I have some questions and need some advise:

1 - Should I provide key field values for 'this' table key field?

2 - Date fields can be imported as serial number? If no, in which
format?

3 - Must I provide all the fields (leaving some of them blank)?

4 - While writing this post I converted some Excel numeric data that
was preceded by an apostrophe (string indeed) using Excel Value()
function and pasting as value. Again, I tested - no luck.

Thanks
Henry
Click to expand...


I'm not sure about your situation but I've found the safest/easiest way
to
import to Access is to format the sheet as text and export it to a
tab-delimited text file. Select the whole sheet (click in upper left
corner of sheet). Then right click in the sheet and set format to text
(from General or whatever it was). Then File->SaveAs - *.txt (Tab
Delimited). The use the Access import wizard to import it to Access.

Regarding your notes:

1. It wont hurt to let Access add a key (auto number field).

2. Import the dates as text, then reformat in Access if necessary.

3. You not only need to provide all the fields but you should make sure
that the the first record (whether it is field names or not) contains
the correct number of fields. All succeeding records must contain this
same number of fields (although they may be empty). I believe the Access
import only checks the first 100 or so records when determining the
number of fields per record. If later records have more fields you will
get an error.

4. Don't convert anything in the Excel sheet. Export it as text and then
import it to Access as text. Then do any required conversions once it is
in Access.


--
 
Back
Top