Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Export CSV file from worksheet using lists, keep export clean

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Export CSV file from worksheet using lists, keep export clean

    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:

    HTML Code:
    "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:

    Code:
    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

  • #2
    Re: Export CSV file from worksheet using lists, keep export clean

    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

    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...

    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 
    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 ???

    Check out our new reputation system. Click on the "star" under the post!
    _______________________________________________

    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

    Comment


    • #3
      Re: Export CSV file from worksheet using lists, keep export clean

      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!

      Comment


      • #4
        Re: Export CSV file from worksheet using lists, keep export clean

        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?

        Comment


        • #5
          Re: Export CSV file from worksheet using lists, keep export clean

          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:

          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 
              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

          Check out our new reputation system. Click on the "star" under the post!
          _______________________________________________

          There are 10 types of people in the world. Those that understand Binary and those that dont.

          Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

          The BEST Lookup function of all time

          Dynamic Named Ranges are your bestest friend

          _______________________________________________

          Comment


          • #6
            Re: Export CSV file from worksheet using lists, keep export clean

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

            Comment

            Trending

            Collapse

            There are no results that meet this criteria.

            Working...
            X