Create a Standard Data File with Excel

In this section, you will find some tips and advice to create your Standard Data File with Excel.

The difficulty is that when you encapsulate all the fields with double quotes and save the file as a CSV file in Excel, the double quotation marks are tripled.

There are different ways to avoid this.

Depending on how often you need to create a Standard Data File with Excel, you can use a Macro or simply a formula to have the correct format.

If you have to create such a file regularly, we suggest to take the time to create a Macro, as explained below (Option 2 : Macro). If it's an one-time manipulation, we recommend you to use a formula to surround the fields by quotes and then use NotePad++ to replace the multiple consecutive instances of the double quotation marks (Option 1 :formula in Excel and NotePad++)

Option 1 : Formula in Excel and NotePad++

1. Open a new Excel File and 'save as' in the Comma Separated Value Format (.csv)

2. Insert the headers and the body of your Standard Data File. Be careful to respect the format of the file as described here

Tip

To have the correct date format easily, you can use the following personalized call format : yyyy-mm-ddThh:mm:ssZ. It will automatically convert the date format as recognized by Excel into the dateTime format needed by Opinum.

3. Encapsulate all the fields in double quotes by using the following formula : =CHAR(34)&A1&CHAR(34) Paste this formula in the first empty header cell and extend it to the same number of columns and rows in which you have data :

   

   Then, copy the result and paste it to replace all the fields unquoted in the file [Choose the option 'paste values']. Your Excel file should now looks like this:

   

4. Close your Excel File and open the file with NotePad++ (or another plain text editor). You should see that the quotes are tripled :

   

Use the shortcut CTRL+F to open the search pop-up. In the tab 'Replace', replace """ by " and click on 'Replace All', then on 'Close'. Your file should now look like this and is ready to be uploaded on Opinum:

Warning

Once your csv file is correctly formatted in NotePad, do not open it in excel. This would modify the file and triples the quotes again. We highly recommend you open your csv file in a plain text editor to check if the datetime values are correct before uploading them on Opinum.

Option 2 : Use a Macro

With this method, you don't have to worry about encapsulating all the fields in double quotation marks. The macro will do it for you.

Display the Developer tab in Excel's ribbon

To create a macro, you need the 'developer' tab in Excel. When opening a workbook, if this tab doesn't appear in the ribbon, please follow the following steps :

1. Go to the 'File' tab and choose 'Options'.
2. In the Excel Options dialogue box, click on 'Customize Ribbon' in the left pane.
3. On the right, within the Main Tabs pane, check the Developer option.
4. Click OK.

Create your Personal Macro Workbook

Now, we are going to create your personal Macro Workbook, where you will be able to store the macro in order to reuse it in all your Excel workbooks.

1. Open a new workbook or any existing workbook.
2. Go to the Developer tab in the ribbon.
3. Click on Record Macro.

4. In the Record Macro dialog box, specify a name (default is fine too).
5. In the ‘Store Macro in’ drop down, select Personal Macro Workbook.
6. Click OK.

Now that the Personal Macro Workbook is created, you need to Close all the open workbooks. Doing this will show a dialog box as shown below:

Create the Macro

We can now create the macro which will be used to generate a StandardDataFile.

1. Open Excel.

2. Go to the Developer tab.

3. Click on Visual Basic option. This will open the VB Editor (or use ALT + F11).

4. In the VB Editor, within the Project Explorer, you will see your Personal Macro Workbook you have just created.Double-click on Module 1.

5. Delete the code that is already there and paste the following macro code in the Module code window :

Sub OutputQuotedCSV()
Const QSTR As String = """"
        Dim myRecord As Range
        Dim myField As Range
        Dim nFileNum As Long
        Dim sOut As String
        Dim FileName As String
        Dim dt As String
        
        nFileNum = FreeFile
        dt = Format(CStr(Now), "yyyy_mm_dd_hh_mm_ss")
        FileName = ActiveWorkbook.Path & "\" & (Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1)) & "_" & dt & ".csv")
        Open FileName 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 & "," & QSTR & _
                        Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR
                Next myField
                Print #nFileNum, Mid(sOut, 2)
                sOut = Empty
            End With
        Next myRecord
        Close #nFileNum
        MsgBox "Your CSV file has been created." & vbNewLine & "Please, don't open the csv in Excel!"

End Sub

6. Close the Vb Editor.

7. Save and Close Excel.

Use the Macro

You have now created your macro and you will be able to use it every time you want to generate an Opisense StandardDataFile from an Excel File.

To do so, Open an Excel workbook and enter your data without worrying about double quoted. You have, of course, to respect other rules about Source identifier, value, datetime, format (csv comma separated), body and headers.

But you can enter your data like this, for example :

Then, you can run your macro by following these steps :

1. Click on Developer > Macros

2. In the Macro box, select the macro which name contains 'OutputQuotedCSV'

3. Click on 'Run'

4. A csv file has been generated in the folder where your excel file is located. This file has the same name as the original excel file, followed by the creation time stamp: ExcelFileName_yyyy_mm_dd_hh_MM_ss.csv.

5. If your triggers are correctly configured on the Opinum, you just need to upload this csv file in Opinum (through the storage, the FTP or via HTTP, depending on the option you have chosen in your trigger) and your data will be processed.

Warning

We highly recommend you open your csv file in a plain text editor to check if the datetime values are correct before uploading them on Opinum.

Note

The 'Macro' tutorial is inspired by Trump Excel