Importing Excel Table to Access

  • Thread starter Thread starter TotallyConfused
  • Start date Start date
T

TotallyConfused

I hope someone can please help me with this! I have an Excel table with
several fields and hundreds of rows. This data consists of Company names
with id numbers. However, a Company name will be listed in Excel as:
ABC Company 50001-52500
ABC Company 53000-53020 but not 53019

How do I export to Access so that ABC Company is listed with ID numbers
consecutively and skip IDs referenced not to be listed.??

Any help will be greatly appreciated as this is being done manually right
now.
 
Totally,

Do you mean that the data in excel is structured such that the name of the
company is in one column, and the "50001-52500" and "53000-53020 but not
53019" is in another column?

How standardised is the data in the 2nd column in the Excel sheet? Does it
*always* consist of a number followed by a hyphen followed by another
number? And are the exceptions *always* shown as ""but not " followed by a
number? Can the "but not " be followed by more than one number? Are there
other ways in which the data is recorded at present?

--
Steve Schapel, Microsoft Access MVP


TotallyConfused said:
I hope someone can please help me with this! I have an Excel table with
several fields and hundreds of rows. This data consists of Company names
with id numbers. However, a Company name will be listed in Excel as:
ABC Company 50001-52500
ABC Company 53000-53020 but not 53019

How do I export to Access so that ABC Company is listed with ID numbers
consecutively and skip IDs referenced not to be listed.??

Any help will be greatly appreciated as this is being done manually right
now.
Click to expand...



__________ Information from ESET Smart Security, version of virus signature database 4136 (20090606) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Thank you thank you for giving some hope. There are two columns in the Excel
table one column for the company name and the other column with the IDs.
example:

Co Name ID
ABC company 60015 - 61014
ABC company 62420 - 62619 and not in 62591


There are only 7 instances where the company is the same and the ID are
listed as above. But there is only one instance of the ID in the second
example with "and not in #". All the other instances like the examples above
have numbers in the hundreds and thousands in between. I just need them to
be listed in my Access table consequtively. Could you please how to make
this work? Thank you very much.
 
Totally,

Well, it's not actually all that difficult. You wouldn't use a macro, which
quickly get awkward when doing things that involve repetitive actions. It
would be much better to use a VBA procedure.

The first step is to import the data from the Excel file into a table in
your Access database. This will be a table to temporarily hold the imported
data, until you process it into its final destination table.

So you need a table with the 2 text fields for CoName and ID, to mirror the
data from Excel. Lets call this table HoldingCompany. And you could use a
TransferSpreadsheet action/method to do the import.

And then, you need a table (let's call it FinalCompany) with 2 fields CoName
and ID_Num, with ID_Num being a Number (Long Integer) data type.

Ok, and then your code will be something like this (untested "air code"!)...

Dim rstFrom As DAO.Recordset
Dim rstTo As DAO.Recordset
Dim StartID As Long
Dim EndID As Long
Dim ExcludeID As Long
Dim i As Integer
Set rstFrom = CurrentDb.OpenRecordset("SELECT * FROM HoldingCompany",
dbOpenSnapshot)
Set rstTo = CurrentDb.OpenRecordset("SELECT * FROM FinalCompany",
dbOpenDynaset)
With rstFrom
Do Until .EOF
StartID = Val(![ID])
EndID = Val(Mid(![ID], InStr(![ID], "-") + 1))
If ![ID] Like "*not in*" Then
ExcludeID = Val(Mid(![ID], InStrRev(![ID], " ")))
Else
ExcludeID = 0
End If
For i = StartID To EndID
If i <> ExcludeID Then
rstTo.AddNew
rstTo![CoName] = ![CoName]
rstTo![ID_Num] = i
rstTo.Update
End If
Next i
.MoveNext
Loop
rstTo.Close
.Close
End With
Set rstTo = Nothing
Set rstFrom = Nothing

Apply the code, along with the TransferSpreadsheet, on a suitable event in
your application.

--
Steve Schapel, Microsoft Access MVP


TotallyConfused said:
Thank you thank you for giving some hope. There are two columns in the
Excel
table one column for the company name and the other column with the IDs.
example:

Co Name ID
ABC company 60015 - 61014
ABC company 62420 - 62619 and not in 62591


There are only 7 instances where the company is the same and the ID are
listed as above. But there is only one instance of the ID in the second
example with "and not in #". All the other instances like the examples
above
have numbers in the hundreds and thousands in between. I just need them
to
be listed in my Access table consequtively. Could you please how to make
this work? Thank you very much.
Click to expand...



__________ Information from ESET Smart Security, version of virus signature database 4136 (20090606) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Back
Top