How to open a .CSV file ?

[fniles]

I have a .CSV file (comma delimited) that I want to open using OLEDB, but I
get the error "External table is not in the expected format."
If I save the .CSV file to an .XLS file, I can open the connection with no
problem.
What is the correct way to open a .CSV file ?
If I can not open the CSV file, how can I programmatically save the CSV file
to an XLS file ?
Thanks a lot.

dim myCon OleDb.OleDbConnection
myCon = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\file.csv; Extended Properties=""Excel 8.0; HDR=NO; IMEX=1""")
--> error "External table is not in the expected format."

 

[Scott M.]

Why not just use a StreamReader class and parse the values at the commas?

 

[GhostInAK]

Hello Scott M.,

Because not all CSV files are supposed to be parsed at the comma: Value
One, "Value, Two", Value Three

OP, your connection string is wrong. Try: Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\;Extended Properties=Text;

-Boo

 

[Michael D. Ober]

Here's a CSVLine class that I developed for this very purpose. It's not
elegant, but it works. It was originally written in VB 6, so it still uses
the VB Collection object instead of .NET framework collections. You can
create an object in one of two methods:

dim csv as new CSVLine
dim csv as new CSVLine(line as string, headers() as string)

The first method allows you to create a csv line from scratch and use the
ToString method to generate an Excel compatible csv line for writing to a
file.

The second method takes an excel compatible line and an array of header
strings and allows you to reference the contents of the line by index name

dim headers() as string = split("H1,H2,H3", ",")
dim line as string = """"Header, 1""",Header 2,"""Header 3""""
dim csv as new CSVLine(line, headers)

Debug.Print csv("H1") ' Returns without quotes "Header, 1"

Although there may be an Excel compatible CSV file that this class can't
parse, I haven't run across it in several years of using this class, first
in VB 6 and now in VB 2005.

Hope this helps,
Mike Ober.


=======================
Option Compare Text
Option Explicit On
Option Strict On

Public Class csvLine
Dim cRecs As New Collection

Public Sub New()
End Sub

Public Sub New(ByVal Line As String, ByVal Keys() As String, Optional
ByVal delim As String = ",")
Dim temp As String
Dim tKey As String
Dim i As Integer
Dim InQuotes As Boolean
Dim c As String = ""
Dim j As Integer

For i = LBound(Keys) To UBound(Keys)
InQuotes = False
temp = ""

If Len(Line) > 0 Then
c = Left$(Line, 1)

Do While Len(Line) > 0
Line = Mid$(Line, 2)

Select Case c
Case """"
InQuotes = Not InQuotes

Case delim
If Not InQuotes Then
c = ""
Exit Do
End If
End Select

temp = temp & c
c = Left$(Line, 1)
Loop
End If

' Append final character
temp = temp & c

' Remove leading and trailing Quotes
Select Case Len(temp)
Case 0
Case 1
If temp = """" Then temp = ""
If temp = delim Then temp = ""
Case Else
If Left$(temp, 1) = """" And Right$(temp, 1) = """" Then
temp = Mid$(temp, 2, Len(temp) - 2)
End Select

' Replace Double Quotes from string with Single Quotes
j = 1
Do While Len(temp) > 0 And j < Len(temp) And j > 0
j = InStr(j, temp, """""")
If j > 0 Then
temp = Left$(temp, j - 1) & Mid$(temp, j + 1)
End If
Loop

' Associate value with column name
tKey = Keys(i)
j = 0
Do While cRecs.Contains(tKey)
j = j + 1
tKey = Keys(i) & "_" & j
Loop
cRecs.Add(temp, tKey)
Next i
End Sub

Public Sub Add(ByVal obj As Object, ByVal Key As String)
cRecs.Add(obj, Key)
End Sub

Public Sub Add(ByVal obj As Object)
cRecs.Add(obj)
End Sub

Default Public ReadOnly Property Item(ByVal index As String) As String
Get
If cRecs.Contains(index) Then Return cRecs(index).ToString
'Debug.Assert(False, "Unknown index: " & index)
Return Nothing
End Get
End Property

Public Shadows Function ToString(Optional ByVal Delim As String = ",")
As String
Dim i As Integer
Dim sOut As String = ""
For i = 1 To cRecs.Count - 1
If IsNumeric(cRecs(i)) Then
sOut = sOut & Trim(cRecs(i).ToString) & Delim
Else
sOut = sOut & """" & cRecs(i).ToString & """" & Delim
End If
Next i
If IsNumeric(cRecs(i)) Then
sOut = sOut & Trim(Str(cRecs(i)))
Else
sOut = sOut & """" & cRecs(i).ToString & """"
End If
Return sOut
End Function
End Class

 

[Cor Ligthert [MVP]]

-Boo
Be aware that this is in the non English speaking cultures mostly not true.
In those cultures the ";" is used as field delimiter.

Cor

 

[GhostInAK]

Hello Michael,
Why in the name of all that is evil and holy would anyone use THAT CRAP instead
of a datatable and the System.Data.OleDb namespace.
It's not a question. No answer is required. ****in amature crackheads.

-Boo

 

[GhostInAK]

Hello Cor Ligthert [MVP],

Yes, because using a semicolon as a record separator in a COMMA-SEPARATED
VALUE file is a sane thing to do.

Doesn't matter.. Value One; "Value; Two"; Value Three would produce identical
results.

-Boo

 

[Cor Ligthert [MVP]]

Boo,

I did not invent that, it is just as it is implementend in countries where
the comma is a decimal seperator (the most). A true CSV file uses a comma as
seperator for numeric fields whithout and than the comma cannot be used in a
CSV file as decimal seperator.

Probably they should in not English speaking countries call it otherwise by
instand a PuntComma gesepareerd bestand. But some letter combinations have
an international meaning without that the real characters real are
meaningful.

Cor

 

[Scott M.]

You can use this technique to parse the file at any character, it doesn't
have to be the comma.

 

[GhostInAK]

Hello Scott M.,

Well, yes, you could write your own CSV parser as MDO did.. but that would
serve no practical purpose other than to teach you how to write a string
parser.

I assume when you said "parse at the comma" you meant string.split. While
you could use this function, it would be stupid to use it on a CSV file.
Quoted values are going to kill you. It's not worth it.

-Boo

 

[Scott M.]

Well, not really. Quoted values (and single quotes and any other special
character) could be escaped before any parsing took place.

As for the .Split method of a string, it most certainly would serve a
practical purpose, it wold split the string at whatever character it is told
to. The fact is that we are talking about a delimited file here and that
means that the delimeter is a known character - this is exactly what
..split() is for.

Now, I grant you that if the CSV is a large file, then a StreamReader and
the .split() method are probably not the most efficient approach, but the OP
didn't indicate it was a large file.

Other than the file size, there is no reason why reading the file contents
in, escaping any trouble characters and parsing the string at the delimeter
wouldn't work just fine.

 

[Scott M.]

And, by the way. we are talking about a file that contains nothing but a
string within it, so using string methods on this string is hardly "stupid".

 

[Michael D. Ober]

Because my clients send me text files, many of which can be quickly and
simply parsed by this code and it's a lot faster than coding up a database
for each client. Also, if you had READ my comments about the code and knew
ANYTHING at all about VB 6, you'd know that there is NO SUCH THING as
System.Data.OleDb in VB 6. Yes, I could have used the VB 6 Excel Text
parser, but it isn't nearly as flexible as expected.

Mike.

 

[Jon Paal]

set the extended properties to 'text' for csv file and remember to create the ini file for field definition

 

[GhostInAK]

Hello Scott M.,

Well, you can do it the amature way or you can do it the right way. Matters
not to me - I won't ever be using any of the crap you write.
The OP had it right, even if the details were buggered. The correct way
to work with CSV files in .NET is vial the OleDb namespace. Any other home-grown
hand-rolled crap is wasted effort.

-Boo

 

[Scott M.]

(really trying to avoid a flame war) Scott M. writes:

With all due respect, your suggested way is great (and may even be the
"better" way in this scenario), but by no means is it the "right" way. The
OP said very little about the size of the CSV or how complex the data inside
it is. Without knowing the answers to those questions, it would be foolish
to suggest what the "right" way to proceed would be.

May I suggest that if you believe that using String methods to parse a
string is "amature", you may be a bit closed minded to other programming
possibilites. There is never just one correct way to solve a programmatic
problem.

If you would take your blinders off, you might realize that different
scenarios require different approaches.

Good luck.

 

[GhostInAK]

Hello Scott M.,

Wow.. I've been such a fool.. it's taken your inspired words to make me see
the light.

I suppose by your reasoning every file is just a big long string and we should
all be using the string manipulation functions to work with them. Yer bein
an ass. Just admit when yer wrong.

-Boo

 

[Scott M.]

I'm really having trouble understanding why you feel the need to insult
people who have not insulted you?

I'm also having difficulty understanding why you haven't bothered to read
any of what I wrote. I know that you haven't, because if you had, you
certainly wouldn't say that by my reasoning every file should be accessed as
a string. How can I know that? Well, because I never said that. In fact,
I said (on more than one occassion) exactly the opposite (and agreed with
you).

Clearly, you just feel you need to take out some frustration you have on
others. Because any objective person reading what I wrote would not come to
the conclusions you've come to. It sort of sounds like it doesn't matter
what anyone says, you've got the "my way or the highway" mentality.

Good luck with that.

 

[GhostInAK]

Hello Scott M.,

I'm really having trouble understanding why you feel the need to
insult people who have not insulted you?

I don't have any tollerence for idiocy.

I'm also having difficulty understanding why you haven't bothered to
read any of what I wrote. I know that you haven't, because if you
had, you certainly wouldn't say that by my reasoning every file should
be accessed as a string. How can I know that? Well, because I never
said that. In fact, I said (on more than one occassion) exactly the
opposite (and agreed with you).

Why not just use a StreamReader class and parse the values at the commas?
You can use this technique to parse the file at any character, it doesn't have to be the comma.
As for the .Split method of a string, it most certainly would serve a practical purpose [...]
we are talking about a file that contains nothing but a string within

it, so using string methods on this string is hardly "stupid".

A string is just a data type that holds a collection of chars.. so all files
are just one big string.

[...] but by no means is it the "right" way. The OP said very little about

the size of the CSV or how complex the data inside it is.

So if one method handles all scenarios, and another doesn't.. and we don't
know what the inputs are.. then yes there is most definately a Right Way
of doing things. String manipulation, in this case, aint it.

Clearly, you just feel you need to take out some frustration you have
on others. Because any objective person reading what I wrote would
not come to the conclusions you've come to. It sort of sounds like it
doesn't matter what anyone says, you've got the "my way or the
highway" mentality.

Yes.. I feel very frustrated when given a choice between the Right Way and
the Wrong Way, people choose the Wrong Way. I feel frustrated at all exhibitions
of idiocy. This is not to say that idiocy is a permanent condition. And
if people would accept that they were being an idiot for a moment and then
look at the solution provided to correct the situation their idiocy level
would drop dramatically. But no, people cling to being called an idiot and
don't pay attention to the provided solution. "OH MY GOD! HE CALLED ME AN
IDIOT!! What an uncaring bastard!" Hell with you. Look past being an idiot
and look at the solution.

Programming is a form of engineering. It's akin to building any kind of
physical structure. You wouldn't, for example, build a suspension bridge
and then line it with gravel, or build a grass hut in Fairbanks, Alaska.
You could certainly do these things, but if the goal is to build a vehicle
bridge that will last 100s of years, or a domicile for humans to live in,
these are Wrong things to do. The same goes for programming. Just because
something "works" (oh look, the grass hut is livable! It's sunny and 80
degrees out!) doesnt mean it's the Right Thing (oh crap, its February and
-45 degrees.. and I'm dead because my grass hut wont hold heat).

-Boo

 

[Cor Ligthert [MVP]]

Boo,

In my opinion is it mostly an idiot who calls an other an idiot.

At least you can hide your less of knowledge behind that.

If you want to show that somebody is an idiot, than proof why, but don't
tell it.

In my eyes you are surely not an idiot, before you would read this in this
message.

Please keep it like that.

About the content of your message, again a prase.

There are more roads that goes to Rome.

The direction from which you come tells which is the best.
Don't tell another one who comes from a complete different direction that
only your road is the best.

Just some opinions from an "Idiot" who still is trying to help.

Cor

Hello Scott M.,

I'm really having trouble understanding why you feel the need to
insult people who have not insulted you?

I don't have any tollerence for idiocy.

I'm also having difficulty understanding why you haven't bothered to
read any of what I wrote. I know that you haven't, because if you
had, you certainly wouldn't say that by my reasoning every file should
be accessed as a string. How can I know that? Well, because I never
said that. In fact, I said (on more than one occassion) exactly the
opposite (and agreed with you).

Why not just use a StreamReader class and parse the values at the
commas?
You can use this technique to parse the file at any character, it
doesn't have to be the comma.
As for the .Split method of a string, it most certainly would serve a practical purpose [...]
we are talking about a file that contains nothing but a string within

it, so using string methods on this string is hardly "stupid".

A string is just a data type that holds a collection of chars.. so all
files are just one big string.

[...] but by no means is it the "right" way. The OP said very little
about

the size of the CSV or how complex the data inside it is.

So if one method handles all scenarios, and another doesn't.. and we don't
know what the inputs are.. then yes there is most definately a Right Way
of doing things. String manipulation, in this case, aint it.

Clearly, you just feel you need to take out some frustration you have
on others. Because any objective person reading what I wrote would
not come to the conclusions you've come to. It sort of sounds like it
doesn't matter what anyone says, you've got the "my way or the
highway" mentality.

Yes.. I feel very frustrated when given a choice between the Right Way and
the Wrong Way, people choose the Wrong Way. I feel frustrated at all
exhibitions of idiocy. This is not to say that idiocy is a permanent
condition. And if people would accept that they were being an idiot for a
moment and then look at the solution provided to correct the situation
their idiocy level would drop dramatically. But no, people cling to being
called an idiot and don't pay attention to the provided solution. "OH MY
GOD! HE CALLED ME AN IDIOT!! What an uncaring bastard!" Hell with you.
Look past being an idiot and look at the solution.

Programming is a form of engineering. It's akin to building any kind of
physical structure. You wouldn't, for example, build a suspension bridge
and then line it with gravel, or build a grass hut in Fairbanks, Alaska.
You could certainly do these things, but if the goal is to build a vehicle
bridge that will last 100s of years, or a domicile for humans to live in,
these are Wrong things to do. The same goes for programming. Just
because something "works" (oh look, the grass hut is livable! It's sunny
and 80 degrees out!) doesnt mean it's the Right Thing (oh crap, its
February and -45 degrees.. and I'm dead because my grass hut wont hold
heat).

-Boo