How Do I: Import and Export Data to/from a CSV file (Dan Seefeldt)

How Do I: Import and Export Data to/from a CSV file (Dan Seefeldt)

Rate This
  • Comments 20

LightSwitch provides weakly-typed data access APIs which allow you to add general purpose code to perform common tasks. In this example, these APIs are used to implement a simple general purpose import/export. The import/export will read and write CSV files. Export will write the names of the properties as the first line of the output file. Import will read this line to get the correct column into the right property. It is designed to be called on the client from screen code. This general purpose code can be placed in its own class file in the Client/UserCode folder of your project so it can be called from any screen. In the following examples, it is used on two buttons on a Customer screen, one to perform an import and one to perform an export.

The export button first gets a list of the properties to export. In this case, I’m only exporting storage properties. Storage properties are simple, scalar properties that are stored in the database.  They do not include calculated or navigation properties. The call to PromptAndExportEntities() takes an enumerable list of entities to export and the list of properties to export.

Private Sub Export_Execute()

    Dim props As New List(Of String)()

 

    ' Only export storage properties

    For Each prop In Me.Customer.SelectedItem.Details.Properties.All.OfType(Of Details.IEntityStorageProperty)()

        props.Add(prop.Name)

    Next

    GeneralImportExport.EntityImportExport.PromptAndExportEntities(Me.DataWorkspace.ApplicationData.Customers, props.ToArray())

End Sub

The import button prompts the user first and then deletes all existing customers in the database. Next, it calls PromptAndImportEntities() to import new customers into the entity set specified, in this case, the Customers entity set.

Private Sub Import_Execute()

    If Me.ShowMessageBox("This will delete and re-import all Customers.  Do you want to continue?", "Continue?", MessageBoxOption.YesNo) = Windows.MessageBoxResult.Yes Then

        For Each cust As Customer In Me.DataWorkspace.ApplicationData.Customers

            cust.Delete()

        Next

        GeneralImportExport.EntityImportExport.PromptAndImportEntities(Me.DataWorkspace.ApplicationData.Customers)

        Me.Refresh()

    End If

End Sub

Export

The start of the export process prompts for a file to save the exported data to. This is accomplished by opening the SaveFileDialog() and getting the file stream that is the result. Then, this information is passed to the export function.

Note: This method of opening a save file dialog is currently a Beta1 limitation. Launching a UI control needs to happen on the UI thread, which is what the Dispatchers.Main.Invoke() is doing. We’re looking at ways of making this easier in the future.

Imports Microsoft.LightSwitch.Threading

 

Public Shared Sub PromptAndExportEntities(ByVal entitiesToExport As IEnumerable, ByVal properties() As String)

    Dim stream As IO.Stream = Nothing

 

    ' SaveFileDialog() must be opened on the UI thread

    Dispatchers.Main.Invoke(Sub()

                                Dim dlg As New Controls.SaveFileDialog

                                dlg.Filter = "CSV Files (*.csv)|*.csv|Text Files (*.txt)|*.txt"

                                dlg.DefaultExt = "csv"

 

                                If dlg.ShowDialog = True Then

                                    stream = dlg.OpenFile

                                End If

                            End Sub)

 

    If (Not stream Is Nothing) Then

        PerformExport(stream, entitiesToExport, properties)

 

        ' Need to close the file on the UI thread as well

        Dispatchers.Main.Invoke(Sub()

                                    stream.Close()

                                End Sub)

    End If

 

End Sub

The export function takes a stream on which to write the output, the list of entities to export and a list of the properties to export. The first step is to setup the export by initializing the stream writer and writing a header containing the property names to the file. Then, it continues by looping over each entity and passing it to the single line exporter.

Private Shared Sub PerformExport(ByVal file As IO.Stream, ByVal entitiesToExport As IEnumerable, ByVal properties() As String)

 

    ' Initialize a writer

    Dim writer As New IO.StreamWriter(file)

    writer.AutoFlush = True

 

    ' Write the header

    writer.WriteLine(String.Join(",", properties))

 

    ' Export each entity separately

    For Each entity In entitiesToExport

        ExportSingle(writer, entity, properties)

    Next

End Sub

The single line exporter takes an entity to export and loops through its properties to add the values of its properties to a string array. It uses the Properties on the Details member of the entity to get the value of each entity property.  The last step is to write the string array to the output as a comma separated list.

Private Shared Sub ExportSingle(ByVal writer As IO.StreamWriter, ByVal entity As IEntityObject, ByVal properties() As String)

    Dim stringArray As New List(Of String)()

    Dim currentProperty As Details.IEntityProperty

 

    ' Write each property to the string array

    For Each prop In properties

        Try

            ' Get the property from the entity by name

            currentProperty = entity.Details.Properties(prop)

        Catch ex As ArgumentException

            Throw New InvalidOperationException(String.Format("A property named {0} does not exist on the entity named {1}.", prop, entity.Details.Name))

        End Try

        stringArray.Add(currentProperty.Value.ToString())

    Next

 

    'Write the string array

    writer.WriteLine(String.Join(",", stringArray.ToArray))

End Sub

Import

Import starts with the PromptAndImportEnties() method. It takes an entity set in which to create the new entities. The start of the import process is to prompt for a file to import. The OpenFileDialog() is used for this purpose.

Note: Again, this method of opening a dialog is a Beta1 limitation for LightSwitch.

Imports Microsoft.LightSwitch.Threading

 

Public Shared Sub PromptAndImportEntities(ByVal entitySetToCreateIn As IEntitySet)

    Dim file As IO.FileInfo = Nothing

 

    ' OpenFileDialog() must be opened on the UI thread

    Dispatchers.Main.Invoke(Sub()

                                Dim dlg As New Controls.OpenFileDialog

                                dlg.Filter = "CSV Files (*.csv)|*.csv|Text Files (*.txt)|*.txt"

 

                                If dlg.ShowDialog = True Then

                                    file = dlg.File

                                End If

                            End Sub)

 

    If (Not file Is Nothing) Then

        PerformImport(file, entitySetToCreateIn)

    End If

 

End Sub

The Import function does pretty much the opposite of the export function. It takes in the file to read and the entity set in which to create the entities. The first step is to open the file and read the header line to get the property names. Then, it continues by looping over all data rows in the file, passing the data to the single line importer. Once all lines are imported into entities, SaveChanges() is called to save the data.

 

Private Shared Sub PerformImport(ByVal file As IO.FileInfo, ByVal entitySetToCreateIn As IEntitySet)

 

    Using reader As IO.StreamReader = file.OpenText

        Dim inputLine As String = reader.ReadLine

 

        ' Get the property names from the first line of input

        Dim headers As String() = inputLine.Split(",").Select(Function(header As String) header.Trim).ToArray()

 

        For Each header In headers

            header = header.Trim

        Next

        Dim count As Int16 = headers.Count

        Dim data As String()

 

        inputLine = reader.ReadLine

        While Not inputLine Is Nothing

            data = inputLine.Split(",")

 

            ' If the right number of data items were found, import the line

            If data.Count = count Then

                ImportSingle(entitySetToCreateIn, headers, data)

            Else

                Throw New InvalidOperationException(String.Format("Line not imported.  Invalid number of elements.  Data = [{0}].", inputLine))

            End If

            inputLine = reader.ReadLine

        End While

 

        reader.Close()

    End Using

    entitySetToCreateIn.Details.DataService.SaveChanges()

End Sub

 

The single line importer creates the new entity by calling AddNew() on the entity set. It then loops through all property names read in the import function and gets the desired property from the new entity by name, again, using the Details.Properties on the entity. It then converts the data for the property to the correct type based on the type information from the entity property. Lastly, it sets the value of the property.

Private Shared Sub ImportSingle(ByVal entitySetToCreateIn As IEntitySet, ByVal headers As String(), ByVal data As String())

 

    ' Create the new entity

    Dim newEntity As IEntityObject = entitySetToCreateIn.AddNew()

 

    Dim currentProperty As Microsoft.LightSwitch.Details.IEntityProperty

    Dim newValue As Object

 

    ' Loop through all propertyNames from the first line of the file

    For index As Integer = 0 To headers.Count - 1

        Try

            ' Get the property from the new entity by name

            currentProperty = newEntity.Details.Properties(headers(index))

        Catch ex As ArgumentException

            Throw New InvalidOperationException(String.Format("A property named {0} does not exist on the entity named {1}.", headers(index), newEntity.Details.Name))

        End Try

 

        Try

            ' Convert the value

            newValue = Convert.ChangeType(data(index), currentProperty.PropertyType, Nothing)

 

            currentProperty.Value = newValue

        Catch ex As System.FormatException

            Throw New InvalidOperationException(String.Format("The following line has an invalid value for property {0}.  Aborting the import." + vbNewLine + "Data: {1}", headers(index), String.Join(",", data)))

        End Try

    Next

End Sub

Hopefully this example gives you an idea of how the weakly-typed API can be used to create general purpose solutions to common problems. There are many possibilities for extending this example including enhancing it to support relationships, using it to create a general purpose entity archiver, or modifying it to export data to Excel using LINQ to XML. I attached the sample code to the bottom of this post.

Try it out and have fun!

Attachment: ImportExport.zip
Leave a Comment
  • Please add 7 and 8 and type the answer here:
  • Post
  • Dan, could you please offer C# code as well?

    Thanks!

    ..Ben

  • Ben,

    I've updated the attached sample to include both VB and C#.

    Thanks for the feedback.

    -Dan

  • Thanks Dan;

    I think for all future samples and docs, it would minimize confusion if both codes are supplied.

    ..Ben

  • Hi Dan,

    thank u for your wonderful code for importing from excel.

    my data are in persian(right to left/ iranian language) and when i try to import them, they appear as odd characters, would u please help me with this issue.

    i think a problem in unicode characters.

    Thanks in Advance

    -peyman

  • peyman ebrahimi,

    The sample uses the default encoding for the StreamWriter when it initializes it.  To make it work with Persian characters, in "PerformExport()", you can initialize it with UnicodeEncoding.  Here is the change:

               // Initialize a writer

               System.IO.StreamWriter writer = new System.IO.StreamWriter(file, new System.Text.UnicodeEncoding());

    Hope this helps,

    -Dan Seefeldt

    LightSwitch Team

  • Hi,

    Thanks for great sample.

    Is it possible to access data from this class?

    For example to check if customer exists.

    Best Regards,

    Igor

  • Hi Dan, do you have step by step procedure to get this done?

    I have downloaded the ImportExport.zip and unzipped the vb code in Client/UserCode folder. After that, I created an ApplicationData named: Customer and created List and Details Screen, so what's next? Please advice, thanks.

  • Hi Dan, in the screen, I have created a Button under Screen Command Bar, renamed the Button as Export and then click Edit Execute() Code and then it brings me to Private Sub Export_Execute(), copy your code:

    Dim props As New List(Of String)()

       ' Only export storage properties

       For Each prop In Me.CustomerCollection.SelectedItem.Details.Properties.All.OfType(Of Details.IEntityStorageProperty)()

           props.Add(prop.Name)

       Next

       GeneralImportExport.EntityImportExport.PromptAndExportEntities(Me.DataWorkspace.ApplicationData.Customers, props.ToArray())

  • Hi Dan, now I got an error saying that GeneralImportExport is not declared.

    Could you please help as I find your Import/ Export very useful, thanks.

  • Hi Keith,

    Make sure you put the class file under the Client/UserCode folder of the client project. To do that, click the "File View" dropdown button on the Solution Explorer to see the file structure. Then right click on the UserCode folder in the client project and select "Add Existing Item" and choose the class file. Then rebuild the solution. Once you do that then the GeneralImportExport class will be available to the screen. To create a button on a screen check out this video:

    msdn.microsoft.com/.../ff961919

    Also make sure you check out these videos to help you learn other areas of LightSwitch: msdn.microsoft.com/.../ff938857

    HTH,

    -B

  • Will this code work for any application data other then 'customer' or do I need to edit this. I am trying to add about 1000+ names to a table property so that the possible names are available to the person entering in data for tht screen.

  • Hi reigh7,

    So the Import and Export methods are the same but the calling code you would change to pass your entity. I saw you in the forum, right? :) In your case you would create an editable grid sceen for your Medication table and then put a button on the screen that called this code. It would be something like:

    '-------------------

    Private Sub Import_Execute()

       If Me.ShowMessageBox("This will delete and re-import all Medications.  Do you want to continue?", "Continue?", MessageBoxOption.YesNo) = Windows.MessageBoxResult.Yes Then

           For Each med As Medication In Me.Details.DataWorkspace.ApplicationData.Medications

               med.Delete()

           Next

           GeneralImportExport.EntityImportExport.PromptAndImportEntities(Me.Details.DataWorkspace.ApplicationData.Medications)

           Me.Refresh()

       End If

    End Sub

    '--------------------

    Download the ZIP at the bottom of the post to get started.

    HTH,

    -Beth

  • The sample code is a wonderful touch to get data in and out of lightswitch with a single table situation. But when i tried it with a 3 tables situation where the relationships are those of (grandparent-parent-son) situation, the code fails. How do we enhance the code to cater for relationships situation?

  • Thanks for the samlpe code!

    Works brilliantly for single entities. If say for example I have an Emploee Entity and I have a country entity that has a one to many relationship with the Employee entity how would I cast/lookup the referenced value when importing to the Employee entity?

    I have seen te LS team use a cutom control which allows the mapping of fields within a file to the entiy fields.

  • Dan, I want to bypass the openfiledialog box and automate the file opening sequence by providing standardized file names. when i try to do something like this.

    Dim file As New IO.FileInfo("C:\\power.csv")

    it comes back with an error

    "File operation not permitted. Access to path 'C:\\power.csv' is denied."

    how do i solve this?

    thank you

Page 1 of 2 (20 items) 12