Helpful Information

Category: Database Management

importing excel to access using sql

I was trying to import an excel file into access 97 I kept receiving an error stating "an error occured trying to import file. The file was not imported." When I did a search on Microsoft Help Website I received this fix however I am unable to get it to run. Can someone please look it over for any errors that I may be missing.

INSERT INTO [HIP COMPONENTS] (CASENO, COMPONENT, COMPONENTNO, COMPONENTTYPE, VENDOR, QTY, COST)
SELECT F1, F2, F3, F4, F5, F6, F7
FROM [EXCEL 8.0 ; HDR=NO; IMEX=2
DATABASE=C:\MYDOUCUMENTS\HIPSTEMSDOWNLOAD.XLS
:confused:

Well if that's word for word then it's not gonna work is it?

INSERT INTO [HIP COMPONENTS] (CASENO, COMPONENT, COMPONENTNO, COMPONENTTYPE, VENDOR, QTY, COST)
SELECT F1, F2, F3, F4, F5, F6, F7
FROM [EXCEL 8.0 ; HDR=NO; IMEX=2
DATABASE=C:\MY DOCUMENTS\HIPSTEMSDOWNLOAD.XLS]

Does the field names and table name match your database? is the Excel version number correct for the file? is the first row lables (if so then use HDR=YES)?

Thanks! The problem was the version. Sometimes we over look the small things.:rolleyes:

Sometimes? No, we always overlook the small things, that's why we need people to test and debug for us.

:)

INSERT INTO tblcustomers ( customername, sourcecode )
SELECT A, B
FROM [EXCEL 8.0; HDR=YES; IMEX=2; DATABASE=intouch.xls];

how does this works please explain. Im new to access.

everytiime i try to sava the query is changes to this

INSERT INTO tblcustomers ( customername, sourcecode )
SELECT A, B
FROM [EXCEL 8].[0; HDR=YES; IMEX=2; DATABASE=intouch].xls;
that's why the SQL can't understant the database

it only accept this query

INSERT INTO tblcustomers ( customername, sourcecode )
SELECT A, B
FROM [EXCEL 8.0; HDR=YES; IMEX=2; DATABASE=intouch].xls;

is this still right?

when running the query

INSERT INTO tblcustomers ( customername, sourcecode )
SELECT A, B
FROM [EXCEL 8.0; HDR=YES; IMEX=2; DATABASE=intouch].xls;

i got a this error

"It is already opened by another user, or you need permission to view it's data."

no one using the database. What to do? HELP!!!!!

Hi,

reading your hints, I tried to do the same.
I built a visual basic procedure like this

sub myprocedure()

Set dbs = CurrentDb

SQLstring = ""
SQLstring = SQLstring & "INSERT INTO [*emptyTMPtable] "
SQLstring = SQLstring & "SELECT F1, F2, F3, F4, F5, F6, F7 "
SQLstring = SQLstring & "FROM [EXCEL 9.0 ; HDR=NO; IMEX=2 "
SQLstring = SQLstring & "DATABASE=C:\Book2.xls]"

MsgBox SQLstring

dbs.Execute SQLstring

End Sub

When it runs, an error message appears after the Execute statement saying

"The Microsoft Jet database engine cannot find the input table or query 'EXCEL 9.0 ; HDR=NO ; IMEX=2 ; DATABASE=C:\Book2.xls'. Make sure it exists and that its name is spelled correctly"

It seems it's not idetifying the contents of the FROM clause!
The file is there and the name is correct!

Any hint??

Thanks!

Well if that's word for word then it's not gonna work is it?

INSERT INTO [HIP COMPONENTS] (CASENO, COMPONENT, COMPONENTNO, COMPONENTTYPE, VENDOR, QTY, COST)
SELECT F1, F2, F3, F4, F5, F6, F7
FROM [EXCEL 8.0 ; HDR=NO; IMEX=2
DATABASE=C:\MY DOCUMENTS\HIPSTEMSDOWNLOAD.XLS]

Does the field names and table name match your database? is the Excel version number correct for the file? is the first row lables (if so then use HDR=YES)?