A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Import old dos sequencial data



 
 
Thread Tools Display Modes
  #1  
Old June 4th, 2010, 01:32 PM posted to microsoft.public.excel.worksheet.functions
Rich Stone
external usenet poster
 
Posts: 52
Default Import old dos sequencial data

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.
Ads
  #2  
Old June 4th, 2010, 01:39 PM posted to microsoft.public.excel.worksheet.functions
Luke M[_4_]
external usenet poster
 
Posts: 451
Default Import old dos sequencial data

Text to Columns, fixed length?

--
Best Regards,

Luke M
"Rich Stone" wrote in message
...
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.



  #3  
Old June 4th, 2010, 02:49 PM posted to microsoft.public.excel.worksheet.functions
Gary Brown[_6_]
external usenet poster
 
Posts: 61
Default Import old dos sequencial data

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.

  #4  
Old June 4th, 2010, 03:32 PM posted to microsoft.public.excel.worksheet.functions
Jackpot
external usenet poster
 
Posts: 28
Default Import old dos sequencial data

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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:22 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 OfficeFrustration.
The comments are property of their posters.