Write #TextFile - quotation marks 1

[JoPaBC]

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.

 

[JoPaBC]

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

 

[DjangMan]

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

 

[JoPaBC]

Thank you for your post.
I considered Print command too but after reading this tutorial

https://wellsr.com/vba/2016/excel/vba-write-to-text-file-print-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 looked more convenient to use Write (only catch is one of eight fields needs to be a number).

 

[Andrzejek]

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):
[tt]
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[/tt]



---- Andy

There is a great need for a sarcasm font.

 

[mintjulep]

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.

 

[JoPaBC]

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

 

[Andrzejek]

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:[pre]
Record type string [blue]1[/blue]
Account code string [blue]1[/blue]
Dept. code string [blue]6[/blue]
Source code string [blue]4[/blue]
Reference string [blue]12[/blue]
Date date [blue]6[/blue]
Description string [blue]30[/blue]
Amount dollar [blue]13[/blue]
[/pre]
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]BLUE[/blue] numbers is just the position/"columns" in the file)
[pre][blue]
1 123456 123456789012 123456789012345678901234567890
1 1234 123456 1234567890123[/blue]
RA 105ARCLREFERENCE 181118DESCRIPTION 2400.5
RA 315ARCLREFERENCE 181118DATA DEPOSIT -2000.5
RA 315ARCLREFERENCE 181118DATA DEPOSIT -500
RA 840ARCLREFERENCE 181118DATA DEPOSIT 100
[/pre]
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:

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:
[tt] "D"," 840"," ","ABCD" [/tt]



---- Andy

There is a great need for a sarcasm font.

 

[JoPaBC]

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.

 

[Andrzejek]

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:

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"[blue]
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)"[/blue]
aryX(6) = .Fields(3)
[green]
'Add Chr(34) " (double quotes) around array elements[/green]
For i = LBound(aryX) To UBound(aryX)
    aryX(i) = Chr(34) & aryX(i) & Chr(34)
Next i
[green]
'Join array elements with comma in between[/green]
VETA = Join(aryX, ",")[green]
'and add the last field without "[/green]
VETA = VETA & "," & .Fields(4)

Print #TextFile, VETA

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

Just a suggestion....


---- Andy

There is a great need for a sarcasm font.

 

[JoPaBC]

Thanks for cleaning my spaghetti code