Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a macro which connects to db and fetches data for me and makes it comma separated. But the problem is the comma is getting appended to the last row, which I don't want. I'm struggling here. Could you please help out?

Here is the part from the code.

If cn.State = adStateOpen Then
  Rec_set.Open "SELECT  concat(trim(Columns_0.ColumnName), '  ','(', 'varchar(2000)' ,')')  columnname FROM    DBC.Columns Columns_0 WHERE   (Columns_0.TableName= " & Chr(39) & Tablename & Chr(39) & "and Columns_0.Databasename=" & Chr(39) & db & Chr(39) & ")ORDER   BY Columns_0.Columnid;", cn 'Issue SQL statement
  If Not Rec_set.EOF And Not Rec_set.EOF Then

  Do Until Rec_set.EOF

       For i = 0 To Rec_set.Fields.Count - 1
           strString = strString & Rec_set(i) & ","
       Next
       strFile.WriteLine (strString)

       strString = ""
       Rec_set.MoveNext
  Loop

Here is the result I am getting.

EMPNO (varchar(2000)), ENAME (varchar(2000)), JOB (varchar(2000)), MGR (varchar(2000)), HIREDATE (varchar(2000)), SAL (varchar(2000)), COMM (varchar(2000)), DEPTNO (varchar(2000)),

I don't want the last comma on the last line.

share|improve this question
2  
The last character you can delete like this: strString = Left(strString, Len(strString) - 1) –  duDE Jun 25 '13 at 13:54
    
Thanks! but its deleting the comma from each line! :( –  Binay Jun 26 '13 at 8:35
    
That depends on where do you make it or not? ;) –  duDE Jun 26 '13 at 8:39
    
So put "strString = Left(strString, Len(strString) - 1)" between Next and strFile.WriteLine. Now it only strips the last character right before writing. –  Jon Peltier Jul 21 '13 at 20:31

3 Answers 3

After a lot of RnD, thisone is working.

  Do Until Rec_set.EOF
       For i = 0 To Rec_set.Fields.Count - 1
           strString = strString & Rec_set(i) & "," & vbNewLine
       Next
       Rec_set.MoveNext
  Loop
        strString = Left(strString, Len(strString) - 3)
        strFile.WriteLine (strString)
        strString = ""

But please let me know, if this will create any problem though its giving the desired result!

share|improve this answer

Something like this should do it:

  Do Until Rec_set.EOF

       For i = 0 To Rec_set.Fields.Count - 1

           strString = strString & Rec_set(i)

           if i < Rec_set.Fields.Count - 1
               strString = strString & ","
           end if 

       Next
       strFile.WriteLine (strString)

       strString = ""
       Rec_set.MoveNext
  Loop

That way it appends the record set info to strString on each iteration through the loop, yet only appends the comma in all but the last iteration of the loop.

share|improve this answer
    
Hi, thanks for the answer. I tried this one. But its trimming the comma from each line. :( i want to delete from the last line in the loop. –  Binay Jun 26 '13 at 8:30

After Next enter the line below:

If Len(strString)>1 Then strString = Left(strString, Len(strString) - 1)

This condition checks that you've actually built a string from the record set before trying to strip off the trailing comma.

share|improve this answer
    
Hi! Thanks for the answer! i tried this one. but its deleting the comma from each line! :( –  Binay Jun 26 '13 at 8:31
    
@Binay Ah, sorry, I misunderstood your request. It looks like you've got the right idea in your posted answer though. –  Excellll Jun 26 '13 at 14:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.