You’re offline. This is a read only version of the page.
Hello:
Create a SmartList in SmartList Builder containing the tracking number field from the SOP10107 table.
Ensure that that tracking number has over 30 characters and beings with the number "42000".
Now, try exporting to Excel from SmartList.
You will get an error message from Excel saying that the file format or extension is invalid.
You will not get this error, if the tracking number begins with 42000 and only contains 7 characters! Very strange!
Any ideas?
Thanks!
John
*This post is locked for comments
I have the same question (0)
Here's the fix. In the ninth position of the tracking number field was a hidden period. This is discussed, as follows:
I was able to find the culprit by pasting the numbers from your spreadsheet into an Oracle query using the dump function, which shows the length plus the ascii representation of all the characters in a string. The ones with the problem character returned an invalid character error and pointed to the ninth position of the string. Speaking of Oracle, this would be relatively simple using the Oracle regexp_like function but, unfortunately, sql server does not provide such functionality. As such the resolution of having more than 22 characters on the right becomes a little more cumbersome to determine. One approach might be like the following:
select case when substring(tracking_number,9,1) in ('0','1','2','3','4','5','6','7','8','9') then tracking_number else left(tracking_number,8) + substring(tracking_number,10,100) end as tracking_number
Where 100 is a length that will accommodate the largest possible number of characters allowed. From the documentation, if the start position plus the length is larger than the length of the expression (i.e.tracking_number), it will return the entire expression beginning at the start position.
So, placing the following formula inside of SmartList Builder provided the solution:
REPLACE(<your column name>, char(29),'')
Share
Report
All responses (
Answers (
