Using CSV Files With ADO.NET

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

I am trying to use Micrsoft's text driver to read and create csv files from
data in SQL Server. In the test code below I cannot get the parameritized
version to work. It works fine if I do not include any parameters. With
the code below I get the error "ERROR [07002] [Microsoft][ODBC Text Driver]
Too few parameters. Expected 1."

Help.

Jay
 
Dim cnx As OdbcConnection
Dim cn As SqlConnection
Dim dt As New DataTable
Dim da As New OdbcDataAdapter
Dim cmd As OdbcCommand
Dim pTest As OdbcParameter


Dim cnxStr As String
Dim CSVFolder As String
Dim CSVFileName As String

CSVFolder = "d:\CSVFolder"
CSVFileName = "test.csv"

cnxStr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + CSVFolder +
";"
cnx = New OdbcConnection(cnxStr)

cmd = New OdbcCommand("Select * from [test.csv] where [Last Name] like
@LastName", cnx)
cmd.CommandType = CommandType.Text
pTest = New OdbcParameter("@LastName", OdbcType.VarChar, 50)
pTest.Value = "lin%"
cmd.Parameters.Add(pTest)
da.SelectCommand = cmd
cnx.Open()
da.Fill(dt)

dgTest.DataSource = dt
dgTest.DataBind()
 
¤ Dim cnx As OdbcConnection
¤ Dim cn As SqlConnection
¤ Dim dt As New DataTable
¤ Dim da As New OdbcDataAdapter
¤ Dim cmd As OdbcCommand
¤ Dim pTest As OdbcParameter
¤
¤
¤ Dim cnxStr As String
¤ Dim CSVFolder As String
¤ Dim CSVFileName As String
¤
¤ CSVFolder = "d:\CSVFolder"
¤ CSVFileName = "test.csv"
¤
¤ cnxStr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + CSVFolder +
¤ ";"
¤ cnx = New OdbcConnection(cnxStr)
¤
¤ cmd = New OdbcCommand("Select * from [test.csv] where [Last Name] like
¤ @LastName", cnx)
¤ cmd.CommandType = CommandType.Text
¤ pTest = New OdbcParameter("@LastName", OdbcType.VarChar, 50)
¤ pTest.Value = "lin%"
¤ cmd.Parameters.Add(pTest)
¤ da.SelectCommand = cmd
¤ cnx.Open()
¤ da.Fill(dt)
¤
¤ dgTest.DataSource = dt
¤ dgTest.DataBind()

Does the first line of your Text file contain column name headers or are you using a schema.ini
file?


Paul
~~~~
Microsoft MVP (Visual Basic)
 
The first line contains the column names.

Jay

Paul Clement said:
¤ Dim cnx As OdbcConnection
¤ Dim cn As SqlConnection
¤ Dim dt As New DataTable
¤ Dim da As New OdbcDataAdapter
¤ Dim cmd As OdbcCommand
¤ Dim pTest As OdbcParameter
¤
¤
¤ Dim cnxStr As String
¤ Dim CSVFolder As String
¤ Dim CSVFileName As String
¤
¤ CSVFolder = "d:\CSVFolder"
¤ CSVFileName = "test.csv"
¤
¤ cnxStr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + CSVFolder +
¤ ";"
¤ cnx = New OdbcConnection(cnxStr)
¤
¤ cmd = New OdbcCommand("Select * from [test.csv] where [Last Name] like
¤ @LastName", cnx)
¤ cmd.CommandType = CommandType.Text
¤ pTest = New OdbcParameter("@LastName", OdbcType.VarChar, 50)
¤ pTest.Value = "lin%"
¤ cmd.Parameters.Add(pTest)
¤ da.SelectCommand = cmd
¤ cnx.Open()
¤ da.Fill(dt)
¤
¤ dgTest.DataSource = dt
¤ dgTest.DataBind()

Does the first line of your Text file contain column name headers or are you using a schema.ini
file?


Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...
Click to expand...
 
¤ The first line contains the column names.
¤

Try using SELECT * to see if you generate the correct column names. I'm thinking that either it
isn't picking up the header or there is no [Last Name] column.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Hi
I need to read 9 files from a sql server table and write these fields in a
text file, (NOT CVS or Excel). How can i do it, please !!?
 
CSV is just a text file with the CSV extension so that any application that can import CSV or TEXT files will know how to treat
it. If you don't need the Quotation marks and commas from the exported CSV file, you can filter those out in code.
james

Gelo said:
Hi
I need to read 9 files from a sql server table and write these fields in a
text file, (NOT CVS or Excel). How can i do it, please !!?

Paul Clement said:
¤ The first line contains the column names.
¤

Try using SELECT * to see if you generate the correct column names. I'm thinking that either it
isn't picking up the header or there is no [Last Name] column.


Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...
Click to expand...
 
Please, I need a complete example.

james said:
CSV is just a text file with the CSV extension so that any application that can import CSV or TEXT files will know how to treat
it. If you don't need the Quotation marks and commas from the exported CSV file, you can filter those out in code.
james

Gelo said:
Hi
I need to read 9 files from a sql server table and write these fields in a
text file, (NOT CVS or Excel). How can i do it, please !!?

Paul Clement said:
¤ The first line contains the column names.
¤

Try using SELECT * to see if you generate the correct column names. I'm thinking that either it
isn't picking up the header or there is no [Last Name] column.


Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...
Click to expand...
Click to expand...
 
My fault. You have hijacked this thread and I didn't notice it. You say you need to read 9 FILES from a
SQL Server TABLE and write these FIELDS to a TEXT file. That statement is confusing.
Figure out exactly what you need to do and start a NEW THREAD and maybe someone can help you.
You need to be more clear on what you want to do. Are you trying to read 9 DIFFERENT SQL TABLE'S data and export the DATA to a
TEXT file? If so, there is a ton of examples all over the net. Use Google and search and you will see there is a lot of
information already available for what you want to do.
It would help to know what language you are programming in too. That way, someone can give you some ideas in code to help you
get started. But, no one is going to give you a complete example. Or even, any kind of example if they cannot understand your
question clearly.
james

Gelo said:
Please, I need a complete example.

james said:
CSV is just a text file with the CSV extension so that any application that can import CSV or TEXT files will know how to
treat
it. If you don't need the Quotation marks and commas from the exported CSV file, you can filter those out in code.
james

Gelo said:
Hi
I need to read 9 files from a sql server table and write these fields in a
text file, (NOT CVS or Excel). How can i do it, please !!?

:


¤ The first line contains the column names.
¤

Try using SELECT * to see if you generate the correct column names. I'm thinking that either it
isn't picking up the header or there is no [Last Name] column.


Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...
Click to expand...
Click to expand...
 
=?Utf-8?B?R2Vsbw==?= said:
Please, I need a complete example.
Click to expand...

Very few people have time to write full code for every user. At best you
should expect poniters and do the coding yourself.
 
¤ Hi
¤ I need to read 9 files from a sql server table and write these fields in a
¤ text file, (NOT CVS or Excel). How can i do it, please !!?
¤

Below is an example of how to export from a SQL Server table to a Text file:

Function ExportSQLServerToText() As Boolean

Dim TextConnection As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "E:\My
Documents\TextFiles" & ";" & _
"Extended
Properties=""Text;HDR=NO;""")

TextConnection.Open()

'New table
Dim TextCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Orders#txt] FROM
[Orders] IN '' [ODBC;Driver={SQL
Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", TextConnection)

TextCommand.ExecuteNonQuery()
TextConnection.Close()

End Function


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top