Import Excel db and splitting table cells

[Christine]

Hi there,

I was pointed to a discussion in this forum about splitting tables, but I
don't think that a code solution is what I need, although I would be willing
to entertain it some more. I searched this group and I don't see anything
that sounds like it would work for me.

I need to import an Excel worksheet into Access.

The data is all in one column, but seperated by commas (ie, apples,
oranges, bananas).

I am aware of the text to columns function in Excel, but when I do this, I
end up with 20+ columns and no way to make them all into one column.

Is there a way to import the data into Access and put each word into a
single cell? Or is there a way in Excel I can do this? Right now I'm copying
my Excel column and pasting it into Word, then replacing all the commas with
paragraph tags so I can get a column of words.

Thanks,
Christine

 

[Klatuu]

I think if you save the xls file as a csv (comma separated values) file, and
create an import specification and use that in a TransferText, you might be
able to make it work.

 

[Jeanette Cunningham]

Christine,
using Word to separate the words is a pretty quick and painless way to do it
if it is a once off import.
Are you planning to import a file like this excel file quite often?


Jeanette Cunningham

 

[Christine]

Hi Jeanette,

Yes, I do this quite often as I work with metadata supplied by vendors. I
was helped in another MS group to streamline my process using an Access
database, and I have eliminated a couple of steps by creating macros to paste
my unformatted text and replace the commas in Word. The person who helped me
felt I should be able to import my data right into Access and skip the Word
step.

Best,
Christine

 

[Christine]

Hi Dave,

Thanks for your suggestion. I saved the data as a .csv. I created a macro to
hold an import specification, but I must be specifying something wrong. My
data still ends up in one field with everything still seperated by a comma.
What else can I do?

Thanks,
Christine

 

[Klatuu]

You need to specify that fields are separated by commas.

 

[Jeanette Cunningham]

Christine,
I have put together some code that will let you split up the 'fruit' into
separate pieces of 'fruit'.
Note I have used your analogy of fruit for this example.
I created a table called tblImportToSplit with one field called ImportData

I added fruit like this
apples,banana,grapes
orange,mandarin
lemon
pineapple,pear,apple

I created a new table to hold the separate pieces of fruit - called tblFruit
with one field called Fruit

Here is the code
---------------------------------------------

Private Function SeparateTheFruit()
'Next, use the Split function to put the different options in an array:
Dim strWords() As String
Dim strSQLImport As String
Dim strSQLSplit As String
Dim db As DAO.Database
Dim rstImport As DAO.Recordset
Dim intCounter As Integer
Dim strVariableToSplit As String
Dim strFruitInsert As String

Set db = CurrentDb
strSQLImport = "SELECT tblImportToSplit.ImportData " _
& "FROM tblImportToSplit"
'Debug.Print strSQLImport

' Open a recordset on the query for the data to export
Set rstImport = db.OpenRecordset(strSQLImport)
' If there are no records, return an error and exit function
If rstImport.EOF Then
'handle error here
Else
' Use the recordset as a base
With rstImport
' Process until end of file
Do While Not .EOF

strVariableToSplit = rstImport("ImportData")


strWords = Split(strVariableToSplit, ",")

For intCounter = LBound(strWords) To UBound(strWords)
'Debug.Print strWords(intCounter)
strFruitInsert = strWords(intCounter)
strSQLSplit = "INSERT INTO tblFruit ( Fruit ) " _
& "VALUES ( """ & strFruitInsert & """) "
'Debug.Print strSQLSplit
db.Execute strSQLSplit, dbFailOnError

Next intCounter

rstImport.MoveNext

Loop
End With
End If


FunctionExit:
If Not rstImport Is Nothing Then
Set rstImport = Nothing
End If
If Not db Is Nothing Then
Set db = Nothing
End If
Exit Function

FunctionErr:
MsgBox Err.Number & " " & Err.Description
Resume FunctionExit

End Function
-----------------------------------------------

To use the code, I put a button on a form.
I name the button cmdSplitFruit

I put code like this
-----------------------------------------------
Private Sub cmdSplitFruit_Click()
Call SeparateTheFruit
End Sub
-----------------------------------------------

The puts each fruit from tblImportToSplit into a separate record in
tblFruit.


Jeanette Cunningham

 

[Christine]

You need to specify that fields are separated by commas.

Hi again,

I tried importing the CSV file again and double-checked that I was checking
off the comma checkbox. It doesn't seem to be working.

Thanks,
Christine

 

[Christine]

Hi Jeanette, thanks so much for the code! I'm eager to try it out, but I have
run into a snag. I created the two tables as you specified, and I know how to
create buttons on a form, but I do not know where to paste the code. I tried
pasting it as an SQL query, but got an error message that it was expecting
'Delete, Insert, Procedure, Select or Update' when I tried to save it.

Christine

 

[Jeanette Cunningham]

Christine,
this is a lot to master when you are learning access.
There are 2 pieces of code - that is 2 routines.
One of them goes on the On Click event of the button
That is this code
-----------------------------------------------
Private Sub cmdSplitFruit_Click()
Call SeparateTheFruit
End Sub
-----------------------------------------------


The other code goes in the same code window as the code above.
To put it in the code window, move the cursor down a couple of lines from
the code for the button.

Copy and paste the code where the cursor is.
Do a save and a compile from the code window Debug | Compile

Make sure you still have those 2 tables you created.

Open the form and click the button.


Jeanette Cunningham

 

[Christine]

Hi Jeannette,

You're a star! Thank you so much, that worked. I am pretty handy with
Access, but haven't tried much in the way of code building or events. This
will make my life a lot easier!

Best,
Christine

 

[Jeanette Cunningham]

Christine,
we love to help.



Jeanette Cunningham -- Melbourne Victoria Australia