Importing From Excel to Access 2000

  • Thread starter Thread starter John Arthur
  • Start date Start date
J

John Arthur

Hi there, I am trying to import an excel spreadsheet into
an existing database file.

At this stage I have an error coming up along the lines
of "An error occured trying to import file". I goies on to
list the files then "The file was not imported".

By process of elimination I worked out that the Currency
column appears to be the culpit. What am I doing wrong. I
have selected to let Access itself set up the new table. I
tried formating the excel spreadsheet to currency then
number. Neither worked.

The process of elimination I used was to import the
spreadsheet column by column (i.e. adding an extra column
after successfully importing a previous set of columns).
It always came back to the currency column.

The error message always comes up, however some times the
data is imported.

Can anyone help me on this

Thanks
 
Can you post some sample records from the EXCEL file? We need a bit more
info to identify what is possibly going wrong. How did ACCESS format the
table's field into which the "currency" data were to go?
 
Headings -

CustID LastName Smith
FirstName John
Address 13 The Street
City State PC Code TermsPaymentisDue
DiscountDays %Discount TaxCode
VolumeDiscount% GST IssueINVID ABN
GrossAmount GSTAmount NetAmount

Data in first row -

1 Anytown NSW 2204 T012 3
0 0 GST 0 YES Y 81
003 157 520 $1,620.66 $147.33 $1,473.33
 
Oops hit the wrong button. Here it is again in a logical
format, forget the previous gobbibty gook

CustID (Number) 1
LastName (Text) Smith
FirstName (Text) John
Address (Text) 13 The Street
City (Text) Anytown
State(Text) NSW
PC (Number) 2345
Code (Text) T1234
TermsPaymentisDue (Number) 3
DiscountDays (Number) 0
%Discount (Number) 0
TaxCode (Text) GST
VolumeDiscount% (Number) 0
GST (Text) YES
IssueINVID (Text) Y
ABN (Number) 12 345 456 789
GrossAmount (Currency) $1,234.50
GSTAmount (Currency) $123.40
NetAmount Currency) $1,200.00

The details to the left are the column headings while the
details in brackets indicate the type of date entered into
those fields. THe last column represent the details in row
1 of the spreadsheet. The % discounts will always be "0"
so I am tempted to lose those, however, the spreadsheet
still require them for other reasons.

As I have been unable to succeed in bringing in the
currency columns I cannot tell you the format of the
currency column, however, during the import process it is
listed as Indexed "No" Data Type "Currency" - this last
bit is greyed out.

As a side issue duriong my attmepts to understand access
in the past I loaded a password to a database which has
been deleted long ago. I still need to enter in the
password. AS there is no need for this in the new database
is there a way of losing the password requirement.

Regards

John
 
Other than the ABN field being numeric, while it appears that it should be
text (because of the spaces), I don't see any obvious problem in the first
row of data. However, it could be any of the rows of data in the EXCEL
sheet.

Try linking to the EXCEL sheet and see if you are then able to "read" the
data through the linked sheet. ACCESS handles data differently when linking
compared to importing.
 
Thanks Ken, I will give it a try.


-----Original Message-----
Other than the ABN field being numeric, while it appears that it should be
text (because of the spaces), I don't see any obvious problem in the first
row of data. However, it could be any of the rows of data in the EXCEL
sheet.

Try linking to the EXCEL sheet and see if you are then able to "read" the
data through the linked sheet. ACCESS handles data differently when linking
compared to importing.

--
Ken Snell
<MS ACCESS MVP>




.
Click to expand...
 
Back
Top