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 programmaticall y save the CSV file
to an XLS file ?
Thanks a lot.

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

[Scott M.]

Re: How to open a .CSV file ?

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


"fniles" <fniles@pfmail. comwrote in message
news:%23pruw%23 K7GHA.4708@TK2M SFTNGP05.phx.gb l...

>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 programmaticall y save the CSV
file to an XLS file ?
Thanks a lot.
>
dim myCon OleDb.OleDbConn ection
myCon = New OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0; Data
Source=c:\file. csv; Extended Properties=""Ex cel 8.0; HDR=NO; IMEX=1""")
--error "External table is not in the expected format."
>
>

[GhostInAK]

Re: How to open a .CSV file ?

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=Micros oft.Jet.OLEDB.4 .0;Data
Source=c:\;Exte nded Properties=Text ;

-Boo

Why not just use a StreamReader class and parse the values at the
commas?
>
"fniles" <fniles@pfmail. comwrote in message
news:%23pruw%23 K7GHA.4708@TK2M SFTNGP05.phx.gb l...
>

>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 programmaticall y save the
>CSV
>file to an XLS file ?
>Thanks a lot.
>dim myCon OleDb.OleDbConn ection
>myCon = New OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0; Data
>Source=c:\file .csv; Extended Properties=""Ex cel 8.0; HDR=NO;
>IMEX=1""")
>--error "External table is not in the expected format."

[Michael D. Ober]

Re: How to open a .CSV file ?

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).To String
'Debug.Assert(F alse, "Unknown index: " & index)
Return Nothing
End Get
End Property

Public Shadows Function ToString(Option al 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).T oString) & Delim
Else
sOut = sOut & """" & cRecs(i).ToStri ng & """" & Delim
End If
Next i
If IsNumeric(cRecs (i)) Then
sOut = sOut & Trim(Str(cRecs( i)))
Else
sOut = sOut & """" & cRecs(i).ToStri ng & """"
End If
Return sOut
End Function
End Class


"GhostInAK" <paco@paco.netw rote in message
news:be1391bf1c 1ee8c8ba8d6a850 af6@news.micros oft.com...

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=Micros oft.Jet.OLEDB.4 .0;Data

Source=c:\;Exte nded Properties=Text ;
>
-Boo
>

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

"fniles" <fniles@pfmail. comwrote in message
news:%23pruw%23 K7GHA.4708@TK2M SFTNGP05.phx.gb l...

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 programmaticall y save the
CSV
file to an XLS file ?
Thanks a lot.
dim myCon OleDb.OleDbConn ection
myCon = New OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0; Data
Source=c:\file. csv; Extended Properties=""Ex cel 8.0; HDR=NO;
IMEX=1""")
--error "External table is not in the expected format."

>
>
>

[Cor Ligthert [MVP]]

Re: How to open a .CSV file ?

-Boo

>

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

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]

Re: How to open a .CSV file ?

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.Ole Db namespace.
It's not a question. No answer is required. Fuckin amature crackheads.

-Boo

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).To String
'Debug.Assert(F alse, "Unknown index: " & index)
Return Nothing
End Get
End Property
Public Shadows Function ToString(Option al 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).T oString) & Delim
Else
sOut = sOut & """" & cRecs(i).ToStri ng & """" & Delim
End If
Next i
If IsNumeric(cRecs (i)) Then
sOut = sOut & Trim(Str(cRecs( i)))
Else
sOut = sOut & """" & cRecs(i).ToStri ng & """"
End If
Return sOut
End Function
End Class
"GhostInAK" <paco@paco.netw rote in message
news:be1391bf1c 1ee8c8ba8d6a850 af6@news.micros oft.com...

>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=Micros oft.Jet.OLEDB.4 .0;Data
>

>Source=c:\;Ext ended Properties=Text ;
>>
>-Boo
>>

>>Why not just use a StreamReader class and parse the values at the
>>commas?
>>>
>>"fniles" <fniles@pfmail. comwrote in message
>>news:%23pruw% 23K7GHA.4708@TK 2MSFTNGP05.phx. gbl...
>>>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 programmaticall y save the
>>>CSV
>>>file to an XLS file ?
>>>Thanks a lot.
>>>dim myCon OleDb.OleDbConn ection
>>>myCon = New OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0; Data
>>>Source=c:\fi le.csv; Extended Properties=""Ex cel 8.0; HDR=NO;
>>>IMEX=1""")
>>>--error "External table is not in the expected format."

[GhostInAK]

Re: How to open a .CSV file ?

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

>-Boo
>>

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

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

[Cor Ligthert [MVP]]

Re: How to open a .CSV file ?

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

"GhostInAK" <paco@paco.coms chreef in bericht
news:c71747b44a f368c8bada033cf c80@news.micros oft.com...

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
>

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

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

>
>

[Scott M.]

Re: How to open a .CSV file ?

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


"GhostInAK" <paco@paco.netw rote in message
news:be1391bf1c 1ee8c8ba8d6a850 af6@news.micros oft.com...

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=Micros oft.Jet.OLEDB.4 .0;Data Source=c:\;Exte nded Properties=Text ;
>
-Boo
>

>Why not just use a StreamReader class and parse the values at the
>commas?
>>
>"fniles" <fniles@pfmail. comwrote in message
>news:%23pruw%2 3K7GHA.4708@TK2 MSFTNGP05.phx.g bl...
>>

>>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 programmaticall y save the
>>CSV
>>file to an XLS file ?
>>Thanks a lot.
>>dim myCon OleDb.OleDbConn ection
>>myCon = New OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0; Data
>>Source=c:\fil e.csv; Extended Properties=""Ex cel 8.0; HDR=NO;
>>IMEX=1""")
>>--error "External table is not in the expected format."

>
>

[GhostInAK]

Re: How to open a .CSV file ?

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

You can use this technique to parse the file at any character, it
doesn't have to be the comma.
>
"GhostInAK" <paco@paco.netw rote in message
news:be1391bf1c 1ee8c8ba8d6a850 af6@news.micros oft.com...
>

>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=Micro soft.Jet.OLEDB. 4.0;Data Source=c:\;Exte nded
>Properties=Tex t;
>>
>-Boo
>>

>>Why not just use a StreamReader class and parse the values at the
>>commas?
>>>
>>"fniles" <fniles@pfmail. comwrote in message
>>news:%23pruw% 23K7GHA.4708@TK 2MSFTNGP05.phx. gbl...
>>>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 programmaticall y save the
>>>CSV
>>>file to an XLS file ?
>>>Thanks a lot.
>>>dim myCon OleDb.OleDbConn ection
>>>myCon = New OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0; Data
>>>Source=c:\fi le.csv; Extended Properties=""Ex cel 8.0; HDR=NO;
>>>IMEX=1""")
>>>--error "External table is not in the expected format."

[Scott M.]

Re: How to open a .CSV file ?

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.




"GhostInAK" <paco@paco.netw rote in message
news:be1391bf1c 5218c8bb6e9594e be3@news.micros oft.com...

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
>

>You can use this technique to parse the file at any character, it
>doesn't have to be the comma.
>>
>"GhostInAK" <paco@paco.netw rote in message
>news:be1391bf1 c1ee8c8ba8d6a85 0af6@news.micro soft.com...
>>

>>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=Micr osoft.Jet.OLEDB .4.0;Data Source=c:\;Exte nded
>>Properties=Te xt;
>>>
>>-Boo
>>>
>>>Why not just use a StreamReader class and parse the values at the
>>>commas?
>>>>
>>>"fniles" <fniles@pfmail. comwrote in message
>>>news:%23pruw %23K7GHA.4708@T K2MSFTNGP05.phx .gbl...
>>>>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 programmaticall y save the
>>>>CSV
>>>>file to an XLS file ?
>>>>Thanks a lot.
>>>>dim myCon OleDb.OleDbConn ection
>>>>myCon = New OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0; Data
>>>>Source=c:\f ile.csv; Extended Properties=""Ex cel 8.0; HDR=NO;
>>>>IMEX=1""" )
>>>>--error "External table is not in the expected format."

>
>

[Scott M.]

Re: How to open a .CSV file ?

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".


"GhostInAK" <paco@paco.netw rote in message
news:be1391bf1c 5218c8bb6e9594e be3@news.micros oft.com...

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
>

>You can use this technique to parse the file at any character, it
>doesn't have to be the comma.
>>
>"GhostInAK" <paco@paco.netw rote in message
>news:be1391bf1 c1ee8c8ba8d6a85 0af6@news.micro soft.com...
>>

>>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=Micr osoft.Jet.OLEDB .4.0;Data Source=c:\;Exte nded
>>Properties=Te xt;
>>>
>>-Boo
>>>
>>>Why not just use a StreamReader class and parse the values at the
>>>commas?
>>>>
>>>"fniles" <fniles@pfmail. comwrote in message
>>>news:%23pruw %23K7GHA.4708@T K2MSFTNGP05.phx .gbl...
>>>>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 programmaticall y save the
>>>>CSV
>>>>file to an XLS file ?
>>>>Thanks a lot.
>>>>dim myCon OleDb.OleDbConn ection
>>>>myCon = New OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0; Data
>>>>Source=c:\f ile.csv; Extended Properties=""Ex cel 8.0; HDR=NO;
>>>>IMEX=1""" )
>>>>--error "External table is not in the expected format."

>
>

[Michael D. Ober]

Re: How to open a .CSV file ?

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.Ole Db in VB 6. Yes, I could have used the VB 6 Excel Text
parser, but it isn't nearly as flexible as expected.

Mike.

"GhostInAK" <paco@paco.comw rote in message
news:c71747b44a f358c8bad9c2749 708@news.micros oft.com...

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.Ole Db namespace.
It's not a question. No answer is required. Fuckin amature crackheads.
>
-Boo
>

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).To String
'Debug.Assert(F alse, "Unknown index: " & index)
Return Nothing
End Get
End Property
Public Shadows Function ToString(Option al 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).T oString) & Delim
Else
sOut = sOut & """" & cRecs(i).ToStri ng & """" & Delim
End If
Next i
If IsNumeric(cRecs (i)) Then
sOut = sOut & Trim(Str(cRecs( i)))
Else
sOut = sOut & """" & cRecs(i).ToStri ng & """"
End If
Return sOut
End Function
End Class
"GhostInAK" <paco@paco.netw rote in message
news:be1391bf1c 1ee8c8ba8d6a850 af6@news.micros oft.com...

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=Micros oft.Jet.OLEDB.4 .0;Data

Source=c:\;Exte nded Properties=Text ;
>
-Boo
>
>Why not just use a StreamReader class and parse the values at the
>commas?
>>
>"fniles" <fniles@pfmail. comwrote in message
>news:%23pruw%2 3K7GHA.4708@TK2 MSFTNGP05.phx.g bl...
>>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 programmaticall y save the
>>CSV
>>file to an XLS file ?
>>Thanks a lot.
>>dim myCon OleDb.OleDbConn ection
>>myCon = New OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0; Data
>>Source=c:\fil e.csv; Extended Properties=""Ex cel 8.0; HDR=NO;
>>IMEX=1""")
>>--error "External table is not in the expected format."

>
>
>

[Jon Paal]

Re: How to open a .CSV file ?

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


"fniles" <fniles@pfmail. comwrote in message news:%23pruw%23 K7GHA.4708@TK2M SFTNGP05.phx.gb l...

>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 programmaticall y save the CSV file to an XLS file ?
Thanks a lot.
>
dim myCon OleDb.OleDbConn ection
myCon = New OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0; Data Source=c:\file. csv; Extended Properties=""Ex cel 8.0; HDR=NO;
IMEX=1""")
--error "External table is not in the expected format."
>
>

[GhostInAK]

Re: How to open a .CSV file ?

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

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".
>
"GhostInAK" <paco@paco.netw rote in message
news:be1391bf1c 5218c8bb6e9594e be3@news.micros oft.com...
>

>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
>>

>>You can use this technique to parse the file at any character, it
>>doesn't have to be the comma.
>>>
>>"GhostInAK" <paco@paco.netw rote in message
>>news:be1391bf 1c1ee8c8ba8d6a8 50af6@news.micr osoft.com...
>>>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=Mic rosoft.Jet.OLED B.4.0;Data Source=c:\;Exte nded
>>>Properties=T ext;
>>>>
>>>-Boo
>>>>
>>>>Why not just use a StreamReader class and parse the values at the
>>>>commas?
>>>>>
>>>>"fniles" <fniles@pfmail. comwrote in message
>>>>news:%23pru w%23K7GHA.4708@ TK2MSFTNGP05.ph x.gbl...
>>>>>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
>>>>>connecti on
>>>>>with no
>>>>>problem.
>>>>>What is the correct way to open a .CSV file ?
>>>>>If I can not open the CSV file, how can I programmaticall y save
>>>>>the
>>>>>CSV
>>>>>file to an XLS file ?
>>>>>Thanks a lot.
>>>>>dim myCon OleDb.OleDbConn ection
>>>>>myCon = New OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0;
>>>>>Data
>>>>>Source=c:\ file.csv; Extended Properties=""Ex cel 8.0; HDR=NO;
>>>>>IMEX=1"" ")
>>>>>--error "External table is not in the expected format."