Errors when outputting data

[Vyx]

Hi All,

I am fairly new to Alteryx and I am trying to create a workflow and need some help as I've hit a brick wall.

A little bit of background, we receive an Excel binary report with global data in it and usually I process the data by filtering two columns (product and country of sales as we don't sell every product offered and are only interested in our own country). This spreadsheet is saved in a central location - each of the teams have individual spreadsheets with just their info that uses vlookups to pull the relevant information they need from this central spreadsheet using a unique identifier (Sales ID).  

I am now creating a workflow to avoid having to manually update this every day.

I created the input tool and used a filter tool to filter the data down to the information we need (I used the Browse Tool to check and the number of rows matches what would be expected from the raw data. 

The two problems I have are as follows:

- There is a unique Sales ID - sometimes this appears as purely numeric i.e. 123456 other times as a mix of numeric and letters i.e. SAL123456. Alteryx automatically allocated this as 'Double' so where the Sales ID comes up SAL123456 it inserts a null value - I have tried to use the Select tool to convert the data type to V WString however I still get null values whenever a Sales ID has a mix of numeric and letter characters. Not sure how to fix this!  

- When I try to output the file as a binary file, I receive the following error message:

Error: Output Data (5): DataWrap2OleDb::SendBatch: Microsoft Access Database Engine: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Insert into `Sheet1`(it lists the names of the fields here)

I have seen from other posts that this tends to happen because one of the data types is incorrect but I have no idea which - all the data types are either Date, V W String or Double as there is no obvious way to discern which (there are over 100 fields in the data set). 

Sorry for not posting data here as the data is commercially sensitive and contains client data. Really appreciate any help you can give me! 

[DavidP]

For your 1st question, you can try using a datacleanse tool to remove the letters and then convert to a number datatype with select.

Not sure about your 2nd question. 

[Vyx]

Hi David,

Thanks - that sadly won't fix the problem. The Sales ID acts as a unique identifier so I need Alteryx to output all values in this column regardless if they are purely numeric or a mix of Letters and numbers! 

Appreciate your help! 

[DavidP]

Sorry, I misunderstood - should have read the question properly. One way of forcing the Input data tool to load your data as Text is to check Option 6 - First row contains Data. This way the first row will contain the field name and the data type will be set to string and all you Sales ID's with letters should be loaded correctly. You can then use a Dynamic Rename tool with option set to take field names from first row of data.

[DavidP · Accepted solution]

As for the error when writing the output file, from searching for that error on the Community it seems that using and Int64 datatype in the output can sometimes cause this and I've also seen posts about the Excel legacy drivers.

Can you check what datatypes you're using for your output data - if there are Int64's try changing them to Double.

Which Excel driver are you using? Perhaps try a different one.

[Vyx]

Thanks David - this seems to have done the trick on both error messages (not entirely sure why for the second error) - this has now thrown up another error though!

Downstream we have spreadsheets that run vlookups into this spreadsheet therefore if the sales ID is purely numeric then the vlookups won't work - from my own research I know Alteryx won't let you have two different data types in one field so I do not believe it is possible to resolve in Alteryx.

The simplest alternative appears to be to ask a member of my team to highlight the column in the Excel output and use the text to columns option as this will convert the purely numeric sales IDs as numbers yet leave the as is - I have looked into the text to columns option in Alteryx but this separates data based on specific values/options.

If you had any other suggestions on how to fix this I would greatly appreciate your help but I want to say thank you for your help thus far - it is really appreciated! 

[Vyx · Accepted solution]

Hi All,

In case anyone stumbles across this the fix to the final problem was rather simple! 

If you change the output file type to CSV it outputs a file that is suitable to run vlookups to.

Viral.