Solved

.csv To .txt

Posted on 2014-09-26
39
156 Views
Last Modified: 2014-10-29
Hi Experts,

Will any one help me with piece of code. it will be very great full ...as manually thing is very tidy.
What mi up to is copy certain data from .csv file - arrange - final result past to .txt file with name as .csv date.
There is all .csv files located at C:\Test\ & I had created one WB called Data To Text.xlsm which term as mediator or say converter who converter .csv file data in to .txt. From Data To Text.xlsm WB Execute this procedure.
flow like this

01.

Open Very First .csv File from location C:\Test\

02.

Clear Data Range A2:H2 Till End (WB Data To Text.xlsm)

03.

Copy Range A2 till End (In .csv) – Past to WB Data To Text.xlsm – A2

04.

Copy Range K2 till End (In .csv) – Past to WB Data To Text.xlsm – B2

05.

Copy Range C2:F2 till end (in .csv) - Past to WB Data To Text.xlsm – C2

06.

Copy Range I2 till end (in .csv) - Past to WB Data To Text.xlsm – G2

07.

Past This Formula in Data To Text.xlsm - Cell H2 “=A2&", "&"D"&", "&TEXT(B2,"yyyymmdd")&", "&C2&", "&D2&", "&E2&", "&F2&", "&G2” & formula drop down till End.

08.

Open New notepad

09.

Copy H1 till End data from WB Data To Text.xlsm  & past to newly open notepad.

10.

Save & Close notepad with naming = WB Data To Text  – Cell B2.(Location same as active WB)

11.

Open 2nd .csv ….Step  2 continue till End

12.

Run till last .csv completed.

See attached sample .csv file Data To Text.xlsm & Final Result .txt File


Thank you Very Much
0
Comment
Question by:itjockey
  • 16
  • 15
  • 3
  • +2
39 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40345784
One suggestion which might make your whole process easier; why are you copying to Notepad? You can use Excel to save as a txt file.

If you have the required data for the txt file on a separate sheet and that sheet is active when saved as txt, only that data will be saved into the txt file.

Thanks
Rob H
0
 
LVL 8

Author Comment

by:itjockey
ID: 40345797
Mr.Rob Henson,

Thank you for your valuable time and suggestion. But thing is that I want to convert this .txt file in to my trading software in chart form .  And it required .txt as well as separate. For each day.

Still I will further check on your suggestion.

Thank You
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40346321
Also, looking at the process again, you are opening the .csv file and then copying into the Data to Text file.

If this is being processed by macro, the process can be run on the .csv file and then saved as .txt without the need to copy to the Data to Text file.

So the process would then be:

1) Open CSV and count number of rows (when opening csv files, the sheet name will be the filename, this will be needed for step 3)
2) Insert new sheet
3) Populate the columns of the new sheet with formulas linking to csv sheet, either direct link or reformatting in the case of the date in column B, with enough rows to cover the data counted in step 1
4) Save this file as .txt with this new sheet active
5) Formulas will be overwritten by values and only the visible sheet will be saved as txt document
6) Close file and repeat for next csv

Thanks
Rob H
0
 
LVL 8

Author Comment

by:itjockey
ID: 40347309
Mr.Rob Henson,

Agreed with you suggestion & it is more logical. but I don't know why my converter which convert data to chart form read only .text files with some heavy words "ASCII". if this kind of process save as same format "ASCII" then I don't mind to save from .csv it self as .text file.


Thank You
0
 
LVL 8

Author Comment

by:itjockey
ID: 40366306
Any Luck?
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40366352
Sorry, I hadn't realised there was a question outstanding. I don't really understand your comment about your data converter needing .txt rather than .csv

I don't know enough about the creation of ASCII files to be able to comment but would have thought that .txt and .csv would be in the same raw data format, dependent on the system in which they are created.

Thanks
Rob H
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40367978
Until you get an automated solution, concatenate all your files into one big txt file and then go through your steps once.  You will need to add a step to filter for the column headers and then delete all the filtered rows but the first one.
Concatenating command:
copy *.csv Import.csv /b

Open in new window

I assume you know how to use autofilter
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40367983

Past This Formula in Data To Text.xlsm - Cell H2 “=A2&", "&"D"&", "&TEXT(B2,"yyyymmdd")&", "&C2&", "&D2&", "&E2&", "&F2&", "&G2” &
I don't understand this formula.  If you aren't able to copy/paste it into your browser, then type it exactly as it appears in your workbook.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 40368839
So where do you stand at this point?
FYI A CSV file IS a text file, but with a very specific format.
An ASCII file is, technically, any file, but is traditionally considered any file with "human-readable" text (rather than a "binary file" which is either encrypted or contains computer-interpretable code.)  So an "ASCII" file would be one containing letters, number and traditional symbols while a "binary" file would be one with smiley faces and arrows ion addition to letter numbers and symbols.

So what are you missing? Let's work on 1 thing at a time since you seem to want about 12 things done.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40368860
@itjockey
Whatever browser you are using (or perhaps it is EE when you used the STEP formatting) is mangling your formula.
Put the formula in a sample Excel sheet and post it.
It is also VERY helpful to post samples for the Experts to look at and work on.
Much of what you want can be easily recorded as a macro(s) and then cleaned up.
The rest is just looping code, primarily a DIR loop that would walk through all the csv files in a given folder and complete the actions that the macro specified.

But knocking that all together without sample data to work with is burdensome.
Post some samples, please, and we'll be able to provide much better help.

"See attached sample .csv file Data To Text.xlsm & Final Result .txt File"
They don't seem to have been successfully attached to your original post.
And given that your system seems to make & very unhappily, I would use the word 'and' :)
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 40368903
Looking at the that formula, I suspect it is just doing a double-translation.  If you replace all &, with a single &, you get this:

Past This Formula in Data To Text.xlsm - Cell H2 “=A2&", "&"D"&", "&TEXT(B2,"yyyymmdd")&", "&C2&", "&D2&", "&E2&", "&F2&", "&G2” & formula drop down till End.

or
=A2&", "&"D"&", "&TEXT(B2,"yyyymmdd")&", "&C2&", "&D2&", "&E2&", "&F2&", "&G2

Open in new window

That should be more readable for everyone.
However, I see what seems to be a bug, where the first D should probably be D2.
0
 
LVL 8

Author Comment

by:itjockey
ID: 40369494
Mr.rspahitz & Other Experts,

I don't know why this happen i.e formula thing ....when I posted question all is good and readable even I had attached sample files also viz .csv file which I need to convert to text - Data To Text.xlsm & .txt file (final result file)....but now my original post doesn't have any attachments ...I wondered why?

@rspahitz - it "D" not D2 ---- In my charting software D stands for Daily.

I think I made my question and description much more complicated.

This is just reframe. I guess it will be much easier then previous.
Clear all data in WB Data To Text.xlsm – Sheet Converter.

Open Very First .csv File from location C:\Test\    & Copy all data & past to WB Data To Text.xlsm.

Hard coded this text line in Code itself for Cell N1 {<ticker>, <per>, <date>, <open>, <high>, <low>, <close>, <vol>, <o/i>}.

Hard coded this formula in code itself for cell N2 {=A2&", "&"D, "&TEXT(K2,"YYYYMMDD")&", "&C2&", "&D2&", "&E2&", "&F2&", "&I2&", "&"0"}.

Formula Drop down from cell N2 till end.

Copy whole column N data & past to newly open text (Notepad) file & save as .csv name which we opened.(save location is same as WB Data To Text.xlsm).

Close both - .csv file as well as .txt file.

Second .csv file and same procedures till last .csv file.

See attached files.
1 csv file which need to be convert to .txt file
2 Data To Text.xlsm which is mediator or say converter file.
3 Final result i.e. .txt file

Thank you and apologies.
cm01OCT2014bhav.csv
Data-To-Text.xlsm
cm01OCT2014bhav.txt
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40370006
Although the following is written in VBA, it could easily be tweaked to run in a VBScript environment.  For simplicity's sake, I assume that the csv files have been consolidated into a single import.csv file.  The output file will be import.txt.
Option Explicit

Sub Q_28526105()
    Dim strData As String
    Dim oRE As Object
    Dim oMatches As Object
    Dim oM As Object
    Dim oSM As Object
    
    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Dim oFS As Object, oTS As Object
    Set oFS = CreateObject("Scripting.FileSystemObject")

    Dim lngSM As Long
    Dim vOutput As Variant
    Const cPath As String = "C:\Users\AikiMark\Downloads\"
    Const cHeader As String = "<ticker>,<per>,<date>,<open>,<high>,<low>,<close>,<vol>,<o/i>"
    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "([^,]*),[^,]*,([^,]*),([^,]*),([^,]*),([^,]*),[^,]*,[^,]*,([^,]*),[^,]*,([^,]*),.*?,\n"
    
    Set oTS = oFS.OpenTextFile(cPath & "import.csv", ForReading, False, False)
    strData = oTS.readall
    oTS.Close
    Set oTS = oFS.OpenTextFile(cPath & "import.txt", ForWriting, True, False)
    oTS.writeline cHeader
    
    If oRE.test(strData) Then
        ReDim vdata(1 To 9)
        Set oMatches = oRE.Execute(strData)
        For Each oM In oMatches
            With oM
                If .submatches(0) = "SYMBOL" Then
                Else
                    vdata(1) = .submatches(0)
                    vdata(2) = "D"
                    vdata(3) = Format(.submatches(6), "yyyymmdd")
                    vdata(4) = .submatches(1)
                    vdata(5) = .submatches(2)
                    vdata(6) = .submatches(3)
                    vdata(7) = .submatches(4)
                    vdata(8) = .submatches(5)
                    vdata(9) = 0
                    oTS.writeline Join(vdata, ",")
                End If
            End With
        Next
        oTS.Close
    End If
    
End Sub

Open in new window

This code could also be tweaked to iterate the daily CSV files.

This just seemed to be a cleaner and more efficient way of doing this conversion than Excel formulas.

Note: the output header string has been tweaked to remove spaces, conforming to the CSV standard.
0
 
LVL 8

Author Comment

by:itjockey
ID: 40370185
Mr.aikimark,

This is my exact location of .csv files.....let me know what change I have to do.My .csv file locations
This debugging message - if I run code with my existing location.Code error

Thank you
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40370406
Run that COPY command I posted earlier before invoking this routine.  The COPY command will create the import.csv file.  If this does the correct conversion, the COPY command invocation can be incorporated into the process.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40370720
It occurred to me that I can use a slightly different pattern to exclude the column header lines.  This makes the VBA/VBScript code a bit simpler.
Option Explicit

Sub Q_28526105()
    Dim strData As String
    Dim oRE As Object
    Dim oMatches As Object
    Dim oM As Object
    Dim oSM As Object
    
    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Dim oFS As Object, oTS As Object
    Set oFS = CreateObject("Scripting.FileSystemObject")

    Dim lngSM As Long
    Dim vOutput As Variant
    Const cPath As String = "C:\Users\AikiMark\Downloads\"
    Const cHeader As String = "<ticker>,<per>,<date>,<open>,<high>,<low>,<close>,<vol>,<o/i>"
    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "(\w[^,]*),[^,]*,(\d[^,]*),([^,]*),([^,]*),([^,]*),[^,]*,[^,]*,([^,]*),[^,]*,([^,]*),.*?,\n"
    
    Set oTS = oFS.OpenTextFile(cPath & "import.csv", ForReading, False, False)
    strData = oTS.readall
    oTS.Close
    Set oTS = oFS.OpenTextFile(cPath & "import.txt", ForWriting, True, False)
    oTS.writeline cHeader
    
    If oRE.test(strData) Then
        ReDim vdata(1 To 9)
        Set oMatches = oRE.Execute(strData)
        For Each oM In oMatches
            With oM
                vdata(1) = .submatches(0)
                vdata(2) = "D"
                vdata(3) = Format(.submatches(6), "yyyymmdd")
                vdata(4) = .submatches(1)
                vdata(5) = .submatches(2)
                vdata(6) = .submatches(3)
                vdata(7) = .submatches(4)
                vdata(8) = .submatches(5)
                vdata(9) = 0
                oTS.writeline Join(vdata, ",")
            End With
        Next
        oTS.Close
    End If
    
End Sub

Open in new window

0
 
LVL 8

Author Comment

by:itjockey
ID: 40372365
Mr.aikimark,

Will you pls post your sample WB with whole code inside - as I am not much familiar with coding.

Thank You
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40372711
You are already doing what you need to do to test this code.  Just place code into a module in your workbook.

The copy command should be run from a command prompt.
0
 
LVL 8

Author Comment

by:itjockey
ID: 40378171
Extremely  Sorry for Delay In Reply.

Cant we incorporate both (Code and copy Command which you suggested) in Excel VBA Code it self?


Thanks
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 45

Expert Comment

by:aikimark
ID: 40378378
we can.

Please run the copy command manually and test the VBA code first.
0
 
LVL 8

Author Comment

by:itjockey
ID: 40379212
This is the messages - Command promptAfter running command prompt
Thank you
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40379573
I said you would have to run the copy command from a command prompt.

Do you know how to open a command prompt window?
Do you know how to change the current directory?
0
 
LVL 8

Author Comment

by:itjockey
ID: 40380874
Do you know how to open a command prompt window?

YesCMD
Do you know how to change the current directory?
No
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40380891
it is the CD command, which is short for chdir
0
 
LVL 8

Author Comment

by:itjockey
ID: 40381076
Mr.aikimark,

I am not familiar with this kind of technical jargon. Apology but I dint get you.

Thanks
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 40381145
copy the desired path from windows explorer (such as C:\mydir\anotherdir) and put the letters cd in front of it.  If the path contains spaces, enclose in quotes:

cd "C:\Program Files\Microsoft\Another Directory"

In the DOS windows, you should then see the directory listed at the prompt:

C:\Program Files\Microsoft\Another Directory>

you can also use "dir" at the command line to see what files and directories are there:

C:\>dir
{listing will appear here}
C:\>
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40381317
the command prompt on your system is configured to display the current path (drive and directory)
C:\Users\Naresh

you need to change to the C:\Test directory using the CD command before you issue the copy command
0
 
LVL 8

Author Comment

by:itjockey
ID: 40381579
Done.....!!!

I had put all files in C:\User\Naresh   and it is working as you described. I had tried to change in CMD path C:\User\Naresh to C:\Test   but I cant so I had done via this way.

Now please let me know how we incorporate this two process in Excel VBA.

Thanks
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40381929
Like this:
Sub Q_28526105()
    Dim strData As String
    Dim oRE As Object
    Dim oMatches As Object
    Dim oM As Object
    Dim oSM As Object
    Dim oWsh As Object
    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Dim oFS As Object, oTS As Object
    Dim lngSM As Long
    Dim vOutput As Variant
    Const cPath As String = "C:\Test\"
    Const cHeader As String = "<ticker>,<per>,<date>,<open>,<high>,<low>,<close>,<vol>,<o/i>"
    
    Set oWsh = CreateObject("wscript.shell")
    Set oFS = CreateObject("Scripting.FileSystemObject")

    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "(\w[^,]*),[^,]*,(\d[^,]*),([^,]*),([^,]*),([^,]*),[^,]*,[^,]*,([^,]*),[^,]*,([^,]*),.*?,\n"
    
    'combine csv files
    oWsh.Run "cmd /c copy " & cPath & "*.csv " & cPath & "import.csv", 0, True
    
    Set oTS = oFS.OpenTextFile(cPath & "import.csv", ForReading, False, False)
    strData = oTS.readall
    oTS.Close
    Set oTS = oFS.OpenTextFile(cPath & "import.txt", ForWriting, True, False)
    oTS.writeline cHeader
    
    If oRE.test(strData) Then
        ReDim vdata(1 To 9)
        Set oMatches = oRE.Execute(strData)
        For Each oM In oMatches
            With oM
                vdata(1) = .submatches(0)
                vdata(2) = "D"
                vdata(3) = Format(.submatches(6), "yyyymmdd")
                vdata(4) = .submatches(1)
                vdata(5) = .submatches(2)
                vdata(6) = .submatches(3)
                vdata(7) = .submatches(4)
                vdata(8) = .submatches(5)
                vdata(9) = 0
                oTS.writeline Join(vdata, ",")
            End With
        Next
        oTS.Close
    End If
    
End Sub

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 40385296
A) No Need To Run Copy Command - Done This Part.
Too late.  I waited until you tested the code.  The copy command is now part of the process.

.txt File Output is some thing Wrong.
Please be more specific about what is "wrong"

What does the import file look like in notepad?  Show or upload the file.  The Excel importing process shows us modified file contents.  The code acts on the actual c:\test\import.csv
0
 
LVL 8

Author Comment

by:itjockey
ID: 40385313
Sorry For Delay In Reply.

I Guess Some Thing Is Wrong. Part A - Creating import.csv is perfect but creating import.txt is wrong.

This is import.csvimport.csvThis is import.txt createdimport.txtActually it must look like this  Proper txt file
Run on only one file just for testing. All File attached
1 Data To Text file from which code is executing.
2 .csv file which need to combine in import.csv (attached only one for testing)
3.import.csv (which code created)
4 import.txt (Which Code created)
5 import2.txt (Which is manfully created & import.txt must like this)

Thanks
Data-To-Text.xlsm
cm01APR2002bhav.csv
import.csv
import.txt
import2.txt
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40385492
Have you actually looked at your CSV files?!?  Every line ends with a comma and a linefeed character combination.  This is very different than the CSV files you posted before.  This can be addressed with a change to the regex pattern.

However, it is a prime example of looking at the actual data, rather than what some program like Excel thinks the data is (should look like)  There is one more field in your data than Excel revealed.
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 40385522
This version of the code:
Sub Q_28526105()
    Dim strData As String
    Dim oRE As Object
    Dim oMatches As Object
    Dim oM As Object
    Dim oSM As Object
    Dim oWsh As Object
    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Dim oFS As Object, oTS As Object
    Dim lngSM As Long
    Dim vOutput As Variant
    Const cPath As String = "C:\Test\"
    Const cHeader As String = "<ticker>,<per>,<date>,<open>,<high>,<low>,<close>,<vol>,<o/i>"
    
    Set oWsh = CreateObject("wscript.shell")
    Set oFS = CreateObject("Scripting.FileSystemObject")

    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "(\w[^,]*),[^,]*,(\d[^,]*),([^,]*),([^,]*),([^,]*),[^,]*,[^,]*,([^,]*),[^,]*,([^,]*),\n"
    
    'combine csv files
    oWsh.Run "cmd /c copy " & cPath & "*.csv " & cPath & "import.csv", 0, True
    
    Set oTS = oFS.OpenTextFile(cPath & "import.csv", ForReading, False, False)
    strData = oTS.readall
    oTS.Close
    Set oTS = oFS.OpenTextFile(cPath & "import.txt", ForWriting, True, False)
    oTS.writeline cHeader
    
    If oRE.test(strData) Then
        ReDim vdata(1 To 9)
        Set oMatches = oRE.Execute(strData)
        For Each oM In oMatches
            With oM
                vdata(1) = .submatches(0)
                vdata(2) = "D"
                vdata(3) = Format(.submatches(6), "yyyymmdd")
                vdata(4) = .submatches(1)
                vdata(5) = .submatches(2)
                vdata(6) = .submatches(3)
                vdata(7) = .submatches(4)
                vdata(8) = .submatches(5)
                vdata(9) = 0
                oTS.writeline Join(vdata, ",")
            End With
        Next
        oTS.Close
    End If
    
End Sub

Open in new window

Produced the results in the attached file.
import.txt
0
 
LVL 8

Author Closing Comment

by:itjockey
ID: 40385627
Perfect
0
 
LVL 8

Author Comment

by:itjockey
ID: 40387423
May I put some request?

 as I see you have gave me two codes. one is working if there is 11 column data and other working good with 13 column data. I have 1000 files and that are mix with this kind of variation i.e. 11 column or 13 column so will you please amend code which identify column count and execute that way as it is very difficult to open each file and execute as per its column count.

 This code working good with 11 column data i.e. 11 column data
Sub Q_28526105()
    Dim strData As String
    Dim oRE As Object
    Dim oMatches As Object
    Dim oM As Object
    Dim oSM As Object
    Dim oWsh As Object
    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Dim oFS As Object, oTS As Object
    Dim lngSM As Long
    Dim vOutput As Variant
    Const cPath As String = "C:\Test\"
    Const cHeader As String = "<ticker>,<per>,<date>,<open>,<high>,<low>,<close>,<vol>,<o/i>"
    
    Set oWsh = CreateObject("wscript.shell")
    Set oFS = CreateObject("Scripting.FileSystemObject")

    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "(\w[^,]*),[^,]*,(\d[^,]*),([^,]*),([^,]*),([^,]*),[^,]*,[^,]*,([^,]*),[^,]*,([^,]*),\n"
    
    'combine csv files
    oWsh.Run "cmd /c copy " & cPath & "*.csv " & cPath & "import.csv", 0, True
    
    Set oTS = oFS.OpenTextFile(cPath & "import.csv", ForReading, False, False)
    strData = oTS.readall
    oTS.Close
    Set oTS = oFS.OpenTextFile(cPath & "import.txt", ForWriting, True, False)
    oTS.writeline cHeader
    
    If oRE.test(strData) Then
        ReDim vdata(1 To 9)
        Set oMatches = oRE.Execute(strData)
        For Each oM In oMatches
            With oM
                vdata(1) = .submatches(0)
                vdata(2) = "D"
                vdata(3) = Format(.submatches(6), "yyyymmdd")
                vdata(4) = .submatches(1)
                vdata(5) = .submatches(2)
                vdata(6) = .submatches(3)
                vdata(7) = .submatches(4)
                vdata(8) = .submatches(5)
                vdata(9) = 0
                oTS.writeline Join(vdata, ",")
            End With
        Next
        oTS.Close
    End If
    
End Sub

Open in new window


Code worked well with 13 column data i.e.13 column data
Sub Q_28526105()
    Dim strData As String
    Dim oRE As Object
    Dim oMatches As Object
    Dim oM As Object
    Dim oSM As Object
    Dim oWsh As Object
    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Dim oFS As Object, oTS As Object
    Dim lngSM As Long
    Dim vOutput As Variant
    Const cPath As String = "C:\Test\"
    Const cHeader As String = "<ticker>,<per>,<date>,<open>,<high>,<low>,<close>,<vol>,<o/i>"
    
    Set oWsh = CreateObject("wscript.shell")
    Set oFS = CreateObject("Scripting.FileSystemObject")

    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "(\w[^,]*),[^,]*,(\d[^,]*),([^,]*),([^,]*),([^,]*),[^,]*,[^,]*,([^,]*),[^,]*,([^,]*),.*?,\n"
    
    'combine csv files
    oWsh.Run "cmd /c copy " & cPath & "*.csv " & cPath & "import.csv", 0, True
    
    Set oTS = oFS.OpenTextFile(cPath & "import.csv", ForReading, False, False)
    strData = oTS.readall
    oTS.Close
    Set oTS = oFS.OpenTextFile(cPath & "import.txt", ForWriting, True, False)
    oTS.writeline cHeader
    
    If oRE.test(strData) Then
        ReDim vdata(1 To 9)
        Set oMatches = oRE.Execute(strData)
        For Each oM In oMatches
            With oM
                vdata(1) = .submatches(0)
                vdata(2) = "D"
                vdata(3) = Format(.submatches(6), "yyyymmdd")
                vdata(4) = .submatches(1)
                vdata(5) = .submatches(2)
                vdata(6) = .submatches(3)
                vdata(7) = .submatches(4)
                vdata(8) = .submatches(5)
                vdata(9) = 0
                oTS.writeline Join(vdata, ",")
            End With
        Next
        oTS.Close
    End If
    
End Sub

Open in new window


If you want me to put fresh question then pls let me know I will do the same.

Thanks
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40387599
Try using this pattern.  It seems to work with both sets of files.
(\w[^,]*),[^,]*,(\d[^,]*),([^,]*),([^,]*),([^,]*),[^,]*,[^,]*,([^,]*),[^,]*,([^,]*).*?,\n

Open in new window

0
 
LVL 8

Author Comment

by:itjockey
ID: 40389144
Awesome working perfect.... thank you very much.
0
 
LVL 8

Author Comment

by:itjockey
ID: 40411595
Mr.aikimark,

Need your assistance to add one more step in existing code, please help me out.

before creating import.txt need sorting column A data in A to Z in .csv file except header & then create import.txt with sorted data.

Regarding this I has posted new question & this the Link.

Thank You
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now