Problem reading text file..

S

Simon Verona

I'm trying to populate a dataset with the contents of a csv text file.... This is one of a number of attempts I've made to read in this file (I don't actually want to display the information, but read through it line by line in code..).

My code at the moment looks like :

Dim TextConnectionString As String
TextConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FilePath & ";" & _
"Extended Properties=""Text;HDR=NO;FMT=Delimited"""
Dim TextConn As New System.Data.OleDb.OleDbConnection(TextConnectionString)
TextConn.Open()
Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from " & filename, TextConn)
Dim ds As DataSet = New DataSet(filename)
da.Fill(ds, filename)
It falls over when filling the dataset (the final line above) with the exception "Database or Object is read only".

The variable "FilePath" is the path to the directory that contains the file and "filename" is the actual filename within that directory.

The file being read looks as below.. I really can't see whats going on.. Any ideas??

Regards

"Mr Tom Dadds","7 Low Bradfield","Sheffield","S6 6LB","","","","Ford","Fiesta","K789THJ","Dear Mr Dadds","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Chris Bracknall","21 Velor Lane","Rivelin Valley","Sheffield","S11 1AD","","","Ford","Mondeo","R135SHE","Dear Mr Bracknall","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr David Gilmour","108 Chesterfield Road","Barlborough","Derbyshire","S43 4TT","","","Fiat","Brava","M815CNV","Dear Mr Gilmour","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr George Grealy","21 Healy Lane","Greenoside","Sheffield","S27 1PL","","","Mazda","323","N206LLP","Dear Mr Grealy","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Simon Templar","89 Portnoy Mews","Newbold","Chesterfield","S33 1AD","","","Volvo","V40","P22SNT","Dear Mr Templar","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Chris Tarrant","26 Freeman Hill","Tinsley","Sheffield","S22 1AD","","","Lexus","Gs300","TRR12","Dear Mr Tarrant","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Chris Bracknall","21 Velor Lane","Rivelin Valley","Sheffield","S11 1AD","","","Land/range Rover","Discovery","L333EEE","Dear Mr Bracknall","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Peter Thompson","106 Chesterfield Road","Barlborough","Derbyshire","S43 4TT","","","Mazda","626","P4TER","Dear Mr Thompson","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003","0860757800"
"Mr Chris Skeaping","The Stables","Barnet Lane","Arkely","Barnet, Herts","EN5 2AP","","Ford","Fiesta","R127MFL","Dear Mr Skeaping","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr G Gray","14 High Bradfield","Sheffield","South Yorkshire","S6 7LB","","","Pontiac","Firebird","H789FDS","Dear Mr Gray","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Miss Carrie Fisher","The Lodge","Richmond Road","Kingston Upon Thames","Surrey","TN24 1PL","","Alfa-romeo","164 Twinspark","C4RIE","Dear Miss Fisher","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mrs Liz Cox","21 Sharkey Road","Greenoside","Sheffield","S16 2AS","","","Renault","Megane","L15COX","Dear Mrs Cox","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Mark Hill","11 Cedar Falls Lane","Rapids City","South Dakota","S11 2WD","","","Porsche","911","SIT911","Dear Mr Hill","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"G & A Petken Ltd","102 Chesterfield Road","Barlborough","Derbyshire","S43 4TT","","","Peugeot","406","R922EWK","Dear Sirs","Yours faithfully","","G:\SPV.DOC","Simon Verona","",""," ","","","","","","","","","","","","4th December 2003",""
"Mr Alan Davis","11 Well End Lane","Shenley","Hertfordshiree","DC24 8UJ","","","Jaguar-daimler","Xjr","C4NDY","Dear Mr Davis","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Peter Dearing","42 Barlborough Road","Clowne","Derbyshire","S41 2DE","","","Nissan","Qx","N100KIL","Dear Mr Dearing","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mrs Shirley Temple","47 Kennington Road","Killamarsh","Sheffield","S12 4KL","","","Ford","Fiesta","K206GGR","Dear Mrs Temple","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Stefan Johanson","The Stables","Grafton Lane","Mossborough","Sheffield","S26 6YH","","Fiat","Marea","SJ40","Dear Mr Johanson","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003","0860STEFAN"
"Mr Barry Cryer","Stable Cottage","Retford Road","Whitwell","Derbyshire","S40 3PP","","Hyundai","Lantra","M223RED","Dear Mr Cryer","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
 
C

Chris Taylor

Hi,

If the name of the file is not standard, that could be the cause of the problem. For example if the file name is 'data.txt' it should work just fine, but if the file name is 'data.abc' or 'data' it will fail with the exception you are getting. Since I do not really work with the text file drivers, I am not sure if there is a set of rules laid out somewhere, but from experience the file should have ether a .txt ro .csv extension, if the filename has spaces in it it must be surrounded by '[' ']' for example [data table.txt].

Hope this helps
--
Chris Taylor
http://www.xanga.com/home.aspx?user=taylorza
I'm trying to populate a dataset with the contents of a csv text file.... This is one of a number of attempts I've made to read in this file (I don't actually want to display the information, but read through it line by line in code..).

My code at the moment looks like :

Dim TextConnectionString As String
TextConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FilePath & ";" & _
"Extended Properties=""Text;HDR=NO;FMT=Delimited"""
Dim TextConn As New System.Data.OleDb.OleDbConnection(TextConnectionString)
TextConn.Open()
Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from " & filename, TextConn)
Dim ds As DataSet = New DataSet(filename)
da.Fill(ds, filename)
It falls over when filling the dataset (the final line above) with the exception "Database or Object is read only".

The variable "FilePath" is the path to the directory that contains the file and "filename" is the actual filename within that directory.

The file being read looks as below.. I really can't see whats going on.. Any ideas??

Regards

"Mr Tom Dadds","7 Low Bradfield","Sheffield","S6 6LB","","","","Ford","Fiesta","K789THJ","Dear Mr Dadds","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Chris Bracknall","21 Velor Lane","Rivelin Valley","Sheffield","S11 1AD","","","Ford","Mondeo","R135SHE","Dear Mr Bracknall","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr David Gilmour","108 Chesterfield Road","Barlborough","Derbyshire","S43 4TT","","","Fiat","Brava","M815CNV","Dear Mr Gilmour","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr George Grealy","21 Healy Lane","Greenoside","Sheffield","S27 1PL","","","Mazda","323","N206LLP","Dear Mr Grealy","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Simon Templar","89 Portnoy Mews","Newbold","Chesterfield","S33 1AD","","","Volvo","V40","P22SNT","Dear Mr Templar","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Chris Tarrant","26 Freeman Hill","Tinsley","Sheffield","S22 1AD","","","Lexus","Gs300","TRR12","Dear Mr Tarrant","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Chris Bracknall","21 Velor Lane","Rivelin Valley","Sheffield","S11 1AD","","","Land/range Rover","Discovery","L333EEE","Dear Mr Bracknall","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Peter Thompson","106 Chesterfield Road","Barlborough","Derbyshire","S43 4TT","","","Mazda","626","P4TER","Dear Mr Thompson","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003","0860757800"
"Mr Chris Skeaping","The Stables","Barnet Lane","Arkely","Barnet, Herts","EN5 2AP","","Ford","Fiesta","R127MFL","Dear Mr Skeaping","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr G Gray","14 High Bradfield","Sheffield","South Yorkshire","S6 7LB","","","Pontiac","Firebird","H789FDS","Dear Mr Gray","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Miss Carrie Fisher","The Lodge","Richmond Road","Kingston Upon Thames","Surrey","TN24 1PL","","Alfa-romeo","164 Twinspark","C4RIE","Dear Miss Fisher","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mrs Liz Cox","21 Sharkey Road","Greenoside","Sheffield","S16 2AS","","","Renault","Megane","L15COX","Dear Mrs Cox","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Mark Hill","11 Cedar Falls Lane","Rapids City","South Dakota","S11 2WD","","","Porsche","911","SIT911","Dear Mr Hill","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"G & A Petken Ltd","102 Chesterfield Road","Barlborough","Derbyshire","S43 4TT","","","Peugeot","406","R922EWK","Dear Sirs","Yours faithfully","","G:\SPV.DOC","Simon Verona","",""," ","","","","","","","","","","","","4th December 2003",""
"Mr Alan Davis","11 Well End Lane","Shenley","Hertfordshiree","DC24 8UJ","","","Jaguar-daimler","Xjr","C4NDY","Dear Mr Davis","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Peter Dearing","42 Barlborough Road","Clowne","Derbyshire","S41 2DE","","","Nissan","Qx","N100KIL","Dear Mr Dearing","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mrs Shirley Temple","47 Kennington Road","Killamarsh","Sheffield","S12 4KL","","","Ford","Fiesta","K206GGR","Dear Mrs Temple","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Stefan Johanson","The Stables","Grafton Lane","Mossborough","Sheffield","S26 6YH","","Fiat","Marea","SJ40","Dear Mr Johanson","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003","0860STEFAN"
"Mr Barry Cryer","Stable Cottage","Retford Road","Whitwell","Derbyshire","S40 3PP","","Hyundai","Lantra","M223RED","Dear Mr Cryer","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
 
S

Simon Verona

Well, you learn something new every day!

Whats the point of saying that it's a delimited file in the definition if it has to have a csv extension as well!!!!

Anyways, renaming the file with a csv extension done the trick.

Thanks
Simon
Hi,

If the name of the file is not standard, that could be the cause of the problem. For example if the file name is 'data.txt' it should work just fine, but if the file name is 'data.abc' or 'data' it will fail with the exception you are getting. Since I do not really work with the text file drivers, I am not sure if there is a set of rules laid out somewhere, but from experience the file should have ether a .txt ro .csv extension, if the filename has spaces in it it must be surrounded by '[' ']' for example [data table.txt].

Hope this helps
--
Chris Taylor
http://www.xanga.com/home.aspx?user=taylorza
I'm trying to populate a dataset with the contents of a csv text file.... This is one of a number of attempts I've made to read in this file (I don't actually want to display the information, but read through it line by line in code..).

My code at the moment looks like :

Dim TextConnectionString As String
TextConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FilePath & ";" & _
"Extended Properties=""Text;HDR=NO;FMT=Delimited"""
Dim TextConn As New System.Data.OleDb.OleDbConnection(TextConnectionString)
TextConn.Open()
Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from " & filename, TextConn)
Dim ds As DataSet = New DataSet(filename)
da.Fill(ds, filename)
It falls over when filling the dataset (the final line above) with the exception "Database or Object is read only".

The variable "FilePath" is the path to the directory that contains the file and "filename" is the actual filename within that directory.

The file being read looks as below.. I really can't see whats going on.. Any ideas??

Regards

"Mr Tom Dadds","7 Low Bradfield","Sheffield","S6 6LB","","","","Ford","Fiesta","K789THJ","Dear Mr Dadds","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Chris Bracknall","21 Velor Lane","Rivelin Valley","Sheffield","S11 1AD","","","Ford","Mondeo","R135SHE","Dear Mr Bracknall","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr David Gilmour","108 Chesterfield Road","Barlborough","Derbyshire","S43 4TT","","","Fiat","Brava","M815CNV","Dear Mr Gilmour","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr George Grealy","21 Healy Lane","Greenoside","Sheffield","S27 1PL","","","Mazda","323","N206LLP","Dear Mr Grealy","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Simon Templar","89 Portnoy Mews","Newbold","Chesterfield","S33 1AD","","","Volvo","V40","P22SNT","Dear Mr Templar","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Chris Tarrant","26 Freeman Hill","Tinsley","Sheffield","S22 1AD","","","Lexus","Gs300","TRR12","Dear Mr Tarrant","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Chris Bracknall","21 Velor Lane","Rivelin Valley","Sheffield","S11 1AD","","","Land/range Rover","Discovery","L333EEE","Dear Mr Bracknall","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Peter Thompson","106 Chesterfield Road","Barlborough","Derbyshire","S43 4TT","","","Mazda","626","P4TER","Dear Mr Thompson","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003","0860757800"
"Mr Chris Skeaping","The Stables","Barnet Lane","Arkely","Barnet, Herts","EN5 2AP","","Ford","Fiesta","R127MFL","Dear Mr Skeaping","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr G Gray","14 High Bradfield","Sheffield","South Yorkshire","S6 7LB","","","Pontiac","Firebird","H789FDS","Dear Mr Gray","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Miss Carrie Fisher","The Lodge","Richmond Road","Kingston Upon Thames","Surrey","TN24 1PL","","Alfa-romeo","164 Twinspark","C4RIE","Dear Miss Fisher","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mrs Liz Cox","21 Sharkey Road","Greenoside","Sheffield","S16 2AS","","","Renault","Megane","L15COX","Dear Mrs Cox","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Mark Hill","11 Cedar Falls Lane","Rapids City","South Dakota","S11 2WD","","","Porsche","911","SIT911","Dear Mr Hill","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"G & A Petken Ltd","102 Chesterfield Road","Barlborough","Derbyshire","S43 4TT","","","Peugeot","406","R922EWK","Dear Sirs","Yours faithfully","","G:\SPV.DOC","Simon Verona","",""," ","","","","","","","","","","","","4th December 2003",""
"Mr Alan Davis","11 Well End Lane","Shenley","Hertfordshiree","DC24 8UJ","","","Jaguar-daimler","Xjr","C4NDY","Dear Mr Davis","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Peter Dearing","42 Barlborough Road","Clowne","Derbyshire","S41 2DE","","","Nissan","Qx","N100KIL","Dear Mr Dearing","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mrs Shirley Temple","47 Kennington Road","Killamarsh","Sheffield","S12 4KL","","","Ford","Fiesta","K206GGR","Dear Mrs Temple","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
"Mr Stefan Johanson","The Stables","Grafton Lane","Mossborough","Sheffield","S26 6YH","","Fiat","Marea","SJ40","Dear Mr Johanson","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003","0860STEFAN"
"Mr Barry Cryer","Stable Cottage","Retford Road","Whitwell","Derbyshire","S40 3PP","","Hyundai","Lantra","M223RED","Dear Mr Cryer","Yours sincerely","","G:\SPV.DOC","Simon Verona","","","","","","","","","","","","","","","4th December 2003",""
 
P

Paul Clement

¤ Well, you learn something new every day!
¤
¤ Whats the point of saying that it's a delimited file in the definition if it has to have a csv extension as well!!!!
¤
¤ Anyways, renaming the file with a csv extension done the trick.

Just an addendum, the valid file extensions are:

- txt
- csv
- tab
- asc

If it is anything else you either need to change the extension or modify a registry entry to accept
other extensions:

HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Text (Disabled Extensions)


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Top