Tag Archives: vb macro

Importing a CSV Into a Windows App Studio data Collection

Steps to import a CSV to App Studio:

Step 1:  Add column headers and data types manually in the IDE

Step 2:  Use same column headers in Excel

Step 3:  For image fields, in your spreadsheet, use URL or fully qualified local file path and file name

Step 4:  Use VB Macro to export with semi colons and put double quotes around all fields. See code below.

Here is some handy code to use in an Excel Macro when importing a CSV file into App Studio collection. This saved me a ton of work!

Public Sub OutputQuotedCSV()
Const DELIMITER As String = ";"
Const QSTR As String = """"
        Dim myRecord As Range
        Dim myField As Range
        Dim nFileNum As Long
        Dim sOut As String

        nFileNum = FreeFile
        Open "File1.txt" For Output As #nFileNum
        For Each myRecord In Range("A1:A" & _
                    Range("A" & Rows.Count).End(xlUp).Row)
            With myRecord
                For Each myField In Range(.Cells(1), _
                            Cells(.Row, 256).End(xlToLeft))
                    sOut = sOut & DELIMITER & QSTR & _
                        Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR
                Next myField
                Print #nFileNum, Mid(sOut, 2)
                sOut = Empty
            End With
        Next myRecord
        Close #nFileNum
    End Sub