Don't be so lazy here: select * from [LASPBS$] do Converts to strings for all your columns and then switch back in VB after the data is in a container..
On 9/25/12, crocboy25 <[email protected]> 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 [email protected] > To unsubscribe from this group, send email to > [email protected] > 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 > -- Stephen Russell Sr. Analyst Ring Container Technology Oakland TN 901.246-0159 cell -- 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 [email protected] To unsubscribe from this group, send email to [email protected] 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
