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 5 23124
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
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
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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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
|
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...
|
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 =...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
|
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...
| |