Excel export delimiter

I have a situation where a user wants to type information into columns within a spreadsheet and that information needs to be saved as a csv file.

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.

How do I do this?

Thanks,

Emma
ems_placeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
MartijnBConnect With a Mentor Commented:
Hi,

You do not need to write code to parse or change the file.
You can use the "Microsoft Text Driver" to import and export information from any office application.

To tell Excel / Access / any app using this driver how to format the .csv file, you can create a schema.ini file.
All information about this configuration file is here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q210001 
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q210073

In the .ini you change the delimiter and Excel will do the job for you AND other applications can still read the file.

Martijn
0
 
deightonCommented:
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
0
 
Richie_SimonettiIT OperationsCommented:
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
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
bruintjeCommented:
0
 
Richie_SimonettiIT OperationsCommented:
Great code but you need to manage too much (to me) excel objects to do a simple job, isn't it?
0
 
bruintjeCommented:
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 ;) )

0
 
glass_cookieCommented:
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 : )
0
 
Chandramouli kArchitectCommented:
***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?.


0
 
ems_placeAuthor Commented:
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 +
0
 
Chandramouli kArchitectCommented:
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

0
 
Chandramouli kArchitectCommented:
The above one is my **PROPOSED ANSWER**
0
 
bruintjeCommented:
***Export this Excel file to a csv and set the delimiter to a + ***

already posted
0
 
DanRollinsCommented:
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
0
 
Computer101Commented:
Comment from expert accepted as answer

Computer101
E-E Admin
0
All Courses

From novice to tech pro — start learning today.