Importing a CSV into a Dataset (preferably. with VB.NET 2005)

S

Scott Lyon

Is there an easy way to import the contents of a CSV text file into a
Dataset, preferably using VB.NET (2005)?


Here's the clincher - I cannot use code I already found out there that
simply splits the lines based on commas in each line, as there are cases
where there may be a comma within a string (surrounded by quotes), and I
don't want to split on that.


Here's the first few rows of the data to give you an idea:


"Subscriber Number","Statement Date","Charge Type","Charge
Description","Amount"
"555-555-5555","02/11/2008","LOCAL AIRTIME, LONG DISTANCE and INTERNATIONAL
CHARGES","","$0.00"
"555-555-5555","02/11/2008","Taxes","","$8.60"
"555-555-5555","02/11/2008","WorldClass Int'l Rate","General","$0.00"
"555-555-5555","02/11/2008","BB BIS&BES AddOn","CUSTOM DATA PACKAGE","$(3.60)"


I also am not having any luck with the OLEDB examples out there too, as my
filename could have spaces in it (for example the current one is "Statement
Detail.csv"), and trying to do a "select * from Statement Detail.csv fails
every time.


Any ideas/suggestions?
 
K

Kerry Moorman

Scott,

For the space in the filename problem you might try enclosing the filename
in square brackets:

select * from [Statement Detail.csv]

This works in similar situations, but I have not tried it for this
particular situation.

Kerry Moorman
 
P

Paul Clement

¤ Is there an easy way to import the contents of a CSV text file into a
¤ Dataset, preferably using VB.NET (2005)?
¤
¤
¤ Here's the clincher - I cannot use code I already found out there that
¤ simply splits the lines based on commas in each line, as there are cases
¤ where there may be a comma within a string (surrounded by quotes), and I
¤ don't want to split on that.
¤
¤
¤ Here's the first few rows of the data to give you an idea:
¤
¤
¤ "Subscriber Number","Statement Date","Charge Type","Charge
¤ Description","Amount"
¤ "555-555-5555","02/11/2008","LOCAL AIRTIME, LONG DISTANCE and INTERNATIONAL
¤ CHARGES","","$0.00"
¤ "555-555-5555","02/11/2008","Taxes","","$8.60"
¤ "555-555-5555","02/11/2008","WorldClass Int'l Rate","General","$0.00"
¤ "555-555-5555","02/11/2008","BB BIS&BES AddOn","CUSTOM DATA PACKAGE","$(3.60)"
¤
¤
¤ I also am not having any luck with the OLEDB examples out there too, as my
¤ filename could have spaces in it (for example the current one is "Statement
¤ Detail.csv"), and trying to do a "select * from Statement Detail.csv fails
¤ every time.

In addition to what Kerry said, you shouldn't have any issue with embedded commas as long as the
text is surrounded by double quotes.

Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "C:\Documents and Settings\...\My Documents\My Database\Text" & ";" & _
"Extended Properties=""Text;HDR=Yes"""

Dim TextConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
TextConnection.Open()

Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [Statement Detail.txt]",
TextConnection)

Dim ds As New DataSet("TextFiles")
da.Fill(ds, "Statement Detail")


Paul
~~~~
Microsoft MVP (Visual Basic)
 
W

William Vaughn \(MVP\)

I might try importing it into SQL Server Express (using SqlBulkCopy) and
letting SSIS/BCP do the conversion work.
 
S

sherifffruitfly

Is there an easy way to import the contents of a CSV text file into a
Dataset, preferably using VB.NET (2005)?

Here's the clincher - I cannot use code I already found out there that
simply splits the lines based on commas in each line, as there are cases
where there may be a comma within a string (surrounded by quotes), and I
don't want to split on that.

Here's the first few rows of the data to give you an idea:

"Subscriber Number","Statement Date","Charge Type","Charge
Description","Amount"
"555-555-5555","02/11/2008","LOCAL AIRTIME, LONG DISTANCE and INTERNATIONAL
CHARGES","","$0.00"
"555-555-5555","02/11/2008","Taxes","","$8.60"
"555-555-5555","02/11/2008","WorldClass Int'l Rate","General","$0.00"
"555-555-5555","02/11/2008","BB BIS&BES AddOn","CUSTOM DATA PACKAGE","$(3.60)"

I also am not having any luck with the OLEDB examples out there too, as my
filename could have spaces in it (for example the current one is "Statement
Detail.csv"), and trying to do a "select * from Statement Detail.csv fails
every time.

Any ideas/suggestions?
Click to expand...

I like this codeproject csv reader:

http://69.10.233.10/KB/database/CsvReader.aspx

(I have no idea why the link is with ip rather than name)

It's in c#, but I would imagine that its use in a vb project would be
as easy as adding a reference to a dll.


Good luck!
 
H

HillBilly

When CSV data is "dirty" as in this form:
"Bill, Mary and Eddie","Apples","Oranges","Grapes"

You will need a regular expression to find and replace the commas that
delineate "Bill, Mary and Eddie" before proceeding to split on the commas
which delineate "Bill, Mary and Eddie" from "Apples","Oranges" and "Grapes"
and then you will need to find the replacement characters and change them
back to commas if you want to maintain what may otherwise be correct
grammar.
 
Top