Solved

import table from excel or CSV file

Posted on 2011-09-12
8
338 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
 

Author Comment

by:al4629740
ID: 36540788
I have Configuration Manager and not Enterprise Manager.  How do I do it with the first?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now