Excel script converted to Access

[Petzl]

Hi,

I am not sure if I have the right forum so please re-direct me if needed.

I had someone create a script for me in Excel to join multiple lines of text. It works perfectly trouble is the csv file I have to convert is 475000 lines so Excel won't do in one go and even splitting up the file it has memory errors.

So I am thinking Access would be the go but I don't know Access at all so can the following script be converted to Excel?

Sub sample()
Dim i As Long
Application.ScreenUpdating = False
For i = Range("a" & Rows.Count).End(xlUp).Row To 2 Step -1
If Cells(i, "a").Value = Cells(i + 1, "a").Value And Cells(i, "b").Value = Cells(i + 1, "b").Value Then
Cells(i, "d").Value = Cells(i, "d").Value & " " & Cells(i + 1, "d").Value
Rows(i + 1).Delete
End If
Next
Application.ScreenUpdating = True
End Sub

I am trying to achieve the following:

I have some data exported into a csv file from a proprietary DB called SPS and I need to be able to work with it in Excel.

It is currently in the format:
Company NoteID LineID Text
Company1 1 0 Text1
Company1 1 1 Text2
Company2 1 0 Text1
Company2 1 1 Text2
Company2 2 0 Text1
Company3 1 0 Text1
Company3 1 0 Text2
Company3 1 0 Text3

I need it in the format:
Company NoteID LineID Text
Company1 1 0 Text1 Text2
Company1 1 1
Company2 1 0 Text1Text2
Company2 1 1
Company2 2 0 Text1
Company3 1 0 Text1 Text2 Text3
Company3 1 0
Company3 1 0

NoteID refers to the occurrence of a note. If is says 1 then that refers to note 1. If the line ID says 0 then that refers to the first line of that note.If there is more that one line to the note then the line ID will say 2, 3 etc. So Company 3 has 1 Note with 3 lines. I then want to delete the unnecessary lines with no text.

I hope this is clear......let me know if I need to explain more clearly!

Thanks,

Petzl

 

[SkipVought]

Petz,

You would be miles ahead to access the SPS database directly, rathern than using a .csv file.

It would also be helpful to know how you intend to use the data once it's "imported." In many cases, stringing data into one field can render the usefulness of the data extremely difficult.

Skip,
[sub]
[red][/red]
[/sub]

 

[stevexff]

Skip has a very valid point here. The data you have are roughly normalised, which in database circles is generally perceived to be a Good Thing. You are proposing to denormalise them. What do you want to do with them afterwards?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[PHV]

Have a look here: faq701-4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Petzl]

Thanks for the responses guys......little bit out of my league I can see. I spoke to a friend who wrote something and got it sorted.

I really appreciate the responses though!

Thanks,

Petzl