Access import from Excel

  • Thread starter Thread starter amb
  • Start date Start date
A

amb

I have a database that I want to import info from a Spreadsheet. I am
running Access 2003. I upgraded to this some months ago, but havent had
need to really use it until now. I have discovered that the File Import
Data seems to have lost the ability to select XLS and CSV type files from
its import options.

Ive tried copy pasting data, but because the date formats arent to accesses
liking it gives copy-paste errors for every row.

I tried saving my excel spreadsheet as XML, but it wont let me import them
correctly. If I save as an XML Spreadsheet, when I try to import I just get
a whole bunch of tables called "DocumentProperties", "Style" etc and no data
and no data in the existing table I want to add to. When I try to save as
an XML Data file I just get an error saying XML cant be saved because it
doesnt contain mappings.

All I want to do is save data that looks a bit like this....

[snip]
Extract wk Extract date subproc Age Total Forms Processed
wk11 12-12-05 DP1 less than 4 days old 1986
wk11 12-12-05 DP1 4 - 7 days old 354

[/snip]


.... Into a pre-existing datatable with the same column names.

I will be doing this with about 6 spreadsheets with a lot of data and
columns, and I dont want to muck around trying to reformat it all so Access
gets it how it likes it so I can copy paste (eg the dd-mm-yy format of the
dates; Note that the '-'s are not a preference, thats they format the dates
where I work for some reason)
 
The following KB article deals with an issue of not being able to import
Excel files in Access 2003. I do not know if it applies, however, you may
want to take a look at it.

http://support.microsoft.com/default.aspx?scid=kb;en-us;283881

Normally, CSV files are imported by choosing Text Files from the Files of
Type combo box. It then starts the Import wizard for text files.

Regarding the date format, if the date column in Excel is using a custom
date format, the underlying values are still dates and should import
correctly. If they are not custom date formatted, then more information
about how the dates are actually formatted would be helpful.


--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have a database that I want to import info from a Spreadsheet. I am
running Access 2003. I upgraded to this some months ago, but havent had
need to really use it until now. I have discovered that the File Import
Data seems to have lost the ability to select XLS and CSV type files from
its import options.

Ive tried copy pasting data, but because the date formats arent to accesses
liking it gives copy-paste errors for every row.

I tried saving my excel spreadsheet as XML, but it wont let me import them
correctly. If I save as an XML Spreadsheet, when I try to import I just get
a whole bunch of tables called "DocumentProperties", "Style" etc and no data
and no data in the existing table I want to add to. When I try to save as
an XML Data file I just get an error saying XML cant be saved because it
doesnt contain mappings.

All I want to do is save data that looks a bit like this....

[snip]
Extract wk Extract date subproc Age Total Forms Processed
wk11 12-12-05 DP1 less than 4 days old 1986
wk11 12-12-05 DP1 4 - 7 days old 354

[/snip]


.... Into a pre-existing datatable with the same column names.

I will be doing this with about 6 spreadsheets with a lot of data and
columns, and I dont want to muck around trying to reformat it all so Access
gets it how it likes it so I can copy paste (eg the dd-mm-yy format of the
dates; Note that the '-'s are not a preference, thats they format the dates
where I work for some reason)
 
Back
Top