CSV to MSSQL

[GeoK]

Hi,

I am copying a csv file to an mssql table and all works well.

What i would like to do is this.

e.g. the csv file has the format [a,b,c] and the Sql table has the
format [id,x,a,b,c]

[id] is the identity column and [x] is a static value that I like to
add to every row the sql table which does not exist in the csv file.

Can I add a "dummy" column contining the value 'x' prior to the
bulkcopy ? Or can I add a dummy column with csvdatareader ?

Thanks
George


e.g.

Using csvData = New CsvDataReader("myfile.csv")
csvData.Settings.HasHeaders = True
' Command to insert dummy column X with static value
csvData.Columns.Add("int") ' a
csvData.Columns.Add("int") ' b
csvData.Columns.Add("int") ' c

OR

Using bulkCopy = New SqlBulkCopy(strConnString)
'ignore identity column
' Command to map static value to column X would go here
bulkCopy.ColumnMappings.Add("a", "a")
bulkCopy.ColumnMappings.Add("b", "b")
bulkCopy.ColumnMappings.Add("c", "c")
bulkCopy.WriteToServer(csvData)

[shriop]

I would suggest adding a dummy column to CsvDataReader. If you add the
column last after adding the other columns to the Columns collection
of CsvDataReader, using the overload for the Add method that allows
you to name the column, then you can add a ColumnMapping for it. To
set it's value, you can either access the CsvDataReader.Column and set
it's default value to the value you want, or you can wire up the
ReadRecord event on CsvDataReader and set it on a row by row basis.

Bruce Dunwiddie

[GeoK]

Hi Brian,

So with that in mind would the syntax be:

Using csvData = New CsvDataReader("myfile.csv")
csvData.Settings.HasHeaders = True

csvData.Columns.Add("int") ' a
csvData.Columns.Add("int") ' b
csvData.Columns.Add("int") ' c

csvData.Columns.Add("int") ' x
csvData.Column("x").value = myVariableName

Thanks
George

[shriop]

Yes, roughly.

csvData.Columns.Add("int", "x") ' x
csvData.Columns.Item("x").DefaultValue = myVariableName

VB.Net syntax off the top of my head. And you would also have to add
to the SqlBulkCopy ColumnMappings.

Bruce Dunwiddie

> > > bulkCopy.WriteToServer(csvData)- Hide quoted text -
>
> - Show quoted text -

[GeoK]

Worked a treat thanks.
George