How to Import CSV file in to SQL table

Any body know How to Import CSV file in to SQL table using asp.netYou can read the file into a DataSet using ADO.NET. See this article for details:
http://www.devarticles.com/c/a/ASP.NET/Reading-a-Delimited-File-Using-ASP.Net-and-VB.Net/2/

Then use that DataSet to update the SQL Server table using a data adapter.|||The old style bcp still works at SQL 2000. This is command for Data Im/Export since SQL 4
At DOS prompt, type bcp to see all help information|||alright i read through the article and it helps alot
the only question that i have is
some csv's have commas in certain fields even if it isn't a break but to signify it, it uses "" around the statement such as
fname,lname,notes,bob,stevens,"he likes things, NOW"

and it would return
fname lname notes
bob stevens he likes things, NOW

here is my code

 Private Sub btnImportCSV_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImportCSV.Click
 Dim FileToRead As String
 FileToRead = Server.MapPath("import.csv")
 Dim FileStream As StreamReader
 filestream = File.OpenText(FileToRead)
 Dim ReadContents As String
 ReadContents = FileStream.ReadToEnd()
 Dim TextDelimiter As String
 TextDelimiter = ","
 Dim SplitOut = Split(ReadContents, TextDelimiter)
 Dim i As Integer
 For i = 0 To UBound(SplitOut)
 btnImportCSV.Text = SplitOut(i)
 Next
 End Sub

it works and it works nice but my only problem is that each field that contains commas is split
does anyone know how to fix this or make the delimiter work better
thanks for your help|||ALL this example is to display CSV on webform but I want to save CSV in to Database , can anybody help me !!!!!|||The problem is the text file uses both commas and double-quotes " as delimiters. If it did one or the other it would not be a problem. Is there any way to change the format of the delimited file?|||kalpesh2000
to input this into a db
you already have the data
all you need to do is know that this array has a certian amount of fields

such as, this is your table
fname, lname, address, zip,
Bob, James, 123 Rd., 43234,
doug, Brown, 321 St., 83943

This would be your data
SplitOut(0)="fname"
SplitOut(1)="lname"
SplitOut(2)="address"
SplitOut(3)="zip"
SplitOut(4)="Bob"
SplitOut(5)="James"
SplitOut(6)="123 Rd."
SplitOut(7)="43234"
SplitOut(8)="doug"
SplitOut(9)="Brown"
SplitOut(10)="321 St."
SplitOut(11)="83943"

then you just need to make an SQLcommand that inserts into the database 1 record
then you will want to skip the first four because they are the headers
so you will say (i dont remember the syntax for the increment for the for statement so look that up)

for x=4 to UBound(SplitOut) increment=4
sqlcommand.parameters("@.firstfield").value=splitout(x+0)
sqlcommand.parameters("@.SecondField").value=splitout(x+1)
sqlcommand.parameters("@.ThirdField").value=splitout(x+2)
sqlcommand.parameters("@.fourthField").value=splitout(x+3)

connection.open
sqlcommand.executenonquery
connection.close
next x

this code is all untested just a representation of how it should work|||Regarding blackdisk's example:

It doesn't work because it assumes that commas are the delimiters, when according to his example it is a mix of commas *and* double-quotes. That's the sticking point.

To the original question:
Is there any way you can alter the flat file? Can you re-export it from whatever program, for example?|||if my data was all Strictly comma delimited it would function correctly as my example shows it should work
probably not very efficiently but would work

i did not know what control i had over the data and i will be able to define the fields and NO Comma restrictions so that will be fixed

thank you all for your help|||the correct for statement is
for x=4 to UBound(SplitOut) Step 4|||what is the syntax for a the enter command

(for a delimiter)|||If you mean a line break, you can use either "\n" or Environment.Newline|||Hi Darrell

In one of your responses you mentioned using a dataset to import the contents of a CSV file into a SQL database. I have managed to use a dataset to read the contents of the file and produce the datatable, but I am now stuck as how to use this datatable/dataset to transfer this information into a SQL database.

Could you give any further guidance?

Many Thanks|||Pass the DataSet to a properly configured data adapter, then call the data adapter's Update method.

For help on Data Adapters, check out the following MSDN docs:

Creating Data Adapters Using Server Explorer
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskCreatingDataAdaptersUsingServerExplorer.asp

Creating Data Adapters Using a Wizard
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskCreatingDataAdaptersUsingWizard.asp

Creating and Configuring Data Adapters Manually
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskCreatingConfiguringDataAdaptersManually.asp