Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
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.
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 felicityM2
0 
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.
0 0
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
0
Not clear yet. Is there anything after the blank row?
=====================================
Helping others achieve escape felicityM2
0
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.
0
multiple csv files
Picked how? Just do everything in the directory that isn't the output.csv?
0
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
nextWscript.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.
0
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.txtset /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
0
This will find the blank for you:
=================================
@echo off & setLocal EnableDELAYedExpansionset B=
for /f "tokens=1* delims=[]" %%a in ('find /v /n "" ^< my.csv') do (
set /a B+=1
if "%%b" equ "" goto :done
)
:doneecho firat blank at !B!
=====================================
Helping others achieve escape felicityM2
0  |
 |
 |
| Login or Register to Reply | |
| Login | Register |
| Ads by Google |
