add double quotes to a text file 3

[newprogamer]

Hello Everyone,

I have a flat file, which is comma deliminted. Each field has double quotes around it. Except for the last field in the file. The last field of the file has no double quotes weather there is text there or not. This causing a Run time error '62' input past end of file. So, I went to the last field and typed empty quotes if there was so text there and typed quotes if there was text there. This solved the problem and I could get the data from the file.

Is there a way to change the data in the file? I need to add double quotes to the last field.

Here is the code:
Open adpattend For Input Access Read Shared As #2

Input #2, Company_Code, Last_Name, First_Name, In_Time, Out_Time, Out_Punch_Type, Hours, Earnings_Code

Here is the data:
"XXX","BROWN","JOE","01/30/2006 05:57:00 AM","01/30/2006 02:00:00 PM","","8",

"XXX","BROWN","JOET","01/31/2006 06:00:00 AM","01/31/2006 02:00:00 PM","","8",VACTON


Please help! Thanks!
New Programmer

 

[fredericofonseca]

Not the solution you need, but please consider STRONGLY changing your file to be TAB delimited and without the quotes.

A lot easier to deal with.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[cyoung]

You could read the input into an array 1 line at a time, and then test whether or not the final element exists.

This may not be the most elegant solution, but it will work.

 

[Sheco]

Maybe if you use some of the lower level functionality to read the file... Like Get ?

 

[HughLerwill]

newprogrammer,

Try something like this;

Open adpattend For Input Access Read Shared As #2

'place the whole file in the string a$
a$ = input$(Lof(2),2)
'split into two pieces
b$ = Left$(a$, InStrRev(a$, Chr$(44)))
c$ = Mid$(a$, InStrRev(a$, Chr$(44)) + 1)
'put pieces back together again including quotes
d$ = b$ & Chr$(34) & c$ & Chr$(34) ' result in d$

It should be possible to do it all in one line; but I will leave that to you!

regards Hugh

 

[newprogamer]

Thanks,
fredericofonseca: This is a file that is generated automaticaly from our Payroll system. I don't know if I could go back and change the file so that it would be made easier than just adding the quotes.

HughLerwill: I don't understand some things about your code. My data is not fixed length. The first and last name can be any length. What is the input(Lof(2)2) doing? Why do you use 44?

 

[strongm]

Something like

Private Sub Command1_Click()
    Dim fso As FileSystemObject
    Dim destinationfile As TextStream
    
    Set fso = New FileSystemObject
    With fso.OpenTextFile("c:\demo.txt", ForReading)
        Set destinationfile = fso.OpenTextFile("c:\demo_out.txt", ForWriting, True)
        Do Until .AtEndOfStream
            destinationfile.WriteLine """" & Join(Split(Replace(.ReadLine, """", ""), ","), """,""") & """"
        Loop
        .Close
        destinationfile.Close
        fso.DeleteFile "c:\demo.txt"
        fso.MoveFile "c:\demo_out.txt", "c:\demo.txt"
    End With
End Sub

 

[HughLerwill]

chr$(44) represents the comma character
chr$(34) represents the double quote character

Checkout VB6 Help (you must do it more) for the Input$(...,...) syntax.

Fixed length or not is not an issue. The code supplied just puts quotes around all text following the last comma in the file.

Could be you have problems processing the file when it has been converted into a string. However you don't appear to have trouble processing the file <<So, I went to the last field and typed empty quotes if there was so text there and typed quotes if there was text there. This solved the problem and I could get the data from the file.>>

So;

Open adpattend For Input Access Read Shared As #2
'place the whole file in the string a$
a$ = input$(Lof(2),2)
'split into two pieces
b$ = Left$(a$, InStrRev(a$, Chr$(44)))
c$ = Mid$(a$, InStrRev(a$, Chr$(44)) + 1)
'put pieces back together again including quotes
d$ = b$ & Chr$(34) & c$ & Chr$(34) ' result in d$

close 2

'now write out the modified version to a new file
f% = freefile
Open "adpattendMod" For Output As f%
print #f, d$
close f

The "adpattendMod" file should be in the required format.


regards Hugh

 

[Sheco]

An opportunity to use both Split() and Join() on a single line... Priceless!

 

[Ed2020]

Frederico Fonseca,

How is a tab delimited text file with no text delimiter easier to deal with?

I'm not disagreeing, just curious to know your reasons for saying this - I've never had any problems with a comma-separated text file providing the text fields are properly delimited as well.

Ed Metcalfe.

Please do not feed the trolls.....

 

[newprogamer]

Thanks everyone,

HughLerwill, I really like your code and I am going to try it. I'll let you know how it turns out!!! Thanks again.

Does anyone know what the Lof is for?
a$ = input$(Lof(2),2)

Thanks,
New Programmer

 

[gmmastros]

LOF will tell you the size of a file. It can only be used with a file that is opened with the OPEN command.

Open "C:\File.ext" For Input As #2
call MsgBox(LOF(2))
Close #2

Or, better yet.

Dim iFile as Integer

iFile = FreeFile
Open "C:\File.ext" For Input As #iFile
Call MsgBox(Lof(iFile))
Close #iFile

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[newprogamer]

Thanks gmmastros!

How are the other variables suppose to be defined? a$ b$ c$ d$

 

[gmmastros]

Dim a$ as String
Dim b$ as String
Dim c$ as String
Dim d$ as String




-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[newprogamer]

Thanks, I tried that and received a compile error message -"expected end of statement". I removed the $ sign when declaring and didn't receive an error msg. I am getting ready to test. Wish me luck.

Dim a as String
Dim b as String
Dim c as String
Dim d as String

Thanks everyone!

 

[newprogamer]

Hi, this helps a lot. But, the code only puts double quotes around the last field of the entire string. It does not add quotes to the last field of each line. Does anyone know why it is not working? Thanks!

Here is the data in adpattendMOD...
"XXX","BROWN","JOE","02/07/2006 05:53:00 AM","02/07/2006 04:00:00 PM","","10",
"XXX","BROWN","JOE","02/09/2006 06:00:00 AM","02/09/2006 04:00:00 PM","","10",REGSAL
"XXX","BROWN","JOE","02/10/2006 06:00:00 AM","02/10/2006 02:01:12 PM","","8","REGSAL"

This is the code I used...

'chr$(44) represents the comma character
'chr$(34) represents the double quote character

'The code supplied just puts quotes around all text following the last comma in the file.
    Dim a As String
    Dim b As String
    Dim c As String
    Dim d As String
    Dim f As Integer

Open adpattend For Input Access Read Shared As #2
   
   'place the whole file in the string a$
    a$ = Input$(LOF(2), 2)
   
   'split into two pieces
    b$ = Left$(a$, InStrRev(a$, Chr$(44)))
    c$ = Mid$(a$, InStrRev(a$, Chr$(44)) + 1)
   
   'put pieces back together again including quotes
    d$ = b$ & Chr$(34) & c$ & Chr$(34) ' result in d$
   
Close 2

'now write out the modified version to a new file
f% = FreeFile
Open "adpattendMod" For Output As f%
   Print #f, d$
Close f
'The "adpattendMod" file should be in the required format.
'f is opened already as adpatttendmod

 

[gmmastros]

Try strongm's suggestion dated 10 Feb 06 15:33.

You will need to add a reference to the microsoft scripting runtime component.

Click Project -> References
Scroll down to 'Microsoft Scripting Runtime'
Select it.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[HughLerwill]

newprogrammer,

You can use the syntax;

Dim a as String 'the preferred way
or
Dim a$ 'the old fashioned way

I use the latter because 1) I'm an oldprogrammer and 2) its just shorter.

As you discovered the;
Dim a$ as String
combination is not acceptable. Shame on you George<g)!

Regards Hugh

 

[gmmastros]

Hugh,

Sorry. I never learned the 'old fashioned' way.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[newprogamer]

Thanks strongm for the suggestion on Feb 10. I didn't understand how it would work so I tried the other suggestion. I still don't understand the "Join(Split(Replace". After I set the reference gmmastros suggested and put my file name, it worked like a charm.

HughLerwill thanks for your suggestion as well. I just couldn't get the quotes to add to the other lines. It only added to the very last field of the file.

Thanks everyone,
New programmer