Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Export CSV file from worksheet using lists, keep export clean

  1. #1
    Join Date
    30th July 2005
    Posts
    5

    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:

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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,519

    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

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

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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    30th July 2005
    Posts
    5

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    30th July 2005
    Posts
    5

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,519

    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:

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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    30th July 2005
    Posts
    5

    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!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. VBA To Export To Xls File
    By heravnda in forum EXCEL HELP
    Replies: 2
    Last Post: December 13th, 2007, 05:20
  2. vba export rows to txt file
    By gmt_v_oz in forum EXCEL HELP
    Replies: 1
    Last Post: April 21st, 2006, 18:16
  3. VBA export file with datestamp?
    By Psilocybin in forum EXCEL HELP
    Replies: 9
    Last Post: August 3rd, 2005, 23:54
  4. Export Excel Worksheet to File (with Macro)
    By StrongSteve in forum EXCEL HELP
    Replies: 1
    Last Post: July 12th, 2004, 20:12

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno