Link to home
Start Free TrialLog in
Avatar of EastC2
EastC2

asked on

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

Avatar of jerryb30
jerryb30
Flag of United States of America image

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.
Avatar of wsh2
wsh2

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
----------------------------------------------------------------------
Why not use File, Get External Data, Import, set the file of type to .csv, and go.  Then reverse it selecting on Export .tab?  
ASKER CERTIFIED SOLUTION
Avatar of wsh2
wsh2

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of EastC2

ASKER

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.  





Avatar of EastC2

ASKER

wsh2:
    This works.  Seems like a lot to go through to get there but you can't argue with results.
    Thank you.
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}.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of EastC2

ASKER

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.
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°)
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?