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."
22  5008 
Why not just use a StreamReader class and parse the values at the commas?
"fniles" <fn****@pfmail. comwrote in message
news:%2******** **********@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."
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" <fn****@pfmail. comwrote in message
news:%2******** **********@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."
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" <pa**@paco.netw rote in message
news:be******** *************** ***@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" <fn****@pfmail. comwrote in message
news:%2******** **********@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."
-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
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" <pa**@paco.netw rote in message
news:be******** *************** ***@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" <fn****@pfmail. comwrote in message
news:%2****** ************@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."
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
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" <pa**@paco.coms chreef in bericht
news:c7******** *************** ***@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
You can use this technique to parse the file at any character, it doesn't
have to be the comma.
"GhostInAK" <pa**@paco.netw rote in message
news:be******** *************** ***@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" <fn****@pfmail. comwrote in message
news:%2******* ***********@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."
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" <pa**@paco.netw rote in message
news:be******** *************** ***@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" <fn****@pfmail. comwrote in message
news:%2****** ************@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."
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: yanivmad |
last post by:
hi
I like to ask if there any option to control the html files?!?
I have some HTML files at my web site that use
one "Help.htm" file for all the pages {I use the
"window.open(.... " option at JS },
and I like to open only one file every time for all the files.
I use "myFunc.js" javascript file for all the files that try to open the
"Help.htm" file so I hava an 'Object' that hold the
|
by: Charles F McDevitt |
last post by:
I'm trying to upgrade some old code that used old iostreams.
At one place in the code, I have a path/filename in a wchar_t string
(unicode utf-16).
I need to open an ifstream to that file. But the open() on ifstream only
takes char * strings (mbcs?).
In old iostreams, I could _wopen() the file, get the filedesc, and call
attach() on the ifstream.
|
by: Dino Buljubasic |
last post by:
My application creates some temporary files that are deleted when my
application terminates.
However, if a temp file is open, it will not be deleted and
application will crash.
How can I check if a file is open before deleting it
Something like this
|
by: Mattbooty |
last post by:
Hello,
Not sure if anyone else has seen this bug, but I have a form where
the entire form is covered with a picturebox. The picturebox has a
mouseup event. I also have an open file dialog for loading images into
the picturebox. If you double click the file you want to open in the
open file dialog, it somehow interperets one of the clicks as a mouseup
on the picturebox and fires the mouseup event for the picturebox. How
can I get...
|
by: qysbc |
last post by:
I have a web page and there is a link to open a TIFF file. The way I do it is
to have the server code open a binary stream, set the content type to
"image/tiff" and call Response.BinaryWrite. On the client machine, the file
type TIFF is associated with Kodak Imaging Preview. This app works on most
client machines. When you click on the link, Kodak Imaging Preview will open
the TIFF file on the client machine. However, on some machines, the...
| | |
by: OutdoorGuy |
last post by:
Greetings,
I have a "newbie" question in relation to opening files from C#. I have
a Windows form where I allow the user to type in a file extension in a
text box (e.g., "xls"). I then take that extension and use that as my
filter criteria for the File Open dialog.
Once the user selects a file with that extension (from the File Open
dialog), I simply want to open that file (whether it is an .xls file,
.txt file, etc.). I am...
|
by: Moumen VB.NET 2003/2005 Developer |
last post by:
How can I detect if a file sitting on a network drive is still open by another
application?
This application resides on another machine on the network?
I am using VB.NET 2003
Your help is highly appreciated
|
by: Ryan Liu |
last post by:
Hi,
Both way works, I'd just ask some experts which way is better?
My application creates a log file daily.
Now each time when I write a log, I will open the file and append to the
end. Ocz, if the file is not exist(e.g. another day), it will creates the
file first.
|
by: Ros |
last post by:
There are 10 files in the folder. I wish to process all the files one
by one. But if the files are open or some processing is going on them
then I do not want to disturb that process. In that case I would
ignore processing that particular file and move to next file.
How can I check whether the file is open or not?
I tried os.stat and os.access but I am not getting the expected
results.
Also I checked in IO exceptions, IO error handler...
|
by: Ofelia |
last post by:
Hi,
I'm new to this forum and to Perl language but I would like to ask for your help.
I'm working in Linux and the files I need to process are in the format “file.gz”.
I created a script which should decompress, open and then delete nearly 400 files. To do so I use "open FILEPT, "zcat $filename|"".
In the beginning the script works fine, but after about 300 files processed I get an error on Open function:
“proc: Could not open file...
|
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...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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...
|
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();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
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...
| |
