Strange data

[MasterRacker]

I'm linking to a spreadsheet for the purposes of "scrubbing" one of the columns. The spreadsheet is a dump from another application and one column contains CR/LF combos that I need to remove (among other things). The wierd thing I'm seeing though is some rows that show visible data are throwing errors not only on a Replace command but even on a IsNull check. What might be causing that?

_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[MasterRacker]

Thought I would add a little more info. Here's my test query

SELECT OriginalXLS.[Incident Nr], OriginalXLS.Blotter, IsNull(OriginalXLS.Blotter) AS Expr1, Len(OriginalXLS.Blotter) AS Expr2
FROM OriginalXLS;

Most rows do what I expect: I get a length and a 0 for IsNull. The empty fields give a -1 for IsNull and a null length. What's got be stumped is a few rows where Blotter shows data but both length and IsNull show #Error.

Once again, the source is a spreadsheet connected as a linked table.

_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[MasterRacker]

OK, one more piece of information: Even this returns an error:

Public Function EchoString(str As String)
    On Error GoTo Err

    EchoString = str
    Exit Function
    
Err:
    EchoString = "--Error--"
End Function

Simply showing the column shows data, viewing the column in the source xls file shows data. Trying to manipulate it in any way throws an error and I don't see "--Error--" I see the system "#Error". It seems that simply trying to touch it throws an error that can't be trapped.

Again, this is not the entire column, just certain rows. Most are fine. If I had any hair I'd have pulled it by now.


_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[CautionMP]

MasterRacker,
Sounds like a data type issue.

I'm guessing that if you isolate one of the cells that is returning an error you will probably find that the Format in Excel is different from the cells that don't return an error.

In Excel try selecting the entire column for [tt]Blotter[/tt] and change the format to Text and see if that solves the problem in your query.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[MasterRacker]

I had forgotten Excel can have multiple data types in a column. Unfortunately, this is still a no go. What's really strange is that the offending cells can't be displayed by Excel in the cell after conversion - they show as "#######...". If you select one of them, the data still shows in the formula bar though.

This survives a round trip conversion as well: save as .CSV. Close then reopen the CSV and save as XLS. I also looked at the CSV using Notepad++ and showing all characters. I don't see anything but CR/LF combos.

_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[MasterRacker]

CMP, Your idea put me on the right track. The problem is not in the data itself, it's an artifact of Excel. I looked at the CSV in a hex editor and saw absolutely nothing. If I create another linked table from the CSV rather than the XLS, I can deal with the data appropriately.

Evidently, some cells are confusing the Excel parser.

_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[SkipVought]

Rather than OPENING the .csv directly in Excel...

Open a NEW workbook or NEW sheet.

Data > Import Data > IMPORT.

Specify DELIMITED and COMMA as the delimiter.

Specify TEXT as the Column Data Format for the offending column, (and specify the column format of other columns as needed) and FINISH.

See if that does not eliminate the ######### problem.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}