Help! Convert to tab delimited file?

Myriad_Rocker

Questioning Reality
Local time
Today, 04:21
Joined
Mar 26, 2004
Messages
166
I have an objective that I need to do and I thought, at first, that it was just to import in a text file using VBA...but now I'm not sure. Here's the objective:

Write a program (do not use DoCmd.TransferText) to convert "test.txt" to a tab delimited file.
All fields must be trimmed of extra spaces.
Include a form to specify the input and output and a button to run the conversion.

So...where do I start? Any hints, tips, or tricks are mucho appreciated.

I have attached the text time I'm supposed to be working with.
 
Last edited by a moderator:
Thanks for that. I tried it...bombs out here...

Code:
rst.Open "TextImportTest", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Says "Invalid SQL statement; expected 'DELETE'....etc"
 
Bob, I found this in another of your posts and it is exactly what I'm looking for...

However, how does it know what the file name is?

I guess you pass it to the sub from the form when you call the sub?

Code:
Public Sub ConvertTextFile(strInputFile As String, strOutputFile As String)

    Dim strInputString As String
    Dim strOutputString As String
    Dim varSplit As Variant
    Dim intUB As Integer
    Dim intCount As Integer
    
    Open strInputFile For Input As #1
    Open strOutputFile For Output As #2

    Do Until EOF(1)
        Line Input #1, strInputString
        varSplit = Split(strInputString, "|", , vbTextCompare)
        intUB = UBound(varSplit)
        intCount = 0
        While intCount <> intUB + 1
            strOutputString = strOutputString & varSplit(intCount) & Chr(9)
            intCount = intCount + 1
        Wend
            Print #2, strOutputString
            strOutputString = ""
            intUB = 0
    Loop
    Close #1
    Close #2

End Sub
 
You don't open the text file in the recordset. You open a table for that. If you are going from one text file to another open the other text file like you did the other except use:

Dim strFileInput As String
Dim strFileOutput As String

Open strFileInput For Input As #1
Open strFileOutPut For Output As #2

Do Until EOF(1)


I'm sorry but I've run out of time to do it now. But, I will try to check back later as I've done before exactly what you're wanting and it takes reading in the lines, using the Split function to split the fields up and then using a string to build each line from the elements of the array, putting them together with CHR(9) tabs and then writing to the output file. See if you can get it and I'll try to check back later tonight, if possible.
 
Bob, I found this in another of your posts and it is exactly what I'm looking for...

However, how does it know what the file name is?

I guess you pass it to the sub from the form when you call the sub?

Code:
Public Sub ConvertTextFile(strInputFile As String, strOutputFile As String)

    Dim strInputString As String
    Dim strOutputString As String
    Dim varSplit As Variant
    Dim intUB As Integer
    Dim intCount As Integer
    
    Open strInputFile For Input As #1
    Open strOutputFile For Output As #2

    Do Until EOF(1)
        Line Input #1, strInputString
        varSplit = Split(strInputString, "|", , vbTextCompare)
        intUB = UBound(varSplit)
        intCount = 0
        While intCount <> intUB + 1
            strOutputString = strOutputString & varSplit(intCount) & Chr(9)
            intCount = intCount + 1
        Wend
            Print #2, strOutputString
            strOutputString = ""
            intUB = 0
    Loop
    Close #1
    Close #2

End Sub

I knew I did it before - I just couldn't find it. GREAT that you did!!! :)
 

Users who are viewing this thread

Back
Top Bottom