Spaces between fields on CSV output table

[AndyB2]

Below is my code that appears to be working as expected. But the output has spaces between each of the fields. Two of the table created are attached as examples.

I did try Trim() on a couple if fields with no luck.

Private Sub CB_RunQueryPrintReport_Click()

Dim stDocName As String
Dim Filename As String
Dim i, RC, X As Integer
Dim MyDB As DAO.Database
Dim REQ As DAO.Recordset
Dim UDI As DAO.Recordset2
Dim CurrSpecies As String

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set REQ = MyDB.OpenRecordset("Tbl_REQData", DB_OPEN_TABLE)
Set UDI = MyDB.OpenRecordset("Tbl_UDIData", DB_OPEN_TABLE)

DoCmd.Hourglass True
DoCmd.SetWarnings False

'Set tables before exporting
DoCmd.OpenQuery "Qry_FindPanelParts"
DoCmd.OpenQuery "Qry_PanelsToCut"
DoCmd.OpenQuery "Qry_PanelsToCutAddOn"
DoCmd.OpenQuery "Qry_PanelsToCutAddOn2"
DoCmd.OpenQuery "Qry_PanelsToCutAddOn3"
DoCmd.OpenQuery "Qry_PanelsToCutAddOn4"
DoCmd.OpenQuery "Qry_ChrPanels"
DoCmd.OpenQuery "Qry_ChrParts_Req_Delete"
DoCmd.OpenQuery "Qry_ChrParts_Req"
DoCmd.OpenQuery "Qry_ChrParts_UDI_Delete"
DoCmd.OpenQuery "Qry_ChrParts_UDI"


'REQ and UDI tables are sorted in species order

RC = REQ.RecordCount
REQ.MoveFirst
UDI.MoveFirst
Select Case REQ("Species")
    Case "CHR"
        Filename = "C:\Cherry_Panels.ptx"
        
    Case "HCK"
        Filename = "C:\Hickory_Panels.ptx"
        
    Case "MAP"
       Filename = "C:\Maple_Panels.ptx"
        
    Case "OAK"
       Filename = "C:\Oak_Panels.ptx"
End Select

CurrSpecies = REQ("Species")
i = 0  'setting counter to zero

Open Filename For Output As #1
For X = 1 To RC
    i = i + 1
        If REQ("WorkOrder") = UDI("WorkOrder") Then
            Print #1, Trim(REQ("RowT")), ",", Trim(REQ("fld2")), ","; i; ",", REQ("part"), ",", REQ("fld5"), ",", REQ("Len"), ",", REQ("WID"), ",", REQ("fld8"), ",", REQ("fld9"), ","; REQ("fld10"), ","; REQ("fld11"), ","; REQ("fld12"), ",", REQ("Fld13")
            Print #1, UDI("rowt"), ",", UDI("fld2"), ","; i; ",", UDI("fld4"), ",", UDI("fld5"), ",", UDI("Len"), ",", UDI("WID"), ",", UDI("fld8"), ",", UDI("fld9"), ",", UDI("fld10"), ",", UDI("CUTDATE"), ",", UDI("ParPart"), ",", Trim(UDI("ParDesc")), ",", UDI("WorkOrder"), ",", UDI("fld16"), ",", UDI("fld17"), ",", UDI("fld18"), ",", UDI("fld19"), ",", UDI("fld20")
        End If
        REQ.MoveNext
        UDI.MoveNext
        If REQ.EOF = False Then
            If CurrSpecies <> REQ("Species") Then
                Close #1
                i = 0 'reseting record counter
                Select Case REQ("Species")
                    Case "CHR"
                        Filename = "C:\Cherry_Panels.ptx"
                        
                    Case "HCK"
                        Filename = "C:\Hickory_Panels.ptx"
                        
                    Case "MAP"
                       Filename = "C:\Maple_Panels.ptx"
                        
                    Case "OAK"
                       Filename = "C:\Oak_Panels.ptx"
                End Select
                Open Filename For Output As #1
                CurrSpecies = REQ("Species")
            End If
        End If
Next
Close #1

DoCmd.Hourglass False
DoCmd.SetWarnings True


End Sub

FYI NeoPa I did Tool|Options|Re quired Variable Declaration. This code was already started so it is not showing the "Option Explicit". Is there any way to set that on already existing code?

[NeoPa]

AndyB2:
FYI NeoPa I did Tool|Options|Re quired Variable Declaration. This code was already started so it is not showing the "Option Explicit". Is there any way to set that on already existing code?

First quick part of answer is "Yes. You can easily add it after the fact simply by copying that line into any module that doesn't already have it."

Next quick point, it works better for both you and us if simple data like text is posted in CODE tags in the post. It doesn't help you if half the experts take one look and decide it's too much trouble to do the download etc. I'll leave that with you for next time.

Now then, You should appreciate that the Print command is not named arbitrarily. It was introduced in order to support printing and formatting of output. You probably want to look at using Put# instead, but be warned, care needs to be taken to use it properly as it's not entirely uncomplicated itself. I suggest you read through the Help page fairly thoroughly. Using the Binary option can give you the most direct control, but is also the most complicated. I suggest you explore other options first, especially as you are using variable-length records.

[ADezii]

If you wish, it is a simple matter to clean up (remove Spaces) in the Input File, then write to an Output File. Assuming Hickory_Panels. txt resides in a Test Directory, namely C:\Test\Hickory _Panels.txt, then the following Code will rewrite the File with Spaces removed to C:\Test\Hickory _Panels_No_Spac es.txt. This may not be the Optimal Solution, but it is probably the simplest.

Dim varTextLine As Variant

Open "C:\Test\Hickory_Panels.txt" For Input As #1                     'Open the INPUT File.
Open "C:\Test\Hickory_Panels_No_Spaces.txt" For Output As #2          'Open the OUTPUT File

Do While Not EOF(1)                                     'Loop until end of File.
  Line Input #1, varTextLine                            'Read line into variable.
    Print #2, Replace(varTextLine, " ", "")             'OUTPUT the Line to another File void
Loop                                                    'of any Spaces

Close #1    'Close INPUT file.
Close #2    'Close OUTPUT File

C:\Test\Hickory _Panels_No_Spac es.txt 'after' Code Execution:

PARTS_REQ,1,1,938979,1,234.95,241.3,1,0,0,0,0,
PARTS_UDI,1,1,0,0,234.95,241.3,0,0,0,20121204,943043,DR-HK13.25X13.00FINCHSC,98410,0,0,0,,
PARTS_REQ,1,2,939121,1,311.15,317.5,1,0,0,0,0,
PARTS_UDI,1,2,0,0,311.15,317.5,0,0,0,20121204,400377,DR-HK16.25X16.00FINCHSN,98411,0,0,0,,
PARTS_REQ,1,3,938767,1,450.85,203.2,1,0,0,0,0,
PARTS_UDI,1,3,0,0,450.85,203.2,0,0,0,20121204,400334,DR-HK11.75X21.50FINCHSN,98414,0,0,0,,
PARTS_REQ,1,4,938988,1,539.75,241.3,1,0,0,0,0,
PARTS_UDI,1,4,0,0,539.75,241.3,0,0,0,20121204,943053,DR-HK13.25X25.00FINCHSC,98416,0,0,0,,
PARTS_REQ,1,5,171564,1,450.85,261.9248,1,0,0,0,0,
PARTS_UDI,1,5,0,0,450.85,261.9248,0,0,0,20121204,171609,DR-HK14.06X21.50FINCHSN,98413,0,0,0,,
PARTS_REQ,1,6,938662,1,615.95,88.9,1,0,0,0,0,
PARTS_UDI,1,6,0,0,615.95,88.9,0,0,0,20121204,400261,DR-HK7.25X28.00FINCHSN,98417,0,0,0,,
PARTS_REQ,1,7,118679,1,619.125,244.475,1,0,0,0,0,
PARTS_UDI,1,7,0,0,619.125,244.475,0,0,0,20121204,118676,DR-HK13.25X28.00FINVHSC,98193,0,0,0,,
PARTS_REQ,1,8,138231,1,374.65,241.3,1,0,0,0,0,
PARTS_UDI,1,8,0,0,374.65,241.3,0,0,0,20121204,400161,DR-HK13.25X18.50FINCHSN,98412,0,0,0,,
PARTS_REQ,1,9,938988,1,539.75,241.3,1,0,0,0,0,
PARTS_UDI,1,9,0,0,539.75,241.3,0,0,0,20121204,400355,DR-HK13.25X25.00FINCHSN,98415,0,0,0,,

[NeoPa]

Sorry to sound critical again ADezii, but while that would work for numerical data, it may corrupt string data in the file with valid spaces included.

It's not an approach I'd recommend, as it gets around the issue rather than dealing with it properly, but a small tweak could make it give correct results in all cases (but relying on the data being in CSV format) :

Dim strTextLine As String, strNewLine As String
 
Open "C:\Test\Hickory_Panels.txt" For Input As #1                     'Open the INPUT File.
Open "C:\Test\Hickory_Panels_No_Spaces.txt" For Output As #2          'Open the OUTPUT File
 
Do While Not EOF(1)                                     'Loop until end of File.
    Line Input #1, strTextLine                          'Read line into variable.
    Do                                                  'loop till all spaces by commas cleared
        strNewLine = Replace(Replace(strTextLine, " ,", ","), ", ", ",")
                                                        'remove spaces around commas
        If strNewLine = strTextLine Then Exit Do        'When same then done
        strTextLine = strNewLine                        'Reset start point
    Loop
    Print #2, strNewLine                                'OUTPUT the Line to another File void
Loop                                                    'of any Spaces
 
Close #1    'Close INPUT file.
Close #2    'Close OUTPUT File.

Clearly this is mostly ADezii's code, but you can see the concept.

[ADezii]

it may corrupt string data in the file with valid spaces included.

This is not the case with the Posted Data, and I assumed that the Format of the data would remain constant. Your point, is valid, of course.

[NeoPa]

If you look at the even numbered lines and check the field 6 from the end, you'll see text data with embedded spaces in both files ADezii. Sorry to be the one to point it out, but that's embedded deeply into my nature. I can't not do it when I see it, which can be considered a failing (so people love to tell me anyway).

[AndyB2]

I stumbled onto a different answer that works, using concatenation with “&” vs. a comma to separate the fields takes care of the problem still using the print statement.

New Print code:

If REQ("WorkOrder") = UDI("WorkOrder") Then
            Print #1, REQ!rowt & "," & REQ!fld2 & "," & i & "," & REQ!part & "," & REQ!fld5 & "," & REQ!Len & "," _
            & REQ!WID & "," & REQ!fld8 & "," & REQ!fld9 & "," & REQ!fld10 & "," & REQ!fld11 & "," & REQ!fld12 & "," _
            & REQ!Fld13
            Print #1, UDI!rowt & "," & UDI!fld2 & "," & i & "," & UDI!fld4 & "," & UDI!fld5 & "," & UDI!Len & "," _
            & UDI!WID & "," & UDI!fld8 & "," & UDI!fld9 & "," & UDI!fld10 & "," & UDI!CUTDATE & "," & UDI!ParPart & "," _
            & UDI!ParDesc & "," & UDI!WorkOrder & "," & UDI!fld16 & "," & UDI!fld17 & "," & UDI!fld18 & "," _
            & UDI!fld19 & "," & UDI!fld20
        End If

New output:

PARTS_REQ,1,1,938662,1,615.95,88.9,1,0,0,0,0,
PARTS_UDI,1,1,0,0,615.95,88.9,0,0,0,20121204,400261,DR-HK  7.25X28.00     FIN CHSN,98417,0,0,0,,
PARTS_REQ,1,2,938988,1,539.75,241.3,1,0,0,0,0,
PARTS_UDI,1,2,0,0,539.75,241.3,0,0,0,20121204,943053,DR-HK 13.25X25.00     FIN CHSC,98416,0,0,0,,
PARTS_REQ,1,3,939121,1,311.15,317.5,1,0,0,0,0,
PARTS_UDI,1,3,0,0,311.15,317.5,0,0,0,20121204,400377,DR-HK 16.25X16.00     FIN CHSN,98411,0,0,0,,
PARTS_REQ,1,4,938988,1,539.75,241.3,1,0,0,0,0,
PARTS_UDI,1,4,0,0,539.75,241.3,0,0,0,20121204,400355,DR-HK 13.25X25.00     FIN CHSN,98415,0,0,0,,
PARTS_REQ,1,5,171564,1,450.85,261.9248,1,0,0,0,0,
PARTS_UDI,1,5,0,0,450.85,261.9248,0,0,0,20121204,171609,DR-HK 14.06X21.50     FIN CHSN,98413,0,0,0,,
PARTS_REQ,1,6,118679,1,619.125,244.475,1,0,0,0,0,
PARTS_UDI,1,6,0,0,619.125,244.475,0,0,0,20121204,118676,DR-HK 13.25X28.00     FIN VHSC,98193,0,0,0,,
PARTS_REQ,1,7,138231,1,374.65,241.3,1,0,0,0,0,
PARTS_UDI,1,7,0,0,374.65,241.3,0,0,0,20121204,400161,DR-HK 13.25X18.50     FIN CHSN,98412,0,0,0,,
PARTS_REQ,1,8,938767,1,450.85,203.2,1,0,0,0,0,
PARTS_UDI,1,8,0,0,450.85,203.2,0,0,0,20121204,400334,DR-HK 11.75X21.50     FIN CHSN,98414,0,0,0,,
PARTS_REQ,1,9,938979,1,234.95,241.3,1,0,0,0,0,
PARTS_UDI,1,9,0,0,234.95,241.3,0,0,0,20121204,943043,DR-HK 13.25X13.00     FIN CHSC,98410,0,0,0,,

Thanks for the input, it got me working down a different path that let me stumble on this.

Andy B.

[NeoPa]

You're quite right Andy. I should have suggested that. A much more straightforward answer than using Put#.

I'm going to go ahead and flag it as Best Answer. Very rare for a post from the OP, but appropriate in this case, I believe.