Extracting rows from multiple csv files using

October 27, 2009 at 13:49:00
Specs: Windows 7
Guys

I'm looking for a bit of code which would allow me to extract rows 3 to 34

from multiple csv files (over 100) and place the rows into one large csv for parsing.The csv files are exactly the same although different filenames and content. They reside in the same folder and it will always be rows 3 to 34 i'll want to extract. vbscript would be best as I want to add it another vbscript I have.

Any help is much appreciated.


See More: Extracting rows from multiple csv files using

Report •


#1
October 28, 2009 at 02:28:12
I dunno vbs [some helpers here do] but here's a bat fil;e:

===================================

@echo off > newfile & setLocal enableDELAYedexpansion

for %%c in (*.csv) do (
set N=
for /f "tokens=* delims= " %%a in (%%c) do (
set /a N+=1
if !N! geq 3 if !N! leq 34 (
>> newfile echo %%a
)
)
)


=====================================
Helping others achieve escape felicity

M2


Report •

#2
October 28, 2009 at 11:27:03
Thanks for that, the only problem I have is that there is a blank row between data sets ie row 32 has data, row 33 is blank, row 34 has data which i don't need. Can the batch be run to extract rows from row 3 until it gets to a blank row.

Thanks for the solution.


Report •

#3
October 28, 2009 at 11:32:42
Just do 3 to 32.


=====================================
Helping others achieve escape felicity

M2


Report •

Related Solutions

#4
October 28, 2009 at 11:48:26
Sorry Mechanix2Go, the csv files holds monthly data and each row of each file contains a days worth of data. Some files will hold 28, 30 or 31 rows of data. There is always a blank row after the last days data. Hence do until blank. Should have made this clear as I have appreciated your advise on other issues. Does this seem clear or would you like me to give an example of the file.

Thanks


Report •

#5
October 28, 2009 at 12:20:16
Not clear yet. Is there anything after the blank row?


=====================================
Helping others achieve escape felicity

M2


Report •

#6
October 28, 2009 at 12:40:16
Yes, the data after the blank row is not needed.
The data required to be extracted always starts at row 3 and will end depending on the of number of days in the month, ie 28, 30 or 31.
So it will either extract rows 3 to 28, 3 to 30 or 3 to 31. Rows 1,2 and everything after the blank row is not needed.

Thanks for your persistence.


Report •

#7
October 29, 2009 at 08:56:36
multiple csv files
Picked how? Just do everything in the directory that isn't the output.csv?

Report •

#8
October 30, 2009 at 02:23:38
bscript, might work... you will know what needs to be
adapted to your situation.

dim fso
Path1=".\"
Set fso = createobject("Scripting.FileSystemObject")
Set Folder = fso.GetFolder(Path1)
File="test"
Dir=Path1
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Set oFile = fso.OpenTextFile (Dir&File, ForAppending, True)
for each file in folder.files
fil=lcase(file.name)
p=instr(fil,".cvs")
if p>0 then
Set iFile = fso.OpenTextFile (Dir & file.name, ForReading)
for i=1 to 3
x=iFile.Readline
next
x="a"
do while iFile.AtEndOfStream=false and len(x)>0
x=ltrim(iFile.readline)
if ltrim(x)<>"" then
oFile.Writeline x
else
exit do
end if
loop
next

Wscript.Quit

this just a hack, not optimized, prob'ly a lot of redundant
code and not fully tested. I'm new to this stuff (vbscript.
javscript) myself so don't be offended if it (my code) sucks.


Report •

#9
October 30, 2009 at 11:10:55
nbrane, never write 'don't be offended', its people like you who give guys like me a steer in the right direction, which is very much appreciated. I'll try the code later as I'm also new to all this. I did modify the scrpt from Mechanix2Go and I got round the issues of the different number of lines by getting the user to enter the number of days. It works but not ideal. It is me that should be saying 'don't be offended by my humble offerings of scripting'. Thanks to all who took the time to respond.

@echo off & setLocal enableDELAYedexpansion

del /Q K:\HFD\combined.csv
del /Q K:\HFD\TEAMImport.txt

set /P days="Enter the number of days in the month:"
set /A days+=2
set location=K:\HFD\%days%\

for %%c in (%location%*.csv) do (
set N=
for /f "tokens=* delims= " %%a in (%%c) do (
set /a N+=1
if !N! geq 3 if !N! leq %days% (
>> combined.csv echo %%a
)
)
)

move /Y "%location%*.*" "K:\HFD\Archive\"

cscript TEAMImport.vbs


Report •

#10
November 12, 2009 at 00:13:39
This will find the blank for you:

=================================
@echo off & setLocal EnableDELAYedExpansion

set B=
for /f "tokens=1* delims=[]" %%a in ('find /v /n "" ^< my.csv') do (
set /a B+=1
if "%%b" equ "" goto :done
)
:done

echo firat blank at !B!


=====================================
Helping others achieve escape felicity

M2


Report •


Ask Question