Quotes in CSV Files

[Help?]

I am trying to find a macro that will force the "" around the below to not
happen. Because the below has a comma in it, on csv convertion it
automatically insert the "". Can I force this through VBA macro to not do
this? This will be the third time I have asked for help, but not received
yet. Can anyone help me in this?

Coming out as:

"%!
XGF
9999 SETBUFSIZE
(,) SETDBSEP
QSTRIP_on
(GuntherIPEP.jdt) SETJDT
(IPEP.dbm) STARTDBM"


I need it to be:

%!
XGF
9999 SETBUFSIZE
(,) SETDBSEP
QSTRIP_on
(GuntherIPEP.jdt) SETJDT
(IPEP.dbm) STARTDBM

 

[Bob O`Bob]

I am trying to find a macro that will force the "" around the below to not
happen. Because the below has a comma in it, on csv convertion it
automatically insert the "". Can I force this through VBA macro to not do
this? This will be the third time I have asked for help, but not received
yet. Can anyone help me in this?

I'm sure many could, but lots of us in the newsgroups would rather not
lead you down a path which goes right off some /other/ cliff.

Are you aware of what the 'C' in "CSV" means?

If you were to do what you seem to be describing, the resulting file
WOULD NOT BE a valid csv file, and therefore unlikely to be read /in/ correctly
by whatever you intend to send it to.

Coming out as:

"%!
XGF
9999 SETBUFSIZE
(,) SETDBSEP
QSTRIP_on
(GuntherIPEP.jdt) SETJDT
(IPEP.dbm) STARTDBM"


I need it to be:

%!
XGF
9999 SETBUFSIZE
(,) SETDBSEP
QSTRIP_on
(GuntherIPEP.jdt) SETJDT
(IPEP.dbm) STARTDBM

I have my doubts that you sufficiently understand the ramifications
of what you are asking for.



Bob
--

 

[Help?]

Yes, I understand the ramifications and what CSV means. However, for the
below text, the csv would work exactly the way it should with the program we
are coordinating with. The below helps to tell another program which VIPP
Xerox system to use. Underneath the below header information will be the
comma delimited information that will then be interpreted for printing. But,
If the header information has "" around it, it will not choose the
appropriate program to use, thus not getting to the csv actual data. Do you
know of a way?

 

[PaulD]

Why not write a second macro to clean the quotes from your csv file? Just
read each line and do a string replace if it finds a quote.
Paul D

: Yes, I understand the ramifications and what CSV means. However, for the
: below text, the csv would work exactly the way it should with the program
we
: are coordinating with. The below helps to tell another program which VIPP
: Xerox system to use. Underneath the below header information will be the
: comma delimited information that will then be interpreted for printing.
But,
: If the header information has "" around it, it will not choose the
: appropriate program to use, thus not getting to the csv actual data. Do
you
: know of a way?
:
: "Bob O`Bob" wrote:
:
: > Help? wrote:
: > > I am trying to find a macro that will force the "" around the below to
not
: > > happen. Because the below has a comma in it, on csv convertion it
: > > automatically insert the "". Can I force this through VBA macro to not
do
: > > this? This will be the third time I have asked for help, but not
received
: > > yet. Can anyone help me in this?
: >
: > I'm sure many could, but lots of us in the newsgroups would rather not
: > lead you down a path which goes right off some /other/ cliff.
: >
: > Are you aware of what the 'C' in "CSV" means?
: >
: > If you were to do what you seem to be describing, the resulting file
: > WOULD NOT BE a valid csv file, and therefore unlikely to be read /in/
correctly
: > by whatever you intend to send it to.
: >
: > >
: > > Coming out as:
: > >
: > > "%!
: > > XGF
: > > 9999 SETBUFSIZE
: > > (,) SETDBSEP
: > > QSTRIP_on
: > > (GuntherIPEP.jdt) SETJDT
: > > (IPEP.dbm) STARTDBM"
: > >
: > >
: > > I need it to be:
: > >
: > > %!
: > > XGF
: > > 9999 SETBUFSIZE
: > > (,) SETDBSEP
: > > QSTRIP_on
: > > (GuntherIPEP.jdt) SETJDT
: > > (IPEP.dbm) STARTDBM
: > >
: >
: > I have my doubts that you sufficiently understand the ramifications
: > of what you are asking for.
: >
: >
: >
: > Bob
: > --
: >

 

[Help?]

Paul,
I tried and the "" are still registering due to the comma in the header
information. I also tried xltestprinter which got rid of the "" but also got
rid of the comma delimiter which I can't have happen. Any suggestions?

 

[JMB]

This appeared to work. I've found TextStreamObjects will write the data to
file w/o enclosing it in quotes. You'll need to test to see if it works for
what you are doing. You will need to set up a reference to the Scripting
library (Tools/References - Microsoft Scripting Runtime). Change strFileIn
and out for your filenames.

To check, I changed the extension on the CSVClean file to .txt so it would
open in Notepad. I'm sure you know that if you open the cleaned file in
Excel, the (,) will split the data into two columns.

Sub test2()
Const strFileIn As String = "I:\Excel\CSVData.csv"
Const strFileOut As String = "I:\Excel\CSVClean.csv"
Dim FSO As Scripting.FileSystemObject
Dim fDest As Scripting.TextStream
Dim strData As String
Dim lngFNum1 As Long

Set FSO = New Scripting.FileSystemObject
Set fDest = FSO.CreateTextFile(strFileOut, True)

lngFNum1 = FreeFile()
Open strFileIn For Input As #lngFNum1

Do While Not EOF(lngFNum1)
Line Input #lngFNum1, strData
strData = Replace(strData, """", "", 1, -1, vbTextCompare)
fDest.WriteLine strData
Loop

Close
fDest.Close

End Sub

 

[Help?]

JMB,
I can't seem to get this to work properly. However, I have an idea but do
not know how to accomplish with a macro. Could I take the header information,
keep it in word or notepad and have an excel macro write the xlcsv to that
template? If so how would I do that?

 

[Help?]

JMB,
I think I am so close to getting what you gave me to work. However, it wants
to debug at fdest. Also, I am not sure what you mean by "Change strFileIn and
out for your filenames"?

 

[Help?]

Okay, I've got it working. But I have one problem. I need the below to except
variable filenames and keep that file name consistant. Right now I have the
filename pulling from columns in the excel for bothe csv and excel pieces
that is automatically saved. How can I make the below to except variable
filename?

Const strFileIn As String = "I:\Excel\CSVData.csv"
Const strFileOut As String = "I:\Excel\CSVClean.csv"

 

[JMB]

I changed two lines in the variable declaration (marked "Change") and added 4
new lines (marked "New", however one of the lines takes up 2 lines. In VBA
the underscore _ is the line continuation character). The NG often wraps
things funny, so hopefully you will be able to sort it out.

It creates a temporary file using the original's name plus "Temp". Then
when it is done it deletes the original data file and renames the temporary
file to the original data filename.

Sub test2()
Dim strFileIn As String '<Change
Dim strFileOut As String '<Change
Dim FSO As Scripting.FileSystemObject
Dim fDest As Scripting.TextStream
Dim strData As String
Dim lngFNum1 As Long

strFileIn = Application.GetOpenFilename '<NEW
strFileOut = Replace(strFileIn, ".csv", _
"Temp.csv", 1, 1, vbTextCompare) '<NEW
Set FSO = New Scripting.FileSystemObject
Set fDest = FSO.CreateTextFile(strFileOut, True)

lngFNum1 = FreeFile()
Open strFileIn For Input As #lngFNum1

Do While Not EOF(lngFNum1)
Line Input #lngFNum1, strData
strData = Replace(strData, """", "", 1, -1, vbTextCompare)
fDest.WriteLine strData
Loop

Close
fDest.Close

Kill strFileIn '<NEW
Name strFileOut As strFileIn '<NEW

End Sub

 

[Help?]

JMB,
You've been awesome with this and I like what you have given me, but is
there a way to keep what you have given me and have the final data file
maintain the variable file (which) you have given me, have the removal of the
"" only on range A1 and save directly to a certain path?

 

[Help?]

JMB,
Here is the code I am trying to use:
Sub test2()
Const strFileIn As String = "I:\04 Production Files\IPEP Records\Cleaning
files\"
ActiveWorkbook.SaveAs filename:=ActiveWorkbook.FullName
Const strFileOut As String = "I:\04 Production Files\IPEP Records\Cleaned\"
ActiveWorkbook.SaveAs filename:=ActiveWorkbook.FullName
Dim FSO As Scripting.FileSystemObject
Dim fDest As Scripting.TextStream
Dim strData As String
Dim lngFNum1 As Long


Set FSO = New Scripting.FileSystemObject
Set fDest = FSO.CreateTextFile(strFileOut, True)<Problem

lngFNum1 = FreeFile()
Open strFileIn For Input As #lngFNum1<Problem

Do While Not EOF(lngFNum1)
Line Input #lngFNum1, strData
strData = Replace(strData, """", "", 1, -1, vbTextCompare)
fDest.WriteLine strData
Loop

Close
fDest.Close

End Sub

I liked the way you had everything directed in paths in the first set of
coding, but I am having a hard time get the areas above to recognize the
filenames. I have marked the ares with the word "<Problems". Do you have any
suggestions?

 

[JMB]

I believe this will work to remove the quotes from the first line of data in
the CSV file (but if you had other data in row 1 of your file when it was
saved as a CSV file, say cell B1, then I think that will be a problem and
we'll need to do more parsing of the first line of data).

Is this closer to what you're looking for?

Sub test3()
Const strPathIn As String = "I:\04 Production Files\IPEP
Records\Cleaningfiles\"
Const strPathOut As String = "I:\04 Production Files\IPEP Records\Cleaned\"
Dim strFileIn As String
Dim strFileOut As String
Dim FSO As Scripting.FileSystemObject
Dim fDest As Scripting.TextStream
Dim strData As String
Dim lngFNum1 As Long

ChDir strPathIn
strFileIn = Application.GetOpenFilename
If strFileIn = "" Or strFileIn = "False" _
Then Exit Sub
strFileOut = Replace(strFileIn, ".csv", _
"Temp.csv", 1, 1, vbTextCompare)
strFileOut = strPathOut & Right(strFileOut, _
Len(strFileOut) - InStrRev(strFileOut, _
"\", -1, vbTextCompare))
Set FSO = New Scripting.FileSystemObject
Set fDest = FSO.CreateTextFile(strFileOut, True)

lngFNum1 = FreeFile()
Open strFileIn For Input As #lngFNum1

Do While Not EOF(lngFNum1)
If Seek(lngFNum1) = 1 Then
Line Input #lngFNum1, strData
strData = Replace(strData, """", "", 1, -1, vbTextCompare)
Else: Line Input #lngFNum1, strData
End If
fDest.WriteLine strData
Loop

Close
fDest.Close

End Sub