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
Solved

Transfer data from .csv file into SQL table via vb.net

Posted on 2011-09-09
41
491 Views
Last Modified: 2012-05-12
I need to convert an MS Access program to VB.NET and move the back end to SQL Server.  The current program reads a .csv file and via docmd.transfertext, pull the data from the csv file into the table.  How would I do that in VB.Net?
0
Comment
Question by:kshumway
  • 26
  • 10
  • 5
41 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 36513401
Try this, I can help if it doesn't quite do what you need.  I think you'll be pleased with this solution however.

http://www.daniweb.com/software-development/vbnet/threads/52051
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36513432
0
 

Author Comment

by:kshumway
ID: 36513638
sl8rz,
 
I'm gettng an error on MSDASQL, I believe because its for an OLEDB.  The data is being transferred into a SQL table.  How would I change the line below if it's being read into a SQL Table.

"Select * " & _
                       " INTO HospitalInvoice.dbo.[Hospital Billing Data SQL] " & _
                      "FROM  OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)}; DEFAULTDIR=\\Kfs\Finance\Hospital Invoices\; Extensions=txt; HDR=No;','SELECT * FROM CGHCharges.txt') "

Thank you so much for your help!
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
LVL 15

Expert Comment

by:David L. Hansen
ID: 36513814
Is there a Schema.ini file in your csv's directory?  If so, open it and change the 'ColNameHeader=True' line to 'ColNameHeader=False'.
0
 

Author Comment

by:kshumway
ID: 36514214
There is no schema.ini file.
0
 

Author Comment

by:kshumway
ID: 36514376
Attached is the error message I am getting.  Should I have a Schema.ini file?  Your help is greatly appreciated.

 Error.doc
0
 

Author Comment

by:kshumway
ID: 36514669
CodeCruiser - can you help?
0
 

Author Comment

by:kshumway
ID: 36515482
I am new to this.  My understanding after some research is that the Schema.ini file is similar to the specification in Access.  Do you have a sample Schema.ini file so I can see the format and then I can create the file in the directory where the .csv file is located?  Thank you!
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36516272
0
 

Author Comment

by:kshumway
ID: 36517706
CodeCruiser,

For the Bulk Import, I would create a stored procedure in my SQL Database, correct?  How would I call that procedure from VB.Net?  This is a windows application.  Thank you so much for your help.
0
 

Author Comment

by:kshumway
ID: 36520408
sl8rz

Is the DefaultDir clause the directory (folder) where the text file is located?  Thank you for your help!
0
 

Author Comment

by:kshumway
ID: 36522369
CodeCruiser,

I read a little on the bulk insert and it sounds like what I want. as it is very quick.  I have a table already created and I tried to create a stored procedure to do the import.  I can't get that to work.  I need to have a process that can be run from a windows application, which either drops and then creates the table and imports the data or clears the existing table and imports the data.  Is a store procedure the right way to go?  Can you call a script from VB.NET?  Could you possibly help me with the code?  I would truly appreciate your help.  This is an important project and I'm sinking!

Thank you.
Kathy
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 36522925
Still there?  Sorry for dropping off like that...big weekend.  I'll keep looking into this for you.  I'm sure we'll get it working for you; it is a commonly encountered request.  Please, let me know where you are at this point.
0
 

Author Comment

by:kshumway
ID: 36522993
I've attached the error I was getting in Error.doc.  Below is my code.  If I understanding what I've read correctly, it should create a Schema.INI file in the directory where the data file exists.  If that's correct, it's not happening.  Any help would be greatly appreciated!


If File.Exists(sHospDataFile) Then
            Try
                conn.Open()
                ' ------ Load the data from the .CSV file: ----------
                Dim strSQL As String
                strSQL = "Select * " & _
                       " INTO HospitalInvoice.dbo.[Hospital Billing Data SQL] " & _
                      "FROM  OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)}; DEFAULTDIR=C:\Data\; Extensions=csv; HDR=No;','SELECT * FROM HospitalInvoicesData.csv') "
                              Dim objCommand As SqlClient.SqlCommand
                objCommand = New SqlClient.SqlCommand(strSQL, conn)

                objCommand.CommandText = strSQL
                objCommand.ExecuteNonQuery()
                conn.Close()
            Catch ex As Exception
                sResultText = sResultText & "<BR>" & ex.Message
                MsgBox(sResultText)
            End Try
        End If

Open in new window

0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 36523194
What is the error and on which line does it occur?
0
 

Author Comment

by:kshumway
ID: 36523382
I'll attach it again. Error.doc
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 36523430
So your HospitalInvoicesData.csv file does exist in C:\Data\  ?
0
 

Author Comment

by:kshumway
ID: 36523748
I copied it to c:\Data\ to see if the problem was that I was trying to access it off the server -  \\cimadhoc\Data Sources\hospitalinvoicesdata.csv.  I adjusted the location in the code and I get the same error no matter where the file is located.  Attached is the code if the file is on the server.
If File.Exists(sHospDataFile) Then
            Try
                conn.Open()
                ' ------ Load the data from the .CSV file: ----------
                Dim strSQL As String
                strSQL = "Select * " & _
                       " INTO HospitalInvoice.dbo.[Hospital Billing Data SQL] " & _
                      "FROM  OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)}; DEFAULTDIR=\\cimadhoc\Data Sources\hospitalinvoicesdata.csv; Extensions=csv; HDR=No;','SELECT * FROM HospitalInvoicesData.csv') "
                Dim objCommand As SqlClient.SqlCommand
                objCommand = New SqlClient.SqlCommand(strSQL, conn)

                objCommand.CommandText = strSQL
                objCommand.ExecuteNonQuery()
                conn.Close()
            Catch ex As Exception
                sResultText = sResultText & "<BR>" & ex.Message
                MsgBox(sResultText)
            End Try
        End If

Open in new window

0
 

Author Comment

by:kshumway
ID: 36523884
s18rz

I also tried the bulk insert option that codecruiser suggested.  I'm getting an error that the csv file cannot be accessed.  Could the errors I am getting with the option you suggested be related to access of the data file?  Our network administrator is looking into the issue and we've tried a number of things with no luck.  Any ideas?  Attached is that error Error2.doc
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 36523948
Using a stored procedure is a more robust solution, so let's work that option for now.

The error is saying that the csv file can not be opened in that location; is it there in that folder?  Also, let's see the script for the stored procedure.
0
 

Author Comment

by:kshumway
ID: 36523968
The file is there and I can access it.  Attached is the stored procedure script.  I truly appreciate your help!
ALTER PROCEDURE dbo.InsertHospitalData
as
begin
BULK INSERT [Hospital Billing Data SQL]
FROM '\\Cimadhoc\Data Sources\HospitalInvoicesData.csv'
WITH 
	(
	FIELDTERMINATOR = ',',
	ROWTERMINATOR = '\n'
	)
END

Open in new window

0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 250 total points
ID: 36524698
Sorry about the delay in my reply. As already mentioned, stored procedure would be a preferred choice though you can execute scripts direct from your program as well.

You isolate the problem, try using the csv from the C drive.
0
 

Author Comment

by:kshumway
ID: 36524900
I get the same error when I try to run the stored procedure against the csv file inmy c:\Data\HospitalInvoicesData.csv.  But if I  move the file to my c:\ drive (c:\HospitalInvoicesData.csv) the error message states the file does not exist...  But it's there.  I am very confused.

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36524938
I would think its UAC. Try another drive (d:, e: etc) if you have one.
0
 

Author Comment

by:kshumway
ID: 36524949
The only local drive I have is the C:\ drive.
0
 

Author Comment

by:kshumway
ID: 36525115
I provided some mis-information.  The error message was cut off and I assumed it was the same as the drror message when the datafile was on the cimadhoc server.  If the data file is in the C:\Data\ folder the error message is:

Could not bulk insert because file 'C:\Data\HospitalInvoicesData.csv' could not be opened. Operating system error code 3(The system cannot find the path specified.).

It appears that my project folder for Visual Studiio 2008 is pointing to my MyDocuments folder which is on a server, no my local workstation.  Not sure if that has any affect on SQL Server finding my C:\ drive.  I'm grasping here, but wanted to give you all the information that I have about my configuration.
0
 

Author Comment

by:kshumway
ID: 36525160
And as one last ditch effort, I copied the csv file to My Doucments folder (\\kfs\users\l92241) and I got the same error message I got when it's located on the Cimadhoc server.  
0
 
LVL 15

Assisted Solution

by:David L. Hansen
David L. Hansen earned 250 total points
ID: 36525342
Let's try this:

Find out what the path is for your sql server's project folder.  Put the csv file there and change the script to look for it in that folder.  We know that sql server CAN access it's own project folder, so try that just as a test.
0
 

Author Comment

by:kshumway
ID: 36525705
Sorry for the delay - had to drive home.  

Ok - SQL Server is installed on the server.  I found a project folder under \Microsoft SQL Server\MSSQL\FTData \SQLServer \ Projects.  Is that the folder?  I'm going to copy it there and see what happens.  If that's not the folder you are referring to, please let me know.  Thank you!
0
 

Author Comment

by:kshumway
ID: 36525745
OK - this may be a stupid questions, but how do I reference the c:\ drive on another server is it's not shared out?  

Server = \\KSQLDB
File Path = C:\Program Files\Microsoft SQLServer\MSSQL\FTDATA\SQLServer\Projects

0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 36525924
Referencing any folder residing on another computer requires that the other folder be shared in some way.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36526128
But if you have access to it anyway then you can use

\\KSQLDB\c$\...


>Operating system error code 3
This error usually means that the file is there but SQL server is unable to access it.
0
 

Author Comment

by:kshumway
ID: 36526700
I used the following path\filename:
'\\KSQLDB\c$\Program Files\Microsoft SQLServer\MSSQL\FTDATA\SQLServer\Projects\HospitalInvoicesData.csv'

and got the following error message:

Could not bulk insert because file '\\KSQLDB\c$\Program Files\Microsoft SQLServer\MSSQL\FTDATA\SQLServer\Projects\HospitalInvoicesData.csv' could not be opened. Operating system error code 3(The system cannot find the path specified.).
0
 

Author Comment

by:kshumway
ID: 36528338
I sent an email to our network administrator asking if he could tempoarily create a share to the project folder so we could test.  I''m in meetings this morning but will check back to see if he was able to create the share.

BTW, on KSQLDB, we are running SQL Server 2000.  It's slated to be upgraded sometime this Fall.

Thank you again for all your hellp.
0
 

Author Comment

by:kshumway
ID: 36528913
Dave created the share and I tested it.  Worked like a charm.  Worse case is that I have to copy the file to that directory...  Can I easily do that in my code?  I'm off to meetings, but will be back later.  
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 36529767
Good to hear you are making progress.

Yes, moving a file in your VB code is very easy.  It's in the IO namespace, here is an example:
System.IO.File.Move('C:\myFolder\myFile.csv', '\\myNetworkPlace\myFileCopy.csv')

Open in new window

0
 

Author Comment

by:kshumway
ID: 36529801
First of all, I'd like to thank both of you for your help.  This was definitely a life saver.  I have a meeting tomorrow that I can now give a positive report on.  I found the code to copy the file from the \\cimadhoc server to the share on \\ksqldb\projects.  Along with the bulk copy I have the data in the e table.  It's very fast!

Since you both helped, I'd like to split the the points if that's okay.  Let me know.
0
 

Author Comment

by:kshumway
ID: 36529814
We must have been writing at the same time.  That's exactly what I used.  Thank you!!!
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 36529984
Welcome...split the points however you feel is best.  Glad we could help you slay the dragon :)
0
 

Author Comment

by:kshumway
ID: 36529990
Thank you!!!!!!
0
 

Author Closing Comment

by:kshumway
ID: 36530095
Thank you so much for all your help.  This was a lifesaver!
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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
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…

828 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