Export CSV file from worksheet using lists, keep export clean

[intheory]

I am developing a workbook for other users to use that they will enter data into, then export the main worksheet as a CSV for importing into a database.

The columns/fields are member_id, Category Name, and Category Text. Category Name must match a very restricted list of options, so using Data Validation, I've made that a list pulled from another sheet in the workbook. The user sees that as a pull-down menu in the Category Name field.

I have a macro that saves the main worksheet as a CSV--that part works fine.

My problem is that I need the data validation list in each row for the user to use. I thought I could just paste it to about 50 rows. But if I export the CSV leaving some rows blank, it still exports those blank rows, which causes confusion for the database I'm importing the CSV to. It looks like this:

"member_id","Category Name","Category Text"
"73","Category 1","Test text, it goes here."
"3","Category 3","asdfasdfsadf"
"999","Category 5","sfasdfasdfsadfsdf"
""
""
""
""
""
""
""
""

Those blank entries ("") cause trouble for my import. The import tool (which I can't modify) sees those as invalid entries, and won't take my import.

I need a way to have a user enter a row of data, and when they go to the next row, it creates the data validation list for that row on the fly. Or if there is a way to clear those out of the CSV as part of the macro that would work also. Hope that's clear. I'd be happy to clarify if needed. Thanks for any help!

Here is the macro I'm using for the CSV export:

Sub NotesExportToCSV() 
     
    Dim TheFileSaveName As String, vFileNum As Integer, qcq As String, tempStr As String 
    Dim i As Long, j As Integer 
     
TryAgain: 
    TheFileSaveName = Application.GetSaveAsFilename(initialfilename:="NotesExport_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hh-mm-ss"), filefilter:= _ 
    "CSV (Comma delimited) (*.csv), *.csv", Title:="Please enter filename to export to") 
    If TheFileSaveName = "False" Then End 
     
    vFileNum = FreeFile() 
    On Error Resume Next 
    Open TheFileSaveName For Output As #vFileNum 
    If Err <> 0 Then MsgBox "Cannot save to filename " & TheFileSaveName: End 
    On Error Goto 0 
     
    qcq = Chr(34) & Chr(44) & Chr(34) 
     
    For i = 1 To [a1].SpecialCells(xlLastCell).Row 
        For j = 1 To Cells(i, 256).End(xlToLeft).Column 
            If j = 1 Then tempStr = Cells(i, j).Text Else tempStr = tempStr & qcq & Cells(i, j).Text 
        Next j 
        Print #vFileNum, Chr(34) & tempStr & Chr(34) 
    Next i 
     
    Close #vFileNum 
End Sub 

[Ger Plante]

Hi intheory,

if I am reading you post right, you simply dont want to have two double quotes ("") on a line to represent an empty row in your CSV file... if this is the case, why dont you just prevent them from being printed in the first place... could you change this code

For i = 1 To [a1].SpecialCells(xlLastCell).Row 
    For j = 1 To Cells(i, 256).End(xlToLeft).Column 
        If j = 1 Then tempStr = Cells(i, j).Text Else tempStr = tempStr & qcq & Cells(i, j).Text 
    Next j 
    Print #vFileNum, Chr(34) & tempStr & Chr(34) 
Next i 

to...

 
For i = 1 To [a1].SpecialCells(xlLastCell).Row 
    For j = 1 To Cells(i, 256).End(xlToLeft).Column 
        If j = 1 Then tempStr = Cells(i, j).Text Else tempStr = tempStr & qcq & Cells(i, j).Text 
    Next j 
    If len(trim(tempstr))>0 Then 
        Print #vFileNum, Chr(34) & tempStr & Chr(34) 
    Else 
        print #vFileNum, " " 
    End If 
Next i 

Sorry, this code is untested, but it is supposed to print a blank line without double quotes if tempstr is empty... u know what I mean ???

[intheory]

That's an idea that I will try...I think I need the double quotes for the Category Text field, as it will contain commas and other punctuation. But yeah, for blank rows just removing the "" should work. I'll give that code a shot and see. Thanks for the suggestion!

[intheory]

That worked great, thanks so much!

Now...just to tweak it a bit...is there anyway to actually remove the blank rows from the export? I tried removing the space and just having

Print #vFileNum, 
End If 
Next i 

, but it still leaves a blank row in the CSV.

Am I revealing my lack of knowledge too much with this?

[Ger Plante]

Sorry for the delay in replying, I was on holidays yesterday.

But yeah, If you want to remove the blank row from the csv file then basically dont output anything to the CSV unless the string length is one or more characters... in this case, all you need to do is just eliminate the Else statement.

So your code would look like:

 
For i = 1 To [a1].SpecialCells(xlLastCell).Row 
    For j = 1 To Cells(i, 256).End(xlToLeft).Column 
        If j = 1 Then tempStr = Cells(i, j).Text Else tempStr = tempStr & qcq & Cells(i, j).Text 
    Next j 
    If len(trim(tempstr))>0 Then 
        Print #vFileNum, Chr(34) & tempStr & Chr(34) 
    End If 
Next i 

That should remove any blank rows (well technically, its not that it will remove them, it just wont output them in the fist place to the CSV file, if you know what I mean...).

Ger

[intheory]

Thanks for all of your help. This has worked exactly how I need it to. I really appreciate your time and assistance!