0

I have used a variety of methods to try and import this data (a 97-2000 xls file generated by a crystal report server each morning that fills the first sheet fully and more than half fills the second sheet - about 120,000 records in total although it varies). There are about fourteen columns in the data, but the problem is that the first and second row have some title data taking up two columns that cannot be stopped from being entered by the report server. When I go to import the data with code such as:

Imports System.Data.OleDb

Public Class ImportData
Private Const databaselocation As String = "G:\db.mdb"
Private Const excelfileloc As String = "D:\"

Private connstr As String = "Provider=Microsoft.JET.OLEDB.4.0; Data Source=" & databaselocation & "; User Id=admin; Password=;"
Private excelstring1 As String = "Provider=Microsoft.JET.OLEDB.4.0; Data Source=" & excelfileloc
Private excelstring2 As String = "; Extended Properties=""Excel 8.0; HDR=NO; IMEX=1"";"

Public Function ImportExcel() As Boolean
    Dim connection As OleDb.OleDbConnection = New OleDb.OleDbConnection(connstr)
        Dim command As New OleDb.OleDbCommand
        connection.Open()
        command.Connection = connection

    Try
                Using conn As New OleDbConnection(excelstring1 & "excel.xls" & excelstring2)
                    Using cmd As New OleDbCommand()
                            cmd.Connection = conn
                            cmd.CommandText = "INSERT INTO [MS Access;Database=" & databaselocation & "].[dbo_impExcel] SELECT * FROM [Sheet1$]"
                            conn.Open()
                            cmd.ExecuteNonQuery()
                    End Using
                End Using
        Catch ex As Exception
        End Try
End Function
End Class

It only imports the first two lines and ignores the rest. When I then repeat the code but using [Sheet2$], the data is returned fine. If I manually go into the excel file and delete the title, the data is imported fine. At the moment I am using code to delete the title lines first, but I'm sure there should be a better way. In my experimentations I have even tried creating a stored procedure in an MS SQL database using OPENROWSET and the problem still occurs (although due to various job constraints this job must only rely on JET.

Makita
  • 726
  • 5
  • 12
  • With more experimentation and research I'm realising that the way Crystal generates the excel files is not entirely compliant with the standard. For example, the files are missing CountryCode information that tells excel what Region the user had in mind when they were created. When I get a chance I will post the code I am now using to 'clean' the excel files as I feel there must be others running into this issue. – Makita Sep 25 '13 at 02:12

1 Answers1

0

You might try using VB in an Excel worksheet to create and copy proper data to a new xls file. Personally I tend to save things as tab delimit or Pipe text file instead of importing as xls. Saves me the frustration of trying to Import xls.

If the file has the same title every day or based on date you should be able to just code it in a xls file which does all the work for you then closes afterward.

Crazyd
  • 406
  • 2
  • 7
  • 15
  • Unfortunately the crystal server replaces the file each morning with the same name. Besides I have perfectly good code in the application that loads the data to first remove the header lines so that the import goes well, it's just that it'd be great if I didn't have to meddle in the excel file first. My first choice was delimited txt files however the only data only report that the server creates correctly is the data only excel option (trying to get this kind of thing fixed at this company is like getting blood from stone). – Makita Sep 16 '13 at 09:38