Importing datas to SQL Server. Need suggestions!

Fly4High · July 31st, 2006, 10:59 PM

Hi forum,
I have hundreds of Excel spreadsheets, each contain full client detail. I need to import all those data to my SQL Server database into the Client table. But if I import manually one by one it'll take a lot of times to complete importing. I also try EMS Data Import for SQL Server but it not better, still importing one by one spreadsheet and might take more time.
Can anyone suggest me some other ways to import all those datas fast and reliable, or should I create a tool for my own purpose? Pls help, in urgent!
Thanks.

Think as you're someone else, act as the way you are!

Peso · August 1st, 2006, 01:10 AM

Read about OPENROWSET function.

The basic syntax is this

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;HDR=No;IMEX=0;Database=\\seludt2135\c$\documen ts and settings\selupln\desktop\book2.xls', 'select * from [Sheet1$a1:q50]')
--where f1 <> 'a'

If you get unexpected erros, try this for extended error messages
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=\\seludt2135\c$\documents and settings\selupln\desktop\book2.xls',
'SELECT * FROM [Sheet1$]')

ninel · August 8th, 2006, 02:10 PM

I am trying to do the same thing. I have a csv file that contains only one column (phone number). There are no headings in the file so the first phone number appears in A1.

I am doing this on my local machine and I got it working with the following code with one exception:

Code:

Private Function ImportLeadFile(ByVal projectfile As String, ByVal sLeadFile As String, ByVal DATABASE As String) As Boolean
        Dim objConn As nsSqlClient.SqlConnection
        Dim ds As New DataSet
        Dim m_strConnection As String = "server=NINEL-D246655F1;Initial Catalog=TimeControl;user id=timeuser;password=timeuser;"

        objConn = New nsSqlClient.SqlConnection
        objConn.ConnectionString = m_strConnection
        objConn.Open()

        ' Make sure the .CSV file exists:
        If File.Exists(sLeadFile) Then
            Try
                ' ------ Load the data from the .CSV file: ----------
                Dim strSQL As String
                strSQL = "Select * " & _
                       " INTO " & DATABASE & ".dbo.[List_staging] " & _
                      "FROM  OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)}; DEFAULTDIR=C:\VoicenetSQL\project\tampa\Politic\" & projectfile & "; Extensions=CSV; HDR=No;','SELECT * FROM at1008.csv') "

                 Dim objCommand As nsSqlClient.SqlCommand
                objCommand = New nsSqlClient.SqlCommand(strSQL, objConn)

                objCommand.CommandText = strSQL
                objCommand.ExecuteNonQuery()
                objConn.Close()
            Catch ex As Exception
                sResultText = sResultText & "<BR>" & ex.Message
            End Try
        End If
End Function

When the file gets imported into a table the first phone number record is created as a column name. How can I get around this?