Re: Faster way to read data from old file to new

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 wrote:

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 *** 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 Work***, wsd As Work***, wst As Work***
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

--

Regards
Roger Govier

--

Dave Peterson
.