+ Reply to Thread
Results 1 to 10 of 10

How To Write A Column To CSV (Create Multiple CSVs for Multiple Columns)?

Hybrid View

eryksd How To Write A Column To CSV... 01-27-2016, 11:17 PM
jewelsharma Re: How To Write A Column To... 01-27-2016, 11:50 PM
eryksd Re: How To Write A Column To... 01-28-2016, 01:22 AM
jewelsharma Re: How To Write A Column To... 01-28-2016, 01:58 AM
eryksd Re: How To Write A Column To... 01-28-2016, 01:58 PM
eryksd Re: How To Write A Column To... 01-28-2016, 07:08 PM
jewelsharma Re: How To Write A Column To... 01-28-2016, 11:05 PM
eryksd Re: How To Write A Column To... 01-29-2016, 12:43 AM
jewelsharma Re: How To Write A Column To... 01-29-2016, 01:22 AM
eryksd Re: How To Write A Column To... 01-29-2016, 01:38 AM
  1. #1
    Registered User
    Join Date
    11-05-2015
    Location
    San Diego, CA
    MS-Off Ver
    Professional Plus 2010
    Posts
    18

    Question How To Write A Column To CSV (Create Multiple CSVs for Multiple Columns)?

    Hello,

    I have many columns in a worksheet, and would like to make a CSV file for each one, with 2 conditions:

    1. Extract the header of the column, and place it in cell A1 of the new CSV
    2. For all the keywords below the header, save them as a string in one cell, surrounded by quotation marks (" "), in cell B1

    I wrote the sample code, however cannot get it to work... does anyone have any suggestions on what I'm doing wrong? (I'm very new to VBA):

    Sub CreateCSV_Single_Sheet_v4()
    
    Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As Integer
    Dim rngHeaders As Range
    Dim m
    Dim n
    Dim v 'to store last row in column
    Dim h 'to store last column in header
    Dim S1 As String, S2 As Range
    Dim S2_Concatinate As String
    'Dim sht As Worksheet 'the sheet where you want the job done
    
    
    h = Range("E1").End(xlToRight).Column
    Set rngHeaders = Range(Cells(1, 5), Cells(1, h)) 'the range with the headers E = Column 5
    'Cells 1 5 = E1
    'Cells 1 h = x1 where x is the last column with data
    
    n = 5
    For Each m In rngHeaders     'for each cell in the headers (every cell in row 1)
        v = Cells(n, 1).End(xlDown).Row     'the number of the last row in column n
        S1 = Cells(n, 1)                                      'write the header string to variable S1
        Set S2 = Range(Cells(n, 1), Cells(n, v))                  'write the rest of the cells to Range S2
        
        myFile = "E:\" & m & ".csv"
        Open myFile For Output As #1
        
        S2_Concatinate = ConcatinateAllCellValuesInRange(S2)
        
            cellValue = Cells(1, n).Value
                Write #1, cellValue
            cellValue = S2_Concatinate
                Write #1, cellValue
        
        Close #1
    Next m
    
    End Sub
    
    Function ConcatinateAllCellValuesInRange(S2) As String
        Dim finalValue As String
    
        Dim cell As Excel.Range
    
        For Each cell In S2.Cells
            finalValue = finalValue + CStr(cell.Value)
        Next cell
    
        ConcatinateAllCellValuesInRange = finalValue
    End Function
    Example of output CSV:

    2zrn97l.jpg


    Layout of Excel Spreadsheet for Which I Want To Run Script:

    30m32na.jpg

    Any suggestions on how to edit my code to get it to work? Any help would be greatly appreciated!

  2. #2
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: How To Write A Column To CSV (Create Multiple CSVs for Multiple Columns)?

    try this:
    Sub Create_CSVs()
    Dim ws As Worksheet, i As Integer, j As Integer, sHead As String, sText As String
    Set ws = ThisWorkbook.Worksheets("Sheet1") 'replace Sheet1 with actual worksheet name
    For j = 1 To ws.Cells(1, Columns.Count).End(xlToLeft).Column
         If ws.Cells(1, j) <> "" And ws.Cells(2, j) <> "" Then
              sHead = ws.Cells(1, j)
              sText = ws.Cells(2, j)
              If ws.Cells(Rows.Count, j).End(xlUp).Row > 2 Then
                   For i = 3 To ws.Cells(Rows.Count, j).End(xlUp).Row
                        sText = sText & " " & ws.Cells(i, j)
                        Next i
                   End If
              Workbooks.Add
              ActiveSheet.Range("A1") = sHead
              ActiveSheet.Range("B1") = sText
              ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Format(Now(), "yyyyMMddhhmmss") & ".csv", _
              FileFormat:=xlCSV, CreateBackup:=False
              ActiveWorkbook.Close SaveChanges:=True
              Application.Wait Now + TimeValue(0, 0, 1)
              End If
         Next j
    
    Set ws = Nothing
    End Sub

  3. #3
    Registered User
    Join Date
    11-05-2015
    Location
    San Diego, CA
    MS-Off Ver
    Professional Plus 2010
    Posts
    18

    Re: How To Write A Column To CSV (Create Multiple CSVs for Multiple Columns)?

    Quote Originally Posted by jewelsharma View Post
    try this:
    Sub Create_CSVs()
    Dim ws As Worksheet, i As Integer, j As Integer, sHead As String, sText As String
    Set ws = ThisWorkbook.Worksheets("Sheet1") 'replace Sheet1 with actual worksheet name
    For j = 1 To ws.Cells(1, Columns.Count).End(xlToLeft).Column
         If ws.Cells(1, j) <> "" And ws.Cells(2, j) <> "" Then
              sHead = ws.Cells(1, j)
              sText = ws.Cells(2, j)
              If ws.Cells(Rows.Count, j).End(xlUp).Row > 2 Then
                   For i = 3 To ws.Cells(Rows.Count, j).End(xlUp).Row
                        sText = sText & " " & ws.Cells(i, j)
                        Next i
                   End If
              Workbooks.Add
              ActiveSheet.Range("A1") = sHead
              ActiveSheet.Range("B1") = sText
              ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Format(Now(), "yyyyMMddhhmmss") & ".csv", _
              FileFormat:=xlCSV, CreateBackup:=False
              ActiveWorkbook.Close SaveChanges:=True
              Application.Wait Now + TimeValue(0, 0, 1)
              End If
         Next j
    
    Set ws = Nothing
    End Sub
    Thank you JewelSharma, the code looks very clear and I'm trying to figure it out - I tried to run it though and am getting a "Wrong number of arguments or invalid property assignment", I think its for the Timevalue(0,0,1) part.

    Edit: I got it to work, just had to replace that line with
    Application.Wait (Now + Timevalue("0:00:01"))
    It almost works! Though I have some more questions:

    1. How can I get cell B1 to be formatted such that they will have a [enter] break between them?

    ie:

    Instead of appearing in B1 in a line, such as:
    stainless steel wire stainless steel sheet stainless steel sheets stainless steel beverage dispenser stainless steel posts stainless steel drink dispenser wire stainless steel stainless steel wiring stainless steel drink

    They would appear as a list (all in B1):
    stainless steel wire
    stainless steel sheet
    stainless steel sheets
    stainless steel beverage dispenser
    (etc)

    (I am also trying to have them enclosed with quotation marks, so another program will recognize all of cell B1 as one block of text, so I can paste it into a text box).

    Many thanks again for the help
    Last edited by eryksd; 01-28-2016 at 01:40 AM.

  4. #4
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: How To Write A Column To CSV (Create Multiple CSVs for Multiple Columns)?

    Good Pick on the Timevalue input.

    Edited lines highlighted in blue:
    Sub Create_CSVs()
    Dim ws As Worksheet, i As Integer, j As Integer, sHead As String, sText As String
    Set ws = ThisWorkbook.Worksheets("Sheet1") 'replace Sheet1 with actual worksheet name
    For j = 1 To ws.Cells(1, Columns.Count).End(xlToLeft).Column
         If ws.Cells(1, j) <> "" And ws.Cells(2, j) <> "" Then
              sHead = ws.Cells(1, j)
              sText = ws.Cells(2, j)
              If ws.Cells(Rows.Count, j).End(xlUp).Row > 2 Then
                   For i = 3 To ws.Cells(Rows.Count, j).End(xlUp).Row
                        sText = sText & Chr(10) & ws.Cells(i, j)
                        Next i
                   End If
              Workbooks.Add
              ActiveSheet.Range("A1") = sHead
              ActiveSheet.Range("B1") = Chr(34) & sText & Chr(34)
              ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Format(Now(), "yyyyMMddhhmmss") & ".csv", _
              FileFormat:=xlCSV, CreateBackup:=False
              ActiveWorkbook.Close SaveChanges:=True
              Application.Wait(Now + TimeValue("0:00:01"))
              End If
         Next j
    
    Set ws = Nothing
    End Sub

  5. #5
    Registered User
    Join Date
    11-05-2015
    Location
    San Diego, CA
    MS-Off Ver
    Professional Plus 2010
    Posts
    18

    Re: How To Write A Column To CSV (Create Multiple CSVs for Multiple Columns)?

    Quote Originally Posted by jewelsharma View Post
    Good Pick on the Timevalue input.

    Edited lines highlighted in blue:
    Sub Create_CSVs()
    Dim ws As Worksheet, i As Integer, j As Integer, sHead As String, sText As String
    Set ws = ThisWorkbook.Worksheets("Sheet1") 'replace Sheet1 with actual worksheet name
    For j = 1 To ws.Cells(1, Columns.Count).End(xlToLeft).Column
         If ws.Cells(1, j) <> "" And ws.Cells(2, j) <> "" Then
              sHead = ws.Cells(1, j)
              sText = ws.Cells(2, j)
              If ws.Cells(Rows.Count, j).End(xlUp).Row > 2 Then
                   For i = 3 To ws.Cells(Rows.Count, j).End(xlUp).Row
                        sText = sText & Chr(10) & ws.Cells(i, j)
                        Next i
                   End If
              Workbooks.Add
              ActiveSheet.Range("A1") = sHead
              ActiveSheet.Range("B1") = Chr(34) & sText & Chr(34)
              ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Format(Now(), "yyyyMMddhhmmss") & ".csv", _
              FileFormat:=xlCSV, CreateBackup:=False
              ActiveWorkbook.Close SaveChanges:=True
              Application.Wait(Now + TimeValue("0:00:01"))
              End If
         Next j
    
    Set ws = Nothing
    End Sub
    Thank you JewelSharma, it works perfectly!! I never knew about the Chr() function, I believe it will definitely come in handy in the future.

    I am trying to do 2 more things with this script, though the main one is: having the CSV's numbered as 1.csv, 2.csv, 3.csv, (so that each is unique)... think that would be possible?

    I might be able to work around it but I was trying to do it this way so my other program (whose loop will read 1.csv, 2.csv, 3.csv, etc...) will be able to read it.

    I'm also trying to loop it across all worksheets (except "AA" and "Word Frequency") but I think I can figure that part out, will work on that now.

    Just the csv numbering is what I'm stuck on how to do.

    Many thanks again!

    EDIT:

    Nevermind, I didn't think this code would work, but it did! Code to get csv's saved as 1.csv, 2.csv, etc:

    k = 1 (before loop)
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & k & ".csv", _
              FileFormat:=xlCSV, CreateBackup:=False
    k = k + 1
    Last edited by eryksd; 01-28-2016 at 07:00 PM.

  6. #6
    Registered User
    Join Date
    11-05-2015
    Location
    San Diego, CA
    MS-Off Ver
    Professional Plus 2010
    Posts
    18

    Re: How To Write A Column To CSV (Create Multiple CSVs for Multiple Columns)?

    Modified the code to try to get it to loop, it almost works.

    For some reason, it only outputs anywhere from ~125 - 136 CSV files (it varies every time I run it), and only seems to work for two worksheets (there's about 30 worksheets).

    Should be outputting many more CSV files (~1000).

    Any idea on what's wrong in my code?

    Link to workbook I'm working with

    Option Explicit
    
    Public counter As Integer
    
    
    Sub Create_CSVs_AllSheets()
    
        Dim sht 'just a tmp var
        
        counter = 1                 'this counter will provide the unique number for our 1.csv, 2.csv.... 999.csv, etc
        appTGGL bTGGL:=False
        For Each sht In Worksheets  ' for each sheet inside the worksheets of the workbook
            If sht.Name <> "AA" And sht.Name <> "Word Frequency" Then
            'IF sht.name is different to AA AND sht.name is diffent to WordFrecuency THEN
    
            'TIP:
            'If Not sht.Name = noSht01 And Not sht.Name = noSht02 Then 'This equal
            'IF (NOT => negate the sentence) sht.name is NOT equal to noSht01 AND
            '                                sht.name is NOT equal to noSht02 THEN
    
                sht.Activate 'go to that Sheet!
                Create_CSVs_v3 (counter) 'run the code, and pass the counter variable (for naming the .csv's)
                
                
            End If '
        Next sht 'next one please!
        appTGGL
    End Sub
    
    Sub Create_CSVs_v3(counter As Integer)
    Dim ws As Worksheet, i As Integer, j As Integer, k As Integer, sHead As String, sText As String
    Set ws = ActiveSheet    'the sheet with the data, _
                            'and we take the name of that sheet to do the job
    
    For j = 5 To ws.Cells(1, Columns.Count).End(xlToLeft).Column
         If ws.Cells(1, j) <> "" And ws.Cells(2, j) <> "" Then
              sHead = ws.Cells(1, j)
              sText = ws.Cells(2, j)
              If ws.Cells(rows.Count, j).End(xlUp).Row > 2 Then
                   For i = 3 To ws.Cells(rows.Count, j).End(xlUp).Row   'i=3 because above we defined that_
                                                                        'sText = ws.Cells(2, j) above_
                                                                        'Note the "2" above and the sText below
                        sText = sText & Chr(10) & ws.Cells(i, j)
                        Next i
                   End If
              Workbooks.Add
              ActiveSheet.Range("A1") = sHead
              ActiveSheet.Range("B1") = Chr(10) & sText 'Modified above line to start with "Return" character (Chr(10))
              'instead of enclosing with quotation marks (Chr(34))
              
              ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & counter & ".csv", _
              FileFormat:=xlCSV, CreateBackup:=False 'counter variable will provide unique number to each .csv
              
              ActiveWorkbook.Close SaveChanges:=True
              counter = counter + 1                 'increment counter by 1, to make sure every .csv has a unique number
              End If
         Next j
    
    Set ws = Nothing
    End Sub
    
    Public Sub appTGGL(Optional bTGGL As Boolean = True)
        Debug.Print Timer
        Application.ScreenUpdating = bTGGL
        Application.EnableEvents = bTGGL
        Application.DisplayAlerts = bTGGL
        Application.Calculation = IIf(bTGGL, xlCalculationAutomatic, xlCalculationManual)
    End Sub
    Last edited by eryksd; 01-28-2016 at 07:13 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: How To Write A Column To CSV (Create Multiple CSVs for Multiple Columns)?

    Sorry, mate! I can't find anything wrong with your code.
    Maybe forum experts can take a look and advice us.

  8. #8
    Registered User
    Join Date
    11-05-2015
    Location
    San Diego, CA
    MS-Off Ver
    Professional Plus 2010
    Posts
    18

    Re: How To Write A Column To CSV (Create Multiple CSVs for Multiple Columns)?

    Quote Originally Posted by jewelsharma View Post
    Sorry, mate! I can't find anything wrong with your code.
    Maybe forum experts can take a look and advice us.
    No worries JewelSharma, I noticed there was an option to pay by buying credits to get answers - do you know how that works? Do I have to post in another special forum, or contact someone perhaps?

  9. #9
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: How To Write A Column To CSV (Create Multiple CSVs for Multiple Columns)?

    To my limited knowledge, the Commercial Services works like this:
    • A user needs to buy a suitable subscription first. (which you can do here).
    • Once you have a valid subscription, you may post a new thread under the commercial service forum.
    • Wherein the forum experts shall provide you a solution. (I'm guessing) the experts shall advice their fee after they understand the problem and the estimated amount of their time required.

    There should be a thread somewhere re this; or maybe a forum admin/moderator can guide you.
    Good luck!

  10. #10
    Registered User
    Join Date
    11-05-2015
    Location
    San Diego, CA
    MS-Off Ver
    Professional Plus 2010
    Posts
    18

    Re: How To Write A Column To CSV (Create Multiple CSVs for Multiple Columns)?

    Quote Originally Posted by jewelsharma View Post
    To my limited knowledge, the Commercial Services works like this:
    • A user needs to buy a suitable subscription first. (which you can do here).
    • Once you have a valid subscription, you may post a new thread under the commercial service forum.
    • Wherein the forum experts shall provide you a solution. (I'm guessing) the experts shall advice their fee after they understand the problem and the estimated amount of their time required.

    There should be a thread somewhere re this; or maybe a forum admin/moderator can guide you.
    Good luck!
    Thanks JewelSharma!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Link multiple CSVs to one worksheet
    By keertikaz in forum Excel General
    Replies: 3
    Last Post: 07-31-2015, 01:56 AM
  2. Replies: 1
    Last Post: 04-29-2015, 11:27 AM
  3. [SOLVED] Create a table with 1 column, that is dynamically updated from other multiple columns.
    By marcusduton in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 02-21-2015, 01:46 AM
  4. [Solved]Merge/Append data from multiple .CSVs
    By Zurar in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-05-2011, 06:35 AM
  5. Formula or Macro to create a concise column from multiple columns
    By mremmenga in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-15-2011, 12:08 PM
  6. How do I create multiple columns within one column?
    By Jeanne in forum Excel General
    Replies: 3
    Last Post: 02-27-2006, 05:50 PM
  7. [SOLVED] How do I create multiple columns from a one-column list in Excel?
    By Melissa in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-04-2005, 11:05 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1