Solved

Populate SQL Server 2005 Express Table from CSV File

Posted on 2007-11-15
7
1,855 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 27

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 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
Need to find substring in SQL 5 52
T-SQL and CLR parameter strings 9 60
What is needed to become a DBA? 7 46
ASP.NET VB code for check and insert in a table 4 39
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

738 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