james
Thu May 26 19:07:19 CDT 2005
Access would have no problems with your strings. First, it would (does) look for the Quotation Marks that denote a seperate
column/field and then the " , " to seperate the columns/fields. Anything, including a ","
(comma) inside the quotation Marks would be ignored and considered part of the string or data. ( column/field names should not
contain commas)
You should be able to do the same thing in code. If you copied the data to a file with the extension .CSV and then opened it
in Access or in VB.NET using code, the Jet Database engine would do the parsing for you. Just look at all the examples of
reading a CSV file in VB.NET.
Like this: (not the best example but, works)
Imports System.io
Imports System.Reflection
Imports System.Data.OleDb
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim strFileName As String
Dim strFilePath As String
Dim sSlash As Single
Try
With OpenFileDialog1
.Title = "Import CSV file"
.InitialDirectory = "C:\"
.Filter = "File (*.xls;*.csv;*.txt)|*.xls;*.csv;*.txt|All files (*.*)|*.*"
.ShowDialog()
sSlash = InStrRev(.FileName, "\")
strFilePath = Mid(.FileName, 1, sSlash)
strFileName = Mid(.FileName, sSlash + 1, Len(.FileName))
End With
Dim strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strFilePath & ";" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited"""
Dim conn As New OleDb.OleDbConnection(strConnectionString)
conn.Open() ' Open connection with the database.
Dim objCmdSelect As New OleDb.OleDbCommand("SELECT * FROM [" & strFileName & "]", conn).
Dim objAdapter1 As New OleDb.OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect 'Pass the Select command to the adapter.
Dim objDataset1 As New DataSet 'Create new DataSet to hold information from the worksheet.
objAdapter1.Fill(objDataset1, "XLData") 'Fill the DataSet with the information from the file.
DataGrid1.DataSource = objDataset1.Tables(0).DefaultView 'Build a table from the original data.
conn.Close() 'Clean up objects.
Catch ex As Exception
MsgBox(ex.Message).ToString()
End Try
Me.Text = "Import CSV:" & strFileName
End Sub
Put a datagrid on a form, a button and the OpenFileDialog and this works. Jet is doing most of the work.
I am sure that it can be done in your case too, without having to save to file and then opening and reading the file. (as others
have said, too slow for you needs) I would parse the incoming data into an array and
do it in a similar fashon that Access/Jet does, looking for the first Quotation Mark and not break the line
until AFTER then next Quotation Mark that is followed by a Comma. Then, reset your array (after copying the data to your
dataset) and start over with the next line.
Hope this gives you some ideas.
james
"andrei" <andrei.toma@era-environmental.com> wrote in message news:up7AmbjYFHA.1368@tk2msftngp13.phx.gbl...
> Thank you guys, Sahil and Adrian
>
> A sample line from the file would look like:
>
> "ABC001","2,4-DINITROPHENOL","01/05/2003",1,0,0,1,1,0,12.25,0.034,"suppliern
> ame"
>
> if importing through sql enterprise manager directly, the engine is capable
> of distinguishing between a comma that's a separator and a comma that is
> just a caracter in a field, so the number of fields I get is the correct
> one.
>
> Using another field delimiter is not an acceptable solution. It is quite
> complicated and lengthy to convince every third party (suppliers) who's
> servicing our clients to change their habits of generating information files
> like this one.
>
> So I'm still looking for a solution to import the csv string into the
> datatable...
>
> Thanks,
>
> Andrei.
>
>
> "Sahil Malik [MVP]" <contactmethrumyblog@nospam.com> wrote in message
> news:OH824riYFHA.3184@TK2MSFTNGP15.phx.gbl...
>> Ditto of what Adrian said. If you cannot reliably split using ",", then
> you
>> need to use a different delimiter.
>>
>> - Sahil Malik [MVP]
>>
http://codebetter.com/blogs/sahil.malik/
>>
>>
>>
>> "andrei" <andrei.toma@era-environmental.com> wrote in message
>> news:OJl71CfYFHA.2768@tk2msftngp13.phx.gbl...
>> > Hi Sahil, and thank you for the reply.
>> >
>> > Unfortunately, I can't do a split on each line using "," as a
> separator -
>> I
>> > have a field (a description) which could contain commas inside, so that
>> > would create more fields than normal :(
>> > ex: "2,4-DINITROPHENOL" or "DICYCLOHEXYLMETHANE-4,4'-DIISOCYANATE"
>> >
>> > I was thinking of splitting by <","> that is using the three characters
> as
>> > delimiter, but there are numerical values which are not surrounded by
>> double
>> > quotes, like : 33.4, 2,0,0,1, 2.5
>> > So this doesn't work either...
>> >
>> > Other ideas ?
>> >
>> > Thanks,
>> >
>> > Andrei.
>> >
>> >
>> >
>> > "Sahil Malik [MVP]" <contactmethrumyblog@nospam.com> wrote in message
>> > news:e$H9pbXYFHA.3032@TK2MSFTNGP10.phx.gbl...
>> > > Yes there is. As a matter of fact, you should avoid writing to the
> disk
>> as
>> > > that is hella slow.
>> > >
>> > > What you can do however is that the "Stream" you get - (is it
>> > > MemoryStream?) - which after decryption and just before writing to the
>> CSV
>> > > file - subject that to a "StreamReader" - to get a string out of it.
>> > >
>> > > Great, now that you have a CSV'ed string out of it, split the string
> for
>> > New
>> > > lines.
>> > > Then run a foreach on the resulting array and split each line (array
>> > > element) using the "," character.
>> > > For each sub array element - create an object array - and do a
>> > > DataTable.LoadDataRow(passinthearray,true).
>> > >
>> > > Thats it. Problem solved.
>> > >
>> > > - Sahil Malik [MVP]
>> > >
http://codebetter.com/blogs/sahil.malik/
>> > >
>> > >
>> > >
>> > > "andrei" <andrei.toma@era-environmental.com> wrote in message
>> > > news:#PDYoXWYFHA.3356@TK2MSFTNGP15.phx.gbl...
>> > > > Hi Group,
>> > > >
>> > > > Here is what I'm trying to do in my application:
>> > > > 1. the customer uploads an encrypted file to the server (it's a
>> ASP.NET
>> > > > application)
>> > > > 2. the uploaded file is decrypted to a comma separated list of
> values
>> > > > 3. the list is being written to a CSV file
>> > > > 4. the csv file is being loaded into a datatable, using
>> OleDbDataAdapter
>> > > > 5. the recordset, after some checking and validation, is being
> written
>> > to
>> > > a
>> > > > SQL database.
>> > > >
>> > > > Question:
>> > > >
>> > > > Is it possible to skip somehow the steps where I'm writing the comma
>> > > > separated list to a csv file and then reloading the list into a
>> > datatable?
>> > > > Something like loading the datatable directly from the comma
>> separated
>> > > > string **in memory** ?
>> > > >
>> > > > Many thanks !
>> > > >
>> > > > Andrei.
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
>>
>
>