We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Another splice up of a bunch of files
Posted on 2012-12-22
DOS runs out of memory thus was going to see if easy VB way. Source files are 2GB each.
I have 10 source files that are ! delimited. The first row is a header row. The first column in each file is the Year column. It appears as: "2012"!"January"!
I need a script that will read every .txt file in the directory and create new files based on the column 1 value. Each new file will only contain the rows of the same year. For Example all rows that begin with "2012" will be a new file named the same as the original but prefixed with '2012'. So if the original file name was 'file.txt' new filename would be '2012file.txt' and so on. I need the header row in each new file too.
Thanks
I have 10 source files that are ! delimited. The first row is a header row. The first column in each file is the Year column. It appears as: "2012"!"January"!
I need a script that will read every .txt file in the directory and create new files based on the column 1 value. Each new file will only contain the rows of the same year. For Example all rows that begin with "2012" will be a new file named the same as the original but prefixed with '2012'. So if the original file name was 'file.txt' new filename would be '2012file.txt' and so on. I need the header row in each new file too.
Thanks
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
8-
7
3- +1
19 Comments
Perhaps the information at one of the below links will help you.
vbscript to split very large text files
http://stackoverflow.com/questions/4606367/vbscript-to-split-very-large-text-files
Function Creates Multidimensional Arrays from Delimited Text Files
This VBScript user-defined function can help streamline many text-based processes
http://www.windowsitpro.com/article/user-defined-function-udf/function-creates-multidimensional-arrays-from-delimited-text-files
Split text fiel searching for specific string of text and saving in mutltiple directories
http://stackoverflow.com/questions/12255167/split-text-fiel-searching-for-specific-string-of-text-and-saving-in-mutltiple-di
Working With Arrays in VBScript
http://www.aspfree.com/c/a/windows-scripting/working-with-arrays-in-vbscript/
Scripts to manage Text Files
http://www.activexperts.com/activmonitor/windowsmanagement/adminscripts/other/textfiles/
Topics for Writing or Appending to a File with VBScript
http://www.computerperformance.co.uk/vbscript/vbscript_file_opentextfile.htm
SciTE A free source code editing component for Win32 and GTK+, including VB & VBScript..
SciTE is a SCIntilla based Text Edito
http://www.scintilla.org/index.html
http://www.scintilla.org/ScintillaDownload.html
Text editing in SciTE works similarly to most Macintosh or Windows editors with the added feature of automatic syntax styling.
http://www.scintilla.org/SciTEDoc.html
HxD - Freeware Hex Editor and Disk Editor
http://mh-nexus.de/en/hxd/
Features
•Available as a portable and installable edition
•RAM-Editor ¿To edit the main memory
¿Memory sections are tagged with data-folds
•Disk-Editor (Hard disks, floppy disks, ZIP-disks, USB flash drives, CDs, ...) ¿RAW reading and writing of disks and drives
¿for Win9x, WinNT and higher
•Instant opening regardless of file-size ¿Up to 8GB, opening and editing is very fast
•Liberal but safe file sharing with other programs
•Flexible and fast searching/replacing for several data types ¿Data types: text (including Unicode), hex-values, integers and floats
¿Search direction: Forward, Backwards, All (starting from the beginning)
•File compare (simple)
•View data in Ansi, DOS, EBCDIC and Macintosh character sets
•Checksum-Generator: Checksum, CRCs, Custom CRC, SHA-1, SHA-512, MD5, ...
•Exporting of data to several formats ¿Source code (Pascal, C, Java, C#, VB.NET)
¿Formatted output (plain text, HTML, Richtext, TeX)
¿Hex files (Intel HEX, Motorola S-record)
•Insertion of byte patterns
•File tools ¿File shredder for safe file deletion
¿Splitting or concatenating of files
•Basic data analysis (statistics) ¿Graphical representation of the byte/character distribution
¿Helps to identify the data type of a selection
•Byte grouping ¿1, 2, 4, 8 or 16 bytes packed together into one column
•"Hex only" or "text only"-modes
•Progress-window for lengthy operations ¿Shows the remaining time
¿Button to cancel
•Modified data is highlighted
•Unlimited undo
•"Find updates..."-function
•Easy to use and modern interface
•Goto address
•Printing
•Overwrite or insert mode
•Cut, copy, paste insert, paste write
•Clipboard support for other hex editors ¿Visual Studio/Visual C++, WinHex, HexWorkshop, ...
•Bookmarks ¿Ctrl+Shift+Number (0-9) sets a bookmark
¿Ctrl+Number (0-9) goes to a bookmark
•Navigating to nibbles with Ctrl+Left or Ctrl+Right
•Flicker free display and fast drawing
vbscript to split very large text files
http://stackoverflow.com/questions/4606367/vbscript-to-split-very-large-text-files
Function Creates Multidimensional Arrays from Delimited Text Files
This VBScript user-defined function can help streamline many text-based processes
http://www.windowsitpro.com/article/user-defined-function-udf/function-creates-multidimensional-arrays-from-delimited-text-files
Split text fiel searching for specific string of text and saving in mutltiple directories
http://stackoverflow.com/questions/12255167/split-text-fiel-searching-for-specific-string-of-text-and-saving-in-mutltiple-di
Working With Arrays in VBScript
http://www.aspfree.com/c/a/windows-scripting/working-with-arrays-in-vbscript/
Scripts to manage Text Files
http://www.activexperts.com/activmonitor/windowsmanagement/adminscripts/other/textfiles/
Topics for Writing or Appending to a File with VBScript
http://www.computerperformance.co.uk/vbscript/vbscript_file_opentextfile.htm
SciTE A free source code editing component for Win32 and GTK+, including VB & VBScript..
SciTE is a SCIntilla based Text Edito
http://www.scintilla.org/index.html
http://www.scintilla.org/ScintillaDownload.html
Text editing in SciTE works similarly to most Macintosh or Windows editors with the added feature of automatic syntax styling.
http://www.scintilla.org/SciTEDoc.html
HxD - Freeware Hex Editor and Disk Editor
http://mh-nexus.de/en/hxd/
Features
•Available as a portable and installable edition
•RAM-Editor ¿To edit the main memory
¿Memory sections are tagged with data-folds
•Disk-Editor (Hard disks, floppy disks, ZIP-disks, USB flash drives, CDs, ...) ¿RAW reading and writing of disks and drives
¿for Win9x, WinNT and higher
•Instant opening regardless of file-size ¿Up to 8GB, opening and editing is very fast
•Liberal but safe file sharing with other programs
•Flexible and fast searching/replacing for several data types ¿Data types: text (including Unicode), hex-values, integers and floats
¿Search direction: Forward, Backwards, All (starting from the beginning)
•File compare (simple)
•View data in Ansi, DOS, EBCDIC and Macintosh character sets
•Checksum-Generator: Checksum, CRCs, Custom CRC, SHA-1, SHA-512, MD5, ...
•Exporting of data to several formats ¿Source code (Pascal, C, Java, C#, VB.NET)
¿Formatted output (plain text, HTML, Richtext, TeX)
¿Hex files (Intel HEX, Motorola S-record)
•Insertion of byte patterns
•File tools ¿File shredder for safe file deletion
¿Splitting or concatenating of files
•Basic data analysis (statistics) ¿Graphical representation of the byte/character distribution
¿Helps to identify the data type of a selection
•Byte grouping ¿1, 2, 4, 8 or 16 bytes packed together into one column
•"Hex only" or "text only"-modes
•Progress-window for lengthy operations ¿Shows the remaining time
¿Button to cancel
•Modified data is highlighted
•Unlimited undo
•"Find updates..."-function
•Easy to use and modern interface
•Goto address
•Printing
•Overwrite or insert mode
•Cut, copy, paste insert, paste write
•Clipboard support for other hex editors ¿Visual Studio/Visual C++, WinHex, HexWorkshop, ...
•Bookmarks ¿Ctrl+Shift+Number (0-9) sets a bookmark
¿Ctrl+Number (0-9) goes to a bookmark
•Navigating to nibbles with Ctrl+Left or Ctrl+Right
•Flicker free display and fast drawing
Active today
Does year data appear in more than one of these files? That is, might we find 2012 lines in more than one file?
Active today
So, solutions need to check for an existing target (year) file and only append the line data, rather than including the header.
Will the target files ever exceed 2GB?
Will the target files ever exceed 2GB?
Active today
If you can work it into your process, you would simplify all this processing by eliminating the header row from the front of each data file. You would only need to append the data to the appropriate year file. If you needed the header at a later time, you could use a simple copy command or convert the header file into a schema.ini file.
Active today
Is PowerShell an option here? The average speed I got with my test data is about 1MB per second. That is 35 minutes per 2GB file.
Active today
No, you will have to download it. All necessary info can be found at http://support.microsoft.com/kb/968929 .
Active today
What extra software do you have on your server? For instance, if you have Syncsort, you should get much better performance due to its I/O optimization.
Active 1 day ago
Nothing that I know of. We are looking possibly to try Cygwin. But not sure. Thinking Linux could do this file manipulation faster than DOS Batch?????
Active today
What DOS command are you using?
If you run the following command, replacing the file name of one of the big source files in place of "Q_27976301_Data.txt", how long does it take?
I/O is almost always going to be the biggest performance obstacle. However, we might take advantage of device buffering to provide a sequential read of the file for several extract tasks.
In theory, you can use the START command to launch several of these FindStr commands with separate CPU affinity. Assuming you have a multi-core processor, you might be able to extract four different years with a single pass. I would also try this on a single CPU (do not use the affinity switch on the START commands), since you are actually I/O bound and not CPU bound.
If you run the following command, replacing the file name of one of the big source files in place of "Q_27976301_Data.txt", how long does it take?
findstr /b """2012""" Q_27976301_Data.txt >> 2012_Data.txt
I/O is almost always going to be the biggest performance obstacle. However, we might take advantage of device buffering to provide a sequential read of the file for several extract tasks.
In theory, you can use the START command to launch several of these FindStr commands with separate CPU affinity. Assuming you have a multi-core processor, you might be able to extract four different years with a single pass. I would also try this on a single CPU (do not use the affinity switch on the START commands), since you are actually I/O bound and not CPU bound.
Active 1 day ago
Aikimark,
It runs in two seconds but produces a blank file.... No idea what's happening.
It runs in two seconds but produces a blank file.... No idea what's happening.
Active today
Did you run this in the same directory as the big data file?
Did you substitute your big file name in place of Q_27976301_Data.txt ?
Note: if the big file name contains spaces, you need to put the file name in quotes.
Did you substitute your big file name in place of Q_27976301_Data.txt ?
Note: if the big file name contains spaces, you need to put the file name in quotes.
Active today
Did that (http:#a38722808) really work for you? It did not for me.
Featured Post
PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Here are a few simple, working, games that you can use as-is or as the basis for your own games.
Tic-Tac-Toe
This is one of the simplest of all games.
Â
The game allows for a choice of who goes first and keeps track of the number of wins for…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code.
This l…
- Microsoft Office
- Office 365
- Microsoft Access
- VBA
- Microsoft Excel
- Visual Basic Classic
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month4 days, 1 hour left to enroll
Earn Certification
CompTIA IT Fundamentals
$49.00$44.10
630 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.