Import CSV to Database

See the very bottom of the page:
HOW TO: Use Jet OLE DB Provider 4.0 to Connect to ISAM Databases
http://support.microsoft.com/default...b;en-us;326548

<quote>
The Text ISAM permits you to handle multiple text file formats. You cannot
define all characteristics of a text file through the connection string. For
example, if you want to open a fixed-width file, or you want to use a
delimiter other than the comma, you must specify all these settings in a
Schema.INI file
</quote>

Schema.ini File (Text File Driver):
http://msdn.microsoft.com/library/de...a_ini_file.asp

--
Chris Hanscom
MVP (Visual Basic)
http://www.veign.com
--

"Ashish Kanoongo" <as*****@armour.com> wrote in message
news:uE**************@tk2msftngp13.phx.gbl...
I am using following connectstring and it works with CSV delimmited file

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""text;HDR=YES;FMT=FixedLength"""

But when I delimited my file by caret(^) character rather than comma(,), it
wont work. I tried various example as followed, but nothing is working

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited(^)"""

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""text;HDR=YES;FMT=CustomDelimited(^)"" "

Let me know whats wrong I am doing?
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.712 / Virus Database: 468 - Release Date: 06/27/2004

Schema.ini is already in the same folder. Here is ini file content

[Copy of 20040630SportRelatedItems.csv]
Format=Delimited(^)
"Veign" <NO***********@veign.com> wrote in message
news:e9**************@TK2MSFTNGP10.phx.gbl...

See the very bottom of the page:
HOW TO: Use Jet OLE DB Provider 4.0 to Connect to ISAM Databases
http://support.microsoft.com/default...b;en-us;326548

<quote>
The Text ISAM permits you to handle multiple text file formats. You cannot
define all characteristics of a text file through the connection string. For example, if you want to open a fixed-width file, or you want to use a
delimiter other than the comma, you must specify all these settings in a
Schema.INI file
</quote>

Schema.ini File (Text File Driver):
http://msdn.microsoft.com/library/de...a_ini_file.asp
--
Chris Hanscom
MVP (Visual Basic)
http://www.veign.com
--

"Ashish Kanoongo" <as*****@armour.com> wrote in message
news:uE**************@tk2msftngp13.phx.gbl...
I am using following connectstring and it works with CSV delimmited file

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""text;HDR=YES;FMT=FixedLength"""

But when I delimited my file by caret(^) character rather than comma(,), it wont work. I tried various example as followed, but nothing is working

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited(^)"""

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""text;HDR=YES;FMT=CustomDelimited(^)"" "

Let me know whats wrong I am doing?
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.712 / Virus Database: 468 - Release Date: 06/27/2004

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.712 / Virus Database: 468 - Release Date: 06/27/2004

If I change caret(^) in search.ini also in csv files, it works perfectly,
why it is not working with caret(^).

"Veign" <NO***********@veign.com> wrote in message
news:e9**************@TK2MSFTNGP10.phx.gbl...

See the very bottom of the page:
HOW TO: Use Jet OLE DB Provider 4.0 to Connect to ISAM Databases
http://support.microsoft.com/default...b;en-us;326548

<quote>
The Text ISAM permits you to handle multiple text file formats. You cannot
define all characteristics of a text file through the connection string. For example, if you want to open a fixed-width file, or you want to use a
delimiter other than the comma, you must specify all these settings in a
Schema.INI file
</quote>

Schema.ini File (Text File Driver):
http://msdn.microsoft.com/library/de...a_ini_file.asp
--
Chris Hanscom
MVP (Visual Basic)
http://www.veign.com
--

"Ashish Kanoongo" <as*****@armour.com> wrote in message
news:uE**************@tk2msftngp13.phx.gbl...
I am using following connectstring and it works with CSV delimmited file

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""text;HDR=YES;FMT=FixedLength"""

But when I delimited my file by caret(^) character rather than comma(,), it wont work. I tried various example as followed, but nothing is working

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited(^)"""

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""text;HDR=YES;FMT=CustomDelimited(^)"" "

Let me know whats wrong I am doing?
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.712 / Virus Database: 468 - Release Date: 06/27/2004

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.712 / Virus Database: 468 - Release Date: 06/27/2004

I changed caret(^) to semicolon(;), it works.

"Ashish Kanoongo" <as*****@armour.com> wrote in message
news:ea**************@TK2MSFTNGP11.phx.gbl...

If I change caret(^) in search.ini also in csv files, it works perfectly,
why it is not working with caret(^).

"Veign" <NO***********@veign.com> wrote in message
news:e9**************@TK2MSFTNGP10.phx.gbl...

See the very bottom of the page:
HOW TO: Use Jet OLE DB Provider 4.0 to Connect to ISAM Databases
http://support.microsoft.com/default...b;en-us;326548

<quote>
The Text ISAM permits you to handle multiple text file formats. You cannot define all characteristics of a text file through the connection string.

For

example, if you want to open a fixed-width file, or you want to use a
delimiter other than the comma, you must specify all these settings in a
Schema.INI file
</quote>

Schema.ini File (Text File Driver):

http://msdn.microsoft.com/library/de...a_ini_file.asp

--
Chris Hanscom
MVP (Visual Basic)
http://www.veign.com
--

"Ashish Kanoongo" <as*****@armour.com> wrote in message
news:uE**************@tk2msftngp13.phx.gbl...
I am using following connectstring and it works with CSV delimmited file

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""text;HDR=YES;FMT=FixedLength"""

But when I delimited my file by caret(^) character rather than comma(,),

it

wont work. I tried various example as followed, but nothing is working

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited(^)"""

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""text;HDR=YES;FMT=CustomDelimited(^)"" "

Let me know whats wrong I am doing?
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.712 / Virus Database: 468 - Release Date: 06/27/2004

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.712 / Virus Database: 468 - Release Date: 06/27/2004

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.712 / Virus Database: 468 - Release Date: 06/27/2004

> I changed caret(^) to semicolon(;), it works.

"Ashish Kanoongo" <as*****@armour.com> wrote in message
news:ea**************@TK2MSFTNGP11.phx.gbl...

If I change caret(^) in search.ini also in csv files, it works perfectly, why it is not working with caret(^).

Although MS documentation doesn't say, I wonder if perhaps they allow
control codes to be specified as delimiters using the traditional notation
of using a carat to indicate a control code, as in (^A) to indicate Ctrl+A.
It might be that your carat is being misinterpreted by the program parsing
the .ini file.

If that's true, then you'll have to guess whether they provide any
syntactical "escape" to have the caret be interpreted literally. You might
try (^^) or perhaps (\^) or something of the sort.

It wouldn't be the first time that Microsoft failed to provide the full
details about the syntax of commands or parameters. There is much
incompetence and carelessness evident in Microsoft documentation these
days. I suspect it's because they delegate documentation to English-major
types who don't know enough to ask developers for more precise information.

I've been programming for 37 years, and I can assure you, without
hesitation, that the quality of programming documentation is worse now than
I've ever seen before.

I think it's wonderful that Bill Gates is donating so much of his fortune
to improving education. I wish he would direct more of it inside his own
company.

Some months ago I wrote a tip of the month that I call 'Using ADO to Read
and Parse a Text File'. Link to http://www.buygold.net/tips then look for
the April 2002 tip of the month. A sample program is provided. From the
tip's intro:

ADO [Active Data Objects] can be used to read a variety of file formats. I
have used it to read and parse a CSV and fixed width formatted text file.
ADO reads and parses the file into a recordset. When I first needed to parse
a text file I tried various routines - some provided via other users - and
none seemed satisfactory. So I did some research and discovered that ADO
will perform the task. I've revisited the code, wrote a demo program and
made this the April 2002 tip-of-the-month.

Cheers,
Larry Rebich

More tips link to:
http://www.buygold.net/tips

Please:
No personal e-mail questions :-)

Interesting, can this be tweaked to work with VBScript from a web UI, do you
know?

wb

"Larry Rebich" <lr************@earthlink.net> wrote in message
news:8e****************@newsread1.news.pas.earthli nk.net...

Some months ago I wrote a tip of the month that I call 'Using ADO to Read
and Parse a Text File'. Link to http://www.buygold.net/tips then look for
the April 2002 tip of the month. A sample program is provided. From the
tip's intro:

ADO [Active Data Objects] can be used to read a variety of file formats. I
have used it to read and parse a CSV and fixed width formatted text file.
ADO reads and parses the file into a recordset. When I first needed to parse a text file I tried various routines - some provided via other users - and
none seemed satisfactory. So I did some research and discovered that ADO
will perform the task. I've revisited the code, wrote a demo program and
made this the April 2002 tip-of-the-month.

Cheers,
Larry Rebich

More tips link to:
http://www.buygold.net/tips

Please:
No personal e-mail questions :-)