Script to Chomp a huge .txt file

It's a pipe delimted file

I run it but in the 'Completed' folder there is only 1 file and it's identical to the input file... There should be 10 different files?

It's splitting the input lines using the pipe symbol "|" and using the first column value as the basis for building the output file name.  Are you sure that the values are separate by pipes?  You originally gave an example using commas...

Actually I think I know the issue.  The file has a '|' as first character.  Let me run after removing that.

If you need to leave that pipe there, for the bigger file, then change "values(0)" to "values(1)" at line #27.

OK. Not sure yet as haven't seen real file.  Another question.  The file is huge thus it sit's out on a share drive on the SAN.

\\share05\transfer\folder\giantfile.csv

When I put that in for the path and file name it says it can't find path.  I tried mapping it to Z:\ drive and it still won't work.  Trying to avoid copying this thing to my laptop to run...

Did you change lines #4 and #5?

inputFolder = "\\share05\transfer\folder\"
inputFile = "giantfile.txt"

I'm not sure how FSO deals with shares like that.  I'm a VB.Net developer and don't deal with VBScript normally.

Yes... I changed those to reflect real paths.   I can run from laptop, just takes 2 hrs to copy file from share drive.... I would think there's a way...

I think this works... So now this begins a 15 step process.  So basically on the source file I will need to do around 15 things to get it to my final format and data in order to load to my database.  Was thinking I could post these one by one on site or if your interested for a nominal fee (real $$) we could communicate on the side about it?

You can just post the requirements here, one by one, or all at once and I'll see what I can do.

OK.  They will be 500pts each.

I will award points on this one as soon as I can attempt it on real file.

Ok.  Let me know of any issues.  You can ask the rest of the questions using the "Related Question" feature so other people can look back and see what was done to get the ball rolling.

Great.  Never knew about related question thing...

So the problem is every pipe is surrounded in double quotes.... like   "|".    If I run the file through Access and then export it the script runs fine.  If I just try and run script on the original file without running through Access I get:

C:\temp\Scripts\scripts>cscript 01_Create_Files.vbs
Microsoft (R) Windows Script Host Version 5.7
Copyright (C) Microsoft Corporation. All rights reserved.

C:\temp\Scripts\scripts\01_Create_Files.vbs(29, 4) Microsoft VBScript runtime er
ror: Bad file name or number


C:\temp\Scripts\scripts>

Hmmm....try this:

            values = Split(line, Chr(34) & "|" & Chr(34))

Well it looks like script will successfully process the very first line.  However it chokes up on the 2nd line.  Here's a very small sample (3 lines) of my file.
file.txt

What possibly could running it through Access fix?  The actual file is directly from our ETL guys.  Could it be some type of Unicode or something?  This is making no sense :)

This file named 'File3.txt' it runs flawlessly on.  Why on this one but not the original file is beyond me.
file3.txt

Here's code that worked perfectly against File3.txt but wont run when I change it to real file or the File2.txt.


Option Explicit

Dim inputFolder, inputFile, inputFileName, outputFolder, outputFilename
inputFolder = "C:\temp\Scripts\Original\"
inputFile = "file3.txt"
inputFileName = inputFolder & inputFile
outputFolder = "C:\temp\Scripts\Original\Completed\"

Dim outputFiles
Set outputFiles = CreateObject("Scripting.Dictionary")

Dim fso, fIn, fOut
Const ForReading = 1, ForWriting = 2

Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(inputFileName) Then
      If Not fso.FolderExists(outputFolder) Then
            fso.CreateFolder(outputFolder)
      End If

      Dim line, values      
      Set fIn = fso.OpenTextFile(inputFileName, ForReading, False)
      While Not fIn.AtEndOfStream
            line = fIn.ReadLine
            values = Split(line, "|") ' <-- Pipe delimited file

            outputFileName = outputFolder & values(0) & ".txt"         
            If Not outputFiles.Exists(outputFileName) Then
                  Set fOut = fso.OpenTextFile(outputFileName, ForWriting, True)
                  outputFiles.Add outputFileName, fOut
            Else
                  Set fOut = outputFiles.Item(outputFileName)
            End If
            fOut.WriteLine line
      Wend
   
      MsgBox inputFile, vbOKOnly, "Done Processing File"
Else
      MsgBox inputFile, vbOKOnly, "Input File Not Found"
End If

OK.  It chokes on the " quotes in the file.  So if I remove all double quotes the script works.  So is there a way to add this to script so it does that first and then runs it?

*I haven't looked at the files.

You can remove the quotes on each line as it is read:

    line = fIn.ReadLine
    line = Replace(line, Chr(34), "") ' <-- Remove quotes
    values = Split(line, "|")

Perfect