# Export data from Access to a fixed width text file



## D-Day (Aug 9, 2005)

Hi,

I've been trying to get data from a table in Access 2007 to a fixed width text file. I've tried using the built-in text file export and defined the width of each field. But the resulting text file has extra spaces between the data and i cannot be used like that.

The solution I've come across was to export the table to Excel and then use a VB function to write the data to a text file. I've attached an example. This works fine however, I would like to export my data directly from Access without having to go through Excel. 

Is there a way to use the built-in export feature that I'm missing? I found the code that I attached on a forum somewhere and modified it to suit my needs. I'm not that good with VB.

Thanks.


----------



## AlbertMC2 (Jul 15, 2010)

Hi

Try:

```
Dim rst As DAO.Recordset
    Dim fs, TextFile, Field01, i
    Dim Fieldlength, CurrentField, FullLine
    
    Set rst = CurrentDb.OpenRecordset("table1")
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set TextFile = fs.CreateTextFile("c:\testfile.txt", True)
    
    Fieldlength = 5 'length of field exported
    
    With rst
        Do Until .EOF = True  'loop records
            FullLine = ""
            For i = 0 To .Fields.Count - 1 'loop fields
            
                If IsNumeric(.Fields(i)) Then 'test numeric values
                    CurrentField = Trim(Str(.Fields(i))) 'convert to text
                Else
                    CurrentField = Trim(.Fields(i))
                End If
                
                If IsNull(.Fields(i)) Then 'test empty/null values
                   CurrentField = Space(fieldlenth) 'convert to spaces
                End If
                
                'test length of field
                If Len(CurrentField) > Fieldlength Then 'if greater cut field
                    Field01 = Mid(CurrentField, 1, Fieldlength)
                ElseIf Len(CurrentField) < Fieldlength Then 'if shorter add spaces
                    Field01 = CurrentField & Space(Fieldlength - Len(CurrentField))
                Else
                    Field01 = CurrentField
                End If
            
                'test for empty line
                If FullLine = "" Then 'if empty just add field
                    FullLine = Field01
                Else 'if not empty add space + next field
                    FullLine = FullLine & " " & Field01
                End If
                
            Next 'move to next field
            
            TextFile.WriteLine (FullLine) 'write the full line to text file
            .MoveNext 'move to next record
        
        Loop
        TextFile.Close 'close file
    End With
    
    rst.Close 'close recordset
    Set rst = Nothing
    Set db = Nothing
```
Where:
*CurrentDb.OpenRecordset("table1")* = the name of the table
*fs.CreateTextFile("c:\testfile.txt", True)* = name of file (and location)to be exported to
*Fieldlength = 5* = length of field to be exported


----------



## D-Day (Aug 9, 2005)

Thanks, I'll try it and get back to you.


----------



## D-Day (Aug 9, 2005)

I just realised that I didn't seem to attach the example code that I was talking about.


```
Sub FixWidth()
Dim Field(12, 1)		'Array that defines position and width of each field. i.e. Field(Position, Width)
Dim fswrite, tswrite
Dim WritePathName
Dim fwrite
Dim LastRow
Dim RowCount, ColumnCount
Dim ColumnText
Dim row, col
row = 0

Const ForReading = 1, ForWriting = 2, ForAppending = 3
MyPath = ActiveWorkbook.Path & "\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Const WriteFileName = "OutputFile.txt"

'Field Name Start Width
Field(0, 0) = 1     'record type
Field(0, 1) = 1
Field(1, 0) = 2     'Parish
Field(1, 1) = 2
Field(2, 0) = 4     'ED
Field(2, 1) = 3
Field(3, 0) = 7     'Building
Field(3, 1) = 3
Field(4, 0) = 10    'Dwelling unit
Field(4, 1) = 3
Field(5, 0) = 13    'Household
Field(5, 1) = 3
Field(6, 0) = 16    'Plink
Field(6, 1) = 8
Field(7, 0) = 24    'Persons in hh
Field(7, 1) = 2
Field(8, 0) = 26    'E8A
Field(8, 1) = 1
Field(9, 0) = 27    'Sex
Field(9, 1) = 1
Field(10, 0) = 28   'Year Departed
Field(10, 1) = 4
Field(11, 0) = 32   'Age at departure
Field(11, 1) = 2
Field(12, 0) = 34   'Country code
Field(12, 1) = 3


Set fswrite = CreateObject("Scripting.FileSystemObject")

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 1 To LastRow
row = row + 1
col = -1

For ColumnCount = 1 To 13
col = col + 1

ColumnText = Cells(RowCount, ColumnCount)
ColumnText = String(Field(ColumnCount - 1, 1) - Len(ColumnText), " ") & ColumnText 
tswrite.Write ColumnText
Next ColumnCount
tswrite.WriteLine
Next RowCount
tswrite.Close
End Sub
```


----------



## D-Day (Aug 9, 2005)

Also, in your code I understand that FieldLength is the length of the field. But how would I define the length of each field? I guess I'll have to define FieldLength as an array and somehow work it in?


----------



## D-Day (Aug 9, 2005)

Hi I think I've got what I wanted. Here's the code I came up with. I guess I'm better at modifying code than writing it from scratch:


```
Sub fixwidth()
    Dim rst As DAO.Recordset
    Dim fs, TextFile, Field01, i
    Dim CurrentField, FullLine
    Dim FieldLength(12, 1)
    
    Set rst = CurrentDb.OpenRecordset("table1")
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set TextFile = fs.CreateTextFile("C:\testfile.txt", True)
    
    'FieldLength = 5 'length of field exported
    'Field Name Start Width
    FieldLength(0, 0) = 1     'record type
    FieldLength(0, 1) = 1
    FieldLength(1, 0) = 2     'Parish
    FieldLength(1, 1) = 2
    FieldLength(2, 0) = 4     'ED
    FieldLength(2, 1) = 3
    FieldLength(3, 0) = 7     'Building
    FieldLength(3, 1) = 3
    FieldLength(4, 0) = 10    'Dwelling unit
    FieldLength(4, 1) = 3
    FieldLength(5, 0) = 13    'Household
    FieldLength(5, 1) = 3
    FieldLength(6, 0) = 16    'Plink
    FieldLength(6, 1) = 8
    FieldLength(7, 0) = 24    'Persons in hh
    FieldLength(7, 1) = 2
    FieldLength(8, 0) = 26    'E8A
    FieldLength(8, 1) = 1
    FieldLength(9, 0) = 27    'Sex
    FieldLength(9, 1) = 1
    FieldLength(10, 0) = 28   'Year Departed
    FieldLength(10, 1) = 4
    FieldLength(11, 0) = 32   'Age at departure
    FieldLength(11, 1) = 2
    FieldLength(12, 0) = 34   'Country code
    FieldLength(12, 1) = 3
    
    With rst
        Do Until .EOF = True  'loop records
            FullLine = ""
            For i = 0 To .Fields.Count - 1 'loop fields
            
                If IsNumeric(.Fields(i)) Then 'test numeric values
                    CurrentField = Trim(Str(.Fields(i))) 'convert to text
                Else
                    CurrentField = Trim(.Fields(i))
                End If
                
                If IsNull(.Fields(i)) Then 'test empty/null values
                   CurrentField = Space(FieldLength(i, 1)) 'convert to spaces
                End If
                
                'test length of field
                If Len(CurrentField) > FieldLength(i, 1) Then 'if greater cut field
                    Field01 = Mid(CurrentField, 1, FieldLength(i, 1))
                ElseIf Len(CurrentField) < FieldLength(i, 1) Then 'if shorter add spaces
                    Field01 = Space(FieldLength(i, 1) - Len(CurrentField)) & CurrentField
                Else
                    Field01 = CurrentField
                End If
            
                'test for empty line
                If FullLine = "" Then 'if empty just add field
                    FullLine = Field01
                Else 'if not empty add space + next field
                    FullLine = FullLine & "" & Field01
                End If
                
            Next 'move to next field
            
            TextFile.WriteLine (FullLine) 'write the full line to text file
            .MoveNext 'move to next record
        
        Loop
        TextFile.Close 'close file
    End With
    
    rst.Close 'close recordset
    Set rst = Nothing
    Set db = Nothing
End Sub
```
I changed *FieldLength* into an array where *FieldLength(i, 0)* contains the starting character position (this isn't actually used but it was easier to copy and paste everything from my code) and *FieldLength(i, 1)* contains the actual length of the field.

I didn't want any spaces between fields at all unless the field itself contained spaces so I changed that.

Finally, I changed the way how the fields are written so that any extra spaces come first instead of after the data (it's a requirement).

Thanks for the help :smile:


----------

