Autofreak
asked on
reading into csvs from other csv
Hello All!
I get a huge ABC.csv file that I would like to sort out into a number of new csvs - a,b,c. Depending on each ABC.csv string's pattern I want to put it into one of the newly created csv files - a,b, or c. Could you please help me with this leaving out the string analysis part. I'd like the program to check if there are a,b, and c already in the designated MyFolder folder and if there are not create them anew. Also, every time I run the program, the new data have to override the old ones.
Thanks you very much
Serge
I get a huge ABC.csv file that I would like to sort out into a number of new csvs - a,b,c. Depending on each ABC.csv string's pattern I want to put it into one of the newly created csv files - a,b, or c. Could you please help me with this leaving out the string analysis part. I'd like the program to check if there are a,b, and c already in the designated MyFolder folder and if there are not create them anew. Also, every time I run the program, the new data have to override the old ones.
Thanks you very much
Serge
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks Shauli ,
can't wait to check the code, will get back to you tomorrow.
Take care!
Serge
can't wait to check the code, will get back to you tomorrow.
Take care!
Serge
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What does this line do with the file?
aryRecords = Split(ABC.csv, vbCrLf)
aryRecords = Split(ABC.csv, vbCrLf)
aryRecords = Split(ABC.csv, vbCrLf)
Splits the file by LineFeed
Each Line is now a Record i.e. aryRecords(idxRecord) where idxRecord = 0 is actuall line 1 of the data
aryFields = Split(aryRecords(idxRecord ), ",")
Splits each line from above in Fields (from a csv file)
Thus aryFields(0) is the First Field of any Line
Consider the line (The, Dog, Ran, Fast)
aryFields(0) would be The
aryFields(1) would be Dog
aryFields(2) would be Ran
.................
Plus in my code I made a slight mistake.
I am going to correct it.
Do this.
Add the Component: Microsoft Common Dialog Control 6.0 under Project--> Components
Double Click it to add one to forms and accept default name.
Add one command button to form and accept default name.
Add the following Code:
Option Explicit
Option Explicit
Public Function FileExists(ByVal sFile As String) As Boolean
On Error Resume Next
FileLen sFile
FileExists = (Err.Number = 0)
End Function
Public Function GetFile(ByVal sFile As String) As String
Dim hFile As Integer
If FileExists(sFile) Then
hFile = FreeFile()
Open sFile For Binary As #hFile
GetFile = Input(LOF(hFile), hFile)
Close #hFile
End If
End Function
Private Sub Command1_Click()
On Error GoTo err_open
Dim aryRecords() As String
Dim aryFields() As String
Dim idxRecord As Long
Dim idxField As Long 'You could use this like idxRecord to look at each field in the line don't need it my example
'You'll be given a menu to pick ABC.csv
With CommonDialog1
.Filter = "All Files (*.*)|*.*"
.FilterIndex = 1
.Flags = cdlOFNOverwritePrompt
.InitDir = ""
.FileName = ""
.ShowOpen
End With
If CommonDialog1.FileName = "" Then Exit Sub
aryRecords = Split(GetFile(CommonDialog 1.FileName ), vbCrLf) 'Which will be ABC.csv if that's what u call it
Open "a.csv" For Output As #2
Open "b.csv" For Output As #3
Open "c.csv" For Output As #4
' Process the records:
For idxRecord = 0 To UBound(aryRecords) - 1
aryFields = Split(aryRecords(idxRecord ), ",")
Select Case aryFields(x) 'x would be field in the comma string to check for case 'i.e. aryFields(23) = "FirstFile"
Case "YourText1" ' make sure u make x a number else u will get an error -- x must be a valid number between 0 and UBound(aryRecords) -1
'Any other analysis
Print #2, aryRecords(idxRecord)
Case "YourText2"
'Any other analysis
Print #3, aryRecords(idxRecord)
Case "YourText3"
'Any other analysis
Print #4, aryRecords(idxRecord)
End Select
Next idxRecord
Exit Sub
err_open:
MsgBox Err.Description
Exit Sub
End Sub
Splits the file by LineFeed
Each Line is now a Record i.e. aryRecords(idxRecord) where idxRecord = 0 is actuall line 1 of the data
aryFields = Split(aryRecords(idxRecord
Splits each line from above in Fields (from a csv file)
Thus aryFields(0) is the First Field of any Line
Consider the line (The, Dog, Ran, Fast)
aryFields(0) would be The
aryFields(1) would be Dog
aryFields(2) would be Ran
.................
Plus in my code I made a slight mistake.
I am going to correct it.
Do this.
Add the Component: Microsoft Common Dialog Control 6.0 under Project--> Components
Double Click it to add one to forms and accept default name.
Add one command button to form and accept default name.
Add the following Code:
Option Explicit
Option Explicit
Public Function FileExists(ByVal sFile As String) As Boolean
On Error Resume Next
FileLen sFile
FileExists = (Err.Number = 0)
End Function
Public Function GetFile(ByVal sFile As String) As String
Dim hFile As Integer
If FileExists(sFile) Then
hFile = FreeFile()
Open sFile For Binary As #hFile
GetFile = Input(LOF(hFile), hFile)
Close #hFile
End If
End Function
Private Sub Command1_Click()
On Error GoTo err_open
Dim aryRecords() As String
Dim aryFields() As String
Dim idxRecord As Long
Dim idxField As Long 'You could use this like idxRecord to look at each field in the line don't need it my example
'You'll be given a menu to pick ABC.csv
With CommonDialog1
.Filter = "All Files (*.*)|*.*"
.FilterIndex = 1
.Flags = cdlOFNOverwritePrompt
.InitDir = ""
.FileName = ""
.ShowOpen
End With
If CommonDialog1.FileName = "" Then Exit Sub
aryRecords = Split(GetFile(CommonDialog
Open "a.csv" For Output As #2
Open "b.csv" For Output As #3
Open "c.csv" For Output As #4
' Process the records:
For idxRecord = 0 To UBound(aryRecords) - 1
aryFields = Split(aryRecords(idxRecord
Select Case aryFields(x) 'x would be field in the comma string to check for case 'i.e. aryFields(23) = "FirstFile"
Case "YourText1" ' make sure u make x a number else u will get an error -- x must be a valid number between 0 and UBound(aryRecords) -1
'Any other analysis
Print #2, aryRecords(idxRecord)
Case "YourText2"
'Any other analysis
Print #3, aryRecords(idxRecord)
Case "YourText3"
'Any other analysis
Print #4, aryRecords(idxRecord)
End Select
Next idxRecord
Exit Sub
err_open:
MsgBox Err.Description
Exit Sub
End Sub
I wasn't at a computer but I tested the above and it works on csv files just fine.
ASKER
Sorry Guys, I had so much on my plate on friday, didn't have a chance to try the code. I Will get back to you next week.
Have a great weekend,
Serge
Have a great weekend,
Serge
Ping
S
S
ASKER
Hey Guys,
it's just a crazy week, deadlines don't wait! I haven't tried the code yet but I have a feeling that those are excellent solutions. I learned a few things from both of you and split the mark even - rdwillett has put more effort but Shauli was first - fair? Great thanks! Keep up the good work!
Serge
it's just a crazy week, deadlines don't wait! I haven't tried the code yet but I have a feeling that those are excellent solutions. I learned a few things from both of you and split the mark even - rdwillett has put more effort but Shauli was first - fair? Great thanks! Keep up the good work!
Serge
S