nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Excel #NAME? error within data

sunderedsea

I'm pulling journal entry data from Oracle and using Alteryx to trim a 159-column standard Oracle report down to 37. However, I'm having a problem with a #NAME? error in Excel, where the data within some of my columns starts with the characters "=-@". Excel renders this as #NAME? (although when I click into that cell, it shows "=-@Data"). I'd like to preserve the "Data" portion of the string.

After Input, Alteryx looks like it hardkeys the "#NAME?" error, so I haven't been able to find a way to trim the leading "=-@" characters within Alteryx so I can see the actual text present in the source. Any insights on how best to accomplish this? I'd like to build this into my workflow and avoid having to open each report in Excel and Find/Replace the problem characters if possible.

Accepted answers

All comments

OTrieger

@sunderedsea
I'm trying to understand where is the issue. Is it with the extracted file from Orcale when opening Excel?

Is it possible to extract an EXL format from Orcale, it might be an easy solution.

sunderedsea

Good question. This is a standard report out of Oracle (called Account Analysis Report). It's possible to pull an Excel version, but it excludes many of the columns I need so I've been downloading a CSV copy instead, then Save As to .xlsx. I'm listing below how the same cell data is rendered in each version:

Excel version, directly from Oracle: "-Company Name"
CSV version, directly from Oracle: "=-Company Name"
Excel version, Save As from CSV: "=-@Company Name"

As mentioned in my original post, I know I could fix this by opening the report and using Find/Replace to remove the leading "=-" characters, but I'd like to avoid having to do that each time.

Appreciate any other thoughts on how to resolve! Thanks.

OTrieger

Ok, then the errors comes from saving it as xlsx format, just pull it in to Alteryx as csv, Alteryx knows how to handle this type of file format. Best to use the original file and not a converted one.

sunderedsea

Agreed, but I unfortunately had an issue importing directly from the CSV formatted file. Alteryx is splitting each row of data into multiple rows, which I've never seen before.

I've explored a few options to address this. Here's a screenshot after reading in with a comma delimiter. Rows 1-7 read as Null, while the cell in row 8 of the first column should actually be in the final column of row 10. Row 9 is Null except for its first column, strangely.

Here's what happens when I read the file in with no delimiter ("\0"), but as you can see, each of my rows are still split into three. I've tried the options to ignore delimiters in Quotes or None. The red indicators say the cell has truncated characters, despite setting the field length to 50,000.

OTrieger

When you open the csv file in a notepad does each entry has 3 rows? Just to ensure that what you see in Alteryx is actually reflecting the text file.

sunderedsea

Notepad is separating each line into three rows:

Row 1: ,300000009055628… (long string of data unique to each row)

Row 2: ","Equals to 20130
Row 3: "

However, Excel interprets this correctly when opening the CSV file there, or saving as .xlsx. So we're back to the original problem of how to remove the leading characters when first reading into Alteryx.

OTrieger

I see. The issue is that the minute that you save it as xlsx the data changes and that the worse scenario than having 3 lines per entry, at least you have the original data.

I would go with csv as you can handle it with Alteryx.

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15908

binu_acs 15783

MarqueeCrew 13710

apathetichell 13703