Column Mapping

[PeterM]

Hi All,

I am in the process of writing a little console application that reads a price list from a supplier in either xls, csv or pdf format and then updates the RRP price in a Pervasive SQL database and also does some caluclations based on cost price, selling price and margin.

As each supplier has a slightly different format for their spreadsheets I have got as far as creating an SQL table with the supplier file type, location, name and a map of the columns as to what each one is. The map consists of a list of the spreadsheet columns, each one containing the field name in the products table to which it relates.

I have also now created and tested the code to read in the spreadsheet so I have both the map and the data, what I am a little unsure of is how to approach actually doing the mapping part.

Can I for example, create SQL select and update statements based on the name of a column in the table which is itself contained in a variable? If it were as simple as that then it would be easy!

Thanks in advance,
Peter.

 

[jmcilhinney]

Sure, e.g.

Dim sql As String = String.Format("INSERT INTO {0} ({1}, {2}) VALUES (@{1}, @{2})", _
                                  tableName, _
                                  columnName1, _
                                  columnName2)

 

[PeterM]

Thanks for your reply, that worked a treat