Excel export delimiter

how about this, it changes the comma separators, but leaves commas within quotes marks

Private Sub SwapSeperator()
Dim bOn As Boolean
Dim b() As Byte
Dim c As Long

Open "c:\download\gram.csv" For Binary As #1
ReDim b(LOF(1) - 1)
Get #1, , b

For c = 0 To UBound(b)
    If Chr(b(c)) = """" Then
        bOn = Not bOn
        ElseIf Not bOn And Chr(b(c)) = "," Then
        b(c) = Asc("+")
    End If
Next
   
Open "c:\download\gram2.csv" For Binary As #2

Put #2, , b

Close #1, #2

End Sub

What about this:

Private Sub Form_Load()
Dim sContents As String
Dim ff As Integer

ff = FreeFile
Open "c:\dmyfile.csv" For Input As #ff
    strcontents = Input(LOF(ff), 1)
Close #ff
strcontents = Replace(strcontents, ",", "+", , , vbTextCompare)

Open "c:\dmyfile.csv" For Output As #ff
    Print #ff, strcontents
Close #ff
End Sub

or from excel itself

http://www.cpearson.com/excel/imptext.htm

Great code but you need to manage too much (to me) excel objects to do a simple job, isn't it?

Hi Richie,

this is a solution completely from within, and i guess you could simply optimize that code for this specific situation without having to deal with path's file checks or whatsoever.....if you're already in excel why not use it?

:O)Bruintje
(of course just pluggin the excel code ;) )

Hi!

How about this (esp. if you're using VB5 rather than VB6):

Dim i As Integer, str As String

i = InStr(1, str, ",")

While i <> 0
Mid$(str, i, 1) = "+"
i = InStr(i + 1, Text1.Text, ",")
Wend

'Save the str variable
'...

Simply open the file in binary, load the entire content into str, use the above code, then write str back to the file and save it : )

That's it!

glass cookie : )

***This is easy, however, I need to change the delimiter to be a + char instead of a , char for reasons of the importation of the csv file into another system.***



if i've properly understood, after generating the csv file(with delimiter) u want to change the "," with "+", is it not? in that case, simply open the text file in a Editor, NoteTablight or VisualInterdev--select all--find "," --replace with-- "+".

otherwise, what did i miss?.

Hmm, you're nearly there but a couple of the Excel fields are descriptions that may contain ',' and therefore doing a global replace will change the commas within the descriptions and then the resulting csv file will get very mixed up thinking it has new fields starting when it hasn't.

Is there no way of saying:

Export this Excel file to a csv and set the delimiter to a +

try this,

open access database and import the excel sheet. once u imported into access table export the same. there it will ask for delimiter. give "+" there.


KCM

The above one is my **PROPOSED ANSWER**

***Export this Excel file to a csv and set the delimiter to a + ***

already posted

Hi ems_place,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept MartijnB's comment(s) as an answer.
    *** the only suggestion that will work with fileds containing embedded commas

ems_place, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer

Comment from expert accepted as answer

Computer101
E-E Admin