Prev: How can i protect a formula and still allow copy and move data?
Next: AMVERAGIF Condition
From: Rich Stone on 4 Jun 2010 08:32
I have an old database that stored it's data in a sequencial format with
fixed lengths for each field. As Windows 7 64-bit no longer allows the
running of the database I need to access the data in another way so wish to
import it to excel. I can get as far as marking the first record's field
lengths but there does not seem to be a way of marking the end of the record.
Instead it leaves the rest of the data in the final field!

Can anyone suggest the best way of doing this? Thank you.
From: Luke M on 4 Jun 2010 08:39
Text to Columns, fixed length?

--
Best Regards,

Luke M
"Rich Stone" <RichStone(a)discussions.microsoft.com> wrote in message
news:2B939A81-1C32-4C07-BCFC-0ABF376F5ACD(a)microsoft.com...
>I have an old database that stored it's data in a sequencial format with
> fixed lengths for each field. As Windows 7 64-bit no longer allows the
> running of the database I need to access the data in another way so wish
> to
> import it to excel. I can get as far as marking the first record's field
> lengths but there does not seem to be a way of marking the end of the
> record.
> Instead it leaves the rest of the data in the final field!
>
> Can anyone suggest the best way of doing this? Thank you.


From: Gary Brown on 4 Jun 2010 09:49
You would need a macro for this.
1st you have to change that file from sequential to 1 line per record.
Since you know the exact fixed length of each field, you know the exact
fixed length of each record.
Using the OPEN, WRITE and CLOSE statements, you can grab each record length,
throw in a carriage return [Chr(13)], form feed [Chr(12)] or line feed
[Chr(10)] at the end of each record, save the file and then pull it up in
Excel using Text to Columns - fixed length.

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Rich Stone" wrote:

> I have an old database that stored it's data in a sequencial format with
> fixed lengths for each field. As Windows 7 64-bit no longer allows the
> running of the database I need to access the data in another way so wish to
> import it to excel. I can get as far as marking the first record's field
> lengths but there does not seem to be a way of marking the end of the record.
> Instead it leaves the rest of the data in the final field!
>
> Can anyone suggest the best way of doing this? Thank you.
From: Jackpot on 4 Jun 2010 10:32
You can try out the below macro to read this file and place this to excel
activesheet. If you are new to macros.. (Please note to change the
recordlength as needed)

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>


Sub Macro2()
Dim intFile As Integer, strFile As String
Dim intLen As Integer, lngRow As Long

strFile = "c:\test.txt"
intLen = 10

intFile = FreeFile
Open strFile For Input As #intFile
Do While Not EOF(intFile)
lngRow = lngRow + 1
Range("A" & lngRow) = Input(intLen, #intFile)
Loop
Close intFile
End Sub


"Rich Stone" wrote:

> I have an old database that stored it's data in a sequencial format with
> fixed lengths for each field. As Windows 7 64-bit no longer allows the
> running of the database I need to access the data in another way so wish to
> import it to excel. I can get as far as marking the first record's field
> lengths but there does not seem to be a way of marking the end of the record.
> Instead it leaves the rest of the data in the final field!
>
> Can anyone suggest the best way of doing this? Thank you.
 | 
Pages: 1
Prev: How can i protect a formula and still allow copy and move data?
Next: AMVERAGIF Condition