×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Write #TextFile - quotation marks

Write #TextFile - quotation marks

Write #TextFile - quotation marks

(OP)
Hello,

I am using this code:

Write #TextFile, "D", " " & .Fields(2), " ", "ARCL", REF, MDT, .Fields(3), .Fields(4)

which gives me the following output:

"D"," 105"," ","ARCL","18/11/10","181110","FIRSTDATA DEPOSIT","2400.5"
"D"," 315"," ","ARCL","18/11/10","181110","FIRSTDATA DEPOSIT","-2000.5"
"D"," 315"," ","ARCL","18/11/10","181110","FIRSTDATA DEPOSIT","-500"
"D"," 840"," ","ARCL","18/11/10","181110","FIRSTDATA DEPOSIT","100"

but I need output in this format (last field without quotation marks):

"D"," 105"," ","ARCL","18/11/10","181110","FIRSTDATA DEPOSIT",2400.5
"D"," 315"," ","ARCL","18/11/10","181110","FIRSTDATA DEPOSIT",-2000.5
"D"," 315"," ","ARCL","18/11/10","181110","FIRSTDATA DEPOSIT",-500
"D"," 840"," ","ARCL","18/11/10","181110","FIRSTDATA DEPOSIT",100

Is there any way around to get it in the requested format?
Thanks.

RE: Write #TextFile - quotation marks

(OP)
PS: It is MS Access VBA writing into a text file

RE: Write #TextFile - quotation marks

A quick Google search suggests that you use the Print command instead of Write.

RE: Write #TextFile - quotation marks

(OP)
Thank you for your post.
I considered Print command too but after reading this tutorial https://wellsr.com/vba/2016/excel/vba-write-to-tex...
I decided to use Write function over Print function since my output file would be read by other program as CSV file and it looked more convenient to use Write (only catch is one of eight fields needs to be a number).

RE: Write #TextFile - quotation marks

"my output file would be read by other program as CSV file " - then why do you need double quotes at all?
If that's supposed to be a Comma Separated Value file, it should look like this (IMHO):

D,105,,ARCL,18/11/10,181110,FIRSTDATA DEPOSIT,2400.5
D,315,,ARCL,18/11/10,181110,FIRSTDATA DEPOSIT,-2000.5
D,315,,ARCL,18/11/10,181110,FIRSTDATA DEPOSIT,-500
D,840,,ARCL,18/11/10,181110,FIRSTDATA DEPOSIT,100


---- Andy

There is a great need for a sarcasm font.

RE: Write #TextFile - quotation marks

I guess that you will need to build up a single string inclusive of quotes where you want them and commas.
Then Print that string to the file.

RE: Write #TextFile - quotation marks

(OP)
to Andy - "my output file would be read by other program as CSV file " - then why do you need double quotes at all?

it is required by other application using this file as input data, see below




RE: Write #TextFile - quotation marks

This is really confusing (maybe just to me....).
You are talking about CSV file format, and in your picture there is "A sample detail in CSV format" portion. CSV to me is what I pointed out already.

But also, you have the upper portion of your picture, which is a records format for fixed length text file:
Record type  string 1
Account code string 1
Dept. code   string 6
Source code  string 4
Reference    string 12
Date         date   6
Description  string 30
Amount       dollar 13
 
So what this portion is saying to me: every record is 73 characters long, and which piece of data is where. No commas required.
The data would look something like this (BLUE numbers is just the position/"columns" in the file)

1 123456    123456789012      123456789012345678901234567890
 1      1234            123456                              1234567890123
RA   105ARCLREFERENCE   181118DESCRIPTION                   2400.5
RA   315ARCLREFERENCE   181118DATA DEPOSIT                  -2000.5
RA   315ARCLREFERENCE   181118DATA DEPOSIT                  -500
RA   840ARCLREFERENCE   181118DATA DEPOSIT                  100
 
But - you need to do what you need to do, and I would go with mintjulep's suggestion and build the string.
And since " is Chr(34), you may try something like this:

CODE

Dim str As String
Dim X As String
X = Chr(34)

str = X & "D" & X & "," & X & " 840" & X & "," & X & " " & X & "," & X & "ABCD" & X

Debug.Print str 

which will get you here:
"D"," 840"," ","ABCD"



---- Andy

There is a great need for a sarcasm font.

RE: Write #TextFile - quotation marks

(OP)
It looks like it is working now.
I replaced Write
Write #TextFile, "D", " " & .Fields(2), " ", "ARCL", REF, MDT, .Fields(3), .Fields(4)

with Print
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

Thanks for your suggestions.

RE: Write #TextFile - quotation marks

I am glad it worked for you, but...
I would hate to maintain code like this, with all """"", long line of concatenation, etc.

Consider this approach:

CODE

Dim VETA As String
Dim i As Integer
Dim aryX(6) As String

aryX(0) = "D"
aryX(1) = .Fields(2)
aryX(2) = " "
aryX(3) = "ARCL"
aryX(4) = "Format(Right(Year(.Fields(1)), 2), YY) Format(Month(.Fields(1)), MM)  Format(Day(.Fields(1)), DD)"
aryX(5) = "Right(Format(Year(.Fields(1)), YY) & Format(Month(.Fields(1)), MM) & Format(Day(.Fields(1)), DD), 6)"
aryX(6) = .Fields(3)

'Add Chr(34) " (double quotes) around array elements
For i = LBound(aryX) To UBound(aryX)
    aryX(i) = Chr(34) & aryX(i) & Chr(34)
Next i

'Join array elements with comma in between
VETA = Join(aryX, ",")
'and add the last field without "
VETA = VETA & "," & .Fields(4)

Print #TextFile, VETA 

You would have to modify BLUE lines of this code, but I think it will work a lot better.

Just a suggestion.... smile


---- Andy

There is a great need for a sarcasm font.

RE: Write #TextFile - quotation marks

(OP)
Thanks for cleaning my spaghetti code smile

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close