Msflexgrid data

[andy999]

Hi all, Iam newbe to this forum and seeks some help. I am using a msflexgrid in vb6 which uploads data from the net. What I want the uploaded data in msflexgrid having 10 cols and 10 rows to be saved in access database and also want to save the uploaded data in a .dat file in application path directory. Since the data is uploaded continuously from the net in say every 20 minute so I want the uploaded data to be saved in .dat file format(with name) of the each and every name of the ist row and ist col of msflexgrid i.e should be parsed with each name, and also in access database.
The format od the data in msflexgrid as foloows:
Name salary date increment %change lastyrsalary Timeofentry .........
Andy $2000 03/08/2008 $100 0.2% 1900 hhmm .........
Alex $4500 ......do......................................................................................
so One csv file should be in the name of Andy and other is Alex and so on....
Can anyone help pe out of this...
Thanks in advance.

[choudhuryshouvi]

If I was not wrong the term should be "Download" instead of "Upload".

Check out this sample code, There you will find some syntax on how to add data from msflexgrid to database.

regards
Shouvik

[andy999]

sorry for late reply,
Thanks for the prompt reply. Sorry to say that this is not which can solve my problem.
Here I am also enclosing a rar extention file in which there is a sample.This is a sample which is good. But In this sample I have to select again and again the file type to parse in different csv file. Here I need the help.I want that if the timer function is added that should auto open and select the path of the file and read csv file and auto load the parsed files in data folder.please look into this file.
BTW thanks again for help.

[andy999]

sorry again perhaps the file has not been attached . I am again trying to attach it.

[choudhuryshouvi]

hi andy,

as far as I see from ur attachment,
you r accepting path of a .csv file and then clicking on import, create a data folder in ur application path and then creating a separate tect file for each rows presented in the csv file.

ok as i read in ur post you want an automated process and this is exactly what is going on in ur existing project.

so what else now u r expecting to do from us?

[andy999]

Thanx for reply
First of all as you have seen that In this project Using commondialog I have to select again and again the csv file and save to the selected folder. Can this be possible without comondialog so In the running process it select automatically the csv file and save it automatically after every one minute using timer control. Or Can this code be used in excel through vba ,if so how?
Thanx again

[choudhuryshouvi]

ok now i understood what you wish to do.
that's not a big issue to solve.
i'll post the code here.

just tell me one thing,
will the csv files be located in the application folder always or might it be stored in other locations also?

there is no need to use the timer control. u can easily fetch all csv files located inside a directory and create an array of those. then running a loop, taking an element from the array one by one, parsing data and creating the text file will do the job.

[andy999]

The path of the file is D:\Temp\Database\mst.csv
Thanx.

[choudhuryshouvi]

ok andy, i think the following code is the complete solution for your question. just place a textbox, a button and a listbox on your form for testing. some changes i have made here over your logic and they are :-

1. you don't need to fix the location of the data folder. this code will create the data folder automatically in the same location from where the csv files are taken.
2. just type a valid path in the textbox and click the button to execute the code. for example, as you have given the path just type D:\Temp\Database in the textbox and click the button. don't mention any filename in the path. this code will automatically extract all csv stored inside this folder for you. if everything goes well you will be notified by a msg which is "Extraction complete."

plz give me a feedback here.

Option Explicit

Dim source_path As String

''this function will fetch all csv files stored inside the directory specified in csv_path variable ''as a parameter and add them in a listbox
[B]Public Function GetCSVFiles(ByVal csv_path As String) As Boolean[/B]
Dim FileName As String, ext As String, pos As Integer, i As Integer

On Error GoTo load_error

If Right(csv_path, 1) <> "\" Then
    csv_path = csv_path & "\"
Else
    csv_path = csv_path
End If

If Dir(Trim(csv_path)) = "" Then
    GetCSVFiles = False
    Exit Function
End If

source_path = csv_path
FileName = csv_path & "*.*"

Do Until FileName = ""
    For i = Len(FileName) To 1 Step -1
        pos = InStr(i, FileName, ".")
        If pos > 0 Then Exit For
    Next i
    ext = LCase(Mid(FileName, pos + 1))
    If ext = "csv" Then
        List1.AddItem FileName
    End If
    FileName = Dir()
    Randomize
    If FileName = "" Then
        Exit Do
    End If
Loop

GetCSVFiles = True

Exit Function

load_error:
    Err.Clear
    GetCSVFiles = False
    Exit Function
[B]End Function[/B]

''this routine will do the actual job. it will read csv file one by one from the listbox and then ''parse it data to create a separate text file.
[B]Private Sub Command1_Click()[/B]
Dim i As Integer, fso As New FileSystemObject

If GetCSVFiles(Trim(Text1.Text)) = False Then
    MsgBox "File extraction error."
    Exit Sub
End If

On Error GoTo err1

If List1.ListCount > 0 Then
    If fso.FolderExists(source_path & "Data\") = False Then
        fso.CreateFolder (source_path & "Data")
    End If
    For i = 0 To List1.ListCount - 1 Step 1
        Call CreateTextFromCSV(source_path & List1.List(i))
    Next i
    MsgBox "Extraction complete."
End If

Exit Sub

err1:
    Err.Clear
    MsgBox "Some files could not be generated."
    Exit Sub
[B]End Sub[/B]

''this routine will be called from command1_click() event to create the text file with parsed ''data from the csv file. the path to the csv file will be passed as parameter in the ''csv_filename variable.
[B]Public Sub CreateTextFromCSV(ByVal csv_filename As String)[/B]
Dim fso, act
Dim total_imported_text As String, total_split_text() As String, myData As String, comma_split() As String
Dim dt1 As Date, dt2 As Date, Fileld2OfExcel As String, myData1 As String, txt() As String
Dim total_num_imported As Double, i As Integer
Dim L_B_Found As Boolean, Less_D_Found As Boolean
                
Set fso = CreateObject("scripting.filesystemobject")
Set act = fso.OpenTextFile(csv_filename)

total_imported_text = act.ReadAll
total_imported_text = Replace(total_imported_text, Chr(13), "*")
total_imported_text = Replace(total_imported_text, Chr(10), "*")
total_imported_text = Replace(total_imported_text, Chr(34), "")
total_split_text = Split(total_imported_text, "*")
total_num_imported = UBound(total_split_text)
For i = 1 To total_num_imported - 1
    comma_split = Split(total_split_text(i), ",")
    On Error Resume Next
    If comma_split(0) <> "" Then
        Fileld2OfExcel = Trim(Mid(comma_split(0), 2))
        If Fileld2OfExcel <> "" Then
            If Dir(source_path & "Data\" & comma_split(0) & ".txt") = "" Then
                Open source_path & "Data\" & comma_split(0) & ".txt" For Output As #1
                    Print #1, Format$(comma_split(10), "dd/mm/yyyy") & "," & comma_split(2) & "," & _
                        comma_split(3) & "," & comma_split(4) & "," & comma_split(5) & "," & comma_split(8)
                Close #1
        Else
            L_B_Found = False
            Less_D_Found = False
            myData = ""
            myData1 = ""
            Open source_path & "Data\" & comma_split(0) & ".txt" For Input As #1
                Do While Not EOF(1)
                    Line Input #1, myData
                    If myData <> "" Then
                       txt = Split(myData, ",")
                       dt1 = CDate(comma_split(10))
                       dt2 = CDate(Format(txt(0), "dd/mm/yyyy"))
                       If dt1 = dt2 Then
                          L_B_Found = True
                          myData1 = myData1 & myData & vbCrLf
                       ElseIf dt1 < dt2 Then
                          If Less_D_Found = False Then
                             Less_D_Found = True
                             myData1 = myData1 & Format$(comma_split(10), "dd/mm/yyyy") & "," & comma_split(2) & "," & comma_split(3) & "," & comma_split(4) & "," & comma_split(5) & "," & comma_split(8) & vbCrLf & myData & vbCrLf
                          Else
                          L_B_Found = False
                             myData1 = myData1 & myData & vbCrLf
                          End If
                       Else
                          myData1 = myData1 & myData & vbCrLf & Format$(comma_split(10), "dd/mm/yyyy") & "," & comma_split(2) & "," & comma_split(3) & "," & comma_split(4) & "," & comma_split(5) & "," & comma_split(8) & vbCrLf
                       End If
                    End If
                Loop
                Close #1
                If L_B_Found = False Then
                    Open source_path & "Data\" & comma_split(0) & ".txt" For Output As #1
                         Print #1, myData1
                    Close #1
              End If
           End If
        End If
     End If
Next i
[B]End Sub[/B]

regards
Shouvik

[andy999]

Hi Shouvik,
I implemented as per your instruction but I get file extraction error.
I am enclosing the zip file .Please look into this.
Thanx

[choudhuryshouvi]

did you read my previous reply thoroughly?

i checked the zip file and what i found is you have modified the code and you did it in wrong way. check my previous reply, there i already said don't include any file name in the textbox where you are typing the path , i.e., don't type like D:\Import\mstfile.csv. just type the path only i.e., D:\Import. the code will fetch all csv files located in the folder.

and the other one is ,
delete this line csv_path = "D:\import\mst.csv"
inside the function GetCSVFiles.

i hope this should now solve your problem.

regards
Shouvik

[andy999]

Thanx for guiding me great support.
I got your point and corrected . My problem has been solved now.I pay my regrads for your effort and prompt reply.Rather I would say this is one of the greatest site for novice .
Thank you very much.

[choudhuryshouvi]

you are welcome my frnd.
now if you got solution to your question completely then plz mark this thread as solved.