473,382 Members | 1,736 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

Import CSV to Database

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
Jul 19 '05 #1
7 18635
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
Jul 19 '05 #2
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
Jul 19 '05 #3
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
Jul 19 '05 #4
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
Jul 19 '05 #5
> 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.

Jul 19 '05 #6
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 :-)
Jul 19 '05 #7
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 :-)

Jul 19 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Steve Holden | last post by:
I'm trying to load module code from a database, which stores for each module its full name, code, load date and a Boolean indicating whether it's a package or not. The following simple program:...
5
by: Steve Holden | last post by:
This is even stranger: it makes it if I import the module a second time: import dbimp as dbimp import sys if __name__ == "__main__": dbimp.install() #k = sys.modules.keys() #k.sort() #for...
1
by: Xif | last post by:
Hello Everyone! Here's a problem with relative imports: Suppose I have a package called some_package (in a separate directory included in the PYTHONPATH, with an __init__.py file etc.) This...
3
by: Doug Baroter | last post by:
Hi, One of my clients has the following situation. They use Access DB for data update etc. some business functions while they also want to view the Access data quickly and more efficiently in...
15
by: DesignGuy | last post by:
I've inherited a site that has 1000+ product pages that follow a similar layout. I would like to import these pages into a database for ease of future updating. The pages have the usual banners,...
2
by: David Berry | last post by:
Hi All. I'm looking for any help or sample code that can show me how to make a file import wizard in ASP.NET (VB preferred) like the one that MS Access uses. I'm working on a web site where the...
1
by: Dan | last post by:
Could someone please help me with auto importing a series of data files into an Access table. I tried to follow code given below in a previous messagebut i'm getting error messages. Here's my...
4
by: Steve Jorgensen | last post by:
I'm restarting this thread with a different focus. The project I'm working on now id coming along and will be made to work, and it's too late to start over with a new strategy. Still, I'm not...
3
by: Iavor Raytchev | last post by:
Hello, We a situation with a central database that contains the data that needs to be presented at N off-line terminals (N can be 5 000 can be 15 000). Each terminal presents unique data. The...
0
by: A3AN | last post by:
Hi. I receive a database backup on a daily basis. I then import this dump on another server which I use for software development. There is two db's being hosted on this server. We test software...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.