import table from excel or CSV file

I would like to import a table created in Excel to create the same table in SQL Server 2008

How can I do this?  It was simple in Access
al4629740Asked:
Who is Participating?
 
khairilConnect With a Mentor Commented:
Hi,

This solution might works, from StackOverflow
(http://stackoverflow.com/questions/2566761/excel-vba-sql-import)

Save this as import.vbs

Dim Cn As ADODB.Connection 
Dim ServerName As String 
Dim DatabaseName As String 
Dim TableName As String 
Dim UserID As String 
Dim Password As String 
Dim rs As ADODB.Recordset 
Dim RowCounter As Long 
Dim ColCounter As Integer 
Dim NoOfFields As Integer 
Dim StartRow As Long 
Dim EndRow As Long 
Dim shtSheetToWork As Worksheet 
Set shtSheetToWork = ActiveWorkbook.Worksheets("Sheet1") 
Set rs = New ADODB.Recordset 
 
 
ServerName = "WIN764X\sqlexpress" ' Enter your server name here 
DatabaseName = "two28it" ' Enter your  database name here 
TableName = "COS" ' Enter your Table name here 
UserID = "" ' Enter your user ID here 
 ' (Leave ID and Password blank if using windows Authentification") 
Password = "" ' Enter your password here 
NoOfFields = 7 ' Enter number of fields to update (eg. columns in your worksheet) 
StartRow = 2 ' Enter row in sheet to start reading  records 
EndRow = shtSheetToWork.Cells(Rows.Count, 1).End(xlUp).Row ' Enter row of last record in sheet 
 
Set Cn = New ADODB.Connection 
Cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & _ 
";Uid=" & UserID & ";Pwd=" & Password & ";" 
 
rs.Open TableName, Cn, adOpenKeyset, adLockOptimistic 
 
For RowCounter = StartRow To EndRow 
    rs.AddNew 
    For ColCounter = 1 To NoOfFields 
        rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter) 
    Next ColCounter 
Next RowCounter 
rs.UpdateBatch 
 
 ' Tidy up 
rs.Close 
Set rs = Nothing 
Cn.Close 
Set Cn = Nothing

Open in new window

0
 
khairilCommented:
Hi,

Try this:
1. Open MSSQL Enteprise Manager
2. Expend the database tree, and select the database that you want to import you table
3. Right on that database and select from menu Task -> Import Data...
4. On Import dialog, On the Choose a Data Source, select Microsoft Excel as data source and select the excel file that you like to import. (Specify version if needed). Click Next to continue.
5. On Destination, set Destination as SQL Native Client... and server name as (local) - OR any server that you wish to import to. Select authentication whether it is Windows or SQL Server. Make sure the database is one that you wish to import to. Click Next to continue.
6. On Specify Query, select option of Copy data from one or more tables or views. Click Next to continue.
7. On Select Source Tables and View. Select sheet that contains the data. On destination you can change table that going to be created. You also can define column data type by taking "Edit Mapping..." button. Click Next to continue.
8. On Sava and Run Package. Tick for Run Immediately and press  Next and Finish.

Import wizard will give you import status and any error that occured.
0
 
nepaluzCommented:
From CSVto Datatable, itused to be simple and it still is! Here is an example
Dim MyTable As DataTable = New DataTable
Using sr = New FileIO.TextFieldParser("YourPath") With {.Delimiters = {","}, .HasFieldsEnclosedInQuotes = True, .TextFieldType = FileIO.FieldType.Delimited}
    Dim hRow = sr.ReadFields()
    With MyTable
        .TableName = "MyTableName"
        Array.ForEach(hRow, Sub(x) .Columns.Add(New DataColumn(x) With {.ColumnName = x}))
    End With
    Dim xRow As DataRow
    While Not sr.EndOfData
        hRow = sr.ReadFields()
        If String.IsNullOrEmpty(hRow(0)) Then Continue While
        xRow = MyTable.NewRow()
        xRow.ItemArray = hRow
        MyTable.Rows.Add(xRow)
    End While
End Using

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Anthony PerkinsCommented:
>>How can I do this?<<
It depends on the Edition you are using and how you want to achieve this:  Are you a GUI/Wizard/MS Access type or do you prefer the control offered by writing code.
0
 
al4629740Author Commented:
I have Configuration Manager and not Enterprise Manager.  How do I do it with the first?
0
 
al4629740Author Commented:
I get plenty of errors that just don't work out.

Here are the errors:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'As'.
Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 4145, Level 15, State 1, Line 10
An expression of non-boolean type specified in a context where a condition is expected, near 'hRow'.
Msg 195, Level 15, State 10, Line 11
'hRow' is not a recognized built-in function name.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'xRow'.
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near 'Using'.
Dim MyTable As DataTable = New DataTable
Using sr = New FileIO.TextFieldParser("c:\export.csv") With {.Delimiters = {","}, .HasFieldsEnclosedInQuotes = True, .TextFieldType = FileIO.FieldType.Delimited}
    Dim hRow = sr.ReadFields()
    With MyTable
        .TableName = "MyTableName"
        Array.ForEach(hRow, Sub(x) .Columns.Add(New DataColumn(x) With {.ColumnName = x}))
    End With
    Dim xRow As DataRow
    While Not sr.EndOfData
        hRow = sr.ReadFields()
        If String.IsNullOrEmpty(hRow(0)) Then Continue While
        xRow = MyTable.NewRow()
        xRow.ItemArray = hRow
        MyTable.Rows.Add(xRow)
    End While
End Using

Open in new window

0
 
khairilCommented:
Hi,

If you have SQL server installation media then you just need to install the Management Console only (not the engine). But if you do not have one, then you should download SQL Server Trial edition and install just management console or use Toad trial.

Both have compatibility and ease of use to import and export data between database.
0
 
khairilCommented:
change accrodingly to suite your needs.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.