Text file import

[ElEdwards]

I work in television and one of the pieces of equipment I'm responsibile for is called a 'stillstore', which I'll refer to as SS.

The SS has a database capable of storing 10,000 images, each with a unique number (from .0000 to .9999) and a description for each. The only method the SS has for exporting database info is for a Generic/Text printer to be setup on the PC, then print to this, specifying the filename, such as "stills.txt".

So far, so good......

When I import this into Excel, the result is all in Column A:

A1   .0000
A2   Description of .0000
A3   .0001
A4   Description of .0001
A5   .0002
A6   Description of .0002

....and so on for thousands of rows.

Here's what I want to do:
I want to massage everything in this single column, so the info ends up in two columns:

          COLUMN A        COLUMN B
A1        .0000             Description of .0000
A2        .0001             Description of .0001
A3        .0002             Description of .0002

.....and so on.

What I'm trying to do is break the data into two columns:
- in the original import, cells A1, A3, A5...etc are all Column A and renumbered to A1, A2, A3...etc.
- then cells A2, A4, A6...etc become Column B and are renumbed B1, B2, B3...etc.

Thoughts?

I've tried massaging this manually but I go blind after a very short time

 

[hatman]

Sounds the delimiter for the file is the carriage return? Have you tried using the Data -> Get External Data -> Import Text File? I don't think that will do it, but it's worth a try if you haven't.

I cut my teeth on BASIC and GW BASIC back in the 80's and 90's, so I usially end up approaching this stuff with a hammer and chisel, so to speak, rather than pre-packaged toolboxes... so I'll give you an approach, but I wouldn't be suprised if someone recommends something more efficient:

Sub Parse_File()

    Dim FileToOpen
    
    Dim tmp As String
    
    ChDrive (Left(ActiveWorkbook.Path, 1))

    ChDir ActiveWorkbook.Path

    FileToOpen = Application.GetOpenFilename(Title:="Please choose a file to import", FileFilter:="Text Files *.txt (*.txt),")

    If FileToOpen = False Then

        MsgBox "No file specified.", vbExclamation, "Duh!!!"

        Exit Sub

    End If
    
    Open FileToOpen For Input As 1
    
    cnt_row = 1
    
    'Application.Calculation = xlCalculationManual
    'Application.ScreenUpdating = False
    
    Do Until EOF(1)
    
        Line Input #1, tmp
        
        Range("a" & cnt_row).Value = tmp
        
        Line Input #1, tmp
        
        Range("b" & cnt_row).Value = tmp
    
        cnt_row = cnt_row + 1
            
    Loop
    
    Close #1

    'Application.Calculation = xlCalculationAutomatic
    'Application.ScreenUpdating = True
    
End Sub

I would step through this one line ata time and watch the worksheet in the background and make sure that things are going where you expect... once it's debugged, un-comment the screenupdating and calculation lines to speed the process up.

THIS CODE IS UNTESTED... in it's urrent fomr... I snagged it from a working macro and lobotomized it for your needs... so it should be pretty clean.

 

[Andrew Poulsom]

Try this:

Sub Test()
    Dim FileName As String
    Dim FileNum As Integer
    Dim r As Long
    Dim Counter As Long
    Dim Data As String
'   *** Change path and file name to suit ***
    FileName = "C:\Temp\stills.txt"
    FileNum = FreeFile
    r = 1
    Counter = 1
    Worksheets.Add
    Open FileName For Input As #FileNum
    While Not EOF(FileNum)
        Line Input #FileNum, Data
        If Counter Mod 2 = 1 Then
            ActiveSheet.Cells(r, 1) = Data
        Else
            ActiveSheet.Cells(r, 2) = Data
            r = r + 1
        End If
        Counter = Counter + 1
    Wend
    Close #FileNum
End Sub

Similar to Hatman's but tested.

 

[ElEdwards]

Thanks I've had to fiddle a bit but I have it working!! :D