473,322 Members | 1,566 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,322 software developers and data experts.

VB.NET reading CSV files (odbc or oledb)

Hiyo,
I'm trying to import a CSV file into a datatable using either ODBC or
OLEDB. One of the columns contains an IP Address. For some reason,
the IP address will not display correctly. All of the other
information in the CSV is accurate.

For example,
10.80.34.100 displays as 10.8034. All the IPs in the column are
displayed like this.

I'm not sure what is causing this error, I have tried using odbc and
the oledb drivers to load the csv and they both seem to cause that so
I'm not sure what I'm doing wrong. When I put quotes around the IP
addresses this fixes the issue, however I don't really want to use
quotes since I'm using commas for a seperate parsing script.

The code I use to load the data is (Right now it's using ODBC, the
commented codes are the oledb connection):

Dim sConnectionString As String = "Driver={Microsoft Text Driver
(*.txt; *.csv)};Dbq=" & Directory & ";Extensions=asc,csv,tab,txt;"
Dim objConn As New Odbc.OdbcConnection(sConnectionString)
'Dim sConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Directory &
";Extended Properties=""text;HDR=Yes;FMT=CSVDelimited"""
'Dim objConn As New
OleDb.OleDbConnection(sConnectionString)
objConn.Open()

Dim objCmdSelect As New Odbc.OdbcCommand("SELECT * FROM " &
FileName, objConn)
Dim objAdapter1 As New Odbc.OdbcDataAdapter

' Dim objCmdSelect As New OleDb.OleDbCommand("SELECT * FROM
" & FileName, objConn)
'Dim objAdapter1 As New OleDb.OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect

Dim objDataset1 As New DataSet()
objAdapter1.Fill(objDataset1, "Table1")
DataGridView1.DataSource =
objDataset1.Tables(0).DefaultView
objConn.Close()
An example line of the CSV is:
10.80.34.116,Name,Location

The datagridview would then display (columns seperated on newlines):
10.8034
Name
Location

Apr 24 '06 #1
5 23124
cj
It's seeing the ip address as a number. Somehow you need to designate
that it is a character field. Adding the "s around it does that but
there is probably another way. I'll be watching to see the solution myself.

Jesse Albert wrote:
Hiyo,
I'm trying to import a CSV file into a datatable using either ODBC or
OLEDB. One of the columns contains an IP Address. For some reason,
the IP address will not display correctly. All of the other
information in the CSV is accurate.

For example,
10.80.34.100 displays as 10.8034. All the IPs in the column are
displayed like this.

I'm not sure what is causing this error, I have tried using odbc and
the oledb drivers to load the csv and they both seem to cause that so
I'm not sure what I'm doing wrong. When I put quotes around the IP
addresses this fixes the issue, however I don't really want to use
quotes since I'm using commas for a seperate parsing script.

The code I use to load the data is (Right now it's using ODBC, the
commented codes are the oledb connection):

Dim sConnectionString As String = "Driver={Microsoft Text Driver
(*.txt; *.csv)};Dbq=" & Directory & ";Extensions=asc,csv,tab,txt;"
Dim objConn As New Odbc.OdbcConnection(sConnectionString)
'Dim sConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Directory &
";Extended Properties=""text;HDR=Yes;FMT=CSVDelimited"""
'Dim objConn As New
OleDb.OleDbConnection(sConnectionString)
objConn.Open()

Dim objCmdSelect As New Odbc.OdbcCommand("SELECT * FROM " &
FileName, objConn)
Dim objAdapter1 As New Odbc.OdbcDataAdapter

' Dim objCmdSelect As New OleDb.OleDbCommand("SELECT * FROM
" & FileName, objConn)
'Dim objAdapter1 As New OleDb.OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect

Dim objDataset1 As New DataSet()
objAdapter1.Fill(objDataset1, "Table1")
DataGridView1.DataSource =
objDataset1.Tables(0).DefaultView
objConn.Close()
An example line of the CSV is:
10.80.34.116,Name,Location

The datagridview would then display (columns seperated on newlines):
10.8034
Name
Location

Apr 24 '06 #2
You can use a schema.ini file to attempt to change this behavior,
http://support.microsoft.com/?kbid=210073 . If you get tired of the
crazyness with these drivers, you can optionally try using my parser
that I sell because of the awkwardness of these configurations,
http://www.csvreader.com .

Bruce Dunwiddie

cj wrote:
It's seeing the ip address as a number. Somehow you need to designate
that it is a character field. Adding the "s around it does that but
there is probably another way. I'll be watching to see the solution myself.

Jesse Albert wrote:
Hiyo,
I'm trying to import a CSV file into a datatable using either ODBC or
OLEDB. One of the columns contains an IP Address. For some reason,
the IP address will not display correctly. All of the other
information in the CSV is accurate.

For example,
10.80.34.100 displays as 10.8034. All the IPs in the column are
displayed like this.

I'm not sure what is causing this error, I have tried using odbc and
the oledb drivers to load the csv and they both seem to cause that so
I'm not sure what I'm doing wrong. When I put quotes around the IP
addresses this fixes the issue, however I don't really want to use
quotes since I'm using commas for a seperate parsing script.

The code I use to load the data is (Right now it's using ODBC, the
commented codes are the oledb connection):

Dim sConnectionString As String = "Driver={Microsoft Text Driver
(*.txt; *.csv)};Dbq=" & Directory & ";Extensions=asc,csv,tab,txt;"
Dim objConn As New Odbc.OdbcConnection(sConnectionString)
'Dim sConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Directory &
";Extended Properties=""text;HDR=Yes;FMT=CSVDelimited"""
'Dim objConn As New
OleDb.OleDbConnection(sConnectionString)
objConn.Open()

Dim objCmdSelect As New Odbc.OdbcCommand("SELECT * FROM " &
FileName, objConn)
Dim objAdapter1 As New Odbc.OdbcDataAdapter

' Dim objCmdSelect As New OleDb.OleDbCommand("SELECT * FROM
" & FileName, objConn)
'Dim objAdapter1 As New OleDb.OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect

Dim objDataset1 As New DataSet()
objAdapter1.Fill(objDataset1, "Table1")
DataGridView1.DataSource =
objDataset1.Tables(0).DefaultView
objConn.Close()
An example line of the CSV is:
10.80.34.116,Name,Location

The datagridview would then display (columns seperated on newlines):
10.8034
Name
Location


Apr 24 '06 #3
One way to see what is causing the problem is to try (as a test) importing
the CSV file into a Access Database table, using Access itself. If it does
the same thing there, you will at least get a good error message.
Otherwise,,,,,,,
In your connection string in your code change this: FMT=CSVDelimited"""
to: FTM=Delimited\"""
The rest of your code looks fine. Just did a test making the changes I
mentioned and it works fine.
james

"Jesse Albert" <Je*********@gmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...
Hiyo,
I'm trying to import a CSV file into a datatable using either ODBC or
OLEDB. One of the columns contains an IP Address. For some reason,
the IP address will not display correctly. All of the other
information in the CSV is accurate.

For example,
10.80.34.100 displays as 10.8034. All the IPs in the column are
displayed like this.

I'm not sure what is causing this error, I have tried using odbc and
the oledb drivers to load the csv and they both seem to cause that so
I'm not sure what I'm doing wrong. When I put quotes around the IP
addresses this fixes the issue, however I don't really want to use
quotes since I'm using commas for a seperate parsing script.

The code I use to load the data is (Right now it's using ODBC, the
commented codes are the oledb connection):

Dim sConnectionString As String = "Driver={Microsoft Text Driver
(*.txt; *.csv)};Dbq=" & Directory & ";Extensions=asc,csv,tab,txt;"
Dim objConn As New Odbc.OdbcConnection(sConnectionString)
'Dim sConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Directory &
";Extended Properties=""text;HDR=Yes;FMT=CSVDelimited"""
'Dim objConn As New
OleDb.OleDbConnection(sConnectionString)
objConn.Open()

Dim objCmdSelect As New Odbc.OdbcCommand("SELECT * FROM " &
FileName, objConn)
Dim objAdapter1 As New Odbc.OdbcDataAdapter

' Dim objCmdSelect As New OleDb.OleDbCommand("SELECT * FROM
" & FileName, objConn)
'Dim objAdapter1 As New OleDb.OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect

Dim objDataset1 As New DataSet()
objAdapter1.Fill(objDataset1, "Table1")
DataGridView1.DataSource =
objDataset1.Tables(0).DefaultView
objConn.Close()
An example line of the CSV is:
10.80.34.116,Name,Location

The datagridview would then display (columns seperated on newlines):
10.8034
Name
Location

Apr 24 '06 #4
Forgot to mention one other thing, your date in the CSV file needs to be
enclosed in quotes:
"IPADDRESS","NAME","LOCATION"
"192.168.0.3","JOE","HERE"

otherwise, if you leave off the quotes, Access will strip off everything
past the second decimal point ( .0.3)
james

"Jesse Albert" <Je*********@gmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...
Hiyo,
I'm trying to import a CSV file into a datatable using either ODBC or
OLEDB. One of the columns contains an IP Address. For some reason,
the IP address will not display correctly. All of the other
information in the CSV is accurate.

For example,
10.80.34.100 displays as 10.8034. All the IPs in the column are
displayed like this.

I'm not sure what is causing this error, I have tried using odbc and
the oledb drivers to load the csv and they both seem to cause that so
I'm not sure what I'm doing wrong. When I put quotes around the IP
addresses this fixes the issue, however I don't really want to use
quotes since I'm using commas for a seperate parsing script.

The code I use to load the data is (Right now it's using ODBC, the
commented codes are the oledb connection):

Dim sConnectionString As String = "Driver={Microsoft Text Driver
(*.txt; *.csv)};Dbq=" & Directory & ";Extensions=asc,csv,tab,txt;"
Dim objConn As New Odbc.OdbcConnection(sConnectionString)
'Dim sConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Directory &
";Extended Properties=""text;HDR=Yes;FMT=CSVDelimited"""
'Dim objConn As New
OleDb.OleDbConnection(sConnectionString)
objConn.Open()

Dim objCmdSelect As New Odbc.OdbcCommand("SELECT * FROM " &
FileName, objConn)
Dim objAdapter1 As New Odbc.OdbcDataAdapter

' Dim objCmdSelect As New OleDb.OleDbCommand("SELECT * FROM
" & FileName, objConn)
'Dim objAdapter1 As New OleDb.OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect

Dim objDataset1 As New DataSet()
objAdapter1.Fill(objDataset1, "Table1")
DataGridView1.DataSource =
objDataset1.Tables(0).DefaultView
objConn.Close()
An example line of the CSV is:
10.80.34.116,Name,Location

The datagridview would then display (columns seperated on newlines):
10.8034
Name
Location

Apr 24 '06 #5
Let me try to type this a bit slower this time, your DATA in the CSV file
needs to be enclosed in quotes:
(then the rest of my example)
Sorry about that, been several hours since my last cup of coffee and I'm
having accuracy problems now!!! :-)
james

"james" <jj***************@earthlink.net> wrote in message
news:ej**************@TK2MSFTNGP05.phx.gbl...
Forgot to mention one other thing, your date in the CSV file needs to be
enclosed in quotes:
"IPADDRESS","NAME","LOCATION"
"192.168.0.3","JOE","HERE"

otherwise, if you leave off the quotes, Access will strip off everything
past the second decimal point ( .0.3)
james

"Jesse Albert" <Je*********@gmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...
Hiyo,
I'm trying to import a CSV file into a datatable using either ODBC or
OLEDB. One of the columns contains an IP Address. For some reason,
the IP address will not display correctly. All of the other
information in the CSV is accurate.

For example,
10.80.34.100 displays as 10.8034. All the IPs in the column are
displayed like this.

I'm not sure what is causing this error, I have tried using odbc and
the oledb drivers to load the csv and they both seem to cause that so
I'm not sure what I'm doing wrong. When I put quotes around the IP
addresses this fixes the issue, however I don't really want to use
quotes since I'm using commas for a seperate parsing script.

The code I use to load the data is (Right now it's using ODBC, the
commented codes are the oledb connection):

Dim sConnectionString As String = "Driver={Microsoft Text Driver
(*.txt; *.csv)};Dbq=" & Directory & ";Extensions=asc,csv,tab,txt;"
Dim objConn As New Odbc.OdbcConnection(sConnectionString)
'Dim sConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Directory &
";Extended Properties=""text;HDR=Yes;FMT=CSVDelimited"""
'Dim objConn As New
OleDb.OleDbConnection(sConnectionString)
objConn.Open()

Dim objCmdSelect As New Odbc.OdbcCommand("SELECT * FROM " &
FileName, objConn)
Dim objAdapter1 As New Odbc.OdbcDataAdapter

' Dim objCmdSelect As New OleDb.OleDbCommand("SELECT * FROM
" & FileName, objConn)
'Dim objAdapter1 As New OleDb.OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect

Dim objDataset1 As New DataSet()
objAdapter1.Fill(objDataset1, "Table1")
DataGridView1.DataSource =
objDataset1.Tables(0).DefaultView
objConn.Close()
An example line of the CSV is:
10.80.34.116,Name,Location

The datagridview would then display (columns seperated on newlines):
10.8034
Name
Location


Apr 25 '06 #6

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

Similar topics

6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
6
by: serge calderara | last post by:
Dear all, Does any one have any idea why an sql statment with INNER JOIN syntax is working well with odbcprovider but not with Oledbprovider when accessing an access 2000 database? here is...
2
by: Roland Hall | last post by:
I have two(2) issues. I'm experiencing a little difficulty and having to resort to a work around. I already found one bug, although stated the bug was only in ODBC, which I'm not using. It...
14
by: Roland Hall | last post by:
I have two(2) issues. I'm experiencing a little difficulty and having to resort to a work around. I already found one bug, although stated the bug was only in ODBC, which I'm not using. It...
3
by: Roland Hall | last post by:
Three times the charm? Sorry for the repost. Trying to get my account right. I have two(2) issues. I'm experiencing a little difficulty and having to resort to a work around. I already...
4
by: Andreas Lauffer | last post by:
Can anyone tell me advantages / disadvantages of DataDirect Server Wire ODBC-driver? Any experiences? What about redistribution? Andreas Lauffer, easySoft. GmbH, Germany
1
by: JoeBobHankey | last post by:
Background: - I'm running MSDE 2000 (not client tools, stored procedure capability, etc). This may change, but not in the first part of development. - My Access file is an Access 2002 project...
3
by: James | last post by:
Hi, I'm importing some csv files with this code /// start of code snippet int iPos = strFileName.LastIndexOf(@"\"); string strPath = strFileName.Substring(0,iPos); string strSelect =...
9
by: dba123 | last post by:
I need some help and direction on what classes and an example or two (article) on how to read an Excel Worksheet and insert one column into a database table column. I am using .NET 2.0 only. What...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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...

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.