Tuesday, February 9, 2016

Import CSV file into Ms Access database and Convert into XML

'Access VBA code to import the CSV file and create a table and then export the table's data into XML File.
'' Note: Open access database -> press Alt+F11 (to open the VBA Editor) ->Insert->Module
'' Copy the below code and paste into the module
' Press F5 to run the below code
'********************************************************************************
Sub Convert_CSV_TO_XML()
    Dim fPath As String
    Dim fOutputFile As String
   
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Title = "please select csv file"
        .Filters.Clear
        .Filters.Add "CSV Files", "*.csv"
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "file not selected...", vbOKOnly + vbInformation, "Task Cancelled"
            Exit Sub
        Else
            fPath = .SelectedItems(1)
            fOutputFile = CurrentProject.Path & "\"
        End If
    End With
   
    If fPath <> vbNullString Then
        DoCmd.TransferText acImportDelim, "", "Tbl_ImportData", fPath, True
        Application.ExportXML acExportTable, "Tbl_ImportData", fOutputFile & "\output.xml", fOutputFile & "\output.xsd"
        CurrentDb.Execute "Drop table Tbl_ImportData"
        MsgBox "file created in the below folder " & Chr(13) & fOutputFile, vbOKOnly + vbInformation, "XML file has been created"
    End If
End Sub

No comments:

Post a Comment

Excel VBA to send email from excel with HTML table in a email body

'' Excel vba script to send email from outlook & email body in HTML table format. ''Write down email body in HTML tags...