.csv To .txt
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
See attached sample .csv file Data To Text.xlsm & Final Result .txt File
Thank you Very Much
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 – A204.
Copy Range K2 till End (In .csv) – Past to WB Data To Text.xlsm – B205.
Copy Range C2:F2 till end (in .csv) - Past to WB Data To Text.xlsm – C206.
Copy Range I2 till end (in .csv) - Past to WB Data To Text.xlsm – G207.
Past This Formula in Data To Text.xlsm - Cell H2 “=A2&", "&"D"&", "&TEXT(B2,"yyyymmd08.
Open New notepad09.
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 End12.
Run till last .csv completed.See attached sample .csv file Data To Text.xlsm & Final Result .txt File
Thank you Very Much
ASKER
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
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
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
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
ASKER
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
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
ASKER
Any Luck?
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
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
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:
Concatenating command:
copy *.csv Import.csv /bI 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.
Past This Formula in Data To Text.xlsm - Cell H2 “=A2&", "&"D"&", "&TEXT(B2,"yyyymmdd")&am p;", "&C2&", "&D2&", "&E2&", "&F2&", "&G2” &
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.
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.
@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' :)
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' :)
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
However, I see what seems to be a bug, where the first D should probably be D2.
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&", "&G2However, I see what seems to be a bug, where the first D should probably be D2.
ASKER
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.
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
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
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.
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.
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 SubThis 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.
ASKER
Mr.aikimark,
This is my exact location of .csv files.....let me know what change I have to do.
This debugging message - if I run code with my existing location.
Thank you
This is my exact location of .csv files.....let me know what change I have to do.
This debugging message - if I run code with my existing location.
Thank you
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.
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 SubASKER
Mr.aikimark,
Will you pls post your sample WB with whole code inside - as I am not much familiar with coding.
Thank You
Will you pls post your sample WB with whole code inside - as I am not much familiar with coding.
Thank You
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.
The copy command should be run from a command prompt.
ASKER
Extremely Sorry for Delay In Reply.
Cant we incorporate both (Code and copy Command which you suggested) in Excel VBA Code it self?
Thanks
Cant we incorporate both (Code and copy Command which you suggested) in Excel VBA Code it self?
Thanks
we can.
Please run the copy command manually and test the VBA code first.
Please run the copy command manually and test the VBA code first.
ASKER
This is the messages - 
Thank you
Thank you
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?
Do you know how to open a command prompt window?
Do you know how to change the current directory?
ASKER
Do you know how to open a command prompt window?
Yes
Do you know how to change the current directory?No
it is the CD command, which is short for chdir
ASKER
Mr.aikimark,
I am not familiar with this kind of technical jargon. Apology but I dint get you.
Thanks
I am not familiar with this kind of technical jargon. Apology but I dint get you.
Thanks
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:\>
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:\>
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
C:\Users\Naresh
you need to change to the C:\Test directory using the CD command before you issue the copy command
ASKER
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
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
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 SubA) 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
ASKER
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.csv
This is import.txt created
Actually it must look like this 
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
I Guess Some Thing Is Wrong. Part A - Creating import.csv is perfect but creating import.txt is wrong.
This is import.csv
This is import.txt createdActually it must look like this 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
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect
ASKER
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.
Code worked well with 13 column data i.e.
If you want me to put fresh question then pls let me know I will do the same.
Thanks
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.
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 SubCode worked well with 13 column data i.e.
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 SubIf you want me to put fresh question then pls let me know I will do the same.
Thanks
Try using this pattern. It seems to work with both sets of files.
(\w[^,]*),[^,]*,(\d[^,]*),([^,]*),([^,]*),([^,]*),[^,]*,[^,]*,([^,]*),[^,]*,([^,]*).*?,\nASKER
Awesome working perfect.... thank you very much.
ASKER
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
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
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