Extract data from a CSV file via batch script.

[foncesa]

Modedit: This first post was replaced with the corrected post

Hi Squashman,

I am extremely sorry, I did make lots of mistake to my presentation/question.
I have rectified the mistakes and represent it.

I have a flat text file of approx. 5000 rows, Starting from the text file 10th digit upto next 9 digit is the corresponding CSV file name which is located in c:\database.

Once the corresponding file is found, our search serial in text file starts from 40th digit to 45 digit thats 6 digits.

This serial of 6 digit is to be searched in CSV file from 4th column onward till the end of file. If serial is found then copy column 1 of CSV File(which is 2 digits) to text file 56-57th digit and overwrite it.

Next copy the column B & C from CSV to the end of row in text file with tab spacing. If corresponding file or serial is not found continue searching next row.

Code: Select all

(-----9--------9--------------8--------------13= 39)-- [40-45]---10--------[56-57] 
222000322 131044003 16102014 0000000500000 310747 0201001052 99     000000001

Editing mistake now rectified.
 
CSV FILES::
131044003.csv
44|2000|325|310741|310742|310743|310744|310745|310746|310747|310748|310749|310750|
356044052.csv
31|2001|9829|041901|041902|041903|041904|041905|041906|041907|041908|041909|041910| 
411044011.csv
17|2014|52119|135301|135302|135303|135304|135305|135306|135307|135308|135309|135310|135311|135312|135313|

[Squashman]

30th digit to 36 digit thats 6 digits.

How about 7.

[Squashman]

TEXT FILE::
222000322131044003161020140000000500000310747020100105299000000001

Your underlined 6 digit code is in 40-45.

[Squashman]

If serial is found then copy column 1 of CSV File(which is 2 digits) to text file 46-47th digit and overwrite it.

You can't overwrite a file. You have to output to a new file.

[Squashman]

CSV FILES::
131044003.csv
44|2000|325||310741|310742|310743|310744|310745|310746|310747|310748|310749|310750|
356044052.csv
31|2001|9829||041901|041902|041903|041904|041905|041906|041907|041908|041909|041910|
411044011.csv
17|2014|52119||135301|135302|135303|135304|135305|135306|135307|135308|135309|135310|135311|135312|135313|

Is the 4th column always blank?
If any of the other columns are blank it could become problematic unless you use Dave's safe parsing of CSV files Hybrid script.

[foncesa]

Hi Squashman,

I am extremely sorry, I did make lots of mistake to my presentation/question.
I have rectified the mistakes and represent it.

I have a flat text file of approx. 5000 rows, Starting from the text file 10th digit upto next 9 digit is the corresponding CSV file name which is located in c:\database.

Once the corresponding file is found, our search serial in text file starts from 40th digit to 45 digit thats 6 digits.

This serial of 6 digit is to be searched in CSV file from 4th column onward till the end of file. If serial is found then copy column 1 of CSV File(which is 2 digits) to text file 56-57th digit and overwrite it.

Next copy the column B & C from CSV to the end of row in text file with tab spacing. If corresponding file or serial is not found continue searching next row.

(-----9--------9--------------8--------------13= 39)-- [40-45]---10--------[56-57]
222000322 131044003 16102014 0000000500000 310747 0201001052 99 000000001

Editing mistake now rectified.

CSV FILES::
131044003.csv
44|2000|325|310741|310742|310743|310744|310745|310746|310747|310748|310749|310750|
356044052.csv
31|2001|9829|041901|041902|041903|041904|041905|041906|041907|041908|041909|041910|
411044011.csv
17|2014|52119|135301|135302|135303|135304|135305|135306|135307|135308|135309|135310|135311|135312|135313|

You can't overwrite a file. You have to output to a new file.

Will that output created be the copy of original text file with new replacements.
One more thing the search in text file to start from row 2 First row is headers.

[Samir]

Can't wait to see what everyone comes up for this one.

[foxidrive]

This serial of 6 digit is to be searched in CSV file from 4th column onward till the end of file.

I think where you use the term column, it should be the pipe delimited record.

This is the fourth column: 123456
and this is the fourth record: abc|def|ghi|jkl|mno


Is there only one data line (plus header line) in the CSV files?

[foncesa]

Hi Foxidrive,

and this is the fourth record abc|def|ghi|jkl|mno

Yes, it will start from the fourth record

Is there only one data line (plus header line) in the CSV files?

No, No its very huge file approx. 1 hundred thousand serial records are stored in each csv files.

[foxidrive]

its very huge file approx. 1 hundred thousand serial records are stored in each csv files.

How long will the longest line be?

Does each line have to search from the 4th record?

[foncesa]

Hi foxidrive,

How long will the longest line be? 500 serials in row/longest line.

Does each line have to search from the 4th record? Yes all the serials are maintained from 4th record.

[foxidrive]

Hi foxidrive,

How long will the longest line be?

500 serials in row/longest line.

Is that 500 characters in the longest line? Or 500 serials of a certain character length?

[foncesa]

Hi,

Or 500 serials of a certain character length?

Yes, i mean each serial of 6 digits and there can be upto 500 serials in single line/row.

[foxidrive]

This code is untested: Change c:\database to a test folder and put some copies of your CSV files inside.
Then run the batch file and see if it does what you need.

I'll be surprised if it works first time but tell me how the CSV file is modified - it may be off by one character position here and there.

The code below uses a helper batch file called `repl.bat` (by dbenham) - download from: https://www.dropbox.com/s/qidqwztmetbvklt/repl.bat
Place `repl.bat` in the same folder as the batch file or in a folder that is on the path.

Code: Select all

@echo off
set "database=c:\database"
for /f "tokens=1,2" %%a in ('type "file.txt" ^|repl ".{9}(.{9}).{21}(.{6}).*" "$1 $2" ') do (
echo file "%%a" serial "%%b"
   if exist "%database%\%%a.csv" (
      type "%database%\%%a.csv" |repl "(.*?)\|(.*?)\|(.*?)\|.*\|%%b\|.*" "copy $1 $&\t$2\t$3" x > "%temp%\%%a.csv.tmp"
      type "%temp%\%%a.csv.tmp" |repl "copy (..) (.{55})(..)(.*)" "$2$1$3" > "%database%\%%a.csv"
      del  "%temp%\%%a.csv.tmp"
   )
)
pause

[foncesa]

Hi foxidrive,

I tested the codes it does not give the expected results, it does not append, No output, and search is very slow.
Please revive.