Convert an Excel File
DataLog has a conversion feature that makes it possible to map Excel spreadsheet sales data to the fields for a DataLog sale sheet and then import the sales.
DataLog will convert the “first” sheet from the Excel workbook alphabetically–not the first sheet physically. DataLog “asks” the Excel file for a list of the sheet names in the workbook. That list is handed to the DataLog in alphabetical order not physical order and it uses ONLY the first sheet name in that list.
To begin, note the location of the spreadsheet containing the sale data to be converted.
The screen shot below shows a typical example of sales data to be converted; a header row of labels for the various columns of sale data is required for DataLog version 4.0 and later.
Next, open DataLog and click the Convert Excel File option under Conversion Tools.
A dialog box opens where several choices must be made.
Select both the Excel file that is to be converted, and the folder where the data will be transferred into DataLog. If desired, create a special folder for this purpose when the Browse button is displayed. Clicking it will open the Select Folder window where the Make New Folder button is found.
The Convert Excel File window will look something like the example below.
The Template option can be ignored for now.
If a Data Map has not already been created, nothing will be listed in that list.
To create a data map, click the Data Map Options button.
There are three options; choose Add.
The next window looks something like this…
and you can see that the arrows show the labels from the spreadsheet.
The column labeled Maps To This DataLog Field will automatically fill with corresponding fields’ contents from the DataLog sale sheet.
If there is an empty cell, it is because DataLog was unable to select the correct match.
Any of the fields can be changed if DataLog “misses” on the match-up.
Click directly in the field to see the drop down list; it is typing intuitive.
For this example # Acres is empty; the correct match for the DataLog field name is DeededAcres.
The column labeled With This Data is empty for the moment.
Once all of the matches are set, click the Show Next Row button (shown below) to display the next row of data from the Excel spreadsheet.
Confirm that the matches are correct and that the data displayed make sense.
Make corrections as needed; click Save.
Name the Data Map; it will be available for future conversions in the map list.
Click the Start button.
A preview window will open to display how the sale sheet actually lines up with a sample of data from the spreadsheet.
Select a view from the drop-down list.
Scroll to view the entire page.
If acceptable, click OK-Convert.
DataLog will display a message that the conversion was completed.
Confirm that the spreadsheet data have been converted by checking the destination folder in DataLog.
Regarding Sale Templates
A sale template may be selected as desired.
Sale templates created by the user will be available as selections in the drop-down window seen at the start of the conversion process.
- Converting Sales That Have Attachments
In DataLog v 4.0 and later, it is possible to convert excel sales to include files to be attached to the sales.
There are several absolute conditions that must be met in addition to the basic steps outlined above.
- The excel spreadsheet sale data must include an index number AND
- The files to be attached for converted sales MUST be in the SAME folder as the spreadsheet that is undergoing conversion AND
- The attachments MUST contain the sale’s index number as the prefix for their file name.
The screen shot above shows that the “.csv” excel file to be converted is selected.
The file preview shows the sale index number for the Fred to Ethel sale; the file name for the jpg to be attached matches it. The three other files seen in the preview do not have any files to be attached during the conversion.
This is how DataLog will know to attach it to the sale during the conversion process.
There is a check-box that allows updates to any sales that might already be in the folder, with the incoming converted sale data.
The screen shot below shows that for the Fred to Ethel sale, the Show Attachments button is enabled, meaning that the jpg converted together with the sale is now accessible in DataLog.
And finally, here is the attached jpg that was converted with the sale.