Solved

import table from excel or CSV file

Posted on 2011-09-12
8
356 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

724 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