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

General Macro Scheduler discussion

Moderators: Dorian (MJT support), JRL

Post Reply
Warren
Pro Scripter
Posts: 83
Joined: Sun Oct 08, 2017 11:57 pm

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

Post by Warren » Mon Nov 27, 2017 2:57 pm

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.
Last edited by Warren on Mon Nov 27, 2017 11:28 pm, edited 1 time in total.

Warren
Pro Scripter
Posts: 83
Joined: Sun Oct 08, 2017 11:57 pm

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

Post by Warren » Mon Nov 27, 2017 7:33 pm

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.

User avatar
Marcus Tettmar
Site Admin
Posts: 7378
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

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

Post by Marcus Tettmar » Wed Nov 29, 2017 3:41 pm

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.
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

Warren
Pro Scripter
Posts: 83
Joined: Sun Oct 08, 2017 11:57 pm

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

Post by Warren » Fri Dec 01, 2017 6:08 pm

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?

User avatar
Marcus Tettmar
Site Admin
Posts: 7378
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

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

Post by Marcus Tettmar » Sun Dec 03, 2017 2:32 pm

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.
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts