connection string to text file.
PCH
I used to use..
Provider=MSDASQL.1;DBQ=" & sPath & ";DefaultDir=" & sPath &
";Driver={Microsoft Text Driver (*.txt;
*.csv)};DriverId=27;MaxBufferSize=2048;PageTimeout=5;
But now it gives an oledb error saying that MSDASQL is not supported.
The code im using is:
' Create the connection object by using the preceding connection string.
Dim objConn As New OleDbConnection(sConn)
' Open connection with the database.
objConn.Open()
' The code to follow uses a SQL SELECT command to display the data from the
worksheet.
' Create new OleDbCommand to return data from worksheet.
Dim objCmdSelect As New OleDbCommand(sSQL, objConn)
' Create new OleDbDataAdapter that is used to build a DataSet
' based on the preceding SQL SELECT statement.
Dim objAdapter1 As New OleDbDataAdapter()
' Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect
' Create new DataSet to hold information from the worksheet.
Dim objDataset1 As New DataSet()
' Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "ImportData")
'set the table to the dataset
tblData = objDataset1.Tables("ImportData")
For Each colData In tblData.Columns
stest = colData.Caption
stest = colData.ColumnName
sbColumns.Append("")
Next
' Clean up objects.
objConn.Close()
William (Bill) Vaughn
supported in ADO.NET.
You need to use the ODBC .NET data provider. It can be installed from
www.msdn.microsoft.com (downloads).
And, why are you using this text provider instead of the FileIO function(s)
in VB.NET? It's far faster and a lot less trouble.
--
William (Bill) Vaughn, Author, Trainer, Mentor
Microsoft Pacwest Regional Director
"ADO and ADO.NET Examples and Best Practices for VB Programmers--2nd
Edition" (ISBN: 1-893115-68-2)
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
________________________________
"PCH" <pc...@hotmail.com> wrote in message
news:OU9$daWcCHA.2188@tkmsftngp08...
PCH
First step, i need to return all the column names.
After that, i then need to import all the data into a sql db.
ill look into the system.io - file options..
"William (Bill) Vaughn" <bil...@nwlink.com> wrote in message
news:eZky$iWcCHA.1652@tkmsftngp11...
William (Bill) Vaughn
directly.
Reading delimited files is what VB has been good at (designed for) for over
20 years. Using an OLE DB provider for this is gross overkill. If you don't
use DTS (which will work as-is) or you're being paid by the line, then by
all means use the OLE DB approach. Otherwise check out the FileIO, or
simply, Get, Input # routines.
--
William (Bill) Vaughn, Author, Trainer, Mentor
Microsoft Pacwest Regional Director
"ADO and ADO.NET Examples and Best Practices for VB Programmers--2nd
Edition" (ISBN: 1-893115-68-2)
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
________________________________
"PCH" <pc...@hotmail.com> wrote in message
news:e#LfzxWcCHA.2624@tkmsftngp09...
PCH
outlook, etc.
Was trying to come up with a common approach (oledbdatareader) for opening
the file using different connection strings and sql statements.
i would think the file io approach would choke on non delimited files...
"William (Bill) Vaughn" <bil...@nwlink.com> wrote in message
news:OkjC2fXcCHA.2548@tkmsftngp10...
William (Bill) Vaughn
--
William (Bill) Vaughn, Author, Trainer, Mentor
Microsoft Pacwest Regional Director
"ADO and ADO.NET Examples and Best Practices for VB Programmers--2nd
Edition" (ISBN: 1-893115-68-2)
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
________________________________
"PCH" <pc...@hotmail.com> wrote in message
news:#pyrSqXcCHA.504@tkmsftngp11...
Rob Epstein
Your design goal is admirable. If I understand you correctly, you want to be
able to dynamically parse a given file based on the file type and put the
files contents into a common data store (SQL Server). If this is the case, I
think you're taking a step in the right direction, but let me offer one
suggestion. Back up one abstraction level. Right now you are abstracting
your code so that you can run the same logic but only vary by the connection
string you provide. Let me suggest that you implement each file type parser
as an object that implements some sort of IBaseParser interface. You can
then create a Factory that given a file extension will return the
appropriate IBaseParser derived object. Example:
IBaseParser parser = ParserFactory.GetParser("xls"); //GetParser will
actually return a ExcelParser concrete object
You can then, without having to explicitly know the file type, parse any
file that you are handed as long as the you have implemented an appropriate
parser for that type. I don't know if this makes sense to you or not, but if
you have any further questions feel free to shoot me an email.
--
Hope that helps,
Rob Epstein
Sr. Application Architect
ComputerJobs.com
http://www.computerjobs.com
"Nothing's impossible until proven so."
"PCH" <pc...@hotmail.com> wrote in message
news:#pyrSqXcCHA.504@tkmsftngp11...
Paul Clement
¤ I'm trying to connect to a text file in ado.net
¤
¤ I used to use..
¤
¤ Provider=MSDASQL.1;DBQ=" & sPath & ";DefaultDir=" & sPath &
¤ ";Driver={Microsoft Text Driver (*.txt;
¤ *.csv)};DriverId=27;MaxBufferSize=2048;PageTimeout=5;
¤
I wouldn't use the Text ODBC driver - use the Jet OLEDB driver and Text ISAM instead:
Dim strCon As String
Dim strSql As String
Dim oleCon As New OleDb.OleDbConnection()
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\My Documents\TextFiles;Extended Properties=Text;"
strSql = "Select * from TextFile.csv"
oleCon.ConnectionString = strCon
oleCon.Open()
Dim odaXL As New OleDb.OleDbDataAdapter(strSql, oleCon)
odaXL.SelectCommand.ExecuteNonQuery()
Dim dsXL As New DataSet("TextFiles")
odaXL.Fill(dsXL, "TextFile")
DataGrid1.SetDataBinding(dsXL, "TextFile")
objCon.Close()
Paul ~~~ pcle...@ameritech.net
Microsoft MVP (Visual Basic)
PCH
But what about custom delimiters, such as a pipe | ?
It errors out when i try that type of file.
Thanks
"Paul Clement" <UseAdddressA...@swspectrum.com> wrote in message
news:rl5mquggbcqn0feq5...@4ax.com...
Paul Clement
¤ Thanks for the info Paul. It works great for csv files.
¤
¤ But what about custom delimiters, such as a pipe | ?
¤
¤ It errors out when i try that type of file.
¤
For a custom delimiter such as a pipe you will probably need to create a schema.ini file:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp
The entry will look something like this:
[TextFile.csv]
ColNameHeader=False
CharacterSet=ANSI
Format=Delimited(|)
PCH
oledb does not need that / doesnt use it, because it still errored out.
I think the schema.ini file is only used by odbc??
"Paul Clement" <UseAdddressA...@swspectrum.com> wrote in message
news:i4a0ru4v79bu33851...@4ax.com...
Paul Clement
¤ Right I have done that, and it resides where the file is, but it seems that
¤ oledb does not need that / doesnt use it, because it still errored out.
¤
¤ I think the schema.ini file is only used by odbc??
¤
Here's the code that works for me:
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "D:\My Documents\TextFiles" & ";" & _
"Extended Properties=""Text;HDR=NO;FMT=Delimited"""
Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)
objConn.Open()
Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM Test#csv", objConn)
Dim ds As New DataSet("TextFiles")
da.Fill(ds, "Test")
Dim dt As DataTable
dt = ds.Tables("Test")
DataGrid1.SetDataBinding(ds, "Test")
Dim drCurrentCol As DataColumn
For Each drCurrentCol In dt.Columns
Console.WriteLine(drCurrentCol.ColumnName)
Next
Dim drCurrent As DataRow
For Each drCurrent In dt.Rows
Console.WriteLine(drCurrent(0).ToString)
Console.WriteLine(drCurrent(1).ToString)
Console.WriteLine(drCurrent(2).ToString)
Next
objConn.Close()
File looks like this:
Clement|Paul|Male
Waybill|Fee|Male
Banks|Tyra|Female
Porzikova|Paulina|Female
Schema.ini file has:
[Test.csv]
ColNameHeader=False
CharacterSet=ANSI
Format=Delimited(|)
You might want to post the error you are getting and the code with line causing the error. Maybe a
line or two from your text file might help as well.
PCH
Got this error:
System.Data.OleDb.OleDbException: In the text file specification
'Data2.txt', the Format option is invalid.
"Paul Clement" <UseAdddressA...@swspectrum.com> wrote in message
news:nvl0ruo764scstpon...@4ax.com...
PCH
I've tried several options for the connection string..
The error is so nondescript that I have no idea what the actual problem is.
"PCH" <pc...@hotmail.com> wrote in message
news:#i6NSmtdCHA.2556@tkmsftngp08...
PCH
If i change the value of the delimiter in the schema.ini file.. from
Format=Delimited (|)
to
Format=TabDelimited
and even though the file is pipe delim, i can return the first row and get
the column names!
but when i later cycle through to import the data, i get blanks for the
rows...
"PCH" <pc...@hotmail.com> wrote in message
news:uJb6BPudCHA.1876@tkmsftngp10...
PCH
Format=Delimited (|)
vs
Format=Delimited(|)
the #$^$&$% space between Delimited and ( was causing the oledb parser (or
whatever the heck it is) to crash!
thanks for everyones help!
white space.. sheesh!
-PCH
"PCH" <pc...@hotmail.com> wrote in message
news:#f6eFWudCHA.1704@tkmsftngp10...
Paul Clement
¤ Ive done some more testing.
¤
¤ If i change the value of the delimiter in the schema.ini file.. from
¤
¤ Format=Delimited (|)
¤
¤ to
¤
¤ Format=TabDelimited
¤
¤ and even though the file is pipe delim, i can return the first row and get
¤ the column names!
¤
¤ but when i later cycle through to import the data, i get blanks for the
¤ rows...
I see a space in your Format spec. Try removing it:
Format=Delimited(|)
Shikal
I have a question almost similar like this. I have to right a batch
program that will read the data from a text file that is delimited by
|(pipe) which is nothing but an dump of data from a SQL server table.
One of the fields in that table is Text. and that field is populated
from a text area in an ASP Page. So when the user enters the data it
they hit ENTER KEY that will also be inserted in to table and when we
export the data in to the text file it comes in the second line. This
creates problems during reading from text file.
Could any one help me to resolve this issue?
Any help or ideas will be greatly appreciated.
Thanks and Regards
Shikal
Bob Grommes
A text field with embedded newlines is incompatible with any sort of
delimited text file format, which by definition requires the newlines be
used to denote end of record. You will not be able to do this with a simple
export, at least not in a straightforward manner.
If all text fields in the entire record are guaranteed not to have pipe
characters within their values, it would be possible to custom-parse the
file, or preprocess it in some way so that it would be readable by more
conventional means -- such as by replacing newlines with "<br>" or some
such. But it is a lot of work and a lot of code exposure. You'd have to
parse your way through, counting pipe characters, until you found the "true"
end of record.
Can't you simply read this info directly from the SQL Server table via
ADO.NET?
--Bob
"Shikal" <shib...@comcast.net> wrote in message
news:9rov6vs75ida11fkc...@4ax.com...