+ Post New Thread
Results 1 to 2 of 2
Coding Thread, Creating multiple files from CSV in Coding and Web Development; We are currently in the throws of creating a VOIP system. One of the things we would like to do ...
  1. #1

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,821
    Thank Post
    272
    Thanked 1,140 Times in 1,036 Posts
    Rep Power
    351

    Creating multiple files from CSV

    We are currently in the throws of creating a VOIP system. One of the things we would like to do is auto-provision the handsets. We have the base config files working which is fine and where i hit a snag.

    I have a CSV which has the relevant information which needs to be inserted into each file.

    I have a field in the spreadsheet which contains all mac addresses, what i need is a config file with the MacAddress as the name but containing the information from the other fields in the spreadsheet.

    If not just the file names created so macaddress.cfg for each address and I can just create a mail-merge and paste the information in which i would rather not!

    Examples of the config and CSV linked

    http://thankyou.theweald.org.uk/phoneexample.csv.txt
    http://thankyou.theweald.org.uk/macaddress.cfg.txt

    Anybody any idea where to start?

  2. #2

    Join Date
    Oct 2010
    Posts
    24
    Thank Post
    6
    Thanked 1 Time in 1 Post
    Blog Entries
    2
    Rep Power
    43
    If I've understood, I had this idea: a cfg file is just a text file, so why not use a bit of VBA code to loop through your CSV of phone examples, subsituting it into your Mac Address template and saving it as a one column CSV (except with a cfg file extension)?

    I've drawn up some VBA code you might use (apologies if you don't like my verbose coding style) that might work: in the workbook the Template worksheet contains your Mac Address template file (opened as a CSV in Excel - this takes up column A in the worksheet) and the CSV worksheet contains the data from your phone example.

    N.B. There is little proper error-handling in the code (e.g. I don't check the worksheets exist or whether the files to be saved already exist).

    I don't seem to be able to upload files, so here's the code:

    Code:
    Option Explicit
    
    ' The template worksheet contains the template of the config file
    ' The CSV worksheet contains the details to be embedded into the template
    ' and then individually saved.
    
    ' Change the values here depending upon how you have named your worksheets
    Private Const strTEMPLATE_WORKSHEET As String = "Template"
    Private Const strCSV_WORKSHEET As String = "CSV"
    
    ' Layout of the template worksheet
    ' (by default this should only have one column of data)
    Private Const lngTEMPLATE_HEADING_ROW As Long = 1
    Private Const lngTEMPLATE_FIRST_DATA_ROW As Long = 2
    Private Const lngTEMPLATE_DATA_COLUMN As Long = 1
    
    ' Layout of the CSV worksheet
    Private Const lngCSV_HEADING_ROW As Long = 1
    Private Const lngCSV_FIRST_DATA_ROW As Long = 2
    Private Const lngCSV_MAC_COLUMN As Long = 1
    Private Const lngCSV_EXTENSION_COLUMN As Long = 2
    Private Const lngCSV_ID_COLUMN As Long = 3
    
    Public Sub CreateMacAddressCfgFile()
    
    On Error GoTo ErrorHandler
    
    Const strROUTINE_NAME As String = "CreateMacAddressCfgFile"
    
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Name              : Public Sub CreateMacAddressCfgFile
    '
    ' Description       : Create text files with a suitable name from a
    '                     "template" worksheet filling in the data from a
    '                     CSV of data.
    '
    '                     The text file will be saved from Excel as a CSV
    '                     but with a cfg file extension.
    '
    ' Created by        : Pico (EduGeek)
    ' Date              : 02/03/2012
    '
    ' Last modified by  :
    ' Date              :
    '
    ' Changes made      :
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    Dim wkshTemplateWorksheet As Worksheet
    Dim wkshCSVWorksheet As Worksheet
    
    Dim strCurrentFilePath As String
    Dim wkbkConfigWorkbook As Workbook
    
    Dim strMacAddress As String
    Dim strExtension As String
    Dim strID As String
    
    Dim lngEachMacAddress As Long
    Dim lngNumberOfMacAddresses As Long
    
        ' The workbook this code has been embedded in
        With ThisWorkbook
            ' N.B. I haven't checked these worksheets exist!
            Set wkshTemplateWorksheet = .Worksheets(strTEMPLATE_WORKSHEET)
            Set wkshCSVWorksheet = .Worksheets(strCSV_WORKSHEET)
            strCurrentFilePath = GetPathToFile(.FullName)
        End With
        
        With wkshCSVWorksheet.Cells(lngCSV_HEADING_ROW, lngCSV_MAC_COLUMN)
            lngNumberOfMacAddresses = .CurrentRegion.Rows.Count
        End With
        
        ' Loop through all rows in the CSV worksheet
        For lngEachMacAddress = lngCSV_FIRST_DATA_ROW To lngNumberOfMacAddresses Step 1
        
            ' Get the data
            With wkshCSVWorksheet
                strMacAddress = CStr(.Cells(lngEachMacAddress, lngCSV_MAC_COLUMN).Value)
                strExtension = CStr(.Cells(lngEachMacAddress, lngCSV_EXTENSION_COLUMN).Value)
                strID = CStr(.Cells(lngEachMacAddress, lngCSV_ID_COLUMN).Value)
            End With
        
            ' Copy the template worksheet to a new workbook (becomes the active workbook)
            ' Save the new workbook as a CSV (text file) but with a cfg file
            ' N.B. You will be prompted whether to overwrite if the file already exists on disk
            wkshTemplateWorksheet.Copy
            Set wkbkConfigWorkbook = ActiveWorkbook
            wkbkConfigWorkbook.SaveAs Filename:=strCurrentFilePath & strMacAddress & ".cfg", FileFormat:=xlCSV
                    
            ' Substitute in the data (add to this list as necessary)
            With wkbkConfigWorkbook.Worksheets(1)
                .Cells(4, lngTEMPLATE_DATA_COLUMN).Value = "Label = " & strID
                .Cells(5, lngTEMPLATE_DATA_COLUMN).Value = "DisplayName = " & strID
                .Cells(6, lngTEMPLATE_DATA_COLUMN).Value = "AuthName = " & strExtension
                .Cells(7, lngTEMPLATE_DATA_COLUMN).Value = "UserName = " & strExtension
            End With
            
            ' Save and close
            With wkbkConfigWorkbook
                .Save
                .Close SaveChanges:=True
            End With
            
            Set wkbkConfigWorkbook = Nothing
        
        Next lngEachMacAddress
    
    CleanUpAndExit:
        If Not wkbkConfigWorkbook Is Nothing Then
            Set wkbkConfigWorkbook = Nothing
        End If
        If Not wkshTemplateWorksheet Is Nothing Then
            Set wkshTemplateWorksheet = Nothing
        End If
        If Not wkshCSVWorksheet Is Nothing Then
            Set wkshCSVWorksheet = Nothing
        End If
        Exit Sub
    ErrorHandler:
        Call MsgBox("An error was encountered in " & strROUTINE_NAME & "." & _
                    vbCrLf & vbCrLf & _
                    "Error Number: " & CStr(Err.Number) & vbCrLf & _
                    "Error Description: " & Err.Description, _
                    vbCritical + vbOKOnly, "Error Message")
        Resume CleanUpAndExit
    End Sub
    
    Private Function GetPathToFile(ByVal strFullFilePath As String) As String
    
    Dim strPathArray() As String
    Dim lngLastPosition As Long
    Dim strFilePath As String
    
        If strFullFilePath = "" Then
            strFilePath = ""
        Else
            strPathArray = Split(strFullFilePath, Application.PathSeparator)
            lngLastPosition = UBound(strPathArray)
            ReDim Preserve strPathArray(lngLastPosition - 1)
            strFilePath = Join(strPathArray, Application.PathSeparator) & Application.PathSeparator
        End If
        
        GetPathToFile = strFilePath
    
    End Function
    HTH.



SHARE:
+ Post New Thread

Similar Threads

  1. Create exchange mailboxes from csv
    By bart21 in forum Enterprise Software
    Replies: 3
    Last Post: 10th November 2011, 01:49 AM
  2. [MS Office - 2007] Create Multiple Appointments Requests from Excel to Outlook?
    By jmair in forum Office Software
    Replies: 0
    Last Post: 7th October 2010, 05:11 PM
  3. Replies: 1
    Last Post: 11th May 2010, 10:34 AM
  4. Printing multiple files from Explorer
    By RabbieBurns in forum Windows 7
    Replies: 4
    Last Post: 16th March 2010, 12:38 PM
  5. Create multiple asx wrapper files for media files
    By mbyrew in forum AV and Multimedia Related
    Replies: 1
    Last Post: 12th January 2010, 01:59 PM

Thread Information

Users Browsing this Thread

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

Posting Permissions

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