[feature] Add support for Excel source files

[R0Wi]

Is your feature request related to a problem? Please describe.

In our company we often use Microsoft Excel to compose data we want to upload to Salesforce. Unfortunately it's currently not possible to use these Excel sheets directly as a datasource inside of the Data Loader.

Describe the solution you'd like

Alternatively to CSV files, add support for .xlsx-files, too.

Describe alternatives you've considered

The current workaround is to create CSV files from the mentioned Excel files and use these as datasource.

Additional context

If you're willing to implement such a feature, I'd be glad to help 👍

[ashitsalesforce]

Hi @R0Wi , .xls and .xlsx files are more complex to process because:

• they can have multiple sheets in a single file
• cell values can be of various formats including formulas
• header may not be specified in the first row

That said, we would like to understand the approach you have in mind to design the support for .xls and .xlsx files.

[R0Wi]

Hi @ashitsalesforce, of course you're right and there might be way more pitfalls with Excel files than with simple CSV textfiles. But what we observe regularly is that after we designed our final dataset to be uploaded to SF via dataloader, the Microsoft Excel "export to CSV" function is quite a mess. Most of the time the format which is generated here cannot be read by the dataloader so manual tweaking in the CSV file is needed before it can be used.

Like I wrote in the initial post we use Excel most of the time to compose our data we want to upload. Therefore we use built-in functionallities like formulas, filters etc.

I think for a MVP it would be enough to let the dataloader read from the first tab in an Excel sheet and assume that the first row contains the header values. Of course we could add a kind of "selection dialog" later where the user can choose from which tab and/or area he would like to load the data but in my opinion this is optional for a first draft.

[ashitsalesforce]

Let's split the ask in two parts:

1. Improve CSV support for CSV files generated using Microsoft Excel's "export to CSV" function.
2. Support .xls and .xlsx uploads.

Regarding 1, can you provide a sample CSV file generated using MSFT Excel that runs into issues when you try to upload? It will be great if you have error messages/exception stack trace details along with it.

Regarding 2, thanks for providing the MVP outline. Given that it is a major feature ask and Data Loader is in maintenance at this juncture, our product team will place it in the feature backlog so that it can be prioritized when we pick up feature work on data loader.

[R0Wi]

Thanks @ashitsalesforce! If you need support with the implementation, just let me know.

Regarding the CSV file error, we're using a file which contains semicolons inside of one custom field, so what Microsoft Excel exports is something like this:

Id;My_CustomField1__c
01000000000000000Q;"Value;using;semicolon"

You can see that the value of My_CustomField1__c gets escaped, which is correct because it contains a semicolon which is also used as field delimiter.

When trying to load that CSV, Dataloader complains with the following error message:

ERROR [main] util.DAORowUtil calculateTotalRows (DAORowUtil.java:74) - Error Calculating Total Rows
com.salesforce.dataloader.exception.DataAccessObjectException: com.sforce.async.CSVReader$CSVParseException: Found unescaped quote. A value with quote should be within a quote
        at com.salesforce.dataloader.dao.csv.CSVFileReader.readRow(CSVFileReader.java:177) ~[dataloader-58.0.3.jar:?]
        at com.salesforce.dataloader.util.DAORowUtil.calculateTotalRows(DAORowUtil.java:68) [dataloader-58.0.3.jar:?]
        at com.salesforce.dataloader.dao.csv.CSVFileReader.getTotalRows(CSVFileReader.java:225) [dataloader-58.0.3.jar:?]
        at com.salesforce.dataloader.ui.DataSelectionDialog$1.run(DataSelectionDialog.java:170) [dataloader-58.0.3.jar:?]
        at org.eclipse.swt.custom.BusyIndicator.showWhile(BusyIndicator.java:67) [swtwin32_x86_64-4.27.jar:3.122.100]
        at com.salesforce.dataloader.ui.DataSelectionDialog.open(DataSelectionDialog.java:119) [dataloader-58.0.3.jar:?]
        at com.salesforce.dataloader.ui.DataSelectionPage.getNextPage(DataSelectionPage.java:200) [dataloader-58.0.3.jar:?]
        at com.salesforce.dataloader.ui.DataSelectionPage.getNextPage(DataSelectionPage.java:55) [dataloader-58.0.3.jar:?]
        at com.salesforce.dataloader.ui.LoaderWizardDialog.nextPressed(LoaderWizardDialog.java:733) [dataloader-58.0.3.jar:?]
        at com.salesforce.dataloader.ui.LoaderWizardDialog.buttonPressed(LoaderWizardDialog.java:315) [dataloader-58.0.3.jar:?]
        at com.salesforce.dataloader.ui.LoaderWizardDialog$5.widgetSelected(LoaderWizardDialog.java:1296) [dataloader-58.0.3.jar:?]
        at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:252) [swtwin32_x86_64-4.27.jar:3.122.100]
        at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:89) [swtwin32_x86_64-4.27.jar:3.122.100]
        at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4256) [swtwin32_x86_64-4.27.jar:3.122.100]
        at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1066) [swtwin32_x86_64-4.27.jar:3.122.100]
        at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4054) [swtwin32_x86_64-4.27.jar:3.122.100]
        at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3642) [swtwin32_x86_64-4.27.jar:3.122.100]
        at org.eclipse.jface.window.Window.runEventLoop(Window.java:823) [dataloader-58.0.3.jar:?]
        at org.eclipse.jface.window.Window.open(Window.java:799) [dataloader-58.0.3.jar:?]
        at com.salesforce.dataloader.ui.uiActions.OperationUIAction.run(OperationUIAction.java:54) [dataloader-58.0.3.jar:?]
        at com.salesforce.dataloader.ui.LoaderWindow$2.widgetSelected(LoaderWindow.java:262) [dataloader-58.0.3.jar:?]
        at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:252) [swtwin32_x86_64-4.27.jar:3.122.100]
        at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:89) [swtwin32_x86_64-4.27.jar:3.122.100]
        at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4256) [swtwin32_x86_64-4.27.jar:3.122.100]
        at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1066) [swtwin32_x86_64-4.27.jar:3.122.100]
        at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4054) [swtwin32_x86_64-4.27.jar:3.122.100]
        at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3642) [swtwin32_x86_64-4.27.jar:3.122.100]
        at org.eclipse.jface.window.Window.runEventLoop(Window.java:823) [dataloader-58.0.3.jar:?]
        at org.eclipse.jface.window.Window.open(Window.java:799) [dataloader-58.0.3.jar:?]
        at com.salesforce.dataloader.ui.LoaderWindow.run(LoaderWindow.java:144) [dataloader-58.0.3.jar:?]
        at com.salesforce.dataloader.controller.Controller.createAndShowGUI(Controller.java:298) [dataloader-58.0.3.jar:?]
        at com.salesforce.dataloader.process.DataLoaderRunner.runApp(DataLoaderRunner.java:122) [dataloader-58.0.3.jar:?]
        at com.salesforce.dataloader.process.DataLoaderRunner.main(DataLoaderRunner.java:83) [dataloader-58.0.3.jar:?]
Caused by: com.sforce.async.CSVReader$CSVParseException: Found unescaped quote. A value with quote should be within a quote
        at com.sforce.async.CSVReader.nextRecordLocal(CSVReader.java:233) ~[dataloader-58.0.3.jar:?]
        at com.sforce.async.CSVReader.nextRecord(CSVReader.java:166) ~[dataloader-58.0.3.jar:?]
        at com.salesforce.dataloader.dao.csv.CSVFileReader.readRow(CSVFileReader.java:175) ~[dataloader-58.0.3.jar:?]
        ... 32 more

In my opinion the error originates from the wsc library at this line. Not sure if this is the intended behaviour in that case.

[ashitsalesforce]

Hi @R0Wi ,

I tried to reproduce with the following csv file:

name;external_id;newfieldtest
one;ext_1;"semi ; colon"

I was able to successfully import it with no errors. Note that I had to modify the CSV file generated by Excel to have semicolon character as the delimiter. The original CSV generated by Excel was:

name,external_id,newfieldtest
one,ext_1,semi ; colon

What settings did you specify in Excel to generate a CSV file with semicolon char as the delimiter?
Can you attach your csv file so that I can download it and try uploading it as-is?

[R0Wi]

Thanks @ashitsalesforce for your patience and your support! Please find the samples attached. Basically you can use any SF object for testing because parsing the CSV takes places before any mapping/upload.

I used these dataloader settings:

To save the Excel as CSV, I used the "Save as" dialog. Please note that my localization settings on Windows are using a semicolon as delimiter, that's why Excel also uses this character.

ProductUpdate.xlsx
ProductUpdate.csv

[ashitsalesforce]

Hi @R0Wi ,

Thanks for the screenshot of Settings dialog along with the csv file. Check the checkbox "Allow other characters as CSV delimiters" and try. It should fix your issue.

[ashitsalesforce]

PR #772 removes the "Allow other characters as CSV delimiters" checkbox because presence/absence of delimiters in "Other delimiters for load operations" text field is sufficient to determine if other characters are to be used as CSV delimiters.

[R0Wi]

Well this is embarrassing... You're absolutely right, after setting the checkbox everything works as expected.

I think I didn't really recognize the checkbox and thought that setting an additional delimiter is enough. So PR #772 make perfect sense to me, thank you!

Quickly coming back to the Excel feature: if you decide to implement such a thing in the future, please let me know. I can help implementing this if needed.

Thanks for your support!

[ashitsalesforce]

Hi @R0Wi , good to hear that your issue got resolved. Yes, that checkbox is not an intuitive one for the users. Thanks for helping us troubleshoot the issue to get to the root-cause.

Can you share a standalone implementation, possibly in Java, that takes a single parameter, name of a file with .xls or .xlsx extension and converts it to CSV and converts it to .csv with the same file name? One approach would be for data loader code to invoke such an implementation after the user chooses a .xls or .xlsx file and use the resulting .csv file for uploading.

[ashitsalesforce]

Here is a sample .xls to .csv converter code based on material and open source implementations found online. It does not work for .xlsx.

package xls;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.Iterator;
 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class XLSToCSVConverter {
    public static void main(String[] args) {
        System.out.println("XLSToCSVConverter called");
        String path =  args[0];
        System.out.println("args[0] = " + args[0]);
        
        String[] fileComponents = path.split("\\.csv");
        if (path.endsWith("csv")) {
            fileComponents = path.split("\\.csv");
        }
        if (path.endsWith("xls")) {
            fileComponents = path.split("\\.xls");
        }
        if (path.endsWith("xlsx")) {
            fileComponents = path.split("\\.xlsx");
        }
        
        String xlsName = fileComponents[0] + ".xls";
        if (!Files.exists(Path.of(xlsName))) {
            xlsName = fileComponents[0] + ".xlsx";
        }
        String csvName = fileComponents[0] + ".csv";
        if (!Files.exists(Path.of(xlsName))) {
            System.out.println("File " + xlsName + " not found at " + Path.of(xlsName));
            System.exit(-1); // did not find a xls or xlsx file
        }

        
        // Creating an inputFile object with specific file path
        File inputFile = new File(xlsName);
 
        // Creating an outputFile object to write excel data to csv
        File outputFile = new File(csvName);
 
        // For storing data into CSV files
        StringBuffer data = new StringBuffer();
 
        try {
            // Creating input stream
            FileInputStream fis = new FileInputStream(inputFile);
            Workbook workbook = null;
 
            // Get the workbook object for Excel file based on file format
            if (inputFile.getName().endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(fis);
            } else if (inputFile.getName().endsWith(".xls")) {
                workbook = new HSSFWorkbook(fis);
            } else {
                fis.close();
                throw new Exception("File not supported!");
            }
 
            // Get first sheet from the workbook
            Sheet sheet = workbook.getSheetAt(0);
 
            // Iterate through each rows from first sheet
            Iterator<Row> rowIterator = sheet.iterator();
 
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                boolean firstCellInRow = true;
                // For each row, iterate through each columns
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    if (firstCellInRow) {
                        firstCellInRow = false;
                    } else {
                        data.append(",");
                    }
 
                    Cell cell = cellIterator.next();
                    data.append("\"");
                    switch (cell.getCellType()) {
                    case BOOLEAN:
                        data.append(cell.getBooleanCellValue());
                        break;
 
                    case NUMERIC:
                        data.append(cell.getNumericCellValue());
                        break;
 
                    case STRING:
                        data.append(cell.getStringCellValue());
                        break;
 
                    case BLANK:
                        data.append("");
                        break;
 
                    default:
                        data.append(cell);
                    }
                    data.append("\"");
                }
                // appending new line after each row
                data.append('\n');
            }
 
            FileOutputStream fos = new FileOutputStream(outputFile);
            fos.write(data.toString().getBytes());
            fos.close();
 
        } catch (Exception e) {
            e.printStackTrace();
        }
 
        System.out.println("Conversion of an Excel file to CSV file is done!");
    }
}

Here is pom.xml:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>xls</groupId>
  <artifactId>xlstocsv</artifactId>
  <packaging>jar</packaging>
  <version>0.1</version>
  <name>XLS To CSV converter</name>
  <url>TBD</url>
  <organization>
    <name></name>
    <url></url>
  </organization>

  <properties>
    <maven.compiler.release>17</maven.compiler.release>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

  </properties>

  <repositories>
    <repository>
      <id>maven-eclipse-repo</id>
      <url>https://maven-eclipse.github.io/maven</url>
    </repository>
  </repositories>

  <dependencies>
<!-- Runtime dependencies -->
<!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-core -->
    <dependency>
      <groupId>org.apache.logging.log4j</groupId>
      <artifactId>log4j-core</artifactId>
      <version>2.20.0</version>
    </dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.3</version>
    </dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-csv -->
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-csv</artifactId>
        <version>1.10.0</version>
    </dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>
  </dependencies>

  <build>
    <defaultGoal>install</defaultGoal>

    <plugins>

<!-- plugins inherited from parent whose version needs to be specified
     to clear mvn versions:display-plugin-updates
 -->
<!-- https://mvnrepository.com/artifact/org.apache.maven.plugins/maven-clean-plugin -->
    <plugin>
      <groupId>org.apache.maven.plugins</groupId>
      <artifactId>maven-clean-plugin</artifactId>
      <version>3.3.1</version>
    </plugin>
<!-- https://mvnrepository.com/artifact/org.apache.maven.plugins/maven-deploy-plugin -->
	<plugin>
	  <groupId>org.apache.maven.plugins</groupId>
	  <artifactId>maven-deploy-plugin</artifactId>
	  <version>3.1.1</version>
	</plugin>
<!-- https://mvnrepository.com/artifact/org.apache.maven.plugins/maven-install-plugin -->
	<plugin>
	  <groupId>org.apache.maven.plugins</groupId>
	  <artifactId>maven-install-plugin</artifactId>
	  <version>3.1.1</version>
	</plugin>
<!-- https://mvnrepository.com/artifact/org.apache.maven.plugins/maven-jar-plugin -->
	<plugin>
	  <groupId>org.apache.maven.plugins</groupId>
	  <artifactId>maven-jar-plugin</artifactId>
      <version>3.3.0</version>
      <configuration>
          <excludes>
            <exclude>contrib/**</exclude>
          </excludes>
      </configuration>
	</plugin>
<!-- https://mvnrepository.com/artifact/org.apache.maven.plugins/maven-site-plugin -->
	<plugin>
	  <groupId>org.apache.maven.plugins</groupId>
	  <artifactId>maven-site-plugin</artifactId>
	  <version>3.12.1</version>
	</plugin>

<!-- plugins explicitly included in the project -->
<!-- https://mvnrepository.com/artifact/org.apache.maven.plugins/maven-enforcer-plugin -->
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-enforcer-plugin</artifactId>
        <version>3.4.0</version>
        <executions>
          <execution>
            <id>enforce-maven</id>
            <goals>
              <goal>enforce</goal>
            </goals>
            <configuration>
              <rules>
                <requireMavenVersion>
                  <version>3.6</version>
                </requireMavenVersion>
              </rules>    
            </configuration>
          </execution>
        </executions>
      </plugin>
        
<!-- https://mvnrepository.com/artifact/org.apache.maven.plugins/maven-compiler-plugin -->
      <plugin>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.11.0</version>
        <configuration>
          <forceJavacCompilerUse>true</forceJavacCompilerUse>
          <compilerArgs>
            <!-- <arg>-verbose</arg> -->
          </compilerArgs>
        </configuration>
      </plugin>

      <plugin>
<!-- https://mvnrepository.com/artifact/org.apache.maven.plugins/maven-shade-plugin -->
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-shade-plugin</artifactId>
        <version>3.5.0</version>
        <configuration>
          <createDependencyReducedPom>false</createDependencyReducedPom>
          <artifactSet>
            <excludes>
                <exclude>local.swt:swt*:</exclude>
           </excludes>
          </artifactSet>
          <transformers>
            <transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
              <mainClass>xls.XLSToCSVConverter</mainClass>
              <manifestEntries>
                <Main-Class>xls.XLSToCSVConverter</Main-Class>
                <Multi-Release>true</Multi-Release>
                <Permissions>all-permissions</Permissions>
                <Application-Name>${project.name}</Application-Name>
              </manifestEntries>
            </transformer>
          </transformers>
          <minimizeJar>true</minimizeJar>
          <filters>
            <filter>
               <artifact>org.apache.logging.log4j:log4j-core</artifact>
               <includes>
                   <include>**</include>
               </includes>
            </filter>
             <filter>
               <artifact>org.apache.logging.log4j:log4j-api</artifact>
               <includes>
                   <include>**</include>
               </includes>
            </filter>
            <filter>
               <artifact>org.springframework:spring-beans</artifact>
               <includes>
                   <include>**</include>
               </includes>
            </filter>
            <filter>
                <artifact>*:*</artifact>
                <excludes>
                    <exclude>**/Log4j2Plugins.dat</exclude>
                    <exclude>META-INF/*.SF</exclude>
                    <exclude>META-INF/*.DSA</exclude>
                    <exclude>META-INF/*.RSA</exclude>
               </excludes>
            </filter>
          </filters>
        </configuration>
        <executions>
          <execution>
            <id>make-shaded-jar</id>
            <phase>package</phase>
            <goals>
              <goal>shade</goal>
            </goals>
            <configuration>
            </configuration>
          </execution>
        </executions>
      </plugin>
    </plugins>
  </build>
</project>

Here is an example:

java -cp target/xlstocsv-0.1.jar xls.XLSToCSVConverter sample.xls

[ashitsalesforce]

Java code and pom.xml checked in contrib folder.

[ashitsalesforce]

A few alternatives to convert from .xls and .xlsx to .csv on command line are:

• Mac and Linux: csvkit. It requires python3. Here is a sample script that uses csvkit to convert to csv:

#!/bin/sh -f
xlsfile=$1
python3 /Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/csvkit/utilities/in2csv.py "${xlsfile}" > "${xlsfile%.xls*}.csv"

• Windows: A VBS script such as the one provided in this Q&A. Use wscript <VBS script name> <.xls or .xlsx file> <.csv file> to convert to .csv.