How to open a .CSV file ?
in [VB.NET]
|
Prev: Coversion problem
Next: Outlook PST Files
From: fniles on 10 Oct 2006 17:04 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."
From: Scott M. on 10 Oct 2006 17:57 Why not just use a StreamReader class and parse the values at the commas? "fniles" <fniles(a)pfmail.com> wrote in message news:%23pruw%23K7GHA.4708(a)TK2MSFTNGP05.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 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." > >
From: GhostInAK on 10 Oct 2006 18:13 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 > Why not just use a StreamReader class and parse the values at the > commas? > > "fniles" <fniles(a)pfmail.com> wrote in message > news:%23pruw%23K7GHA.4708(a)TK2MSFTNGP05.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 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."
From: Michael D. Ober on 10 Oct 2006 22:43 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 "GhostInAK" <paco(a)paco.net> wrote in message news:be1391bf1c1ee8c8ba8d6a850af6(a)news.microsoft.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=Microsoft.Jet.OLEDB.4.0;Data > Source=c:\;Extended Properties=Text; > > -Boo > > > Why not just use a StreamReader class and parse the values at the > > commas? > > > > "fniles" <fniles(a)pfmail.com> wrote in message > > news:%23pruw%23K7GHA.4708(a)TK2MSFTNGP05.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 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." > > >
From: Cor Ligthert [MVP] on 11 Oct 2006 01:03
> -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 |