Here are some ideas I have....
1. The value '1000-' is not a number. Throw it out. Enforce some
standards in the data.
2. Do a search and replace on 'xxxx-' and replace it with '-xxxx'.
3. Based on this.....
> Something else I noticed was
> that if there is any sort of string in the column BEFORE the currency values
> start, .net will read the column as a string and everything will be
> processed.
......try adding an empty string row as the first row, then start
processing everything as usual.
Eric
On Tue, Sep 25, 2012 at 10:19 AM, crocboy25 <icestor...@hotmail.com> wrote:
> Hello Group,
>
> Summary of the issue. Using vb.net 2010 I am trying to read an excel
> spreadsheet using ACE/Jet. We do not have excel on the server. One of the
> columns has data in it that is currency and formatted either as (1000) or
> could also be -1000 for negatives. The actual format of the column in excel
> is currency. However, if down the page someone has entered in 1000- for a
> negative amount, .net will not read it. It gives it a "" value. I tried
> setting IMEX=1 but that still didnt fix it. Something else I noticed was
> that if there is any sort of string in the column BEFORE the currency values
> start, .net will read the column as a string and everything will be
> processed. I can't depend on that assumption though that the user will have
> some sort of header for that column. Basically unless the column type is
> switched to TEXT those 1000- instances wont work from what I can tell..... I
> had read that setting the IMEX=1 would resolve it but at times a loss of
> data could occur. Not sure if that is the issue here specifically but the
> value for that field is still "". Here is how is select the data:
>
> strConnection="Provider= Microsoft.ACE.OLEDB.12.0;" & "Data Source=" &
> mstrExcelFilePath & strSpreadsheet & "; " &
>
> "Extended Properties=""Excel 12.0;hdr=no;imex=1;;"";"
>
> pobjDataAdapter =New OleDbDataAdapter("select * from [LASPBS$]",
> strConnection)
>
> 'Fill the DataTable with data from the Excel spreadsheet.
>
> pobjDataAdapter.Fill(pobjExcelDataTable)
>
>
>
> Has anyone out there had this sort of issue and could provide some help?
>
> Thanks in advance.
>
> Lee
>
> --
> You received this message because you are subscribed to the Google
> Groups "DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML
> Web Services,.NET Remoting" group.
> To post to this group, send email to dotnetdevelopment@googlegroups.com
> To unsubscribe from this group, send email to
> dotnetdevelopment+unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/dotnetdevelopment?hl=en?hl=en
> or visit the group website at http://megasolutions.net
--
You received this message because you are subscribed to the Google
Groups "DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML
Web Services,.NET Remoting" group.
To post to this group, send email to dotnetdevelopment@googlegroups.com
To unsubscribe from this group, send email to
dotnetdevelopment+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/dotnetdevelopment?hl=en?hl=en
or visit the group website at http://megasolutions.net
