Solved

import table from excel or CSV file

Posted on 2011-09-12
8
350 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:al4629740
8 Comments
 
LVL 13

Expert Comment

by:khairil
ID: 36524192
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
 
LVL 17

Expert Comment

by:nepaluz
ID: 36524246
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36526721
>>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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 

Author Comment

by:al4629740
ID: 36540788
I have Configuration Manager and not Enterprise Manager.  How do I do it with the first?
0
 

Author Comment

by:al4629740
ID: 36540796
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
 
LVL 13

Expert Comment

by:khairil
ID: 36540967
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
 
LVL 13

Accepted Solution

by:
khairil earned 500 total points
ID: 36541181
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
 
LVL 13

Expert Comment

by:khairil
ID: 36541184
change accrodingly to suite your needs.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question