Faster way to read data from old file to new

[Roger Govier]

I am having to convert several files of data from an old DOS program and
read them into Excel.
Each file has between 10,000 and 12,000 records of 229 bytes
The records are all contiguous data within the files, with Chr(255)+Chr(255)
determining the start of each new record.
With Excel 2007, I was able to easily use Data>Text to Columns, setting
Chr(255) as the delimiter and each record of 227 bytes (without the
Chr(255)'s) was created in a separate column on row 1 of the file.
A simple Copy>Paste Special>Transpose allowed me to turn this into 10,139
rows (for the first file) each with 227 characters in column A.

Each of these 227 byte records, is made up of 67 fields of varying lengths.
These I have listed on another sheet with the length of each field and it's
starting position.

Whilst the short piece of code shown below does work and extracts all of the
data into the relevant columns for me, I was wondering whether there was any
faster way of effecting the conversion.
I am using Vista SP1 and XL2007 SP1

Sub CreateRecords()

Dim wss As Worksheet, wsd As Worksheet, wst As Worksheet
Dim i As Long, j As Long, lr As Long, start As Long, length As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wss = ThisWorkbook.Sheets("OldData")
Set wsd = ThisWorkbook.Sheets("NewData")
Set wst = ThisWorkbook.Sheets("Definition")

lr = wss.Cells(Rows.Count, "A").End(xlUp).Row

For j = 2 To lr
' row 1 on Source is blank, row 1 on Destination is a Header row
For i = 1 To 67
start = wst.Cells(i, 3).Value: length = wst.Cells(i, 2).Value
wsd.Cells(j, i) = Mid(wss.Cells(j, 1), start, length)
Next i
Next j
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

 

[Dave Peterson]

After you've transposed the data, you could record a macro that parses your data
(Data tab|Data tools group|text to columns).

You'll have to separate & specify all 67 fields again, but once it's done, it's
done.

Well, until the layout changes!

Maybe you could do all of it in one macro.

Create a new workbook and start record when you open the file, copy|transpose,
and data|text to columns. (And continue formatting (print headers & footers,
column headers, freeze panes, autofilter, pivottables...)

Then save that new workbook with a nice name:
WorkbookToOpenParseAndFormatDOSOutput.xlsm

And just open that whenever you need to do it again.

 

[Roger Govier]

Hi Dave

Many thanks for the suggestion. I have just managed to get back to the
problem this morning.
The code I wrote worked absolutely fine, but naturally was very slow.
My mind had become fixated on reading the data and chopping it into the
appropriate length strings.
The problem with Data>text to Columns, was that there are many fields which
are empty. Trying to "eyeball" where the column dividers should be placed
was very difficult, so I discarded that.
However, following your suggestion about using a macro (also made to me by
Debra in a private email -great minds think alike eh!!), I decided to record
a macro on a copy set of data, letting Excel make it's own decision as to
where the splits should be, then manually amend the macro with the field
sizes as per the values in my Definition sheet and setting the range that is
to be used for carrying out the conversion.

The resulting code, which worked perfectly (and instantaneously) is shown
below as it may help others approaching this type of problem
Many thanks for "dragging" me out of my fixed way of looking at the problem.

Sub ParseToColumns()
Dim wss As Worksheet
Dim lr As Long
Set wss = ThisWorkbook.Sheets("OldData")
lr = wss.Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(2, 1), Cells(lr, 1)).Select

Selection.TextToColumns Destination:=Range("A2"), DataType:=xlFixedWidth,
_
FieldInfo:= _
Array(Array(0, 1), Array(14, 1), Array(17, 1), Array(23, 1),
Array(26, 1), _
Array(35, 1), Array(38, 1), Array(47, 1), Array(50, 1), Array(53,
1), Array(54, 1), _
Array(57, 1), Array(67, 1), Array(77, 1), Array(79, 1), Array(81,
1), Array(91, 1), _
Array(92, 1), Array(95, 1), Array(105, 1), Array(108, 1), Array(111,
1), Array(113, 1), _
Array(117, 1), Array(119, 1), Array(121, 1), Array(123, 1),
Array(125, 1), Array(127, 1), _
Array(130, 1), Array(132, 1), Array(133, 1), Array(135, 1),
Array(137, 1), Array(140, 1), _
Array(143, 1), Array(145, 1), Array(147, 1), Array(148, 1),
Array(149, 1), Array(150, 1), _
Array(158, 1), Array(159, 1), Array(163, 1), Array(165, 1),
Array(166, 1), Array(168, 1), _
Array(170, 1), Array(173, 1), Array(176, 1), Array(178, 1),
Array(180, 1), Array(182, 1), _
Array(184, 1), Array(186, 1), Array(188, 1), Array(191, 1),
Array(193, 1), Array(195, 1), _
Array(199, 1), Array(203, 1), Array(208, 1), Array(213, 1),
Array(216, 1), Array(219, 1), _
Array(222, 1), Array(225, 1))

End Sub

 

[Dave Peterson]

I don't think I'd trust excel to guess at the field lengths. But if it worked
ok for you, then congrats.

I might have edited a text file (in notepad--or another text editor) and put a
dummy record in near the top to help get the columns where I wanted them.

Letting excel guess at anything always scares me.

 

[Roger Govier]

It wasn't a case of trusting Excel to get the parsing correct.
It was just letting it create a macro, so that I could see the form it took
for specifying the layout.

Once I had that, I created the array from scratch, using the values that I
already knew for field lengths.

 

[Dave Peterson]

Ahhh.

I thought you might have used the data on that other sheet to create the code
that you needed. Or even used that data to create the array you needed.

It wasn't a case of trusting Excel to get the parsing correct.
It was just letting it create a macro, so that I could see the form it took
for specifying the layout.

Once I had that, I created the array from scratch, using the values that I
already knew for field lengths.