batch file issue for Dbf to csv

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Post Reply
Message
Author
pradeepcarya
Posts: 4
Joined: 18 Oct 2010 06:16

batch file issue for Dbf to csv

#1 Post by pradeepcarya » 18 Oct 2010 06:19

Hello,
I am in a tough situation.i need to create a batch file to convert .dbf file to .csv.
i am using below code for doing this .
dbf_2_ora.bat.....

f:
cd \Retail_Work\DATA_201\test
copy C_N_DETL.DBF C_N_DETL_TEST.csv

when I am running above bat file "C_N_DETL_TEST.csv" is creating but when I am opening this file data is separated by "tab"/spaces.i need to remove n replace it by comma separated.

kindly help me to create a csv file by dbf file.

aGerman
Expert
Posts: 4678
Joined: 22 Jan 2010 18:01
Location: Germany

Re: batch file issue for Dbf to csv

#2 Post by aGerman » 18 Oct 2010 11:11

Probably another event for ghostmachine4's gawk :wink:

A native batch solution could look like this

Code: Select all

@echo off &setlocal
>"C_N_DETL.csv" type nul
for /f "tokens=* delims=1234567890" %%a in ('findstr /n "^" "C_N_DETL.DBF"') do (
  set "line=%%a"
  call :proc
)
goto :eof

:proc
setlocal enabledelayedexpansion
if "!line!"==":" (
  >>"C_N_DETL.csv" echo\
  goto :eof
)
set "line=!line:~1!"
set "line=!line:   =,!"
>>"C_N_DETL.csv" echo\!line!
endlocal
goto :eof


Note that the forum software cannot show tab characters, thats why you have to replace the triple space in this line with a real tab:

Code: Select all

set "line=!line:   =,!"


Regards
aGerman

ghostmachine4
Posts: 319
Joined: 12 May 2006 01:13

Re: batch file issue for Dbf to csv

#3 Post by ghostmachine4 » 18 Oct 2010 18:34

aGerman wrote:Probably another event for ghostmachine4's gawk :wink:

already did in the other thread :)

thats why you have to replace the triple space in this line with a real tab:

but tabs are also dependent of how many spaces. can be 4, 8 etc.
Any way for your batch to just detect a tab character regardless of how each user's environment is?
like "\t" in my gawk solution. .

aGerman
Expert
Posts: 4678
Joined: 22 Jan 2010 18:01
Location: Germany

Re: batch file issue for Dbf to csv

#4 Post by aGerman » 18 Oct 2010 18:49

ghostmachine4 wrote:but tabs are also dependent of how many spaces. can be 4, 8 etc.

No ghost. A tab is a regular ASCII character (hex 09)! It has nothing to do with any environment settings.
Of course some text editors (and also this forum software) change tab characters automaticaly to spaces. This depends on the settings for this editor/software. But again: a tab is a tab and not a bunch of spaces!!

Regards
aGerman

ghostmachine4
Posts: 319
Joined: 12 May 2006 01:13

Re: batch file issue for Dbf to csv

#5 Post by ghostmachine4 » 18 Oct 2010 19:18

aGerman wrote:
ghostmachine4 wrote:but tabs are also dependent of how many spaces. can be 4, 8 etc.

No ghost. A tab is a regular ASCII character (hex 09)! It has nothing to do with any environment settings.
Of course some text editors (and also this forum software) change tab characters automaticaly to spaces. This depends on the settings for this editor/software. But again: a tab is a tab and not a bunch of spaces!!

Regards
aGerman

That's not what i meant. I mean that , because different people use different editors that may create variable number of spaces for tabs etc, that's why If i were to use your batch file, i must determine first how many spaces there are and change the batch. For example, my editor use 8 spaces for tabs. If i want to use your batch, i have to change it as well. Then if i were to edit my file on another editor on another machine which uses 4 spaces as tabs, i again have to change you batch. I do not want this. That's why i suggest, if your batch can automatically detect tabs, no matter how many spaces it may be.

pradeepcarya
Posts: 4
Joined: 18 Oct 2010 06:16

Re: batch file issue for Dbf to csv

#6 Post by pradeepcarya » 18 Oct 2010 22:53

hey aGerman,

thnx for ur reply ..
I did d same solution,C_N_DETL.csv is also generating but in tat there is no data coming.
only !line! is showing..all data which was coming earlier now not coming.

help ..


aGerman wrote:Probably another event for ghostmachine4's gawk :wink:

A native batch solution could look like this

Code: Select all

@echo off &setlocal
>"C_N_DETL.csv" type nul
for /f "tokens=* delims=1234567890" %%a in ('findstr /n "^" "C_N_DETL.DBF"') do (
  set "line=%%a"
  call :proc
)
goto :eof

:proc
setlocal enabledelayedexpansion
if "!line!"==":" (
  >>"C_N_DETL.csv" echo\
  goto :eof
)
set "line=!line:~1!"
set "line=!line:   =,!"
>>"C_N_DETL.csv" echo\!line!
endlocal
goto :eof


Note that the forum software cannot show tab characters, thats why you have to replace the triple space in this line with a real tab:

Code: Select all

set "line=!line:   =,!"


Regards
aGerman

amel27
Expert
Posts: 177
Joined: 04 Jun 2010 20:05
Location: Russia

Re: batch file issue for Dbf to csv

#7 Post by amel27 » 18 Oct 2010 23:34

pradeepcarya wrote:I am in a tough situation.i need to create a batch file to convert .dbf file to .csv.

try this perl script for cmd use (BAT sample inside):
http://www.burtonsys.com/download/DBF2CSV.ZIP

ghostmachine4
Posts: 319
Joined: 12 May 2006 01:13

Re: batch file issue for Dbf to csv

#8 Post by ghostmachine4 » 19 Oct 2010 03:56

try this Python module called dbfpy . Here's how you use it

Code: Select all

import csv
import sys
from dbfpy import dbf
dbf_input = sys.argv[1]
csv_output = sys.argv[2]
in_db = dbf.Dbf(dbf_input)
out_csv = csv.writer(open(csv_output, 'wb'))
names = []
for field in in_db.header.fields:
    names.append(field.name)
out_csv.writerow(names)
for rec in in_db:
    out_csv.writerow(rec.fieldData)
in_db.close()


save the above as myconvert.py and

Code: Select all

c:\test> python myconvert.py mydbf.dfb mycsv.csv

Its that simple.

aGerman
Expert
Posts: 4678
Joined: 22 Jan 2010 18:01
Location: Germany

Re: batch file issue for Dbf to csv

#9 Post by aGerman » 19 Oct 2010 13:48

OK, it seems like a .dbf file is not simply a file with tab separated values. In this case you will probably don't have luck with native batch.

@ghost
ghostmachine4 wrote:That's not what i meant. I mean that , because different people use different editors that may create variable number of spaces for tabs etc, that's why If i were to use your batch file, i must determine first how many spaces there are and change the batch. For example, my editor use 8 spaces for tabs. If i want to use your batch, i have to change it as well. Then if i were to edit my file on another editor on another machine which uses 4 spaces as tabs, i again have to change you batch. I do not want this. That's why i suggest, if your batch can automatically detect tabs, no matter how many spaces it may be.

:? I have no idea what you're talking about. If you copy/paste my code to any text editor then you will find exactly 3 spaces behind the colon. This is because the forum software replaced my real tab character with 3 spaces.
My suggestion: Use the good old notepad.exe, delete the 3 spaces hit the tab key at this place and save the file. Now you can be sure its a real tab character and not an undefined number of spaces.

Regards
aGerman

ghostmachine4
Posts: 319
Joined: 12 May 2006 01:13

Re: batch file issue for Dbf to csv

#10 Post by ghostmachine4 » 19 Oct 2010 17:46

aGerman wrote: :? I have no idea what you're talking about. If you copy/paste my code to any text editor then you will find exactly 3 spaces behind the colon. This is because the forum software replaced my real tab character with 3 spaces.
My suggestion: Use the good old notepad.exe, delete the 3 spaces hit the tab key at this place and save the file. Now you can be sure its a real tab character and not an undefined number of spaces.


Forget about forum spaces or anything. Its not the issue i am talking about. Let's say you had specified 3 spaces in your batch file using your own editor. And Let's say you distribute your batch to various clients and they start to use your batch file. Then they created test files using their own editors to test your batch code. But their editors gives them 4 spaces, or 8 spaces instead. Without knowing they must change your batch tabs to 4 and 8 spaces, they run it, and found it not working. That's the problem. They must manually change the tabs spaces in your batch everytime its use in different places, where different people use different editors. So the question again, can you specify in your batch the "\t" tab character, so that it knows its a tab its dealing with and the user of your batch don't have to change your batch file to make it work seamlessly.

aGerman
Expert
Posts: 4678
Joined: 22 Jan 2010 18:01
Location: Germany

Re: batch file issue for Dbf to csv

#11 Post by aGerman » 20 Oct 2010 12:07

Seems we can't strike an agreement :P
ghostmachine4 wrote:So the question again, can you specify in your batch the "\t" tab character, so that it knows its a tab its dealing with and the user of your batch don't have to change your batch file to make it work seamlessly.

No, except I could upload my file here as an attachment. But of course you can use tab characters in a batch file. Do what I suggested yesterday (notepad and replace by tab ...). Now open the batch file in a hex editor. If you find "3A 09 3D" (:TAB=) then I'm right and we got the tab character. If you find something like "3A 20 20 20 20 3D" you are right and it is impossible to use a tab character in batch.

Regards
aGerman

ghostmachine4
Posts: 319
Joined: 12 May 2006 01:13

Re: batch file issue for Dbf to csv

#12 Post by ghostmachine4 » 20 Oct 2010 18:12

aGerman wrote:Seems we can't strike an agreement :P

there is nothing to agree with in the first place, since I am asking a valid question (which i hope you can answer), and not asking you to agree anything with me.. :)


But of course you can use tab characters in a batch file.

yes, this , i know. I can use tab characters in batch. Say if my data has 2 tabs, then I have to change the batch to 2 tabs in order for it to work. I don't want to do it (change the batch) every time my data changes to different number of tabs. I hope you get what i meant now.

aGerman
Expert
Posts: 4678
Joined: 22 Jan 2010 18:01
Location: Germany

Re: batch file issue for Dbf to csv

#13 Post by aGerman » 21 Oct 2010 16:01

ghostmachine4 wrote:Say if my data has 2 tabs, then I have to change the batch to 2 tabs in order for it to work.

Why do you think you have to do that?
Let say we have a TAB separated file (this is I first thought what a dbf file would be) like
123TABabcTAB456TABxyz
234TABbcdTABTABuvw

Then my batch file would replace each TAB character by comma.
123,abc,456,xyz
234,bcd,,uvw

Do you think this is a wrong way?

Regards
aGerman

ghostmachine4
Posts: 319
Joined: 12 May 2006 01:13

Re: batch file issue for Dbf to csv

#14 Post by ghostmachine4 » 21 Oct 2010 17:52

aGerman wrote:
ghostmachine4 wrote:Say if my data has 2 tabs, then I have to change the batch to 2 tabs in order for it to work.

Why do you think you have to do that?
Let say we have a TAB separated file (this is I first thought what a dbf file would be) like
123TABabcTAB456TABxyz
234TABbcdTABTABuvw

Then my batch file would replace each TAB character by comma.
123,abc,456,xyz
234,bcd,,uvw

Do you think this is a wrong way?

Regards
aGerman


Well, let's say I have many customers, and each customers pass me their dbf files to process. Some gives me 2 tabs, some 1 tab, some 4 tabs. Now , if i use your script, i have to either change the tab in your batch each time i process those files, or change those tabs in my customer's file to that defined in the batch. I don't want to do that. does that make sense now? I would just want to run your batch no matter what the customer's dbf file may be.

No, i don't think it is wrong the way you do. But just not flexible. with a regular expression like "[ \t]*" or "[ \t]+" or "[[:space:]]+" , this should be able to handle dbf file with different tab/space format seamlessly.

Post Reply