Import Advice

[deejay_totoro]

Import advice and help!

Hello,

I have a strange difficult problem I would really appreciate help with!

I have to import some data from a excel spreadsheet. The data is basically a list of shops with some information about work that has been done to them.

The problem is the format:

The excel spreadsheet looks like this:

[StreetField] MyStreetName
[NumberField] PropertyNumber
[OtherField] otherData

But! The problem is the data is not listed by individual property. Instead it looks like this:

[StreetField] MyStreetName
[NumberField] PropertyNumber, PropertyNumber, PropertyNumber, PropertyNumber, PropertyNumber
[OtherField] otherData

So each individual property number is stored in one cell, separated only by a comma.

Does anyone have any idea how I might split this data? I would like to see each individual house number in a separate cell/field.

Anyhelp would be greatly appreciated!

Thanks!
dj_T

 

[theprez]

It may be difficult to get help on this, unless we can see the spreadsheet. My initial question is how does the Streefield and Otherdata relate to property number, especially since there is multiple properties in one cell. How are the fields related?

 

[jeremie_ingram]

How many numbers per street? How many other per number?

Obviously there can be 0 or more numbers to every street, but for every number there has to be 1 street (1 to many relationship). Now, if there can be 0 OR MORE other fields to each number, but only one Number field per other (a possible second 1 to many).

Once you get the data straightened out, you will have to start breaking in into the proper NORMALIZED format.
How many records are you talking about? 100? 1,000,000? Is it so many that it is necessary to use an import, or would it just be overall easier to re-enter the data?

 

[deejay_totoro]

Example

Thanks for the replies.

It may well be that the best way to handle this data is to normalise the spreadsheet data the old fashioned way...

However, here is an example of what I am talking about (see attached file).

There is a tremendous amount of records! So if there was an automated way of doing this - that would be great!

thanks!

dj_T

 

[jeremie_ingram]

This could be done via script, but thats not really my speciality. I would suggest searching the forum for importing from excel since I am sure I have seen something similiar posted in the past. If you cant find what you need, I would post in the MODULES AND VBA part of the forum.

 

[theprez]

Yes this can be done. You will need to import the spreadsheet into access keeping the street numbers together in one field when it gets to access in a temptable.

Then you will need to write a function that creates a new table. It will read through each record in the temptable (use the recordset methodology).

You will send flsStreet, fldRooms, fldOther data from the temp table to corresponding fields in the new table. Then you will need to manipulate or parse through the numberfield and pick off each number using the "," as your delimiter. When you get a valid number, send over the number to the new table and then add the record. When you have all the numbers, get the next record from the temptable. Let me know if this helps. If you have a lot of records, you may want to spend time writing this. If I have time later I can maybe right some code to do this, but a little busy now.

 

[deejay_totoro]

Thanks for the reply.

I understand what you mean, that makes sense.

The problem is that I have no idea how to begin writing such code or how it works.

It would be really great if you could help me with this - but it seems like a difficult (and interesting!) thing to explain

Thanks again!

dj_T

Yes this can be done. You will need to import the spreadsheet into access keeping the street numbers together in one field when it gets to access in a temptable.

Then you will need to write a function that creates a new table. It will read through each record in the temptable (use the recordset methodology).

You will send flsStreet, fldRooms, fldOther data from the temp table to corresponding fields in the new table. Then you will need to manipulate or parse through the numberfield and pick off each number using the "," as your delimiter. When you get a valid number, send over the number to the new table and then add the record. When you have all the numbers, get the next record from the temptable. Let me know if this helps. If you have a lot of records, you may want to spend time writing this. If I have time later I can maybe right some code to do this, but a little busy now.

 

[theprez]

Check back here, I will take a shot and send you the code.

 

[deejay_totoro]

thanks

cheers mate!

& good luck.

dj_T

 

[theprez]

Here is the function. Run it from the immediate window or call from a button.
Before running, you must import the spreadsheet. When importing, tell the import wizard that the first row is the column heading. Also when asked about the primary key select "No primary key". Give this new table the name of "Data". The field names MUST be exactly like you posted in the bit map. Create a new table called "Shops" and give it the same field names (fldStreet, fldNum, fldRooms, FldOther). Also one note, this function assumes the numbers are separated by a comma. If not, it may not work properly.
Good luck. Let me know. The new formated rows will be in the "Shops" table.
Sorry for the format, my code is nicely indented.

Function Convert()
Dim rsInput, rsOutPut As Recordset
Dim varNum As String
Dim intLength, intCnt As Integer
Dim db As Database

Set db = CurrentDb
Set rsInput = db.OpenRecordset("Data")
Set rsOutPut = db.OpenRecordset("Shops")

Do While Not rsInput.EOF
intLength = Len(rsInput!fldnum)
intCnt = 1
Do Until intCnt > intLength
If IsNumeric(Mid(rsInput!fldnum, intCnt, 1)) Then
varNum = varNum & Mid(rsInput!fldnum, intCnt, 1)
ElseIf Mid(rsInput!fldnum, intCnt, 1) = "," Then
rsOutPut.AddNew
rsOutPut!fldstreet = rsInput!fldstreet
rsOutPut!fldnum = Val(varNum)
rsOutPut!fldrooms = rsInput!fldrooms
rsOutPut!fldother = rsInput!fldother
rsOutPut.Update
varNum = ""
End If
intCnt = intCnt + 1
Loop
rsOutPut.AddNew
rsOutPut!fldstreet = rsInput!fldstreet
rsOutPut!fldnum = Val(varNum)
rsOutPut!fldrooms = rsInput!fldrooms
rsOutPut!fldother = rsInput!fldother
rsOutPut.Update
varNum = ""
rsInput.MoveNext
Loop
rsInput.Close
rsOutPut.Close
End Function

 

[deejay_totoro]

Can't work

Hello again,

Now this is probably my lack of experience - BUT! I can't get it to work

This is what I did:

Created a blank form.
Added a button (then canceled the wizard)
Used "Build Event" to enter VBA editor.

Inside VBA, I went to the Click Proceedure and pasted your code. However this didnt work, so I removed the Function Convert() and changed the last line to End Sub.

This is the only way I could think of running the code by mouse click.

When I click on the button on the form, I get this:

Run-time error '3078':
The MS Jet database engine cannot find the input table or query "Shops"...

Now - the table really is there, honestly!


If I try to run your code directly in the immediate pane, its says:

Compile Error
Invalid in immediate pane.

Where I have gone wrong?

Thanks again!

dj_T

PS: Should I move this post to the VBA section? How do I do that?



Private Sub Command0_Click()

Dim rsInput, rsOutPut As Recordset
Dim varNum As String
Dim intLength, intCnt As Integer
Dim db As Database

Set db = CurrentDb
Set rsInput = db.OpenRecordset("Data")
Set rsOutPut = db.OpenRecordset("Shops")

Do While Not rsInput.EOF
intLength = Len(rsInput!fldnum)
intCnt = 1
Do Until intCnt > intLength
If IsNumeric(Mid(rsInput!fldnum, intCnt, 1)) Then
varNum = varNum & Mid(rsInput!fldnum, intCnt, 1)
ElseIf Mid(rsInput!fldnum, intCnt, 1) = "," Then
rsOutPut.AddNew
rsOutPut!fldstreet = rsInput!fldstreet
rsOutPut!fldnum = Val(varNum)
rsOutPut!fldrooms = rsInput!fldrooms
rsOutPut!fldother = rsInput!fldother
rsOutPut.Update
varNum = ""
End If
intCnt = intCnt + 1
Loop
rsOutPut.AddNew
rsOutPut!fldstreet = rsInput!fldstreet
rsOutPut!fldnum = Val(varNum)
rsOutPut!fldrooms = rsInput!fldrooms
rsOutPut!fldother = rsInput!fldother
rsOutPut.Update
varNum = ""
rsInput.MoveNext
Loop
rsInput.Close
rsOutPut.Close

End Sub

 

[theprez]

I wouldn't move the post. To run an immiedate window you must place a "?" in front of the function name, but running off the mouse click should be fine. Again, what line of code specifically is the debugger going to on the error.
Worse case, if I had the spreadsheet, I could run it through my code and send you back the results. I assume, this is a one shot deal and not something you need to run over and over again.

 

[dakcg]

Excel Text to Columns function

Hi,

I am not sure if you solved your problem, but I wanted you to know that Excel has a wizard that performs that function for you. First move the column to a new datasheet and highlight the whole column. Then click Data>Text to Columns. The wizard will guide you through the rest of the process.

dakcg