|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
VB.NET reading CSV files (odbc or oledb)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 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: Show quoteHide quote > 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: Show quoteHide quote > 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 Show quoteHide quote "Jesse Albert" <Jess.Alb***@gmail.com> wrote in message news:1145902645.423815.291600@i39g2000cwa.googlegroups.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 Show quoteHide quote "Jesse Albert" <Jess.Alb***@gmail.com> wrote in message news:1145902645.423815.291600@i39g2000cwa.googlegroups.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 Show quoteHide quote "james" <jjames700REMOV***@earthlink.net> wrote in message news:ejq94K$ZGHA.4248@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" <Jess.Alb***@gmail.com> wrote in message > news:1145902645.423815.291600@i39g2000cwa.googlegroups.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 >> > > 
Other interesting topics
Accessing Foxpro Database
Make font Bold Register DLL for COM interop vb.net 2005 - how to populate a listbox i need Code Generator... Performance testing tools for VB.NET application A couple of easy datagrid questions COM reference (X) as Interop.X Sql query..... Sum of another aggragate function DEALING WITH NULL QUERY RESULTS |
|||||||||||||||||||||||
