Solved

Populate SQL Server 2005 Express Table from CSV File

Posted on 2007-11-15
7
1,847 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
  • 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 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 26

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 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

23 Experts available now in Live!

Get 1:1 Help Now