2

How can I write per column in .csv file using VB6? I use File System Object .writeline to read and write in .csv file but my problem is it only write in one column only. Can someone please help me.

Dim fso As New FileSystemObject
Dim fsoStream As TextStream

Set fsoStream = fso.CreateTextFile("C:\Users\Users\Desktop\File\Sample.csv", True)

fsoStream.WriteLine "Like This is what I have tried" 'this must be written in the first column
fsoStream.WriteLine "But this is a sample only" 'this must be written in the first column
fsoStream.WriteLine "Sample 1" 'this must be written in the second column
fsoStream.WriteLine "Sample 1" 'this must be written in the second column
fsoStream.WriteLine "Sample 2" 'this must be written in the third column
fsoStream.WriteLine "Sample 2" 'this must be written in the third column

fsoStream.Close
Set fsoStream = Nothing
Set fso = Nothing

This picture must be the output.

enter image description here

but this is what I have got in using the code above enter image description here

Improve this question
4
  • Please provide code for what you have tried Commented Apr 23, 2014 at 7:00
  • You have to add your own commas. fso.writeline var1 & "," & var2 Commented Apr 23, 2014 at 7:43
  • @tonybd i edit my question and add some picture. How can I make it like that? Commented Apr 23, 2014 at 7:50
  • Use: fsoStream.WriteLine data1 + your_delimiter + data2 + your_delimiter + data3 Commented Apr 23, 2014 at 9:42

5 Answers 5

Reset to default
2

As the others have pointed out you write an entire line of text at once. Text files don't have the concept of columns so you need to rethink how you're preparing your data. User tony bd suggested that you use a disconnected recordset and I think he is right. Recordsets allow you to easily assign data to columns. Once you have processed all of your data into the recordset you can then save it to a csv file.

Add a reference to Microsoft ActiveX Data Objects 2.8 Library

Option Explicit

Private mMyRs As ADODB.Recordset

Private Sub Form_Load()

    CreateRecordset

End Sub

Private Sub Command1_Click()

    SaveRecordset

End Sub

Private Sub CreateRecordset()

    Set mMyRs = New ADODB.Recordset
    With mMyRs
        Set .ActiveConnection = Nothing
        .CursorLocation = adUseClient
    End With

    With mMyRs.Fields
        .Append "Column1", adVarChar, 40
        .Append "Column2", adVarChar, 20
        .Append "Column3", adVarChar, 20
    End With
    mMyRs.Open

    mMyRs.AddNew
    mMyRs.Fields("Column1").Value = "Like This is what I have tried"
    mMyRs.AddNew
    mMyRs.Fields("Column1").Value = "But this is a sample only"
    mMyRs.MoveFirst
    mMyRs.Fields("Column2").Value = "Sample 1"
    mMyRs.MoveNext
    mMyRs.Fields("Column2").Value = "Sample 1"
    mMyRs.MoveFirst
    mMyRs.Fields("Column3").Value = "Sample 2"
    mMyRs.MoveNext
    mMyRs.Fields("Column3").Value = "Sample 2"

End Sub

Private Sub SaveRecordset()
    Dim fHndl As Integer
    Dim strCsvFile As String

    strCsvFile = "C:\Temp\MyCSV.csv"
    fHndl = FreeFile

    Open strCsvFile For Output As fHndl
    mMyRs.MoveFirst
    While mMyRs.EOF = False
        Print #fHndl, mMyRs.Fields("Column1").Value & "," & mMyRs.Fields("Column2").Value & "," & mMyRs.Fields("Column3").Value
        mMyRs.MoveNext
    Wend
    Close #fHndl

End Sub

Result

csv results image

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You can build a string for each row, and when the row-string is complete then write it to file

for example using the code from your post :

Dim strLine As String
Dim fso As New FileSystemObject
Dim fsoStream As TextStream

Set fsoStream = fso.CreateTextFile("C:\Users\Users\Desktop\File\Sample.csv", True)

'prepare the first row
strLine = "Like This is what I have tried" 'this must be written in the first column
'write the first row
fsoStream.WriteLine strLine

'prepare the second row
strLine = "But this is a sample only"      'this must be written in the first column
strLine = strLine & "," & "Sample 1"       'this must be written in the second column
'write the seconde row
fsoStream.WriteLine strLine

'prepare the third row
strLine = ""                               'an empty first column
strLine = strLine & "," & "Sample 1"       'this must be written in the second column
strLine = strLine & "," & "Sample 2"       'this must be written in the third column
'write the third row
fsoStream.WriteLine strLine

'prepare the fourth row
strLine = ""                               'an empty first column
strLine = strLine & ","                    'an empty second column
strLine = strLine & "," & "Sample 2"       'this must be written in the third column
'write the fourth row
fsoStream.WriteLine strLine

fsoStream.Close
Set fsoStream = Nothing
Set fso = Nothing

This will not give the result which you posted in your desired output picture, but that is because of the extra write actions you had in your original code as well

Just delete those extra lines in your code, and the result will be as you posted in the picture. I am sure you can find which extra lines i am talking about :)

Improve this answer

Comments

1

Assuming you know what columns you want to write in advance, the following can be used (with optional header row:

Dim iFileNo As Integer

iFileNo = FreeFile

Open sFileName For Output As #iFileNo
Write #iFileNo, "ID", "Name", "Age", "Address"
Write #iFileNo, 1, "Person #1", 42, "Place #1"
Write #iFileNo, 2, "Person #2", 57, "Place #2"
Close #iFileNo

You read this as follows:

Dim sHeaders As String
Dim iFileNo As Integer
Dim lID As Long
Dim sName As String
Dim iAge As Integer
Dim sAddress As String

iFileNo = FreeFile

Open sFileName For Input As #iFileNo
Line Input #iFileNo, sHeaders
Do Until Eof(iFileNo)
    Input #iFileNo, lID, sName, iAge, sAddress
Loop
Close #iFileNo
Improve this answer

12 Comments

the data is dynamically written in the csv file assuming that the first line of the parsed data is done then it is written in the first column of the csv file then the second line of the parsed data is done then it will write at the second column of the csv file and so forth..
@bebebe Sorry - I don't understand what you mean. There is no punctuation in that last comment.
to make it easy to explain.. it will write horizontally in CSV file not vertically.
@bebebe The method is called "WriteLine". So it is doing exactly what it says. I would suggest you use the built-in VB Write statement, as in my code.
What I would do is create a disconected recordset. It's the ultimate datatype. Then write your data how you want. Disconected recordsets can save themselves to file as XML or a binary format. Or you can read through it, a row at a time writing each column like so fso.writeline rs.Fields("column1").Value & "," rs.Fields("column2").Value. You could also use an array. See my posts here social.msdn.microsoft.com/Forums/de-DE/… The sample code of interest is at the bottom
|
1

The other answers are fine, but the simplest modification to your code would be this:

fsoStream.WriteLine "Like This is what I have tried, Sample1, Sample2" 
fsoStream.WriteLine "But this is a sample only, Sample1, Sample2"

in place of this:

fsoStream.WriteLine "Like This is what I have tried" 'this must be written in the first column
fsoStream.WriteLine "But this is a sample only" 'this must be written in the first column
fsoStream.WriteLine "Sample 1" 'this must be written in the second column
fsoStream.WriteLine "Sample 1" 'this must be written in the second column
fsoStream.WriteLine "Sample 2" 'this must be written in the third column
fsoStream.WriteLine "Sample 2" 'this must be written in the third column

The point is that "csv" means "comma-separated values". That means that each column has to be separated by a comma, and on the same line. New line means new row, as you have already seen for yourself.

Improve this answer

Comments

1

I see no reason though to use the FileSystemObject, it's much easier to use the standard open,close,print functions from VB

To create the output you want you can use the following code:

Dim intFile As Integer
Dim strFile As String
Dim strLine As String

'open the file
intFile = FreeFile
strFile = "c:\temp\test.csv"
Open strFile For Output As #intFile

  'prepare the first row
  strLine = "Like This is what I have tried" 'this must be written in the first column
  strLine = strLine & "," & "Sample 1"       'this must be written in the second column
  strLine = strLine & "," & "Sample 2"       'this must be written in the third column
  'write the first row
  Print #intFile, strLine

  'prepare the second row
  strLine = "But this is a sample only"      'this must be written in the first column
  strLine = strLine & "," & "Sample 1"       'this must be written in the second column
  strLine = strLine & "," & "Sample 2"       'this must be written in the third column
  'write the seconde row
  Print #intFile, strLine

'close the file
Close #intFile

Instead of writing each line separately you can also build the whole csv in 1 string by adding a vbCrLF to the previous line and then add the next line to the string

strTotalLines = strTotalLines & vbCrLf & strNewLine

and write the total lines to file at the end in 1 print statement

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.