Solved

Populate SQL Server 2005 Express Table from CSV File

Posted on 2007-11-15
7
1,856 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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

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 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.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

688 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