Delete empty rows in CSV files in vbscript

Question

I'm trying to export CSV files and this is my code. The Excel CSV files have some empty cells and I wanted to delete them so I added some code (which I have marked in the comments). The program doesn't have any errors but the empty cell still there. Hopefully, someone can tell me what's wrong.

<%Option Explicit%>
<%
Dim strConn, strScriptName,strSQL

strConn = Application("eDSNSMS")

strSQL = Request.querystring("SQL")

sub Write_CSV_From_Recordset(RS)
  if RS.EOF then
        exit sub
    end if

    dim RX
    set RX = new RegExp
        RX.Pattern = "\r|\n|,|"""

    dim i
    dim Field
    dim Separator

    do until RS.EOF
        Separator = ""
        for i = 0 to RS.Fields.Count - 1
            Field = RS.Fields(i).Value & ""
            if RX.Test(Field) then
                Field = """" & Replace(Field, """", """""") & """"
            end if

            If Left(Field, 2) = "01" and  InStr(Field, "-") <> 0 Then
                if Len(Field) = 11 Then
                 Field = "6" & Field
                 Field = """" & Replace(Field, "-", "") & """"
                else
                 Field = ""
                end if
            elseif Left(Field, 2) = "01"  and  InStr(Field, "-") = 0 then
                if Len(Field) = 10 Then
                 Field = "6" & Field
                else
                 Field = ""
                end if
            elseif Left(Field, 3) = "011"  and  InStr(Field, "-") <> 0 then
                if Len(Field) = 12 Then
                 Field = "6" & Field
                 Field = """" & Replace(Field, "-", "") & """"
                else
                 Field = ""
                end if
            elseif Left(Field, 3) = "011"  and  InStr(Field, "-") = 0 then
                if Len(Field) = 11 Then
                 Field = "6" & Field
                else
                 Field = ""
                end if
            elseif Left(Field, 2) <> "01" and IsNumeric(Field) = true then
                 Field = ""
            elseif Left(Field, 2) <> "01" and InStr(Field, "-") <> 0 then
                 Field = ""
            end if
            Response.Write Separator & Field
            Separator = ","
        next
        Response.Write vbNewLine
        RS.MoveNext
    loop
end sub

Dim objRS, objConn, objFile, objFSO, strNewContents

 ' Const ForReading = 1
 ' Const ForWriting = 2

set objConn = server.CreateObject("ADODB.Connection")
objConn.ConnectionString = strConn
objConn.Open
set objRS = server.CreateObject("ADODB.RecordSet")
objRS.Open strSQL, strConn, 0, 1

 ' Set objFSO = CreateObject("Scripting.FileSystemObject")
 ' Set objFile = objFSO.OpenTextFile("export.csv", ForReading)

 ' Do Until objFile.AtEndOfStream
     ' strLine = objFile.Readline
     ' strLine = Trim(strLine)
     ' If Len(strLine) > 0 Then
         ' strNewContents = strNewContents & strLine & vbCrLf
     ' End If
 ' Loop

 ' objFile.Close

 ' Set objFile = objFSO.OpenTextFile("export.csv", ForWriting,true)
 ' objFile.Write strNewContents
 ' objFile.Close

Write_CSV_From_Recordset objRS
Response.ContentType = "text/csv"
Response.AddHeader "Content-Disposition", "attachment;filename=export.csv"
%>

Bond · Accepted Answer · 2015-08-26 13:07:21Z

1

If you want to keep from writing empty fields to your CSV, test the field just before you write it. For example, change:

Response.Write Separator & Field

To:

If Len(Field) > 0 Then
    Response.Write Separator & Field
End If

But unless all values in this specific column/field are blank, doing this is going to throw off the alignment of your CSV.

answered Aug 26, 2015 at 13:07

Bond

16.3k6 gold badges34 silver badges56 bronze badges

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

9 Comments

Jan Over a year ago

thank you, u r helpful..i have try but yet the cell is still empty.. i have make some unwanted phone number to Field = "" (empty cell), so i wish to delete the empty cell that i made...@bond

Bond Over a year ago

I see you've added a screenshot. Are you trying to prevent writing empty rows?

Jan Over a year ago

ya, i have make the unwanted field to empty, that's why the cell/row is empty..so i want to delete the empty cell and move upward the record down there..@bond

Bond Over a year ago

OK, that changes things. You can't write a field at a time then. You'll need to write a row at a time. So instead of Response.Write Separator & Field, you'll need to concatenate your field values into a string that represents a row in your CSV. Something like: strRow = strRow & Separator & Field. Then you can write your row (Response.Write strRow & vbNewline) only if it contains data.

Jan Over a year ago

and then the record become what i screen shot now in my question..is it possible to make the format same as the previously?

|

Collectives™ on Stack Overflow

Delete empty rows in CSV files in vbscript

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related