Converting Pipe Delimited Text File To Tab Delimited Text file

[Probie Coder]

I am new to MS Access and was wondering how to perform the following task.

How to write a program to convert pipe delimited text file to a tab
delimited text file.
All fields must be trimmed of extra spaces (I assume either the TRIM, LTRIM
OR RTRIM commands can be used for this. A form to specify the input and
output and a button to run the conversion is required for this task.

Any assistance you can offer would be greatly appreciated!

 

[Stefan Hoffmann]

hi,

How to write a program to convert pipe delimited text file to a tab
delimited text file.

Import the file to a table using DoCmd.TransferText and an import
specification.
Modify the contents using update queries.
Export the file using DoCmd.TransferText and an export specification.

http://support.microsoft.com/kb/209740
http://www.blueclaw-db.com/download/transfertext_outputto_examples.htm

http://support.microsoft.com/kb/230265


mfG
--> stefan <--

 

[Magius96]

Here's an alternative. Call this function with the full path and filename of
the input file and the output file, and it'll do the rest. (I just wrote
this, and haven't tested it)

Function PipeToTab(ByVal InputFile As String, ByVal OutputFile As String)
Dim ThisString As String
Dim NewString As String
Dim A As Integer
Open InputFile For Input As InFile
Open OutputFile For Output As OutFile

While Not EOF(InFile)
NewString = ""
Line Input #InFile, ThisString
For A = 0 To Len(ThisString)
If Mid(ThisString, A, 1) = "|" Then
NewString = NewString & Chr$(9)
Else
NewString = NewString & Mid(ThisString, A, 1)
End If
Next

Print #OutFile, ThisString
Loop
End Function

 

[Stefan Hoffmann]

hi,

Here's an alternative. Call this function with the full path and filename of
the input file and the output file, and it'll do the rest. (I just wrote
this, and haven't tested it)

It doesn't handle pipes when they are the actual payload.

mfG
--> stefan <--

 

[treva31]

I have also been having problems trying to import a pipe ( | ) delimited file into access.
I fixed up and used the code above which works great so I thought I would post the updated version.
I also changed it to replace the pipes with commas instead of tabs.

I imagine this code could be useful in may situations.

Thanks to Magius96 for the original.

Function PipeToComma(ByVal InputFile As String, ByVal OutputFile As String)
 On Error GoTo error1
 Dim ThisString As String, NewString As String, A As Integer
 
 Open InputFile For Input As #1
 Open OutputFile For Output As #2
 
 While Not EOF(1)
 NewString = ""
 
 Line Input #1, ThisString
 For A = 1 To Len(ThisString)
 If Mid(ThisString, A, 1) = "|" Then
 NewString = NewString & ","
 Else
 NewString = NewString & Mid(ThisString, A, 1)
 End If
 Next
 
 Print #2, NewString
 Wend
 
Close #1
 Close #2
 Exit Function
 error1:
 Close #1
 Close #2
 End Function

 

[treva31]

In fact here is a version that takes two more inputs.
Specify the string to replace and what to replace it with.
It can be any length and be replaced by a different length.

So now you can search and replace any text in a textfile using vba, saving the output to a new file (overwriting if one already exists).

Once again thanks to Magius96 for the original.

Call it like this:

Sub testpipe2()
   
   Call TextFileReplacer("C:\InputFile.txt", "C:\OutputFile.txt", "Wrong Word", "Correct")
   
   End Sub

Here is the function:

Function TextFileReplacer(ByVal InputFile As String, ByVal OutputFile As String, ReplaceThis As String, WithThis As String)
   On Error GoTo error1
   Dim ThisString As String, NewString As String, A As Integer, len1 As Integer
   
   Open InputFile For Input As #1
   Open OutputFile For Output As #2
   
   len1 = Len(ReplaceThis)
   
   While Not EOF(1)
   NewString = ""
   
   Line Input #1, ThisString
   For A = 1 To Len(ThisString)
   If Mid(ThisString, A, len1) = ReplaceThis Then
   NewString = NewString & WithThis
   A = A + (len1 - 1)
   Else
   NewString = NewString & Mid(ThisString, A, 1)
   End If
   Next
   
   Print #2, NewString
   Wend
   
   Close #1
   Close #2
   Exit Function
   error1:
   Close #1
   Close #2
   End Function