T
TheScullster
Hi all
Can anyone advise on the best way to approach an import from Excel please?
The background:
Traditionally we have created lists of valves for purchase using Excel.
I have now been asked to assist with importing these into an Access database
to make use of reporting/querying features etc.
I am of the opinion that this data is probably better manipulated in Excel
but hey-ho! Don't get me wrong I much prefer delving into Access, but don't
believe it is particularly applicable here due to lack of relationships etc.
So the question(s)
The deal will be that with each new project there will be a requirement to
import a list of valves and their attributes.
The lists come out of Autocad Drawing Software in comma delimited format
with abreviated attributes eg SW stands for Socket Weld End Connections.
There are ten fields in total including the valve label (or tag) which would
be the PK.
If necessary, the lists could be imported to Excel first to "search and
replace" all of the abreviations with the appropriate expanded detail, then
imported from there into Access. Otherwise would it be possible/advisable
to have a report show the expanded detail for each record, with the
abreviated version in the field behind?
Initially I tried importing the valve data from Excel into a pre-structured
table, but the option to import into an existing table was greyed out.
Importing into a new table worked fine, although there were some reported
errors (presumably duplicates of PK entries).
I then tried importing CSV file which worked OK into pre-structured table.
I also tried using an append query, which I suspect is the clean way to do
this, using the following SQL:
INSERT INTO Valves ( Tag, Type, [Size], Matl, Conn, [Proc], Press, Temp,
Locn, PID )
SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10
FROM [EXCEL 11; Hdr=No; Database="d:\temp\XLtoAcc2.xls";].[Sheet1$];
Are there any errors in the text above?
When I attempted the append, I received error cannot find installed ISAM. I
believe that this can be down to incorrect syntax in the above statement.
To create a usable framework for future projects, how would this best be
achieved?
I believe that I should be starting with an empty database but with a
pre-structured table, forms, reports, queries etc?
Should I be trying to pull the data into Access as described above, or is it
preferable/less glitchy to push the data using a module from Excel?
All software is Office 2003.
Any guidance appreciated
Phil
Can anyone advise on the best way to approach an import from Excel please?
The background:
Traditionally we have created lists of valves for purchase using Excel.
I have now been asked to assist with importing these into an Access database
to make use of reporting/querying features etc.
I am of the opinion that this data is probably better manipulated in Excel
but hey-ho! Don't get me wrong I much prefer delving into Access, but don't
believe it is particularly applicable here due to lack of relationships etc.
So the question(s)
The deal will be that with each new project there will be a requirement to
import a list of valves and their attributes.
The lists come out of Autocad Drawing Software in comma delimited format
with abreviated attributes eg SW stands for Socket Weld End Connections.
There are ten fields in total including the valve label (or tag) which would
be the PK.
If necessary, the lists could be imported to Excel first to "search and
replace" all of the abreviations with the appropriate expanded detail, then
imported from there into Access. Otherwise would it be possible/advisable
to have a report show the expanded detail for each record, with the
abreviated version in the field behind?
Initially I tried importing the valve data from Excel into a pre-structured
table, but the option to import into an existing table was greyed out.
Importing into a new table worked fine, although there were some reported
errors (presumably duplicates of PK entries).
I then tried importing CSV file which worked OK into pre-structured table.
I also tried using an append query, which I suspect is the clean way to do
this, using the following SQL:
INSERT INTO Valves ( Tag, Type, [Size], Matl, Conn, [Proc], Press, Temp,
Locn, PID )
SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10
FROM [EXCEL 11; Hdr=No; Database="d:\temp\XLtoAcc2.xls";].[Sheet1$];
Are there any errors in the text above?
When I attempted the append, I received error cannot find installed ISAM. I
believe that this can be down to incorrect syntax in the above statement.
To create a usable framework for future projects, how would this best be
achieved?
I believe that I should be starting with an empty database but with a
pre-structured table, forms, reports, queries etc?
Should I be trying to pull the data into Access as described above, or is it
preferable/less glitchy to push the data using a module from Excel?
All software is Office 2003.
Any guidance appreciated
Phil