DBF to CSV in batch: how to do?

[sev7en]

Dear Community,
I need to convert every minute (1 minute) a file generate in format DBF into the CSV format. I looked around some script, I found one script in Python but unfortunately it doesn't work.

Can you help me?


Cheers,

 

[Kaktussoft]

Dear Community,
I need to convert every minute (1 minute) a file generate in format DBF into the CSV format. I looked around some script, I found one script in Python but unfortunately it doesn't work.

Can you help me?


Cheers,

What program does create the DBF file each minute?

 

[sev7en]

Good afternoon,
thanks for the prompt reply. It is a internal monitor application for enviromental data mining. I have uploaded it here: https://www.m-e-d-i-a-f-i-r-e.com/?a2nn9ihat6zs90k


Cheers,

 

[Kaktussoft]

Can't you simply adjust the "monitor application" so it output to CSV?
Otherwise I don't have a solution.

 

[sev7en]

Hi, no sorry I cannot...

 

[Alejandro85]

I think Excel can deal with both formats. Do you have it avaiable?
Try opening with Excel, and saving in CSV afterwards, see if the resulting file satisfies your needs.

 

[sev7en]

Good evening,
yes, Excel is able to handle it but I have to do that via command line, as it's a trigged operation (via file batch).


Regards,

 

[Alejandro85]

Great. Then, it's just a matter of scripting Excel to do the job automatically. For example, you may use VBScript to instruct Excel to load the dbf, then re-save it as csv:

Const xlCSV = 6

'Open Excel and load dbf within it
Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = FALSE
objExcel.Visible = FALSE
Set objWorkbook = objExcel.Workbooks.Open("C:\temp\test.dbf")

'Just save it without any modification, chosing CSV as format
objWorkbook.SaveAs "C:\temp\test.csv", xlCSV
objWorkbook.Close

'Close Excel and quit
objExcel.Quit
WScript.Quit

Save this as a .vbs file. It can be called directly from command line afterwards, included in a .bat file, scheduled task or whatever.

Be sure to fully test it on real sample data, I've just played with it a little. While I think it's a reasonably good approach, it's definitely not production quality. For instance, it lacks error handling . But you may be able to tune this to your exact needs.

 

[sev7en]

Dear Alejandro85,
thank you! The script is working like a charm but I have a problem: the file keep opened in the application and I cannot copy or overwrite it again.
Does it is a way to kill Excel after the conversion automatically? That script should be executed every 60 seconds.

 

[sev7en]

Dear Alejandro,
I just added that to avoid the security warning when the script is launched...

set oShell= CreateObject("Wscript.Shell")
set oEnv = oShell.Environment("PROCESS")
oEnv("SEE_MASK_NOZONECHECKS") = 1
oShell.Run "script.vbs /norestart",0,True
oEnv.Remove("SEE_MASK_NOZONECHECKS")

but now we have to fix the issues of the message above as well the one for the security warning as the script is executed on a network drive.

 

[Alejandro85]

The script closes Excel gracefully already, the line objExcel.Quit is precisely for that. Do you see a leaking Excel instance in task manager? If so, it may means that an error ocurred and the script did not finish succesfully.

No idea about the warning (I never tested over a network), but if a ridiculous Windows restriction comes by, the .bat file that launches it might simply copy from the network to the temp directory, run it from there, then delete it afterwards.

 

[sev7en]

Alejandro85, good afternoon... thanks for your support but there is a big problem: Excel trunk the DBF format, it misses the last 20 columns after the conversion. If I open the file also with Excel 2013 I see that problem.

As I seen Libreoffice is able to manage with no issue the DBF format but... did you can help me to convert that script in a one what works for LibreOffice?

 

[Alejandro85]

I would not expect Excel truncating the file. The DBF format supports up to 255 columns, while Excel uses 256 in .xls format and .xlsx up to 16384, maybe a row lenght limit? I've never tried such usage, an my trivial tests used just small files. Try this, if you open the file in Excel and save it in .xlsx (NOT standard .xls) does it saves correctly? Maybe an intermediate conversion would be all that's needed. If not, Excel is out of question most likely.

Libreoffice is a completely different beast. Their documentation mentions that it supports the very same OLE automation that Excel does. I've never used it, nor even have it to test. It's most likely possible to command Libreoffice from VbScript or similar language, but no idea how to do it exactly. The only official sample I could find (unrelated to your needs) is here: http://api.libreoffice.org/examples/OLE/vbscript/WriterDemo.vbs

Otherwise we can try with some other environment, maybe some other language without relying on Excel or something like that.

 

[sev7en]

Dear Alejandro,
thanks as first for your support. If you try to open the file with Excel and then with Calc, you see the Calc version has more columns. It looks like it is trunked and it loses several columns (20). Can you do something to trick that limitation if so?


Kindly