Solved: Advanced import of txt files data in to Excel

[ferverx]

Hello!

I hava a lot of txt files (see attachment) which from I need to import data into excel file. The problem is to:
1)import those data so that sheet name matches the five symbols (exch line of txt file starts with different characters so there will be for example 3 sheets - for three lines), if there is not such a sheet, it will be created automaticaly.
2)the next thing is date, which comes second (after coma) in txt file (and other data which has to be puted in table so that first column is date, second is other data, what comes after coma in txt file.
3)txt file name is "yyyymmdd" format mybe ther's a way to create log of all imported files, so that excel won't try to import data again or make dublicates.
4)records in excel are stored ascending by date - the are no dublicated records (by date)

Thanks in advance!

[Killian]

Hi and welcome to VBAX :hi:

This seems quite straight-forward - the data can be read using VBA's Input # function - the main problem is keeping the files and records organised...

I have some questions:

A line of data from the file you attached looks like this:
ALT1L,D,11/14/2005,3.15,3.20,3.15,3.20,3179
the second item is "D", not the date - what happens with the "D"?

Do you need to select a folder to search or will it always be the same?

Rather than keeping a log of the files processed, would it be OK for the code to move the data file to a subfolder (maybe named "Processed")

[Killian]

I got some code together to start you off...
Note: I've ignored the "D" in you records and hard-coded the file paths
You might also need to add a test to check there isn't a record for that date already on the worksheet

Enjoy :)

Sub ProcessDataFiles()

'declare object variables for file/folders
'uses late-binding
Dim fso As Object
Dim fldrSource As Object
Dim fldrDone As Object
Dim f As Object
'declare object variables for workbook
Dim ws As Worksheet
Dim wsTargetSheet As Worksheet
'declare variables for data
Dim strRecordCode As String, strLetter As String
Dim arrRecord(1 To 6) As String

'create filesystem
Set fso = CreateObject("Scripting.FileSystemObject")
Set fldrSource = fso.GetFolder("C:\DataFiles")
Set fldrDone = fso.GetFolder("C:\DataFiles\Processed")

For Each f In fldrSource.Files 'loop through each file in the folder
'open the file for processing
Open fldrSource.Path & "\" & f.Name For Input As #1

Do While Not EOF(1) ' Loop until end of file.
'read record into variables
Input #1, strRecordCode, strLetter, arrRecord(1), arrRecord(2), _
arrRecord(3), arrRecord(4), arrRecord(5), arrRecord(6)
'assign worksheet for record
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = strRecordCode Then
Set wsTargetSheet = ws
Exit For
End If
Next
'if worksheet for record doesn't exist, make new
If wsTargetSheet Is Nothing Then
Set wsTargetSheet = ActiveWorkbook.Worksheets.Add
wsTargetSheet.Name = strRecordCode
End If
'add record to worksheet
With wsTargetSheet
.Range((.Cells(TargetRow(wsTargetSheet), 1)), .Cells(TargetRow(wsTargetSheet), 6)) = arrRecord()
End With
Set wsTargetSheet = Nothing
Loop
Close #1
'move processed file
f.Move fldrDone.Path & "\" & f.Name
Next f

End Sub

Function TargetRow(ws As Worksheet) As Long
'function to return the row index of the first empty row
Dim lngLastRow As Long
lngLastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
If IsEmpty(ws.Cells(lngLastRow, 1)) Then
TargetRow = 1
Else
TargetRow = lngLastRow + 1
End If
End Function

[ferverx]

Many thanx to you, Killian!!
The code works, I am wery happy of your help:bow: !
1)The thing is :think: that date is not recognized as date, maybe there's some way to code the first column to always be the date?
2)what would the "test" code look like, it would be great
3)is there a way to add code that checks, wether there are data files left, if not it asks "maybe you want to specifay another location" and then takes file from there?
4)the primary data are found here (http://market.rfb.lv/upload/statistics/metastock/), so maybe there is a way to take the file directly from internet (maybe some dialog, asking if I want to use local or web folder)?

Many thanx!

[Killian]

Your welcome, happy to help...

Now for the fine tuning:
1) I think the best thing to do here is add some more variables of the correct data types (Date and Single for the data) and cast them after the text has been read in. That way Excel will use their format.
2) to test for an exisiting entry by date, we'll add a function that uses "Find" on the target worksheet
3) it might be better to prompt the user for a target folder at the start, then loop that until the user cancels the folder path prompt

Rather than have pages of code posted, I've attached a file with the changes...
I see a couple of problems, though:
1) the processed files folder is still always the same (maybe thats not a problem) although now we check the records for duplicates, maybe it's redundant?)
2) The code scans ALL the files in a folder - you might want to trap errors (in case there are other files in that folder) or validate the Input # has got the expected results before you try to add the record