Hello,
Is there any way to remove the quotation marks from one/some fields in the output file when using Write #TextFile to export data from MS Access to a text file?
Thanks.
Hello,
Is there any way to remove the quotation marks from one/some fields in the output file when using Write #TextFile to export data from MS Access to a text file?
Thanks.
You are referring to single quotes as in O'Hara? Then Replace function, as in
Replace([FieldName],"'","")
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
I am referring double quotes since I am using the Write VBA command (Write # statement inserts commas between items and quotation marks around strings as they are written to the file); I want one field to be just number without quotes (like 99.9 instead of "99.9")
Try the Print # as described in the link I gave in post #3.
If that doesn't help, then post your code.
Working from the example on the referenced page.
Here is the result:Code:Sub test_Print() Open "TESTFILE" For Output As #1 ' Open file for output. Print #1, "This is a test" ' Print text to file. Print #1, ' Print blank line to file. Print #1, "Zone 1"; Tab; "Zone 2" ' Print in two print zones. Print #1, "Hello"; " "; "World" ' Separate strings with space. Print #1, Spc(5); "5 leading spaces " ' Print five leading spaces. Print #1, Tab(10); "Hello" ' Print word at column 10. ' Assign Boolean, Date, Null, Error and Number values. Dim MyBool, MyDate, MyNull, MyError, MyNumber MyBool = False: MyDate = #2/12/1969#: MyNull = Null MyError = CVErr(32767): MyNumber = 47.37 ' True, False, Null, and Error are translated using locale settings of ' your system. Date literals are written using standard short date ' format. Print #1, MyBool; " is a Boolean value" Print #1, MyDate; " is a date" Print #1, MyNull; " is a null value" Print #1, MyError; " is an error value" Print #1, MyNumber; " is a number value via Print" Write #1, MyNumber; " is a number value via Write" Close #1 ' Close file. End Sub
Code:This is a test Zone 1 Zone 2 Hello World 5 leading spaces Hello False is a Boolean value 12-Feb-69 is a date Null is a null value Error 32767 is an error value 47.37 is a number value via Print 47.37," is a number value via Write"
Thank you for your post. After reading this:
https://wellsr.com/vba/2016/excel/vb...int-statement/
I decided to use Write function over Print function since my output file would be read by other program as csv file and it sounds more convenient to use Write (only catch is one of eight fields needs to be a number)
My code
My current output:Code:Private Sub Command16_Click() On Error GoTo doADOErr Dim myData As New ADODB.Recordset Dim conn As New ADODB.Connection Dim mySQL, COMM As String Dim TextFile, TID, PREV, ACCT, i As Integer Dim FilePath, MDT, REF, VETA As String FilePath = "C:\GLT\MyFile.txt" TextFile = FreeFile Open FilePath For Output As TextFile Set conn = CurrentProject.Connection mySQL = "SELECT * FROM TRNS WHERE TRANSID>=" & Forms![TRNS]![Text12].Value & " AND TRANSID<=" & Forms![TRNS]![Text14].Value & "" myData.Open mySQL, conn, adOpenForwardOnly, adLockReadOnly If Not myData.EOF Then With myData i = 1 Do Until .EOF TID = .Fields(0) MDT = Right(Format(Year(.Fields(1)), YY) & Format(Month(.Fields(1)), MM) & Format(Day(.Fields(1)), DD), 6) If Len(.Fields(5)) > 1 Then COMM = COMM & ", " & .Fields(5) End If REF = Format(Right(Year(.Fields(1)), 2), YY) & "/" & Format(Month(.Fields(1)), MM) & "/" & Format(Day(.Fields(1)), DD) If (i > 1 And PREV <> .Fields(0)) Then If Len(COMM) > 1 Then Write #TextFile, "C", COMM End If COMM = "" End If If PREV <> .Fields(0) Then Write #TextFile, "H,", MDT, 0 End If Write #TextFile, "D", " " & .Fields(2), " ", "ARCL", REF, MDT, .Fields(3), .Fields(4) PREV = TID i = i + 1 .MoveNext Loop If Len(COMM) > 1 Then Write #TextFile, "C", COMM End If End With End If Close TextFile myData.Close conn.Close MsgBox "Export finished." doADOExit: Exit Sub doADOErr: MsgBox Err.Description, vbOKOnly, Err.Source & ":" & Err.Number Resume doADOExit End Sub
Desired outputCode:"H,","181130",0 "D"," 105"," ","ARCL","18/11/30","181130","FIRSTDATA DEPOSIT","2200.75" "D"," 315"," ","ARCL","18/11/30","181130","FIRSTDATA DEPOSIT","-2000" "D"," 315"," ","ARCL","18/11/30","181130","FIRSTDATA DEPOSIT","-300.75" "D"," 840"," ","ARCL","18/11/30","181130","FIRSTDATA DEPOSIT","100" "C",", F. TEBB, Y. SHOJI" "H,","181115",0 "D"," 105"," ","ARCL","18/11/15","181115","FIRSTDATA DEPOSIT","99.9" "D"," 315"," ","ARCL","18/11/15","181115","FIRSTDATA DEPOSIT","-9.9" "D"," 315"," ","ARCL","18/11/15","181115","FIRSTDATA DEPOSIT","-90"
Thanks againCode:"H,","181130",0 "D"," 105"," ","ARCL","18/11/30","181130","FIRSTDATA DEPOSIT",2200.75 "D"," 315"," ","ARCL","18/11/30","181130","FIRSTDATA DEPOSIT",-2000 "D"," 315"," ","ARCL","18/11/30","181130","FIRSTDATA DEPOSIT",-300.75 "D"," 840"," ","ARCL","18/11/30","181130","FIRSTDATA DEPOSIT",100 "C",", F. TEBB, Y. SHOJI" "H,","181115",0 "D"," 105"," ","ARCL","18/11/15","181115","FIRSTDATA DEPOSIT",99.9 "D"," 315"," ","ARCL","18/11/15","181115","FIRSTDATA DEPOSIT",-9.9 "D"," 315"," ","ARCL","18/11/15","181115","FIRSTDATA DEPOSIT",-90
Maybe not the cause but you realize every variable not explicitly typed on your lines with multiple declarations is a variant? E.g. TID, PREV, ACCT? This can cause unexpected results
As micron says, Variables in Access will be of type Variant, unless you explicitly Dim them as specific datatypes.
For example, your code:
will be expressed in Access asCode:Dim mySQL, COMM As String Dim TextFile, TID, PREV, ACCT, i As Integer
mySql--Variant
COMM --string
TextFile--Variant
TID--Variant
PREV--Variant
ACCT--Variant
i--Integer
In Access vba you can say
Dim COMM as String
Dim TID as Integer
OR
Dim COMM as String, TID as Integer
each variable must be explicitly DIMmed or else it will be understood to be a Variant.
It would be helpful to readers if you could post your original/input data.
Good luck.
It looks like it is working now; the biggest change was replacing Write with Print.
It isn't the nicest code but it does what I need :-)
Thanks for your input.
Code:Private Sub Command16_Click() 'On Error GoTo doADOErr Dim myData As New ADODB.Recordset Dim conn As New ADODB.Connection Dim mySQL As String Dim COMM As String Dim TextFile As Integer Dim TID As Integer Dim PREV As Integer Dim ACCT As Integer Dim i As Integer Dim FilePath As String Dim MDT As String Dim REF As String Dim VETA As String FilePath = "C:\MyFile.txt" TextFile = FreeFile Open FilePath For Output As TextFile Set conn = CurrentProject.Connection mySQL = "SELECT * FROM TRNS WHERE TRANSID>=" & Forms![TRNS]![Text12].Value & " AND TRANSID<=" & Forms![TRNS]![Text14].Value & "" myData.Open mySQL, conn, adOpenForwardOnly, adLockReadOnly If Not myData.EOF Then With myData i = 1 Do Until .EOF TID = .Fields(0) MDT = Right(Format(Year(.Fields(1)), YY) & Format(Month(.Fields(1)), MM) & Format(Day(.Fields(1)), DD), 6) If Len(.Fields(5)) > 1 Then COMM = COMM & ", " & .Fields(5) End If REF = Format(Right(Year(.Fields(1)), 2), YY) & "/" & Format(Month(.Fields(1)), MM) & "/" & Format(Day(.Fields(1)), DD) If (i > 1 And PREV <> .Fields(0)) Then If Len(COMM) > 1 Then Write #TextFile, "C", COMM End If COMM = "" End If If PREV <> .Fields(0) Then Write #TextFile, "H,", MDT, 0 End If 'Write #TextFile, "D", " " & .Fields(2), " ", "ARCL", REF, MDT, .Fields(3), .Fields(4) VETA = Chr$(34) & "D" & """," & Chr$(34) & .Fields(2) & ""","""" """", ""ARCL"""",""" & Format(Right(Year(.Fields(1)), 2), YY) & "/" & Format(Month(.Fields(1)), MM) & "/" & Format(Day(.Fields(1)), DD) & """,""" & Right(Format(Year(.Fields(1)), YY) & Format(Month(.Fields(1)), MM) & Format(Day(.Fields(1)), DD), 6) & """,""" & .Fields(3) & """," & .Fields(4) Print #TextFile, VETA PREV = TID i = i + 1 .MoveNext Loop If Len(COMM) > 1 Then Write #TextFile, "C", COMM End If End With End If Close TextFile myData.Close conn.Close MsgBox "Export finished." doADOExit: Exit Sub doADOErr: MsgBox Err.Description, vbOKOnly, Err.Source & ":" & Err.Number Resume doADOExit End Sub
You are missing the hash mark (#) in the Open command
and in the close commandCode:FilePath = "C:\MyFile.txt" TextFile = FreeFile Open FilePath For Output As TextFile '<<-- should be #Textfile
Code:Close TextFile '<<-- should be #Textfile myData.Close conn.Close MsgBox "Export finished."
Example from HELP:
Code:Dim MyIndex, FileNumber For MyIndex = 1 To 5 ' Loop 5 times. FileNumber = FreeFile ' Get unused file ' number. Open "TEST" & MyIndex For Output As #FileNumber ' Create file name. Write #FileNumber, "This is a sample." ' Output text. Close #FileNumber ' Close file. Next MyIndex
Thanks, I corrected it.