Text To Columns Macro (to avoid out of memory error)

[dougf]

I am trying to convert cells (all in column D) which are separated by "~" into columns. Unfortunately, running the text to columns command on several rows at a time can cause Excel to panic with an out of memory error (error #7 etc.).

The file is ~100mb and contains 500k-700k rows (I have 4GB of RAM so I know this is more a limit of Excel's 2GB RAM constraint).

Can you please help me write a macro to text to column convert each cell in column D?

I tried a macro which started with a for loop, and called the function for each cell individually, but even this led to an out of memory exception after 156,000 rows (although the same macro worked fine on a similar sheet with 700,000 rows).

Are there any other ways of clearing the Excel buffer/temporary space during the function calls to avoid causing Excel to crash?

Thanks!

 

[VoG]

Try this - launch the macro then go make a cup of coffee or whatevver

Sub TTC()
Dim LR As Long, i As Long, j As Integer, X As Variant
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
LR = Range("D" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("D" & i)
        X = Split(.Value, "~")
        For j = LBound(X) To UBound(X)
            .Offset(, j).Value = X(j)
        Next j
    End With
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
E

 

[Legacy 14611]

Dougf,

Several comments:

1. Before posting I generated 1,000,000 rows of test data for text to cols with about 20 ~'s to separate data over next 20 cols to right of colD. With 2gb memory it ran fine. Timed that at 8.6 seconds on the excel timer.

2. That code ran in computer memory rather than via the worksheet. Faster that way, but uses more memory.

3. You can modify it, as in the following, to run via your worksheet. This should save on memory use, but will take longer to run. If it gives an error message, you'll also be able to read off from your worksheet how many rows it does before giving error. This should help to track down the problem.

Sub txt_to_colcells()
Dim n As Long, i As Long, j As Long, y
n = Cells(Rows.Count, "d").End(xlUp).Row
For i = 1 To n
    y = Split(Cells(i, "d"), "~", -1)
For j = 0 To UBound(y)
    Cells(i, j + 5) = y(j)
Next j, i
End Sub

 

[PCL]

Hi dougf,
I"m a bit stuck because it looks like XLS2007 don't accept the statement RIGHT when it's OK for XLS_XP, I will investigate.
NB:
In VoG code the last statement E certainly needs to be changed by END SUB

 

[dougf]

Excel still manages to lock up (there are many more than 20 columns, all the way up to ABE).

VoG,

Your macro worked well. Is there a quick change we can make to force excel to treat the new cells as text? It's strange but Excel decided to round values and add in a zero in front of all of the cells because every entry should either start with a "." or a "D." and thus appears like a decimal which it is not.

Rugila,
Your macro also works well. But, here's the killer, I need to get rid of the original column D, and now this causes an out of memory error as soon as I click the button... I have a feeling some Microsoft programmer left some functions that require ints instead of longs, or an old maximum array size.

I assume solving this is just a matter of adding an extra line into the formula to shift the previous row one left?

Thanks again!

 

[dougf]

Another problem I noticed is that when Excel tries to save the file (Save As...) after the file has its new columns, a new window appears saying "Trying to connect to folder" and displays "Contacting C:\.[...]..\Documents"

Any thoughts on a way to also force excel to save the document without panicking like this?

 

[GTO]

Greetings,

Not sure about #15, but was curious as to whether you have tried breaking the text-to-columns up in chunks?

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Text2Col_GetChunks()<br>    <br>Dim _<br>lLRow           <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br>lRemainder      <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br>lChunkCount     <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br>i               <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br>rngCurrent      <SPAN style="color:#00007F">As</SPAN> Range<br>    <br><SPAN style="color:#007F00">'Dim Start As Single: Start = Timer</SPAN><br>    <br><SPAN style="color:#00007F">Const</SPAN> CHUNK_SIZE <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 10000    <SPAN style="color:#007F00">'<---Increase to suit.</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet <SPAN style="color:#007F00">'Sheet2</SPAN><br>        <br>        lLRow = .Cells(Rows.Count, "D").End(xlUp).Row<br>        <br>        lChunkCount = lLRow \ CHUNK_SIZE<br>        lRemainder = lLRow Mod CHUNK_SIZE<br>        <br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> lChunkCount<br>            <SPAN style="color:#00007F">Set</SPAN> rngCurrent = .Range(.Cells((i * CHUNK_SIZE + 1) - CHUNK_SIZE, "D"), _<br>                                    .Cells(i * CHUNK_SIZE, "D"))<br>            <br>            rngCurrent.TextToColumns Destination:=rngCurrent(1, 1), _<br>                                     DataType:=xlDelimited, _<br>                                     TextQualifier:=xlTextQualifierNone, _<br>                                     Other:=True, OtherChar:="~"<br>        <SPAN style="color:#00007F">Next</SPAN><br>        <br>        <SPAN style="color:#00007F">If</SPAN> lRemainder > 0 <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> rngCurrent = .Range(.Cells((i * CHUNK_SIZE + 1) - CHUNK_SIZE, "D"), _<br>                                    .Cells(.Cells( _<br>                                            (i * CHUNK_SIZE + 1) - CHUNK_SIZE, "D").Row _<br>                                            + lRemainder - 1, "D" _<br>                                           ) _<br>                                    )<br>            <br>            rngCurrent.TextToColumns Destination:=rngCurrent(1, 1), _<br>                                     DataType:=xlDelimited, _<br>                                     TextQualifier:=xlTextQualifierNone, _<br>                                     Other:=True, OtherChar:="~"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <br><SPAN style="color:#007F00">'Debug.Print "Text2Col_GetChunks: " & Timer - Start</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

NOTE: Could you specify which of Rugila's you are referring to?

Hope this helps,

Mark

 

[dougf]

Mark,

Thanks. I am responding to Rugila's second (optimized) formula.

Will your formula properly keep the decimal-looking cell contents as the text they should remain?

Best,
Doug

 

[GTO]

Hi Doug,

I don't have the data you have in front of you. I do believe the code is worth testing, as at least with the fake data I cobbled together, the speed increase was noticeable.

In the data you are parsing, can we count on a certain amoount of columns, or do the number of columns change from row to row?

Mark

 

[GTO]

ACK! Dang ten minute limit and a slow typist do not mix.

Anyways, I tossed in:

.00001~orange~pecos~D2.2~Lime~.10~orange~pecos~2.2.2~Lime~00.001

... and it seems to work fine.

Mark

 

[dougf]

ACK! Dang ten minute limit and a slow typist do not mix.

Anyways, I tossed in:

.00001~orange~pecos~D2.2~Lime~.10~orange~pecos~2.2.2~Lime~00.001

... and it seems to work fine.

Mark

GTO,

The problem is many of these identifiers begin with a period and are 7 digits long. Excel is still adding in the 0 prefix, and truncating off zeros at the end, which invalidates the identifier.