Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

bcp from vb.net app to sql

Posted on 2006-05-04
8
Medium Priority
?
774 Views
Last Modified: 2012-05-05
I want to take a csv file and import it into a table called upload in sql.  How can I do this from a vb.net app please.

thank you
0
Comment
Question by:running32
  • 4
  • 3
8 Comments
 
LVL 25

Expert Comment

by:SStory
ID: 16607190
Well are you wanting to shell to BCP?
Or are you wanting to just read the CSV, and map to SQL Insert statements, called over and over again?

0
 

Author Comment

by:running32
ID: 16607245
What I would like to do is take a csv file and import the contents into a sql table called upload from a vb application.

I would need to delete any exiting data in the upload table and then populate with the new data from the file.

Thanks
0
 
LVL 7

Expert Comment

by:wnross
ID: 16608482
Two ways

(Easier)
Create a DTS Package to do the job and either store it on the database server or embed it in your application.  Microsoft supplies a CSV ODBC driver
so you can open the CSV as simply another table.  Very easy.  Then in VB.Net just tell SQL-Server/MSDE to run the job

(Harder)
Again, pointing out CSV files can be opened as Database objects, use ADO.NET to open the CSV file, then loop through the CSV table
taking each row and using it to fill a prepared statement.  Any fixes can be done as each row is read.

Cheers
-Bill
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:running32
ID: 16608498
thanks, the user needs to upload the file so I have a variable file name.   Here is what I have so far

****************vb.net *******************
Dim ExcelObj = New Excel.Application
        Dim currentfilename
        Me.OpenFileDialog1.FileName = "*.csv"
        If (Me.OpenFileDialog1.ShowDialog() = DialogResult.OK) Then
            currentfilename = OpenFileDialog1.FileName
            'pass currentfilename to stored procedure and upload csv file to upload table in sql
            Dim objCmd As New SqlCommand
            Dim objDR As SqlDataReader
            Connection1.Open()
            'Upload current file name to tblUpload on biz-talk healthdistrict db.
            objCmd.Connection = Connection1
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.CommandText = "usp_Update_Emp11"
            objCmd.Parameters.Add(New SqlParameter("@FILE_NAME", SqlDbType.Text, 20))
            objCmd.Parameters("@FILE_NAME").Value = currentfilename
            objCmd.ExecuteNonQuery()
            Console.WriteLine("Bulk inserted")

            Connection1.Open()


***************sql*********************
CREATE PROCEDURE usp_Update_Emp11

   @FILE_NAME AS VARCHAR(70)
AS
Delete from upload
EXEC('BULK INSERT upload FROM '''+@FILE_NAME+''' WITH  (FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')')
GO

usp_update_emp11
0
 
LVL 7

Accepted Solution

by:
wnross earned 2000 total points
ID: 16608820
Well, thats an interesting way assuming you trust your data.

1) Have you tested your bulk insert to see if it works
2) Have you tested your stored procedure to see if it works
3) What errors are you getting in your code?

Cheers,
-Bill
0
 

Author Comment

by:running32
ID: 16639376
It will not let me upload because the data is stored as below and the lastname, firstname are together.  Therefore I get an error on the dob.  thank you


Cxxxxx, Lxxx     11-02-48     58     S1111-02     1-Feb-06     d22     mfc     203.3     12455     124-42-1245     f     401 Taaddd Dr     xxxxx Cxxxxx     xx     8xxxx     XX
0
 

Author Comment

by:running32
ID: 16639434
I can upload it if ok now if i create a fname lastname but I get " around the name" .  How can I avoid this.

Thanks
0
 
LVL 7

Expert Comment

by:wnross
ID: 16686597
Can you paste a sample row?

Also, that looks tab separated, not comma, I would look at either cleaning up the "tsv" file first or transform it during the load.  One way would be to make the first column
fullname, then use a stored proc or query update to split the first and last names.

Cheers,
-Bill
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Introduction to Processes

581 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