Need "Text only" data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

All

I have an Excel worksheet (also saved as .CSV) that I need to read into a .Net dataset

Using System.Data.Oledb namespace, I can connect to either and extract data. The problem is that one column contains alphanumeric values, or which the majority are numerical in appearance. ie: "100101", "100102"..."1001AA", "1001AB"

When the data is extracted, the column comes across as numerical, and all alphanumerical values are then NULLed. Hence, I lose a good chunk of the data I need

So, how can I persuade the data to come across as Text-Only

Simon
 
Simon:

I can't find the documentation so I'm going off memory (90% sure though) on
this. The type of the column is derived based on the data in the cells if
it's not specified (ie the Cell Format is General). I seem to recall it
taking a guess as to the type if it's general. Try Changing the type of the
cell to "Text" in the format section (Format ->Cells (then highlight the
cell or range that's giving you drama)-> Number then select "Text" from
the listbox. There's no subitems for Text so you can just click on it.
Make sure that you highlight all of the cells in the row so there's no
confusion.

This should work unless I'm imaging stuff, but I'm still looking for the
documentation just to be thorough. If it doesn't work, let me know but I'm
pretty sure it will.

HTH,

Bill
Simon said:
All,

I have an Excel worksheet (also saved as .CSV) that I need to read into a ..Net dataset.

Using System.Data.Oledb namespace, I can connect to either and extract
Click to expand...
data. The problem is that one column contains alphanumeric values, or which
the majority are numerical in appearance. ie: "100101", "100102"..."1001AA",
"1001AB".
When the data is extracted, the column comes across as numerical, and all
Click to expand...
alphanumerical values are then NULLed. Hence, I lose a good chunk of the
data I need.
 
William, you may be right, but I don't think it will work, because the
reverse does not work (writing to Excel).
We had to insert 3 rows in front, having dummy data (the OleDb provider does
not check any Excel formats specified, it looks at the data).
 
Hey Chris:

I can't find the document I seem to remember seeing and I don't have an IDE
in front of me now. I may be mixed up but I could have sworn that the
OleDbSchemaTable was based on the values of the cells if general format was
selected but otherwise it would grab it from the format. This is purely
from memory and since I can't find what I was looking for, I'm definitely
not sure but it's worth a try. Since excel can store names for instance, it
has to have more than one type that it can use and it's got to make that
determination dynamically. Since it's reading those as numbers (almost
looks like it's using the VB Val function and just strips that stuff out)
and ignoring the rest, there's got to be some way to manipulate this. I'm
stilll looking b/c this is driving me nuts.. defintitely a good FAQ subject.

Thanks again,

Bill
 
Bill,

Have tried formatting as "Text", with no difference in results. From my experiments (running into 2+ days now) the format of the cells makes no difference. [Tried it again just now, so as not to embarass myself.] I've determined that if the number of numeric values is >= the alphanumeric then I get "null"s. And since the alphanumeric values are always in the minority....

At the moment I have to load my Excel into Access, then go through the wizard to save it as a CSV with double quotes, then to load it into my app, when all I want is to read it straight from Excel. And as I mentioned earlier, I tried savinf CSV from Excel, but this also doesn't work (presumably coz the duoble quotes are missing, with no obvious way to get them included.)

Simon.
 
Hi Guys,

Ditto, I lost hours recently over this. Bummer is I just also lost a couple
more today. I have a column called ContCode, it has values like 401, 401B,
JKH404, etc.

Every time I copy it in, the numeric values come but the text values get
NULLed out. I have selected the column and set its format to Text.

In desperation, I'm now having to export these files to tab-delimited text
and then read them in. That seems to work ok. It's a real bummer that DTS &
the OLEDB provider for Excel don't want to play this game properly though
:-(

HTH,

--
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com

Simon said:
Bill,

Have tried formatting as "Text", with no difference in results. From my
Click to expand...
experiments (running into 2+ days now) the format of the cells makes no
difference. [Tried it again just now, so as not to embarass myself.] I've
determined that if the number of numeric values is >= the alphanumeric then
I get "null"s. And since the alphanumeric values are always in the
minority....
At the moment I have to load my Excel into Access, then go through the
Click to expand...
wizard to save it as a CSV with double quotes, then to load it into my app,
when all I want is to read it straight from Excel. And as I mentioned
earlier, I tried savinf CSV from Excel, but this also doesn't work
(presumably coz the duoble quotes are missing, with no obvious way to get
them included.)
 
While I'm on it, my other pet dislike is that if I have a column in the
target database that is NOT NULL but has a DEFAULT value, the data won't
insert and complains that it can't insert a NULL value. That's daft. If I
make the column NULLable, the data inserts ok. If I then make it NOT
NULLable, the default then takes effect. This is silly.

HTH,

--
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com


Simon said:
Bill,

Have tried formatting as "Text", with no difference in results. From my
Click to expand...
experiments (running into 2+ days now) the format of the cells makes no
difference. [Tried it again just now, so as not to embarass myself.] I've
determined that if the number of numeric values is >= the alphanumeric then
I get "null"s. And since the alphanumeric values are always in the
minority....
At the moment I have to load my Excel into Access, then go through the
Click to expand...
wizard to save it as a CSV with double quotes, then to load it into my app,
when all I want is to read it straight from Excel. And as I mentioned
earlier, I tried savinf CSV from Excel, but this also doesn't work
(presumably coz the duoble quotes are missing, with no obvious way to get
them included.)
 
¤ All,
¤
¤ I have an Excel worksheet (also saved as .CSV) that I need to read into a .Net dataset.
¤
¤ Using System.Data.Oledb namespace, I can connect to either and extract data. The problem is that one column contains alphanumeric values, or which the majority are numerical in appearance. ie: "100101", "100102"..."1001AA", "1001AB".
¤
¤ When the data is extracted, the column comes across as numerical, and all alphanumerical values are then NULLed. Hence, I lose a good chunk of the data I need.
¤
¤ So, how can I persuade the data to come across as Text-Only?

For Excel you can use the IMEX=1 parameter to work with mixed mode columns:

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\Book20.xls;Extended Properties=""Excel 8.0;HDR=NO;IMEX=1;"""

For a Text file you will probably need to use a schema.ini file to define the structure and/or data
types:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
How about while using the Primary Interop Assemblies?

I'm running into the same problem but using the Microsoft.Office.Interop.Excel object.

Given that there is no documentation that I'm aware of and that MS didn't see fit to include documentation with interop downloads, I don't have much clue as to even where to look for that.
 
Back
Top