473,692 Members | 2,333 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem with reading delimited textfile '|' with OleDBDataAdapte r

'--this code works but only reads text into one column when contains multiple
cols

Dim ds1x As New DataSet
Dim ConStr As String = _
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=C:\dir1A \;Extended
Properties=""Te xt;HDR=No;FMT=D elimited\"""

Dim conn1x As New OleDb.OleDbConn ection(ConStr)
Dim dax1 As New OleDbDataAdapte r("Select * from testabc1x.txt", conn1x)
dax1.Fill(ds1x, "tbl0")
datagridview1.D ataSource = ds1x.Tables("tb l0")

-------------------------------------------------------
The above code works fine for reading text from a textfile and adding it to
a datagridview. My problem is that I have mutiple columns delimited by a
pipe '|', and the code above is not delimiting the text. I get it all in one
column. How to make it delimit the text into multiple columns?

Thanks,
Rich
Jul 2 '07 #1
6 22539
Hi Rich -

It doesn't look like you ever specified your delimiter.

Try changing FMT=Delimited to FMT=Delimited(< delimiter>) or in your
case, FMT=Delimited(| )

Good Luck,

-Mark

Jul 2 '07 #2
thanks for your reply. Anyway, I am using '|' as the delimiter. So I tried

....;Extended Properties=""Te xt;HDR=No;FMT=D elimited(|)\"""
and
....;Extended Properties=""Te xt;HDR=No;FMT=D elimited('|')\" ""

But no delimiting ensued. However, I created a csv file - by hand - and
that did come in delimited with

....;Extended Properties=""Te xt;HDR=No;\"""

The only problem is in writing to a csv file.

Dim oWrite As StreamWriter
oWrite = File.AppendText ("C:\1A\testabc 1x.csv")
oWrite.WriteLin e("A" & vbTab & "B")
oWrite.Close()

when I open the csv file, I don't get 2 columns with oWrite.WriteLin e("A" &
vbTab & "B"). I used to be able to write VBA code like that, and it would
have 2 columns. Any ideas?

"ma*********@bi naryswitch.com" wrote:
Hi Rich -

It doesn't look like you ever specified your delimiter.

Try changing FMT=Delimited to FMT=Delimited(< delimiter>) or in your
case, FMT=Delimited(| )

Good Luck,

-Mark

Jul 2 '07 #3
On Mon, 2 Jul 2007 11:02:04 -0700, Rich <Ri**@discussio ns.microsoft.co mwrote:

§ '--this code works but only reads text into one column when contains multiple
§ cols
§
§ Dim ds1x As New DataSet
§ Dim ConStr As String = _
§ "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=C:\dir1A \;Extended
§ Properties=""Te xt;HDR=No;FMT=D elimited\"""
§
§ Dim conn1x As New OleDb.OleDbConn ection(ConStr)
§ Dim dax1 As New OleDbDataAdapte r("Select * from testabc1x.txt", conn1x)
§ dax1.Fill(ds1x, "tbl0")
§ datagridview1.D ataSource = ds1x.Tables("tb l0")
§
§ -------------------------------------------------------
§ The above code works fine for reading text from a textfile and adding it to
§ a datagridview. My problem is that I have mutiple columns delimited by a
§ pipe '|', and the code above is not delimiting the text. I get it all in one
§ column. How to make it delimit the text into multiple columns?

For a column delimiter other than a comma you either need to change a Registry setting or use a
schema.ini file.

[testabc1x.txt]
ColNameHeader=F alse
CharacterSet=AN SI
Format=Delimite d(|)
Paul
~~~~
Microsoft MVP (Visual Basic)
Jul 3 '07 #4
Thank you for your reply. I have seen several examples on the web suggesting
the same thing as you about using an schema.ini file. This seems the way to
go except that I can't figure out how this file is used. Ex: I write my
OleDBDataAdapte r code. I place the schema.ini file in the same directory as
the text file I want to read. How does the schema.ini file get
invoked/implemented in the OleDBDataAdapte r code? None of the examples I saw
on the web showed where the schema.ini file was referenced.
"Paul Clement" wrote:
On Mon, 2 Jul 2007 11:02:04 -0700, Rich <Ri**@discussio ns.microsoft.co mwrote:

¤ '--this code works but only reads text into one column when contains multiple
¤ cols
¤
¤ Dim ds1x As New DataSet
¤ Dim ConStr As String = _
¤ "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=C:\dir1A \;Extended
¤ Properties=""Te xt;HDR=No;FMT=D elimited\"""
¤
¤ Dim conn1x As New OleDb.OleDbConn ection(ConStr)
¤ Dim dax1 As New OleDbDataAdapte r("Select * from testabc1x.txt", conn1x)
¤ dax1.Fill(ds1x, "tbl0")
¤ datagridview1.D ataSource = ds1x.Tables("tb l0")
¤
¤ -------------------------------------------------------
¤ The above code works fine for reading text from a textfile and adding it to
¤ a datagridview. My problem is that I have mutiple columns delimited by a
¤ pipe '|', and the code above is not delimiting the text. I get it all in one
¤ column. How to make it delimit the text into multiple columns?

For a column delimiter other than a comma you either need to change a Registry setting or use a
schema.ini file.

[testabc1x.txt]
ColNameHeader=F alse
CharacterSet=AN SI
Format=Delimite d(|)
Paul
~~~~
Microsoft MVP (Visual Basic)
Jul 3 '07 #5
On Tue, 3 Jul 2007 08:04:01 -0700, Rich <Ri**@discussio ns.microsoft.co mwrote:

§ Thank you for your reply. I have seen several examples on the web suggesting
§ the same thing as you about using an schema.ini file. This seems the way to
§ go except that I can't figure out how this file is used. Ex: I write my
§ OleDBDataAdapte r code. I place the schema.ini file in the same directory as
§ the text file I want to read. How does the schema.ini file get
§ invoked/implemented in the OleDBDataAdapte r code? None of the examples I saw
§ on the web showed where the schema.ini file was referenced.
§

It's automatic. The Text ISAM driver will look for the schema.ini file and search for an entry (like
the one I posted in my prior response) that corresponds to the text file that is being opened.
Paul
~~~~
Microsoft MVP (Visual Basic)
Jul 5 '07 #6
Thanks Very much

"Paul Clement" wrote:
On Tue, 3 Jul 2007 08:04:01 -0700, Rich <Ri**@discussio ns.microsoft.co mwrote:

¤ Thank you for your reply. I have seen several examples on the web suggesting
¤ the same thing as you about using an schema.ini file. This seems the way to
¤ go except that I can't figure out how this file is used. Ex: I write my
¤ OleDBDataAdapte r code. I place the schema.ini file in the same directory as
¤ the text file I want to read. How does the schema.ini file get
¤ invoked/implemented in the OleDBDataAdapte r code? None of the examples I saw
¤ on the web showed where the schema.ini file was referenced.
¤

It's automatic. The Text ISAM driver will look for the schema.ini file and search for an entry (like
the one I posted in my prior response) that corresponds to the text file that is being opened.
Paul
~~~~
Microsoft MVP (Visual Basic)
Jul 6 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
21826
by: TF | last post by:
Hi, I have to access data from a comma delimited file in .net windows application (VB.net or C#). I am using Microsoft.Jet.OLEDB.4.0 provider for this purpose. Now the problem is when i extract data from a file with extension csv or txt it works fine but as soon as i change the extension, like 'log' or 'abc' it gives following error in 'Fill' function of 'OleDbDataAdapter': Cannot update. Database or object is read-only
16
4783
by: Roy | last post by:
I use a Access 2K application.I am trying to use Chuck Grimsby(clsReadTextFile.txt)class utility to read a text file and then do a import of the same into my database.The question is how to call these in a button event or any other method. Thanks, Roy
0
1819
by: TJS | last post by:
attempting to read a delimited text file into a dataset using oledb text file connection getting this error message when trying to open connection ---------------------------------------------------- System.Data.OleDb.OleDbException: No error information available: E_NOINTERFACE(0x80004002). ---------------------------------------------------- I have the schema.ini file in the folder with the delimited text file
0
1655
by: Ray | last post by:
Hi All I've tried to read a .csv file and bind the data to a datagrid. I am doing sth like this: conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"+ "Data Source=" + csvPath + ";" + "Extended Properties=\"text;HDR=YES;FMT=Delimited\""); conn.Open(); //Read all data into a data table
3
7115
by: Avi | last post by:
I need to create a text file that has the data from the 10 tables in the database. The number of fields in the tables exceeds 255 and so I cannot make a new table with all the fields and then export it into a text file. Is there any s/w out there I could use? I am not much of a programmer but I heard I could use VBA to get this done. Any help with the code will be appreciated. Thanks
4
5066
by: tshad | last post by:
I have a program that is reading a .csv file into a dataset and works fine except that it is dropping the first line. I assume that is because it is dropping the header. The problem is the first line is not a header. This was working before and I am not sure what caused it not to work. I am using OleDbDataAdapter. Dim ConStr As String = _ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ path & ";Extended...
1
1499
by: Anthony1312002 | last post by:
I have a working script that imports a space or tab delimited textfile using the vbTab attribute. But because of an upgrade the file has now become comma delimited. what would I need to change to make this script work with the new file format? strTextLine=objText.Readline data=Split(strTextLine,vbTab) RLog.AddNew RLog("fldUnknown")=data(0) 'Site ID RLog("fldAccountName")=data(1) 'Client ID ...
2
5021
by: tshad | last post by:
I have a program that is reading a csv file into a dataset. I want it to read the 1st line as data. But it ignores it. I have the Connection set up as: OleDbConnection csvConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + csvPath + ";Extended Properties=\"Text;HDR=Yes;FMT=Delimited\"");
1
1191
by: liwaste | last post by:
hi.. I am trying to read a textfile nd store it in a dataset.. so tht i can move it to a database later my file looks like this climate,Soil,lutype,area TRD,HAM,CRO,14.54 TRD,HAM,CRO,0.01 TRD,HAM,FOR,18.44 TRD,HAM,FOR,79.48 TRD,HAM,FOR,3.42 TRD,HAM,FOR,9.23
0
8610
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9090
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8810
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8810
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6462
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Duprť who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5821
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4325
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2242
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1961
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.