Importing into Access

  • Thread starter Thread starter jenniferspnc
  • Start date Start date
J

jenniferspnc

In Excel I have several cells that have hundreds of account numbers. I know
there is a limit on the view in Excel unless you click on the formula bar,
which is where I can see my entire list of account numbers. How can I get
this to import into Access correctly? In each cell in the spreadsheet I have
a comma separating the account numbers.

I need to see all the account numbers for a customer (but it appears it only
imports a certain number of account numbers even after changing from text to
memo). Honestly, instead of having all the account numbers for one customer
dumped into one cell, I would prefer to have multiple customer entries with
one account number (unless that's a bad idea). Is there a way to do either
without keying in all the entries? Hope my question is clear. Thank you!!

What Excel looks like:
Customer US EMEA
APAC
XYZ "100's of accounts" "100's of accounts" "100's
of accounts"

Would like it to be like:
Customer Region Account
XYZ US 1234
XYZ US 5678
 
Hi Jennifer,

One way would be to copy and paste the contents of one cell (let's say
XYZ's EMEA accounts) into Word. Then:

1) replace all
,
with
^pXYZ,EMEA,
.. This will give you a comma-separated list containing all the records
corresponding to that cell.

2) Tidy up the beginning and end of the list as necessary. Add a
header line
Customer,Region,Account

3) Save as a text file without formatting and import into Access.

Repeat for the remainder.

If this is going to be a regular task it can be automated, but for a
one-off I'd do it that way.
 
Jennifer,

Another option would be to use automation to open the Excel file, then loop
through the cells. Assuming that the account numbers in your spreadsheet are
all separated by commas, something like the following (untested) should work:

Public sub ImportFromExcel

Dim strSQL As String, intLoop As Integer
Dim strCustomer As String, arAccounts() As String
Dim RowPointer As Long

Dim xlobj As Excel.Application
Dim wbk As Excel.Workbook
Dim sht As Excel.Worksheet

Set xlobj = CreateObject("Excel.Application")
xlobj.Visible = True

Set wbk = xlobj.Workbooks.Open("C:\Temp\Test.xls")
Set sht = wbk.Sheets("Sheet1")
sht.Activate

RowPointer = 2
While sht.Cells(RowPointer, 1) <> ""
strCustomer = sht.Cells(RowPointer, 1)
arAccounts = Split(Replace(sht.Cells(RowPointer, 2), " ", ""), ",")
For intLoop = LBound(arAccounts) To UBound(arAccounts)
strSQL = "INSERT INTO yourTable (Customer, Region, Account) " _
& "VALUES('" & strCustomer & "', 'US'" _
& "'" & arAccounts(intLoop) & "')"
currentdb.execute strSQL
Next
RowPointer = RowPointer + 1
Wend

Set sht = Nothing
Set wbk = Nothing
xlobj.Quit

End Sub

Basically, this opens Excel and loops through each of the rows in sheet1.
If the value in column 'A' is not blank, then pulls the data from column 2
and inserts it into a table. You will need to expand this to do the same for
the EMEA column and whatever other columns you have for each of the rows.

If your Account# column can have leading zeros, you need to make sure that
your Account# column is formatted as a string.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Back
Top