Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Populate SQL Server 2005 Express Table from CSV File

Posted on 2007-11-15
7
Medium Priority
?
1,859 Views
Last Modified: 2013-11-26
Hello.
Each day, I'd like to this programmatically using VB.NET (Visual Studio 2005)
1)  Delete all rows in table Table1 of a SQL Server 2005 Express DB (no DTS available)
2) Re-populate with about 30,000 rows from a flat file (CSV or tab-delimited, 10 columns)

I can easily wipe the table using "DELETE FROM Table1 .."
But I'm having trouble re-populating it easily. Do I have to parse the file and get 30,000 x 10 = 300,000 values the hard way, or is there an easier method in VB.NET or SQL? Can I have an example please?
0
Comment
Question by:LarryZ
[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
  • 4
  • 2
7 Comments
 
LVL 21

Expert Comment

by:Craig Wagner
ID: 20289091
I had to do something similar using an Excel spreadsheet.

I'd look at using the OLEDB Text driver to read the file and then use the SqlBulkCopy object to blast it into the table.

You can find examples of the text driver connection strings here:

http://www.connectionstrings.com/?carrier=textfile

If that doesn't work for you, you could always parse the text file and still use the SqlBulkCopy. Here's an example of doing that.

http://weblogs.sqlteam.com/mladenp/archive/2006/08/26/11368.aspx
0
 

Author Comment

by:LarryZ
ID: 20289343
Regarding the first method using an OLEDB text driver:
How would I insert the data into Table1? Can you help with the INSERT statement and command objects please?
---------------------------------------
Imports System.Data.OleDb

Private Sub GetData()

'   Connection string
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\CSVfile.txt;Extended   Properties=text;HDR=Yes;FMT=Delimited','"
Dim conn As New OleDbConnection(strConn)
Dim strSQLInsert As String = "Insert INTO Table1 ????????"
Dim cmd As New OleDbCommand(strSQLInsert, conn)
Try
'   Open the connection
            conn.Open()
 '   Execute the command object
?????

Catch ex as Exception
Finally
End Try

End Sub
0
 

Author Comment

by:LarryZ
ID: 20289393
Seems to me I have no connection to Table1 in the code above
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 total points
ID: 20289531
Here is a simple way:

INSERT INTO YourTable
      (<column_list_here>)
SELECT
      <column_list_here>
FROM
     OPENROWSET('MSDASQL',--provider name (ODBC)
        'Driver={Microsoft Text Driver (*.txt; *.csv)};
          DEFAULTDIR=C:\YourFolder\;Extensions=CSV;',--data source
        'SELECT * FROM YourFile.CSV')

make sure that C:\YourFolder\YourFile.CSV and is on the SQL server machine.

If you're not set for ad-hoc queries on the SQL server run:

sp_configure 'show advanced options',1
RECONFIGURE WITH override  
go
sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE WITH override
go


0
 

Author Closing Comment

by:LarryZ
ID: 31409320
I created a new query in MS SQL Server Management Studio Express, then used Zberteoc's concept as a guide. Works great - thanks! I'm going to try to write a stored procedure next, and then just call that SP from VB.Net.  I may post another question later today on this - I've only written a couple of stored procedures before. Thanks.
0
 

Author Comment

by:LarryZ
ID: 20291085
Zberteoc's solution worked well.  I first tweaked it a bit using a new query in MS SQL Server Management Studio Express, then was able to turn it into a stored procedure. All thats left to do is call the procedure with a VB.Net console application.  Thanks Zberteoc !!
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 20291192
You don't need to build anything special to run that script. You can simply use the existing sqlcmd.exe utility like here:

http://msdn2.microsoft.com/en-us/library/ms170572.aspx
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

618 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