Import CSV to Database

**Veign** · Jul 19 '05, 01:01 PM

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

Error - Office.com

http://support.microsoft.com/default.aspx?scid=kb;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/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

--
Chris Hanscom
MVP (Visual Basic)

Veign | Columbia SC

http://www.veign.com

Veign, Columbia. 119 likes. A South Carolina development company whose primary focus is to leverage the power of machine learning to better understand your data. From image and object detection to...

--

"Ashish Kanoongo" <ashishk@armour .com> wrote in message
news:uEA6ARrXEH A.2716@tk2msftn gp13.phx.gbl...
I am using following connectstring and it works with CSV delimmited file

ConnectionStrin g = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""te xt;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

ConnectionStrin g = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""te xt;HDR=YES;FMT= Delimited(^)"""

ConnectionStrin g = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""te xt;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

**Ashish Kanoongo** · Jul 19 '05, 01:01 PM

Re: Import CSV to Database

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

[Copy of 20040630SportRe latedItems.csv]
Format=Delimite d(^)

"Veign" <NOSPAMinveign@ veign.com> wrote in message
news:e9V04WrXEH A.2908@TK2MSFTN GP10.phx.gbl...[color=blue]
> 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.[/color]
For[color=blue]
> 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):
>[/color]
http://msdn.microsoft.com/library/de...a_ini_file.asp[color=blue]
>
> --
> Chris Hanscom
> MVP (Visual Basic)
> http://www.veign.com
> --
>
> "Ashish Kanoongo" <ashishk@armour .com> wrote in message
> news:uEA6ARrXEH A.2716@tk2msftn gp13.phx.gbl...
> I am using following connectstring and it works with CSV delimmited file
>
> ConnectionStrin g = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
> "Data Source=" & App.Path & ";" & _
> "Extended Properties=""te xt;HDR=YES;FMT= FixedLength"""
>
> But when I delimited my file by caret(^) character rather than comma(,),[/color]
it[color=blue]
> wont work. I tried various example as followed, but nothing is working
>
> ConnectionStrin g = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
> "Data Source=" & App.Path & ";" & _
> "Extended Properties=""te xt;HDR=YES;FMT= Delimited(^)"""
>
> ConnectionStrin g = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
> "Data Source=" & App.Path & ";" & _
> "Extended Properties=""te xt;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
>
>[/color]

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

**Ashish Kanoongo** · Jul 19 '05, 01:01 PM

Re: Import CSV to Database

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

"Veign" <NOSPAMinveign@ veign.com> wrote in message
news:e9V04WrXEH A.2908@TK2MSFTN GP10.phx.gbl...[color=blue]
> 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.[/color]
For[color=blue]
> 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):
>[/color]
http://msdn.microsoft.com/library/de...a_ini_file.asp[color=blue]
>
> --
> Chris Hanscom
> MVP (Visual Basic)
> http://www.veign.com
> --
>
> "Ashish Kanoongo" <ashishk@armour .com> wrote in message
> news:uEA6ARrXEH A.2716@tk2msftn gp13.phx.gbl...
> I am using following connectstring and it works with CSV delimmited file
>
> ConnectionStrin g = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
> "Data Source=" & App.Path & ";" & _
> "Extended Properties=""te xt;HDR=YES;FMT= FixedLength"""
>
> But when I delimited my file by caret(^) character rather than comma(,),[/color]
it[color=blue]
> wont work. I tried various example as followed, but nothing is working
>
> ConnectionStrin g = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
> "Data Source=" & App.Path & ";" & _
> "Extended Properties=""te xt;HDR=YES;FMT= Delimited(^)"""
>
> ConnectionStrin g = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
> "Data Source=" & App.Path & ";" & _
> "Extended Properties=""te xt;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
>
>[/color]

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

**Ashish Kanoongo** · Jul 19 '05, 01:01 PM

Re: Import CSV to Database

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

"Ashish Kanoongo" <ashishk@armour .com> wrote in message
news:eaSTtprXEH A.3796@TK2MSFTN GP11.phx.gbl...[color=blue]
> If I change caret(^) in search.ini also in csv files, it works perfectly,
> why it is not working with caret(^).
>
> "Veign" <NOSPAMinveign@ veign.com> wrote in message
> news:e9V04WrXEH A.2908@TK2MSFTN GP10.phx.gbl...[color=green]
> > 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[/color][/color]
cannot[color=blue][color=green]
> > define all characteristics of a text file through the connection string.[/color]
> For[color=green]
> > 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):
> >[/color]
>[/color]
http://msdn.microsoft.com/library/de...a_ini_file.asp[color=blue][color=green]
> >
> > --
> > Chris Hanscom
> > MVP (Visual Basic)
> > http://www.veign.com
> > --
> >
> > "Ashish Kanoongo" <ashishk@armour .com> wrote in message
> > news:uEA6ARrXEH A.2716@tk2msftn gp13.phx.gbl...
> > I am using following connectstring and it works with CSV delimmited file
> >
> > ConnectionStrin g = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
> > "Data Source=" & App.Path & ";" & _
> > "Extended Properties=""te xt;HDR=YES;FMT= FixedLength"""
> >
> > But when I delimited my file by caret(^) character rather than comma(,),[/color]
> it[color=green]
> > wont work. I tried various example as followed, but nothing is working
> >
> > ConnectionStrin g = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
> > "Data Source=" & App.Path & ";" & _
> > "Extended Properties=""te xt;HDR=YES;FMT= Delimited(^)"""
> >
> > ConnectionStrin g = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
> > "Data Source=" & App.Path & ";" & _
> > "Extended Properties=""te xt;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
> >
> >[/color]
>
>
> ---
> 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
>
>[/color]

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

**Russ Holsclaw** · Jul 19 '05, 01:02 PM

Re: Import CSV to Database

> I changed caret(^) to semicolon(;), it works.[color=blue]
>
> "Ashish Kanoongo" <ashishk@armour .com> wrote in message
> news:eaSTtprXEH A.3796@TK2MSFTN GP11.phx.gbl...[color=green]
> > If I change caret(^) in search.ini also in csv files, it works[/color][/color]
perfectly,[color=blue][color=green]
> > why it is not working with caret(^).[/color][/color]

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.

**Larry Rebich** · Jul 19 '05, 01:03 PM

Re: Import CSV to Database

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

**Willie Bodger** · Jul 19 '05, 01:03 PM

Re: Import CSV to Database

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

wb

"Larry Rebich" <lrebich_nospam @earthlink.net> wrote in message
news:8eXEc.1358 $oD3.440@newsre ad1.news.pas.ea rthlink.net...[color=blue]
> 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[/color]
parse[color=blue]
> 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 :-)
>
>[/color]

Import CSV to Database

Import CSV to Database

Comment

Comment

Comment

Comment

Comment

Comment

Comment