Does Not a Text Field Accept ANY File Format? 1

[kjv1611]

I have several tables with all their fields formatted as text, so that I won't have to worry about import errors.

Well, apparently that aint workin'.

On at least 3 tables, a few rows records gave import errors, though strangest thing is they seem to have been imported anyway???

Again, ALL fields are formatted as text.

The import error in the "$_ImportErrors" tables I am getting in ever case is:

Type Conversion Failure

Here are a few of the samples of data:

[OL][LI]<A Blank cell in Excel, though previous blanks processed with no errors in exact same column in exact same spreadsheet>[/LI]
[LI]713[/LI]
[LI]<Another Empty Cell>[/LI]
[LI]<Yet another empty cell>[/LI][/OL]

For the life of me, I can't see ANY consistent pattern with the errors, nor any inconsistency in the data when compared to other data that IS being imported.


If anybody can think of any reason for such a quirk as this, please let me know. Again, it seem so far that the data IS being imported anyway, but just giving errors...

I first thought that perhaps the data was old, but nope, date modified was same as the actual import tables..

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Okay, I looked at the wrong rows... so, I'll put the accurate field values that aren't being imported (examples) below...

Still doesn't make sense to me, b/c it is "text" in every case...

[OL]
[LI]130A[/LI]
[LI]144-23[/LI]
[LI]71-37[/LI]
[LI]39 11[/LI]
[LI]116P[/LI]
[/OL]

Any clues?

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

One other thought: meant to put it in last post..

The data is NOT being imported, as I expected with the ImportErrors tables... but I didn't expect it with the text format..

--

"If to err is human, then I must be some kind of human!" -Me

 

[maxhugen]

I've had a number of odd problems with importing spreadsheet data over the years. Just a thought: have you tried importing to a New Table?

Max Hugen
Australia

 

[kjv1611]

Yes, and I had bigger problems, b/c some of the fields would get formatted as a numeric value when way down the list a couple of items should be text.

That's why I went through the whole routine with the text files.

Thankfully, though, I finally got some code worked out that imports the spreadsheets, then goes back and "fixes" the values that get left out - by copying it in from Excel for those few instances.

And then I've already got other code in place to combine them all into one table - though I think I'm needing to clean a bit of it up now as well.. figures, I thought it worked perfectly before! :0)

All this because the original workbook is supposed to contain sheets that all have the exact same format, etc, but no - of course they do not.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

The problem with picking up the wrong tables or fields has been corrected. I fixed the last "oops" in that one just a minute ago, I think, so all of that is working 100% correctly for now.

There is once piece in the "ImportErrors" part that I may go back and fix later, but I'm going to leave as is for now. Basically, only one field has been a problem so far, so I've been specifying the field. It'll best if I use a variable instead in case they decide to change something else in the future - original data format.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

kjv,

"Does Not a Text Field Accept ANY File Format?"

No!

If you ALSO have NUMERIC values along with TEXT values in the source data, you've got problems.

Skip,

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

 

[kjv1611]

IC

So, is there any other indirect way of getting around that, or does the data have to be "fixed" somehow?

The only field that has caused any issue is a "house #" field which might contain 143B for an apartment address, for instance.

--

"If to err is human, then I must be some kind of human!" -Me

 

[maxhugen]

@Skip, I don't think you're correct about a text field not accepting numeric values...

I used the 5 data examples that kjv1611 listed in an earlier post, plus some extra numeric values, and they all import into a text field in an existing table aok.

@kjv1611, just another thought, have you tried specifically setting the format of the Excel column that's causing the problems to 'General'?

Max Hugen
Australia

 

[SkipVought]

[ol]
[li]130A[/li]
[li]144-23[/li]
[li]71-37[/li]
[li]39 11[/li]
[li]116P[/li]
[/ol]
are NOT NUMBERS! They are TEXT!

Turns out that IMPORT does a conversion of NUMERIC to TEXT, at least in a small limited test.

However if you LINK the workbook as a table, any NUMERIC value will display a #Num! error.

And there are DEFINITELY problems when you try to QUERY a workbook having MIXED data types.

But as a matter of practice, it is never a good design practice, to mix data types in a column. And when I get workbooks from other sources that do this, I always CONVERT my numeric values to TEXT.

Skip,

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

 

[maxhugen]

@Skip, I agree they are non-numeric values, which is why, as I said, I added some numeric values as well.



Max Hugen
Australia

 

[kjv1611]

maxhugen,
Well, unfortunately, changing the Excel cell format to General won't work. They are already set to general.

Skip,
That is very interesting that Access can't see alphanumeric values, and just accept them as text in an import. I am 100% able to manually enter such values in a text field, just not import them. But perhaps that throws off the "convert to text" portion that runs that you were talking about.

Thankfully, with the import errors tables, I can just have the code fix that all up after the import is done. The last few attempts (tests) seemed to work 100% okay.

Thanks for all the advice.

--

"If to err is human, then I must be some kind of human!" -Me