Login / Signup

Read/Write CSV file with Microsoft.Jet.OLEDB.4.0

 
Universal Data Connector
Last Post by AD 4 years ago
3 Posts
1 Users
0 Likes
46 Views
RSS
AD
 AD
(@a-degrearcinfo-com)
Posts: 34
Trusted Member
Topic starter
 

Since PcVue 12, Universal Data Connector is able to read/write data with a free OLEDB provider (not CDATA provider).
> Microsoft.Jet.OLEDB.4.0

This provider allow you to :
- read a CSV file like a database with SELECT queries
- write a CSV file (append mode only) with INSERT queries
- but it CAN'T update data values with UPDATE queries.

DOWNLOAD
If necessary you can download it here (take x86 version) : https://www.microsoft.com/en-us/download/details.aspx?id=54920

CONFIGURE PCVUE
Create a new SqlConnection with following parameters.

General tab
- Data source => Other
- Data provider => .Net Framework Data Provider for OleDb

Other tab
-Connection string => provider=Microsoft.Jet.OLEDB.4.0;text;database=E:MyFolder;fmt=CSVDelimited

Apply this configuration and test the connection
You should have the message "Connection successfuly established"

QUERY SAMPLES

! Caution !
When the default separators of the .csv are not the right ones, it is possible to force them by adding a "schema.ini" file to the CSV files with the following content:
[status.csv]
Format=Delimited(;)
DecimalSymbol=.
[status1.csv]
Format=Delimited(;)
DecimalSymbol=.
...

In following samples I use a file named "status.csv" with this content

Chrono;Value
04/12/2018 13:10:00;47
04/12/2018 13:11:00;10.1
04/12/2018 13:12:00;9.14
04/12/2018 13:13:00;50.15

Ex: to select all the fields where the value is bigger than 10

SELECT * FROM [status.csv] WHERE Value > 10 ORDER BY Chrono

Ex: to select and concat all the fields from 2 files (status.csv and status1.csv)

SELECT * FROM [status.csv] UNION SELECT * FROM [status1.csv]

Ex: to insert a new line in the file

INSERT INTO [status.csv]  VALUES('04/12/2018 15:52:00',336.47)

Enjoy !!!!

 
Posted : 05/12/2018 4:35 pm
AD
 AD
(@a-degrearcinfo-com)
Posts: 34
Trusted Member
Topic starter
 

To read a CSV file without header, you have to modify schema.ini file and add ColNameHeader parameter

[status.csv]
Format=Delimited(;)
DecimalSymbol=.
ColNameHeader=False

First field name is : F1
Second field name is : F2

SELECT F1,F2 FROM [status.csv]
 
Posted : 05/12/2018 7:55 pm
AD
 AD
(@a-degrearcinfo-com)
Posts: 34
Trusted Member
Topic starter
 

It is now preferable to use the ADO.NET provider offered by Arc Informatique.

 
Posted : 02/05/2022 6:59 pm
Forum Jump:
Next Topic