Importing file that has one column whose first row is empty

Hello everyone,

I'm struggling with the import of a file: there is one column that has the first row empty, therefore the file won't upload properly.

I can't remove the column from the combination of properties because I need it to generate the unique key.

The only solution I've found so far is importing the file with column headers/empty headers, however I am looking for a better solution as this one would require the user to manually modify the file every time when importing.

Do you have any suggestion?

Thank you.

Answers

  • Note: the original file doesn't have column headers/empty headers originally

  • @AlessandraPorroACN

    When you map your columns you should be able to ignore the first column. Set the line items mapping to column headers and you should be fine.

  • I can't do that because I need this column. Every month the user uploads a different file, however if the first row of the column AJ is empty, the uploading fails.

  • @AlessandraPorroACN

    For an import file to work you do not need column headers and the first column can be in the second position. Important though that the file is .csv or .txt with some type of delimiter. Set column header row to zero. First data row to 1. Anaplan will ignore the first column. Just tested it and it worked fine.

  • @AlessandraPorroACN

    Also, the column with missing values - if that column is pointed to a list then Anaplan does not know what to do with the row whether it's blank on the first row or not. You may have to add a line item to your system module that looks for blanks and then add another line item that adds a list item, like "None". Then import to your destination module.

  • Unfortunately it doesn't work if one of the columns has the first row empty;

    as regards your example, if you remove the 100, the import will fail.

  • @AlessandraPorroACN

    Is column AJ pointing to a list formatted line item? Change the format to text then use another line item to test for blanks.

  • That may actually work, but can I still do it if the file is firstly imported into a flat list, that is where the upload fails, and later on imported in the module?

  • @AlessandraPorroACN

    Just tested this. You're right. Learn something new al the time. Only way to get that to work with this file format is to make sure the "last column" is populated. Can you add a column at the end of the file, OR can you move the last column to the left so the final column is always populated with something?

  • I could but the user would prefer to not manually modify the file everytime.. So I was looking for a solution to avoid that

  • Hi @AlessandraPorroACN

    are the blank values stored in the source file as " " ?

    If that's the case you can try to set the text delimiter to None instead of ". That way, Anaplan recognizes the " as a value and will import the data as " " text value. You could then remove the quotation marks by using the substitute formula or an if then else statement.