Updating a CSV file based on data read from another

[mozgheib]

Hello,

I have multiple csv files. These files need to be updated based on data contained in another csv file which is used
as a reference.

All records containing a key in the csv files that is not in the reference file should be removed. All records containing a key that is in the reference file, the key has to be updated and replaced by its mapping key which is in the reference file.

Here is an example:

I have a directory which has multiple csv files.
The format is as such

tagname date (csv1.csv)

1234A 1998/02/04
1235B 1998/02/04
1235B 1998/02/03

The reference file contains (ref.csv)
oldtag new tag
1235B X1235B


In this case csv1.csv should drop 1234A since
it is no longer in ref.csv and should replace
all occurences of 1235B by X1235B.


Thanks

 

[VBrit]

I would suggest:

Write a DTS package to import your files into SQL Server. Then write SQL statement in a Stored Procedure to produce the resultset that you want. Then export that resultset back to csv.

 

[getdani]

you might not need to go as far as SQL server u can just use access, containing two tables related by the tag, whenever the csv is requierd u can export it using the adodb recordset object save(filename,scv) to scv format.

it is faster consistant and reliable than a text file

 

[mozgheib]

Thanks to all very much.

 

[dilettante]

Might even be possible using just Jet and the Text provider if you can craft a "SELECT... INTO" that meets the need.

 

[BobRodes]

Actually, I think you can just do it with ADO recordsets. Set up an ADO Recordset object. Open the recordset like this:

rs.Open myfilepath, "Provider=MSPersist"

I've only used this in read only situations, but you should be able to open an updatable recordset this way. Once you've done your manipulations, any changes you can save using the save method, as getdani points out.

HTH

Bob

 

[dilettante]

I'm pretty sure MSPersist only supports XML (starting with ADO 2.1) and native ADO (PersistFormat = adPersistADTG) persisted formats though, not CSV. It'd be interesting to find out otherwise of course.

Both ADO and Jet offer some amazing functionality. Many of us are only beginning to appreciate it now that the sun is setting on these technologies. I wish I'd found some good books about this stuff six years ago. Lately I realize that I was using them in cave-man fashion for way too long, hand-coding things they would have done for me almost automagically.

 

[BobRodes]

Yeah, that's right, I've only done it with xml files.

 

[mozgheib]

Here is the code I wrote. Now to turn my select into a (select into) instead obviously I will need to create a connection to my sequel server and reference a sequel server table name next to into. To do so I was wondering what should the code look like below?

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
TextBox1.Text = csvFile.Value

Dim objFI As New System.IO.FileInfo(TextBox1.Text)
TextBox2.Text = objFI.Name
TextBox3.Text = objFI.DirectoryName


Dim Conn As System.Data.Odbc.OdbcConnection, dt As DataTable
Dim da As System.Data.Odbc.OdbcDataAdapter
Dim strConnstr, strImportfolder, strFilename As String

strImportfolder = objFI.DirectoryName
strFilename = objFI.Name
strConnstr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + strImportfolder + ";"
Conn = New Odbc.OdbcConnection(strConnstr)
da = New System.data.Odbc.OdbcDataAdapter("select * from [" + strFilename + "]", Conn)
da.Fill(dt)

'DataGrid1.DataSource = dt
'DataGrid1.DataBind()

End Sub