?
Solved

Populate SQL Server 2005 Express Table from CSV File

Posted on 2007-11-15
7
Medium Priority
?
1,858 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
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…

752 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