importing data from excel 1

[mattpearcey]

is there a simple way to import records from an Excel spreadsheet, say if the spreadsheet has been set up to hold all the same field names, etc, specifically for this purpose?

Thank you for your help already.

Matt Pearcey

 

[davidd]

Yes, If you select File.. Get External Data... Import

select the file extension and file directory where your Excel file is located. when going thru the import select "first row contains titles" . Chose a new table name or append an existing table. HTH

 

[mattpearcey]

If importing from an excel sheet for the first time. Does everything have to match so that the data will all fit in together? so the field name, size, order in which it is placed has to be the same, etc

In other words is it best to make a excel document to use as the importing document? rather than any old document being used as an import? which is what i am doing and its not working. Thank you for your help already.

Matt Pearcey

 

[mattpearcey]

No matter what i do, i cannot get this to work. I have made an excel document to look like the table datasheet, so that it can import easily, but it keeps on saying that it cannot import the data. Why is this? What do i need to do to get this working correctly?

Thank you for your help already.

Matt Pearcey

 

[mattpearcey]

Does anyone know what happens to automatically generated numbers in Access, when importing new records via an excel document?

I.E My situation:- I am drawing up a excel spreadsheet for people to fill in new records, or new data to current records; and i am basically copying over the order of the data from the Datasheet view in Access. BUT, the first field is my record ID number which i made by access when a new record is entered. How do i get around this? And will this only work for new record to import and not ammendments?!
Thank you for your help already.

Matt Pearcey

 

[GingerR]

hi matt. you have a lot going on.

First, AUTONUM: don't reference this field at all when importing or adding records. Access will increment it and add it itself.

Second, your importing problems: What exactly is the error message? Does it generate an ImportErrors table? It's hard to tell exactly what the problem is without looking at your stuff. if you want you can send me it and i'll look at it. There could be lots of different solutions depending on your situation.

The best solution if it is feasible is that you could have your users enter data directly into an Access Form. Then you don't have to mess with importing.

But if that is not the route you wanna take, are you trying to import the new Excel stuff directly into an existing Access table? if so that could be you're problem. there are always issues with the data types matching. Or some of your Access table fields could not accept blanks, or could have the 'REQUIRED' flag set to True while the Excel sheet has empty cells, or perhaps someone has typed text into a date field...on and on. lots of possiblities. That's why entering the stuff directly into access could be better--you can make sure you get the right kind of info.

How is your Access table designed? are all the fields types just TEXT?

Try this: Link to the Excel spreadsheet. (FILE-GET EXTERNAL DATA) Then write a Make Table Query and make a table consisting simply of everything in your Excel spreadsheet. Look at the design of the resulting Access table. is it the same as the Access table you want to append to? If not, write an Append query which appends the Excel records to the existing Access table and converts them to the kind of data they need to be, i.e. integers, dates, etc.

Importing can cause a lot of headaches, but once you have it set up it will work ok. like i said, if you want you can send me your stuff and i'll take a look.

g

 

[mattpearcey]

G

You are the man! In response to your ideas:-

Auto No:- I havent put this in the excel doc, thinking that like you said, it should be able to create the autonumber when it imports. So, great. At leasti know that i am right for once.

Importing:- Error message states " An error occured trying to import file........blah blah blah....The file was not imported."

So, not much info at all.

No, it is not generating an errors table. I think th eproblem lies in where you talk about importing into a current access table. I think that the errors happen as there are many varying forms of field, such as yes/no, memo, text, date, tick boxes, etc. Then, some blank data is being entered. So i think that the errors could well be happening because of this.

So, i think i will try the other route that you have mentioned, and import to another table. And see whta i come up with.

Yes, i am in total agreement with yourself about getting the users to imput the data, but, i am very new to this, and although i have picked things up very quickly i really struggle with the diea of replication and security. Especially when i am in the Uk and i want this database to be sent and used all over the world (God help me!). So i figured that the best way to get around inputing new records and new data into the database would be to issue the database as a read only, along with importing forms so that they can fill these in and send them back to me so i can update the database, and re-issue. A long winded way, but, the only way i think i can manage it so far.

Your views?

I wil have a go at doing the importing again, but i would appreciate some advice / help, if youare willing? That is very kind of you, and i am most appreciative of your help already. Thank you for your help already.

Matt Pearcey

 

[mattpearcey]

Sorry to bother you again G,

I am trying to work out the linking tables, make table query, etc. I think i can do this ok, but i am having trouble. I get the link ok, but then i cannot put all the fields into the query design. Then i cannot get it to run. It deletes the table, then says it cannot find it to make the table. All very strange. Do i need to have both the new linked table, and themaster table that i evenually want it to go into, up on the query desing view? I ama bit confused as to where i can go from here?

So close, but yet, again, so far!!

Thank you for your help already.

Matt Pearcey

 

[mattpearcey]

G

I have zipped up my DB to 900KB if you would like to take a look?


Thank you for your help already.

Matt Pearcey

 

[GingerR]

you might be naming your 'new' table (created in the Make Table query) the same as your linked one? when it runs a MT query, it first deletes the table that you're making. might have gotten yourself into a Catch-22.

anyhow, sure send it to datachick10@hotmail.com. let me know when you do it so i can make sure it makes it here. all over the world, huh? is it top secret stuff?? is it just within your own company so everyone is using the same Access version? let's continue in email since we are getting off the orig topic of this string.

be in touch--

g

 

[mattpearcey]

G

I have sent over the DB for your undoubtably large amoutn of critisism! ha ha

Thanks again. And be in touch

Thank you for your help already.

Matt Pearcey

 

[Crystalguru]

I am receiving the same Error message while importing my excel spreadsheet. "An error occured trying to import the file blah blah. The file was not imported".

Can you tell me how you resolved this?

thanks
crystalguru

 

[dcanfield]

I have sometimes received this error message, especially when I am trying to import into an existing table. I checked, and all of the column headers in Excel matched the field names in Access, field types matched, so it looked like it should work, but I got the same error message.

What I found was that I was trying to import some "blank" columns from Excel. If in Excel, you do a CTRL-END, you can see the full range of data your spreadsheet is going to try to import. In my situation, I was only trying to import columns A,B,C,D, but when doing the CTRL-END, found that it was also trying to import columns E and F (which were blank, and did NOT have a column heading). That kept the entire file from being imported.

Good Luck,
David

 

[Hap007]

The old Excel import problem.

I too also had to struggle with issue, but I was able to solve the problem, or at least find a work around solution.

I discovered that if you have a link to an excel tabel and run a query to import the Excel data into an Access table, well, it will work fine for several attempts and then it will fail.

Turns out that this seems to be a bug in Access/Excel.

My solution, creat a new, temp access table, every time the import is run. This must be a newly named table. Once the data is brought into a one time, temporary Access table, I run a second query to import from the temp access table to my 'working' access table.

Seems like a lot of trouble, but it works every time.

So, I have included the code on how I did this.

Call this function below:

Private Function LoadTheExcelData() As Integer

'Need to refresh Links to make Excel Work Correctly
Dim MySql As String
Dim TempTableName As String

DoCmd.Hourglass True
DoCmd.SetWarnings False

'On Error Resume Next

Randomize
TempTableName = "tblTemp" & Mid$(Trim$(Str$(Rnd) & " "), 2)

'Remove our working dataset
DoCmd.OpenQuery "qryStep1_DeleteWorkEntries"
'TempTableName = "tblTempxxx"

'Get new data
'Excel Not always behaving, so vary table name
'DoCmd.OpenQuery "qryStep2_ImportWorkEntries"
'1) Move to a new, table
MySql = "SELECT tblWebDataSheet1.* INTO " & TempTableName & " FROM tblWebDataSheet1"
DoCmd.RunSQL MySql
'2) Import from New Temp table
MySql = "INSERT INTO tblWebDataWorkArea SELECT [" & TempTableName & "].* FROM [" & TempTableName & "] "
DoCmd.RunSQL MySql
'3) Remove New Temp table
DoCmd.DeleteObject acTable, TempTableName

DoCmd.SetWarnings True
DoCmd.Hourglass False

DoEvents
MsgBox "Import is done"

End Function

Note:
When I do an import, I make sure all target data dields are 'text' type. That way, the data always get's loaded. I later qun code or a query to validate or move to the true field. That way, null or bad dates and bad nuemric data will not cause the import query to fail.

Hope This Helps,
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121