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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|