import from excel to access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi all, i have faced a problem in importing to the existing table
it show me this error:
"File" was unable to append all data to the table
The contents of fields in 0 record(s) were deleted, & 0 record(s) were lost
due to key violations.
However i lost over thousands of records. What had happened?
I have already checked the field type, it is the same as in excel.
I do thought of doing on a temporary table and do an append query to the
existing table instead as mentioned in other discussion.
But i need to automate the importation, which does not allow me to do so in
macro, as it require me to key in the table name. Is there any other way of
doing?
I'm really 'green' in access, pls help me in more simplified term :)
Thanks in advance
 
Hello green,

Even though your field types may be the same, there is something about the
Excel data Access does not like. I am very familiar with this as I have to
import serveral Excel files each month. Here are some things you need to
consider:
1. Look at the validation rules and data rules in your Access table. The
easiest way to handle this is to be sure there is no Input Mask, no
Validation Rule, set Required to No, set Allow Zero Length to Yes. In other
words, allow anything to come in. We will deal with bad data later.
2. Null Values. This can be your biggest problem. If you have an Excel
column that has numbers in it, but is formatted as General, any cells where a
user did not put a value in will come into Access as Null. This will cause
an error on import. It will usually be imported, but it will cause any
calculations used on that cell to be incorrect. If you can control this
problem, the best thing to do is be sure that any such columns are formatted
as some kind of number. Then, and empty cell will come into Access as zero.
3.Once you have it in, then clean it up. I use a table that is only for
importing. It is not related to anything. In fact, I have found that if you
put import tables in the Front End database instead of the Back End,
performance is better. I then have an append query that validates the data
and puts it into the production table in the Back End database. One trick
here, to avoid the Nasty Null problem is in my Append Query, every numeric
field has the Nz function in the Update To row of the query: Say the field
is dblMyNumber, In the Update To row I put:
= Nz([dblMyNumber],0) This converts any nulls to zero, but allows any
other value.

As to automation. Even though I do not user Macros, If you are not a VBA
programmer, they can be very useful. You need to create a Maco to do all
this for you. To get your Excel sheet in, create a new Macro, here are the
settings to import your Excel sheet:
First, Turn warning messages off (This is option, but it saves having to
answer a bunch of warnings.
Action - SetWarnings
Warnings On - No

Next, Delete old data from the import table:
Action - RunSQL
SQL Statement - DELETE * FROM MyImportTable;
Use Transaction - No

Next, copy the data into your table from Excel:
Action - TransferSpreadsheet
Transfer Type - Link (This does not bring the spreadsheet in, it just copies
the date)
Table Name - The name of the table you want to put the data in
File Name - The full path and file name of the spreadsheet you want to import
Has Field Names - Yes if your first row of data in Excel has field or column
names
Range - The name of the worksheet in your Excel file that you want.

You may want to turn warnings back or, or run your append query after that,
but I think you can take it from here. Post back if you need more help.
 
Thank You Klatuu!!!! I believed it will be very useful!!!! I will try it out,
and will not hesitate to post back if i need more help ;)

Klatuu said:
Hello green,

Even though your field types may be the same, there is something about the
Excel data Access does not like. I am very familiar with this as I have to
import serveral Excel files each month. Here are some things you need to
consider:
1. Look at the validation rules and data rules in your Access table. The
easiest way to handle this is to be sure there is no Input Mask, no
Validation Rule, set Required to No, set Allow Zero Length to Yes. In other
words, allow anything to come in. We will deal with bad data later.
2. Null Values. This can be your biggest problem. If you have an Excel
column that has numbers in it, but is formatted as General, any cells where a
user did not put a value in will come into Access as Null. This will cause
an error on import. It will usually be imported, but it will cause any
calculations used on that cell to be incorrect. If you can control this
problem, the best thing to do is be sure that any such columns are formatted
as some kind of number. Then, and empty cell will come into Access as zero.
3.Once you have it in, then clean it up. I use a table that is only for
importing. It is not related to anything. In fact, I have found that if you
put import tables in the Front End database instead of the Back End,
performance is better. I then have an append query that validates the data
and puts it into the production table in the Back End database. One trick
here, to avoid the Nasty Null problem is in my Append Query, every numeric
field has the Nz function in the Update To row of the query: Say the field
is dblMyNumber, In the Update To row I put:
= Nz([dblMyNumber],0) This converts any nulls to zero, but allows any
other value.

As to automation. Even though I do not user Macros, If you are not a VBA
programmer, they can be very useful. You need to create a Maco to do all
this for you. To get your Excel sheet in, create a new Macro, here are the
settings to import your Excel sheet:
First, Turn warning messages off (This is option, but it saves having to
answer a bunch of warnings.
Action - SetWarnings
Warnings On - No

Next, Delete old data from the import table:
Action - RunSQL
SQL Statement - DELETE * FROM MyImportTable;
Use Transaction - No

Next, copy the data into your table from Excel:
Action - TransferSpreadsheet
Transfer Type - Link (This does not bring the spreadsheet in, it just copies
the date)
Table Name - The name of the table you want to put the data in
File Name - The full path and file name of the spreadsheet you want to import
Has Field Names - Yes if your first row of data in Excel has field or column
names
Range - The name of the worksheet in your Excel file that you want.

You may want to turn warnings back or, or run your append query after that,
but I think you can take it from here. Post back if you need more help.

'green' said:
hi all, i have faced a problem in importing to the existing table
it show me this error:
"File" was unable to append all data to the table
The contents of fields in 0 record(s) were deleted, & 0 record(s) were lost
due to key violations.
However i lost over thousands of records. What had happened?
I have already checked the field type, it is the same as in excel.
I do thought of doing on a temporary table and do an append query to the
existing table instead as mentioned in other discussion.
But i need to automate the importation, which does not allow me to do so in
macro, as it require me to key in the table name. Is there any other way of
doing?
I'm really 'green' in access, pls help me in more simplified term :)
Thanks in advance
Click to expand...
Click to expand...
 
Hi Klatuu,
I faced another problem in the append query part.
As doing what you say, i have change all the data rules. I even changed the
field type that is supposed to be 'Date/Time' to 'Text', it allowed
successful importation.
But how can i convert the field type back to 'Date/Time' in the append
query???
As the destination table that it is append to, is with all the field types
that i want, when i append, it gave me error in type conversion failure.
Sorry if i asked a stupid question, but i really don't know.

Klatuu said:
Hello green,

Even though your field types may be the same, there is something about the
Excel data Access does not like. I am very familiar with this as I have to
import serveral Excel files each month. Here are some things you need to
consider:
1. Look at the validation rules and data rules in your Access table. The
easiest way to handle this is to be sure there is no Input Mask, no
Validation Rule, set Required to No, set Allow Zero Length to Yes. In other
words, allow anything to come in. We will deal with bad data later.
2. Null Values. This can be your biggest problem. If you have an Excel
column that has numbers in it, but is formatted as General, any cells where a
user did not put a value in will come into Access as Null. This will cause
an error on import. It will usually be imported, but it will cause any
calculations used on that cell to be incorrect. If you can control this
problem, the best thing to do is be sure that any such columns are formatted
as some kind of number. Then, and empty cell will come into Access as zero.
3.Once you have it in, then clean it up. I use a table that is only for
importing. It is not related to anything. In fact, I have found that if you
put import tables in the Front End database instead of the Back End,
performance is better. I then have an append query that validates the data
and puts it into the production table in the Back End database. One trick
here, to avoid the Nasty Null problem is in my Append Query, every numeric
field has the Nz function in the Update To row of the query: Say the field
is dblMyNumber, In the Update To row I put:
= Nz([dblMyNumber],0) This converts any nulls to zero, but allows any
other value.

As to automation. Even though I do not user Macros, If you are not a VBA
programmer, they can be very useful. You need to create a Maco to do all
this for you. To get your Excel sheet in, create a new Macro, here are the
settings to import your Excel sheet:
First, Turn warning messages off (This is option, but it saves having to
answer a bunch of warnings.
Action - SetWarnings
Warnings On - No

Next, Delete old data from the import table:
Action - RunSQL
SQL Statement - DELETE * FROM MyImportTable;
Use Transaction - No

Next, copy the data into your table from Excel:
Action - TransferSpreadsheet
Transfer Type - Link (This does not bring the spreadsheet in, it just copies
the date)
Table Name - The name of the table you want to put the data in
File Name - The full path and file name of the spreadsheet you want to import
Has Field Names - Yes if your first row of data in Excel has field or column
names
Range - The name of the worksheet in your Excel file that you want.

You may want to turn warnings back or, or run your append query after that,
but I think you can take it from here. Post back if you need more help.
Click to expand...
 
Klaatu.

Reading your post solved a rather large problem for me, i.e. the automation
of data import from excel to access. One twist though.

I have a database which needs to import data from a number of different
spreadsheets each month. Is there a way in a macro to 'pause/break' the
macro so an input box comes up asking for the filename\location of the
spreadsheet to be imported, and once that is filled out, automatically
completes the data import from the spreadsheet?

Vector
Klatuu said:
Hello green,

Even though your field types may be the same, there is something about the
Excel data Access does not like. I am very familiar with this as I have to
import serveral Excel files each month. Here are some things you need to
consider:
1. Look at the validation rules and data rules in your Access table. The
easiest way to handle this is to be sure there is no Input Mask, no
Validation Rule, set Required to No, set Allow Zero Length to Yes. In other
words, allow anything to come in. We will deal with bad data later.
2. Null Values. This can be your biggest problem. If you have an Excel
column that has numbers in it, but is formatted as General, any cells where a
user did not put a value in will come into Access as Null. This will cause
an error on import. It will usually be imported, but it will cause any
calculations used on that cell to be incorrect. If you can control this
problem, the best thing to do is be sure that any such columns are formatted
as some kind of number. Then, and empty cell will come into Access as zero.
3.Once you have it in, then clean it up. I use a table that is only for
importing. It is not related to anything. In fact, I have found that if you
put import tables in the Front End database instead of the Back End,
performance is better. I then have an append query that validates the data
and puts it into the production table in the Back End database. One trick
here, to avoid the Nasty Null problem is in my Append Query, every numeric
field has the Nz function in the Update To row of the query: Say the field
is dblMyNumber, In the Update To row I put:
= Nz([dblMyNumber],0) This converts any nulls to zero, but allows any
other value.

As to automation. Even though I do not user Macros, If you are not a VBA
programmer, they can be very useful. You need to create a Maco to do all
this for you. To get your Excel sheet in, create a new Macro, here are the
settings to import your Excel sheet:
First, Turn warning messages off (This is option, but it saves having to
answer a bunch of warnings.
Action - SetWarnings
Warnings On - No

Next, Delete old data from the import table:
Action - RunSQL
SQL Statement - DELETE * FROM MyImportTable;
Use Transaction - No

Next, copy the data into your table from Excel:
Action - TransferSpreadsheet
Transfer Type - Link (This does not bring the spreadsheet in, it just copies
the date)
Table Name - The name of the table you want to put the data in
File Name - The full path and file name of the spreadsheet you want to import
Has Field Names - Yes if your first row of data in Excel has field or column
names
Range - The name of the worksheet in your Excel file that you want.

You may want to turn warnings back or, or run your append query after that,
but I think you can take it from here. Post back if you need more help.
Click to expand...
Click to expand...
 
Green,

It is like converting the Nulls with the Nz function. To convert your date
in string format to a date, in the Update To row of the query:

=datevalue(MyStringDate)

'green' said:
Hi Klatuu,
I faced another problem in the append query part.
As doing what you say, i have change all the data rules. I even changed the
field type that is supposed to be 'Date/Time' to 'Text', it allowed
successful importation.
But how can i convert the field type back to 'Date/Time' in the append
query???
As the destination table that it is append to, is with all the field types
that i want, when i append, it gave me error in type conversion failure.
Sorry if i asked a stupid question, but i really don't know.

Klatuu said:
Hello green,

Even though your field types may be the same, there is something about the
Excel data Access does not like. I am very familiar with this as I have to
import serveral Excel files each month. Here are some things you need to
consider:
1. Look at the validation rules and data rules in your Access table. The
easiest way to handle this is to be sure there is no Input Mask, no
Validation Rule, set Required to No, set Allow Zero Length to Yes. In other
words, allow anything to come in. We will deal with bad data later.
2. Null Values. This can be your biggest problem. If you have an Excel
column that has numbers in it, but is formatted as General, any cells where a
user did not put a value in will come into Access as Null. This will cause
an error on import. It will usually be imported, but it will cause any
calculations used on that cell to be incorrect. If you can control this
problem, the best thing to do is be sure that any such columns are formatted
as some kind of number. Then, and empty cell will come into Access as zero.
3.Once you have it in, then clean it up. I use a table that is only for
importing. It is not related to anything. In fact, I have found that if you
put import tables in the Front End database instead of the Back End,
performance is better. I then have an append query that validates the data
and puts it into the production table in the Back End database. One trick
here, to avoid the Nasty Null problem is in my Append Query, every numeric
field has the Nz function in the Update To row of the query: Say the field
is dblMyNumber, In the Update To row I put:
= Nz([dblMyNumber],0) This converts any nulls to zero, but allows any
other value.

As to automation. Even though I do not user Macros, If you are not a VBA
programmer, they can be very useful. You need to create a Maco to do all
this for you. To get your Excel sheet in, create a new Macro, here are the
settings to import your Excel sheet:
First, Turn warning messages off (This is option, but it saves having to
answer a bunch of warnings.
Action - SetWarnings
Warnings On - No

Next, Delete old data from the import table:
Action - RunSQL
SQL Statement - DELETE * FROM MyImportTable;
Use Transaction - No

Next, copy the data into your table from Excel:
Action - TransferSpreadsheet
Transfer Type - Link (This does not bring the spreadsheet in, it just copies
the date)
Table Name - The name of the table you want to put the data in
File Name - The full path and file name of the spreadsheet you want to import
Has Field Names - Yes if your first row of data in Excel has field or column
names
Range - The name of the worksheet in your Excel file that you want.

You may want to turn warnings back or, or run your append query after that,
but I think you can take it from here. Post back if you need more help.
Click to expand...
Click to expand...
 
Sorry, Vector, I don't know a way to do what you are describing with a Macro.
I can do it with VBA. Do you code in VBA at all?

Vector said:
Klaatu.

Reading your post solved a rather large problem for me, i.e. the automation
of data import from excel to access. One twist though.

I have a database which needs to import data from a number of different
spreadsheets each month. Is there a way in a macro to 'pause/break' the
macro so an input box comes up asking for the filename\location of the
spreadsheet to be imported, and once that is filled out, automatically
completes the data import from the spreadsheet?

Vector
Klatuu said:
Hello green,

Even though your field types may be the same, there is something about the
Excel data Access does not like. I am very familiar with this as I have to
import serveral Excel files each month. Here are some things you need to
consider:
1. Look at the validation rules and data rules in your Access table. The
easiest way to handle this is to be sure there is no Input Mask, no
Validation Rule, set Required to No, set Allow Zero Length to Yes. In other
words, allow anything to come in. We will deal with bad data later.
2. Null Values. This can be your biggest problem. If you have an Excel
column that has numbers in it, but is formatted as General, any cells where a
user did not put a value in will come into Access as Null. This will cause
an error on import. It will usually be imported, but it will cause any
calculations used on that cell to be incorrect. If you can control this
problem, the best thing to do is be sure that any such columns are formatted
as some kind of number. Then, and empty cell will come into Access as zero.
3.Once you have it in, then clean it up. I use a table that is only for
importing. It is not related to anything. In fact, I have found that if you
put import tables in the Front End database instead of the Back End,
performance is better. I then have an append query that validates the data
and puts it into the production table in the Back End database. One trick
here, to avoid the Nasty Null problem is in my Append Query, every numeric
field has the Nz function in the Update To row of the query: Say the field
is dblMyNumber, In the Update To row I put:
= Nz([dblMyNumber],0) This converts any nulls to zero, but allows any
other value.

As to automation. Even though I do not user Macros, If you are not a VBA
programmer, they can be very useful. You need to create a Maco to do all
this for you. To get your Excel sheet in, create a new Macro, here are the
settings to import your Excel sheet:
First, Turn warning messages off (This is option, but it saves having to
answer a bunch of warnings.
Action - SetWarnings
Warnings On - No

Next, Delete old data from the import table:
Action - RunSQL
SQL Statement - DELETE * FROM MyImportTable;
Use Transaction - No

Next, copy the data into your table from Excel:
Action - TransferSpreadsheet
Transfer Type - Link (This does not bring the spreadsheet in, it just copies
the date)
Table Name - The name of the table you want to put the data in
File Name - The full path and file name of the spreadsheet you want to import
Has Field Names - Yes if your first row of data in Excel has field or column
names
Range - The name of the worksheet in your Excel file that you want.

You may want to turn warnings back or, or run your append query after that,
but I think you can take it from here. Post back if you need more help.
Click to expand...
Click to expand...
Click to expand...
 
Yes, you can use the InputBox function to ask for this. For example, in the
Range argument of the macro:

=InputBox("Enter name of spreadsheet:","Sheet Name") & "!"

--

Ken Snell
<MS ACCESS MVP>

Vector said:
Klaatu.

Reading your post solved a rather large problem for me, i.e. the
automation
of data import from excel to access. One twist though.

I have a database which needs to import data from a number of different
spreadsheets each month. Is there a way in a macro to 'pause/break' the
macro so an input box comes up asking for the filename\location of the
spreadsheet to be imported, and once that is filled out, automatically
completes the data import from the spreadsheet?

Vector
Klatuu said:
Hello green,

Even though your field types may be the same, there is something about
the
Excel data Access does not like. I am very familiar with this as I
have to
import serveral Excel files each month. Here are some things you need
to
consider:
1. Look at the validation rules and data rules in your Access table.
The
easiest way to handle this is to be sure there is no Input Mask, no
Validation Rule, set Required to No, set Allow Zero Length to Yes. In
other
words, allow anything to come in. We will deal with bad data later.
2. Null Values. This can be your biggest problem. If you have an
Excel
column that has numbers in it, but is formatted as General, any cells
where a
user did not put a value in will come into Access as Null. This will
cause
an error on import. It will usually be imported, but it will cause any
calculations used on that cell to be incorrect. If you can control
this
problem, the best thing to do is be sure that any such columns are
formatted
as some kind of number. Then, and empty cell will come into Access as
zero.
3.Once you have it in, then clean it up. I use a table that is only
for
importing. It is not related to anything. In fact, I have found that
if you
put import tables in the Front End database instead of the Back End,
performance is better. I then have an append query that validates the
data
and puts it into the production table in the Back End database. One
trick
here, to avoid the Nasty Null problem is in my Append Query, every
numeric
field has the Nz function in the Update To row of the query: Say the
field
is dblMyNumber, In the Update To row I put:
= Nz([dblMyNumber],0) This converts any nulls to zero, but allows any
other value.

As to automation. Even though I do not user Macros, If you are not a
VBA
programmer, they can be very useful. You need to create a Maco to do
all
this for you. To get your Excel sheet in, create a new Macro, here are
the
settings to import your Excel sheet:
First, Turn warning messages off (This is option, but it saves having
to
answer a bunch of warnings.
Action - SetWarnings
Warnings On - No

Next, Delete old data from the import table:
Action - RunSQL
SQL Statement - DELETE * FROM MyImportTable;
Use Transaction - No

Next, copy the data into your table from Excel:
Action - TransferSpreadsheet
Transfer Type - Link (This does not bring the spreadsheet in, it just
copies
the date)
Table Name - The name of the table you want to put the data in
File Name - The full path and file name of the spreadsheet you want to
import
Has Field Names - Yes if your first row of data in Excel has field or
column
names
Range - The name of the worksheet in your Excel file that you want.

You may want to turn warnings back or, or run your append query after
that,
but I think you can take it from here. Post back if you need more
help.
Click to expand...
Click to expand...
Click to expand...
 
Thanks Klatuu!
I had successfully imported!!!
:)

Thanks again
Green

Klatuu said:
Green,

It is like converting the Nulls with the Nz function. To convert your date
in string format to a date, in the Update To row of the query:

=datevalue(MyStringDate)

'green' said:
Hi Klatuu,
I faced another problem in the append query part.
As doing what you say, i have change all the data rules. I even changed the
field type that is supposed to be 'Date/Time' to 'Text', it allowed
successful importation.
But how can i convert the field type back to 'Date/Time' in the append
query???
As the destination table that it is append to, is with all the field types
that i want, when i append, it gave me error in type conversion failure.
Sorry if i asked a stupid question, but i really don't know.

Klatuu said:
Hello green,

Even though your field types may be the same, there is something about the
Excel data Access does not like. I am very familiar with this as I have to
import serveral Excel files each month. Here are some things you need to
consider:
1. Look at the validation rules and data rules in your Access table. The
easiest way to handle this is to be sure there is no Input Mask, no
Validation Rule, set Required to No, set Allow Zero Length to Yes. In other
words, allow anything to come in. We will deal with bad data later.
2. Null Values. This can be your biggest problem. If you have an Excel
column that has numbers in it, but is formatted as General, any cells where a
user did not put a value in will come into Access as Null. This will cause
an error on import. It will usually be imported, but it will cause any
calculations used on that cell to be incorrect. If you can control this
problem, the best thing to do is be sure that any such columns are formatted
as some kind of number. Then, and empty cell will come into Access as zero.
3.Once you have it in, then clean it up. I use a table that is only for
importing. It is not related to anything. In fact, I have found that if you
put import tables in the Front End database instead of the Back End,
performance is better. I then have an append query that validates the data
and puts it into the production table in the Back End database. One trick
here, to avoid the Nasty Null problem is in my Append Query, every numeric
field has the Nz function in the Update To row of the query: Say the field
is dblMyNumber, In the Update To row I put:
= Nz([dblMyNumber],0) This converts any nulls to zero, but allows any
other value.

As to automation. Even though I do not user Macros, If you are not a VBA
programmer, they can be very useful. You need to create a Maco to do all
this for you. To get your Excel sheet in, create a new Macro, here are the
settings to import your Excel sheet:
First, Turn warning messages off (This is option, but it saves having to
answer a bunch of warnings.
Action - SetWarnings
Warnings On - No

Next, Delete old data from the import table:
Action - RunSQL
SQL Statement - DELETE * FROM MyImportTable;
Use Transaction - No

Next, copy the data into your table from Excel:
Action - TransferSpreadsheet
Transfer Type - Link (This does not bring the spreadsheet in, it just copies
the date)
Table Name - The name of the table you want to put the data in
File Name - The full path and file name of the spreadsheet you want to import
Has Field Names - Yes if your first row of data in Excel has field or column
names
Range - The name of the worksheet in your Excel file that you want.

You may want to turn warnings back or, or run your append query after that,
but I think you can take it from here. Post back if you need more help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top