• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1224
  • Last Modified:

Access VBA Import and convert fixed width text file to tab delimited trimmed of extra spaces

I'm in Access 2002 using VBA to convert an external fixed width coma delimited text file to tab delimited file trimmed of extra spaces.    I started with getting the file input and written planning to deal with the extra spaces later.  I thought I could use the trim function but it's not working.  Is it because original file is fixed width?  Any help with this would be appreciated.  Note:  Unfortuately I can't use a solutions that involves Transfer Text.  Can I work with this below or am I better off using a different approach.  

Private Sub Convert_Click()
    Dim hFileSource As Long
    Dim hFilePrint As Long
    Dim strLine As String
    strLine = Trim(strLine)
    filSource = Me!filSource.Value
    filPrint = Me!filPrint.Value
    hFileSource = FreeFile
    Open filSource For Input Access Read Shared As hFileSource
    hFilePrint = FreeFile
    Open filPrint For Output Access Write As hFilePrint
    Do Until EOF(hFileSource)
        Line Input #hFileSource, strLine
        Print #hFilePrint, strLine
    Loop
    Close hFileSource
    Close hFilePrint
End Sub

0
EastC2
Asked:
EastC2
  • 3
  • 3
  • 2
  • +3
2 Solutions
 
jerryb30Commented:
Are there spaces within the fields?
is the white space always at the end of a field?
Are you looking to replace the white space with a tab character?
A quick sample of a row or two might help.
0
 
wsh2Commented:
Assuming that the commas are only used as a field separator (and NOT part of the data).. try this code:
----------------------------------------------------------------------
Private Sub Convert_Click()
'
'  Variables
   Dim strBuffer As String
   Dim lngCol As Long, strCols() As String
   Dim strFilePath As String
   Dim intFreeFile As Integer: intFreeFile = FreeFile
   Dim lngRow As Long, strRows() As String
'
'  Read
   strFilePath = Me!filSource.Value
   strBuffer = Space(FileLen(strFilePath))
   Open strFilePath For Binary As intFreeFile
   Get intFreeFile, , strBuffer
   Close intFreeFile
'
'  Process
   strRows = Split(strBuffer, vbCrLf)
   For lngRow = 0 To UBound(strRows)
      If Len(strRows(lngRow)) > 0 Then
         strCols() = Split(strRows(lngRow), ",")
         For lngCol = 0 To UBound(strCols)
            strCols(lngCol) = Trim(strCols(lngCol))
         Next lngCol
      End If
      strRows(lngRow) = Join(strCols, vbTab)
   Next lngRow
'
'  Write
   strFilePath = Me!filPrint.Value
   strBuffer = Join(strRows, vbCrLf)
   Open strFilePath For Binary As intFreeFile
   Get intFreeFile, , strBuffer
   Close intFreeFile
'
End Sub
----------------------------------------------------------------------
0
 
GRayLCommented:
Why not use File, Get External Data, Import, set the file of type to .csv, and go.  Then reverse it selecting on Export .tab?  
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
wsh2Commented:
Oops.. correction.. the last Get should be a Put.. <sheepsigh grin>
Assuming that the commas are only used as a field separator (and NOT part of the data).. try this code:
----------------------------------------------------------------------
Private Sub Convert_Click()
'
'  Variables
   Dim strBuffer As String
   Dim lngCol As Long, strCols() As String
   Dim strFilePath As String
   Dim intFreeFile As Integer: intFreeFile = FreeFile
   Dim lngRow As Long, strRows() As String
'
'  Read
   strFilePath = Me!filSource.Value
   strBuffer = Space(FileLen(strFilePath))
   Open strFilePath For Binary As intFreeFile
   Get intFreeFile, , strBuffer
   Close intFreeFile
'
'  Process
   strRows = Split(strBuffer, vbCrLf)
   For lngRow = 0 To UBound(strRows)
      If Len(strRows(lngRow)) > 0 Then
         strCols() = Split(strRows(lngRow), ",")
         For lngCol = 0 To UBound(strCols)
            strCols(lngCol) = Trim(strCols(lngCol))
         Next lngCol
      End If
      strRows(lngRow) = Join(strCols, vbTab)
   Next lngRow
'
'  Write
   strFilePath = Me!filPrint.Value
   strBuffer = Join(strRows, vbCrLf)
   Open strFilePath For Binary As intFreeFile
   Put intFreeFile, , strBuffer                            ' <-- Corrected
   Close intFreeFile
'
End Sub
----------------------------------------------------------------------
0
 
EastC2Author Commented:
wsh2:



jerryb30:
  There are spaces within the field (addresses) as well as at the end of the field.  I want to remove the spaces completely.  I tried to use Replace() but it took the spaces out of the addresses too.  The txt file has about 80 rows.  Some names (with spaces between first & last name) some totally blank fields.

GRayL:
  Need to use vba so the menu options our out this round.  





0
 
EastC2Author Commented:
wsh2:
    This works.  Seems like a lot to go through to get there but you can't argue with results.
    Thank you.
0
 
wsh2Commented:
The thing that makes this so difficult is your requirement to remove trailing spaces. No other way to do this other than 'brute force and igonorance'.. {smile} and a {wink}.
0
 
harfangCommented:
There is some confusion in your question. It's not clear whether the original file is comma-separated, but with some extra unwanted spaces, or a fixed-width file. In any case, here is a correction of your code for fixed-width data:

Private Sub Convert_Click()
    Dim hFileSource As Long
    Dim hFilePrint As Long
    Dim strLine As String
    Dim strOutput As string   ' added
    strLine = Trim(strLine)   ' no effect, strLine is still empty
    filSource = Me!filSource.Value
    filPrint = Me!filPrint.Value
    hFileSource = FreeFile
    Open filSource For Input Access Read Shared As hFileSource
    hFilePrint = FreeFile
    Open filPrint For Output Access Write As hFilePrint
    Do Until EOF(hFileSource)
        Line Input #hFileSource, strLine
        ' convert strLine to strOutput here:
        strOutput = Trim(Mid(strLine, 1, 10))   ' first field: 10 chars
        strOutput = strOutput & "," & Trim(Mid(strLine, 11, 5))   ' second: 5 chars
        strOutput = strOutput & "," & Trim(Mid(strLine, 16, 8))   ' third: 8 chars
        Print #hFilePrint, strOutput
    Loop
    Close hFileSource
    Close hFilePrint
End Sub

Cheers!
(°v°)
0
 
EastC2Author Commented:
harfang:
    Thank you for your input.  This is the type of solution I was looking for (sorry, I wasn't specific when asking).   I typed coma delimited file in my question but it's actually a vertical bar delimited file.  I think it's fixed because most of the fields are at a fixed size of 255 characters regardless of how much text is in them.  I also have 80 columns, not 80 rows.

How do I use this trim(mid())?  Get the character size for each the columns?  the 90% of the files are text are all 255 long, but there are a few number columns and two date columns.
0
 
harfangCommented:
You will need to open your text file in a good editor showing cursor position, and start taking notes:
Name: starts at: 1, ends at: 15, length: 15-1+1 = 15 characters
Bar separator: position 16
Description: starts at: 17, ends at: 46, length: 46-17+1 = 30 characters
Bar separator: position 47
etc.

This will provide you with the values needed in Mid() to extract each field.

Note that you may not need this at all. Have you tried importing your file directly to Access using the fixed-width wizard? You can easily skip the columns contain the bar separators and Access will perform the Trim() automatically. You can also save the import specification if you need to import the same type of files on a regular basis.

At the very least, the wizard can help you determine where your fields start and end.

Good luck!
(°v°)
0
 
AccessCodeHelpCommented:
I have a similar case.  How do you determine where the the file is.  For example, I need to convert a fixed width text file name "Test.txt". Let's say it resides in "c:\test.txt".  How would this code work?
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now