Problem Importing data Excel (6.05708e+008)

[bakesomecakes]

I am trying to import some data from excel.
i am using visual studio 2008 .net 3.5, on win xp sp3

the data that is being imported form excel is being edited somewhere in the import.

i am sure it is somthing todo with the olddb connection.

the data is coming out like this 6.05708e+008

the connection string goes somthing like this:
("provider=Microsoft.Jet.OLEDB.4.0; Data Source='C:\test.xls'; Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";")

i have tryed change the IMEX value to 0,1 and 2. nothing seems to work. each time the data is imported incorrectly.


Could anyone please help. i am been working on this non stop for 2days and i am getting now where quickly. and my client is going nuts, i depend on this job to pay the rent.


all comment welcome.

[RobDog888]

Welcome to the Forums.

did you also try editing the registry to increase the sample number of rows to determine the data types?

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

Change the value to 0 so it samples the entire sheet. The default is 8.

Also...

"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

[jmcilhinney]

Also, you've said that the data comes out wrong and that it comes out a certain way but you haven't told us exactly what it is you expect. What EXACTLY is the data to begin with? How EXACTLY are you using it?

[bakesomecakes]

Thank alot RobDog888

Great idea i was sure it was going to work, but i didnt


i edited "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows"

With the value of "0" but i got the same result


Good point jmcilhinney i expect the data to be the same value as what it is in the excel file:
I thought it was a problem with high numbers, i still think this is true because the second set of data contains "-" so i think this is being treated as a string, which is why it is not being modifed.

excel data: 605708140
after import: 6.05708e+008


excel data: 60609653-46761606-46743275
after import: 60609653-46761606-46743275


Really appreciated the quick reply guys didnt think i would get help so quickly

[bakesomecakes]

HERE IS A SIMPLIFIED VERSION OF THE CODE:
the real code imports it directly into SQL Server 2005, if that gives you anymore ideas

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DtSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

        MyConnection = New System.Data.OleDb.OleDbConnection _
        ("provider=Microsoft.Jet.OLEDB.4.0; Data Source='C:\test.xls'; Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";")
        '("provider=Microsoft.Jet.OLEDB.4.0; Data Source='C:\test.xls'; Extended Properties=""Excel 8.0;HDR=Yes;IMEX=0"";")
        '("provider=Microsoft.Jet.OLEDB.4.0; Data Source='C:\test.xls'; Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";")
        '("provider=Microsoft.Jet.OLEDB.4.0; Data Source='C:\test.xls'; Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"";")
        'strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", strFile);

        MyCommand = New System.Data.OleDb.OleDbDataAdapter _
        ("select * from [BIMOTO$]", MyConnection)
        MyCommand.TableMappings.Add("Table", "TestTable")

        DtSet = New System.Data.DataSet
        MyCommand.Fill(DtSet)
        DataGridView1.DataSource = DtSet.Tables(0)
        MyConnection.Close()



    End Sub
End Class

[RobDog888]

What is your table field's definitions where the values are being changed to scientific notation?
Perhaps a change in the affected field's data type would be needed.

[bakesomecakes]

sorry for the late reply i had somthing to do. but i should be on all day now and most of the night, coding till 4am is what i live for hahaha.

anyway back on topic i have attached a copy of the excel file, maybe somone could take a look, there is also the sourcecode above you should be able to get it working in a few seconds if anyone has the time or will. would be great thanks

its the information in the last column (column H) that is being messed up by the import.

i dont think my client can change the layout or format of the spread sheet, so in an ideal situation it would be great if I could do somthing in code.

any question i should be around all day.


thanks again RobDog888 for the time.

[CodinToTheMusic]

I had this same issue today. Just figured it out.

As it turns out, you need to CAST the incoming data through your select statement. Not sure why exactly that Jet decides to use exponents by default, I can't think of anyone who would want to use them. Anyways, the below code worked for me.

This was my original code that didn't work
(data was imported in the format 2.0067e+008)

INSERT INTO [MetricsManagerDB].[dbo].[metdsEHS] ([EmployeeSSO])
SELECT [Employee SSO]
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=D:\Users\501797607\VS Projects\ATOMerticsManager\Uploaded_Files\EHS Tracking - 200943.xls;Extended Properties=Excel 8.0')...[EHSdownloadXL$];

This is the updated code that does work
(data was imported in the format 200672157)
(notice the use of the CAST statement)

INSERT INTO [MetricsManagerDB].[dbo].[metdsEHS] ([EmployeeSSO])
SELECT CAST([Employee SSO] AS bigint)
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=D:\Users\501797607\VS Projects\ATOMerticsManager\Uploaded_Files\EHS Tracking - 200943.xls;Extended Properties=Excel 8.0')...[EHSdownloadXL$];

Hope this helps!

CodinToTheMusic

[bakesomecakes]

thanks alot CodinToTheMusic

i haven't tested it yet, but soon as i get some time i will give it a go.

In the end i just used a work around by calling the excel sheet several time with different IMAX arguments, then matched the data up in code.

but this is a much better solution, i didn't know that the JET interface was so expansive. I goggled around for days and found nothing on forums or MS sites.

even MSDN didn't have the answer, although it had the problem.

like i said i will test this when i have some free time and get back to you guys.


Owe you one CodinToTheMusic, really appreciated you getting back to me.

[bakesomecakes]

posted twice ops

[bakesomecakes]

I just rememberd some of the data in the column is String and some is numerical.

for example "34-234234-2342-23432" and "2232442"

i have complete the project now so there is no rush. but this does seem to be an ongoing problem for older version of office (i think all the new versions are xml based).

so it would be great if we could come up with a solution to this. i would be more than happy to post the solution around to other forums, becuase when i was trying to find a solution i must have seen 100 plus forums and websites with developers having the same problem and no answers