Reading and writing from tables (csv, excell, etc)

[Warren]

EDIT: After a full day working through this issue, I see that writing to a specific field in a .csv is much more difficult than anticipated. I tried just about every method mentioned online including a couple using other tools. As far as MS, WriteLn may work for writing new data under certain circumstances, but in complex existing documents that need pinpoint edits with possible commas within fields or other issues, I haven't been able to get it to work reliably or find a breakdown from someone who has.

My first 8 or 9 attempts with the DBConnect method all failed because I didn't realize that "specify the path of the CSV file" in the instructions meant to do so... but only the path leading to, and not including the filename.

Read side working now, so digging into writing using the following method:

https://www.mjtnet.com/blog/2009/03/12/ ... csv-files/

So now able to finally dig into the write side from the followup post.

[Warren]

Finally through that, and find out at the end it can't actually do what I need at all. Is there any known method at all that allows updates to existing csv fields? If not, should I be going to XL?

I've been through every post I can find, some many times, tried several methods, and I'm still not seeing a way to simply write to my field of choice. Has anone worked out how to get the following (ADO method) working in MS? Seems like all the ingredients are there, though I don't know how to interface with MS.

http://erlandsendata.no/english/index.p ... dacwbdbado

Or is there some VBscript way to do this? I'm using OpenOfficeCalc, so it can open XL files. Is there any way to do it with Excel functions that don't requrie actually running excel (since I don't have it?) OLEDB/ODBC drivers?Something else? This has been such a dead end, I'll take anything at this point... abandon spreadsheets entirely and goto database storage (not preferred as the spreadsheets are human readable as well)

Then again, if just using databases significantly better (or the only workable solution), then perhaps I could also have a databaseRead script which essentially outputts the db into reader friendly format allowing me to visually cheeck that everything looks in order as I would with a spreadsheet.

[Marcus Tettmar]

CSV is flat. You can't "update". You can read and insert (add to the end) but you cannot update. You can treat CSV like a database table but can only query and insert. "updating" would be like reconstructing the entire text in memory and writing it all out again.

If you need to update records then you shouldn't be using CSV.

[Warren]

OK, so can I do this with XL format (without excel installed)?

If not, what CAN I update via automation? Do I need to set up a database? If so, what's the easiest to setup, update, save, and delete via MS automation?

[Marcus Tettmar]

Use whatever database you are most comfortable with. Macro Scheduler allows you to access databases via ODBC using the DBConnect, DBQuery and DBExec functions and passing SQL in. Macro Scheduler doesn't know or care what the database is because it's all down to the ODBC driver.