Reading CSV file

[koie]

Hi guys,
I am strugling here with a CSV file.
The file starts of with a lot of blablabla..
After that is starts of with a line beginning with VOR_ where I want to fetch the second field.
After this line it I want to fetch all the fields in this line for about 20 lines.
Then it starts over with a line beginning with VOR_ and so on..

It looks somewhat like this:

rubish
rubish
rubish
rubish

VOR_1234,123456,,,,,,and_some_more
1,FIELD1,FIELD2,FIELD3,...,FIELD30
2,FIELD1,FIELD2,FIELD3,...,FIELD30
3,FIELD1,FIELD2,FIELD3,...,FIELD30
4,FIELD1,FIELD2,FIELD3,...,FIELD30
etc.
VOR_1235,654321,,,,,,and_some_more
etc.

I am trying to use a loop and the

Input #1, field1

function.
But can't get it to work.

As I am just an amateur in this and still learning,
I was wondering if someone could give me some advice on how to proceed.

Thanks in advance.

 

[gjconely]

I always use a Line Input command. Then seperate the CSV entries into an array using the SPLIT command. Here is an example

open "myfile.csv" for input as #1
while not eof(1)
line input #1, tmpvar
tmpArray=split(tmpvar,",")
....
wend

The tmpArray would then have each of the fields as an array item. The only problem with this is true CSVs allow commas within the field if the field is surrounded in quotes. for this I had to write my own split statement. let me know if this helps or if you need a copy of my modified split statement.


Greg Conely

http://www.ImageMentor.net

http://www.seo-position-plus.com

 

[johnwm]

As the file appears to have different structures on different lines it may be best to use the 'Line Input #' statement to get each line. You can determine if it's a VOR using the Left$ function and do a Split on the lines to extract your data

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[koie]

Whow...you guys are fast!!. I will give it a try
Thanks.

 

[johnwm]

Sorry - slow typist at work!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[koie]

I am getting close....

But i just discovered that there are "'s in my file what screws up my output.

This is what I have so far:

Open inputfile For Input As #1
look_for_sampleid:

While Not EOF(1)
Line Input #1, tmpstr
If Left$(tmpstr, 3) = "VOR" Then
    tmpArray = Split(tmpstr, ",")
    sampleid = tmpArray(1)
    
    Debug.Print "SampleID=" & sampleid
    
    
    
    GoTo endloop
End If
Wend

endloop:
While Not EOF(1)
Line Input #1, tmpstr
tmpArray = Split(tmpstr, ",")
component = tmpArray(1)
concentration = tmpArray(9)
Debug.Print "Component:" & component & "    Concentration:" & concentration
If Left$(tmpstr, 3) = "VOR" Then GoTo endloop2

Wend
endloop2:
End

and it gives me this output

SampleID=LCMS ISTD
Component:TMA    Concentration:0.000124253
Component:HBA    Concentration:0.00192976
Component:PA    Concentration:0
Component:HMBA    Concentration:0.000217865
Component:TCBP    Concentration:0
Component:TCB    Concentration:0.00127181
Component:BA    Concentration:0.00309794
Component:Acet-HMBA    Concentration:0.000218148
Component:p-TA    Concentration:0
Component:[b]"4[/b]    Concentration:bbbb
Component:[b]"2[/b]    Concentration:
Component:[b]"2[/b]    Concentration:
Component:[b]"2[/b]    Concentration:
Component:[b]"4[/b]    Concentration:dbdb
Component:MCF    Concentration:0.000345902
Component:MCA    Concentration:0.74392

The bold is were it goes wrong.

 

[TallOne]

Just to show a different approach, try an ADO connection and recordset to manipulate your data. To use this example set a reference to Microsoft ActiveX Data Objects 2.X Libray.

Option Explicit

Private cn As ADODB.Connection
Private rs As ADODB.Recordset
Private bolStartComponentData As Boolean
Private strCSVPath As String
Private strCSVFileName As String
Private Sub Form_Load()

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
With CommonDialog1
            .Filter = "CSV Files |*.csv"
            .DefaultExt = "csv"
            .Flags = cdlOFNHideReadOnly Or cdlOFNFileMustExist Or _
                cdlOFNNoReadOnlyReturn
            .DialogTitle = "Select Your File!"
            .CancelError = True
            .ShowOpen
            End With
            
            
            strCSVPath = ExtractPath(CommonDialog1.FileName)
            strCSVFileName = ExtractFileName(CommonDialog1.FileName)
With cn
    .CursorLocation = adUseClient
    .Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
        & "Data Source=" & strCSVPath & ";" _
        & "Extended Properties=""text;FMT=Delimited;HDR=NO"""
End With

With rs
    .Open "Select * From [" & strCSVFileName & "]", cn, _
            adOpenStatic, adLockPessimistic, adCmdText
    rs.ActiveConnection = Nothing
    
    If .BOF And .EOF Then
        MsgBox "Recordset Empty"
        .Close
        Exit Sub
    End If
    
    .MoveFirst
    
    Do While .EOF <> True
        If bolStartComponentData = True Then
            If Left$(.Fields(0).Value, 3) <> "VOR" Then
                GetComponentData
                Else: GetSampleID
                .MoveNext
            End If
        Else:
           .MoveNext
            If .EOF Then Exit Sub
            bolStartComponentData = (Left$(.Fields(1).Value, 3) = "VOR")
        End If
    Loop
End With
End Sub
Private Sub GetSampleID()
'Get your SampleID info here
Debug.Print "SampleID=" & .Fields(1).Value
End If

End Sub
Private Sub GetComponentData()
'Get your component info here
    Debug.Print "Component: " & .Fields(2).Value & vbTab & _
        "Concentration: " & .Fields(9).Value
End Sub
Private Function ExtractPath(path As String)
Dim i As Integer
For i = Len(path) To 1 Step -1
    If Mid$(path, i, 1) = "\" Then
        ExtractPath = Left$(path, i)
        Debug.Print Left$(path, i)
        Exit Function
    End If
Debug.Print Mid$(path, i, 1)
Next
End Function
Private Function ExtractFileName(path As String)
Dim i As Integer
For i = Len(path) To 1 Step -1
    If Mid$(path, i, 1) = "\" Then
        ExtractFileName = Right$(path, (Len(path) - i))
        Debug.Print Right$(path, (Len(path) - i))
        Exit Function
    End If
Next

End Function

This is not tested so Good Luck!

 

[ADoozer]

well its not the prettiest or most efficient of code, but it might do what you want

Option Explicit

Private Sub SplitSpecial(theString As String, theArray() As String)

    Dim i As Integer
    Dim arrayCount As Integer
    Dim flagPunctuate As Boolean
    Dim StartPos As Integer
    
    StartPos = 1
    arrayCount = 0
    flagPunctuate = False
    
    For i = 1 To Len(theString)
        If Mid(theString, i, 1) = "," Then
            If Not flagPunctuate Then
                ReDim Preserve theArray(arrayCount)
                theArray(arrayCount) = Mid(theString, StartPos, i - StartPos)
                StartPos = i + 1
                arrayCount = arrayCount + 1
            End If
        ElseIf Mid(theString, i, 1) = """" Then
            If flagPunctuate = True Then
                flagPunctuate = False
            Else
                flagPunctuate = True
            End If
        End If
    Next i
        
    If Not Mid(theString, Len(theString), 1) = "," Then
        ReDim Preserve theArray(arrayCount)
        theArray(arrayCount) = Mid(theString, StartPos, i - StartPos)
    End If
    
End Sub

Private Sub Command1_Click()
    
    Dim myfile As Integer
    Dim myString As String
    Dim myArray() As String
    Dim ValidData As Boolean
    
    ValidData = False
    
    myfile = FreeFile
    
    Open "c:\demofile.csv" For Input As #myfile
    While Not EOF(myfile)
        
        Line Input #myfile, myString
        If Left$(myString, 3) = "VOR" Then
            SplitSpecial myString, myArray
            'sampleid = myArray(1)
            'Debug.Print "SampleID=" & sampleid
            ValidData = True
        ElseIf ValidData = True Then
            SplitSpecial myString, myArray
            'component = tmpArray(1)
            'concentration = tmpArray(9)
            'Debug.Print "Component:" & component & "    Concentration:" & concentration
        End If
    Wend
    Close #myfile
    
End Sub

good luck

If somethings hard to do, its not worth doing - Homer Simpson

 

[koie]

Thanks you all for your responses. It is grately appreciated.

I have tried the above examples but still no luck. With the code from TallOne i am getting a "invallid us of NULL" on this line

bolStartComponentData = (Left$(.Fields(0).Value, 3) = "VOR")

This is were the blank line appears in the file.


and I haven't been able to figure out yet how Adoozer's code works but I will keep on trying. (best way to learn I guess)

Also I will post part of the CSV file I am trying to parse.
I am trying to automate resultentry to a Laboratory information Management System.

LIMS EXPORT FILE:
C:\resultfile\lims.csv
Mon Nov 29 14:11:02 2004
DATASET:
C:\MassLynx\TPA_IMPURITIES.PRO\Rotterdam txt file1.qld
Mon Nov 29 13:58:21 2004

SAMPLELIST:
C:\MassLynx\TPA_IMPURITIES.PRO\SampleDB\11242004A_TPA.SPL
Wed Nov 24 14:35:18 2004
QUANMETHOD:
C:\MASSLYNX\TPA_IMPURITIES.PRO\MethDB\LC_IMPURITIES.mdb
Mon Nov 22 09:45:32 2004
QUANCALIBRATION:
C:\MassLynx\TPA_IMPURITIES.PRO\CurveDB\TPACAL11152004.cdb
Mon Nov 15 14:30:08 2004

VOR_5352,LCMS ISTD,,,,,,24-Nov-04,14:53:34
1,TMA,236,2.989,0,1.299,110,4.6216e-005,bbbb,0.000124253,0,TIC,NaN,16,28107.3,204918,21.929,,,,,0,3,0,1,1,2.973,2.994,1.299,0,,0,,,,,0,183.5,0,0.00722265,0.0192604,0,
2,HBA,62,5,0,71.407,770,0.00254052,dddd,0.00192976,0,TIC,NaN,16,28107.3,204918,21.929,,,,,0,4.951,0,1,1,4.949,5.117,71.407,0,,0,,,,,0,292.677,0,0.0032547,0.00867919,0,
3,PA,76,5.373,0,-2419.06,-896,-0.0860651,MMMM,0,0,165.13,NaN,16,28107.3,204918,21.929,29-Nov-04,13:58:07,,,0,5.274,0,1,1,5.273,5.373,-2419.06,0,,0,,,,,0,6935.77,0,0.139122,0.370991,0,
4,HMBA,52,4.827,0,17.849,329,0.000635031,bbbb,0.000217865,0,TIC,NaN,16,28107.3,204918,21.929,,,,,0,4.712,0,1,1,4.743,4.861,17.849,0,,0,,,,,0,367.479,0,0.00184572,0.00492192,0,
5,TCBP,148,13.736,0,20.425,242,0.00072668,dsds,0,0,313.25,NaN,16,28107.3,204918,21.929,,,,,0,13.672,0,1,1,13.629,13.75,20.425,0,,0,,,,,0,204.512,0,0,0,0,
6,TCB,384,14.297,0,13.67,194,0.000486351,bbbb,0.00127181,0,TIC,NaN,16,28107.3,204918,21.929,,,,,0,14.163,0,1,1,14.244,14.402,13.67,0,,0,,,,,0,165.306,0,0.00632855,0.0168761,0,
7,BA,68,13.407,0,35.062,937,0.00124744,dddd,0.00309794,0,TIC,NaN,16,28107.3,204918,21.929,,,,,0,13.426,0,1,1,13.367,13.42,35.062,0,,0,,,,,0,683.885,0,0.0248645,0.0663053,0,
8,Acet-HMBA,92,15.317,0,2.57,95,9.1435e-005,bbbb,0.000218148,0,TIC,NaN,16,28107.3,204918,21.929,,,,,0,15.274,0,1,1,15.291,15.344,2.57,0,,0,,,,,0,134.916,0,0.00471239,0.0125664,0,
9,p-TA,0,0,0,0,0,0,,0,0,TIC,NaN,16,28107.3,204918,21.929,,,,,0,17.34,0,1,1,0,0,0,0,,0,,,,,0,370.488,0,0.0240883,0.0642354,0,
10,"4,4 DCB",72,19.043,0,12.591,303,0.000447962,bbbb,0.000221483,0,TIC,NaN,16,28107.3,204918,21.929,,,,,0,18.954,0,1,1,18.998,19.088,12.591,0,,0,,,,,0,363.882,0,0.0026339,0.00702374,0,
11,"2,7 DCF",0,0,0,0,0,0,,0,0,TIC,NaN,16,28107.3,204918,21.929,,,,,0,18.716,0.988,1,1,0,0,0,12,,0,,,,,0.22,158.537,0,0.00315504,0.00841345,0,
12,"2,6 DCF",0,0,0,0,0,0,,0,0,TIC,NaN,16,28107.3,204918,21.929,,,,,0,19.226,0,1,1,0,0,0,0,,0,,,,,0.22,158.537,0,0.00323839,0.00863571,0,
13,"2,6 DCA",0,0,0,0,0,0,,0,0,TIC,NaN,16,28107.3,204918,21.929,,,,,0,19.645,0,1,1,0,0,0,0,,0,,,,,0,202.647,0,0.00101655,0.00271081,0,
14,"4,4 DCS",67,21.117,0,40.423,464,0.00143817,dbdb,0.00101106,0,TIC,NaN,16,28107.3,204918,21.929,,,,,0,21.103,0,1,1,21.06,21.188,40.423,0,,0,,,,,0,190.02,0,0.00195572,0.00521525,0,
15,MCF,40,20.723,0,9.675,268,0.000344217,sdsd,0.000345902,0,TIC,NaN,16,28107.3,204918,21.929,,,,,0,20.757,0,1,1,20.723,20.783,9.675,0,,0,,,,,0,177.462,0,0.00261076,0.00696202,0,
16,MCA,66,21.929,0,28107.3,204918,28107.3,bbbb,0.74392,0,TIC,-25.608,0,,,,,,,,0,21.999,0,1,1,21.749,22.156,28107.3,0,,0,,,,,0,463.302,0,0.00504582,0.0134555,28107.3,
VOR_5353,RR CTA,,,,,,24-Nov-04,15:18:20
1,TMA,228,2.956,0,19110.4,64250,0.589809,bbbb,260.811,0,TIC,NaN,16,32400.9,237152,21.957,,,,,0,3,0,1,1,2.738,3.695,19110.4,0,,0,,,,,0,313.145,0,1.75168,4.67114,0,
2,HBA,57,4.917,0,2998.08,18392,0.0925307,bbbb,11.5601,0,TIC,NaN,16,32400.9,237152,21.957,,,,,0,4.951,0,1,1,4.766,5.169,2998.08,0,,0,,,,,0,489.634,0,0.773826,2.06354,0,
3,PA,53,4.989,0,5200.7,19185,0.160511,bbbb,36.1709,0,165.13,NaN,16,32400.9,237152,21.957,,,,,0,5.274,0,1,1,4.737,5.358,5200.7,0,,0,,,,,0,556.197,0,1.58554,4.22811,0,
4,HMBA,50,4.795,0,5815.37,32427,0.179482,bbbb,10.1277,0,TIC,NaN,16,32400.9,237152,21.957,,,,,0,4.712,0,1,1,4.577,5.182,5815.37,0,,0,,,,,0,323.716,0,0.231073,0.616193,0,
5,TCBP,140,13.63,0,7.021,168,0.000216691,bbbb,0,0,313.25,NaN,16,32400.9,237152,21.957,,,,,0,13.672,0,1,1,13.59,13.67,7.021,0,,0,,,,,0,215.953,0,0,0,0,
6,TCB,372,14.087,0,1427.72,8300,0.0440642,bbbb,18.952,0,TIC,NaN,16,32400.9,237152,21.957,,,,,0,14.163,0,1,1,13.946,14.442,1427.72,0,,0,,,,,0,246.922,0,1.34346,3.58255,0,
7,BA,79,13.554,0,10987.2,79971,0.339102,bbbb,138.51,0,TIC,NaN,16,32400.9,237152,21.957,,,,,0,13.426,0,1,1,13.407,13.741,10987.2,0,,0,,,,,0,3400.42,0,17.5703,46.8541,0,
8,Acet-HMBA,78,15.131,0,2205.49,15837,0.0680688,bbbb,26.7105,0,TIC,NaN,16,32400.9,237152,21.957,,,,,0,15.274,0,1,1,14.971,15.425,2205.49,0,,0,,,,,0,267.603,0,1.32837,3.54232,0,
9,p-TA,213,17.398,0,16705.4,126347,0.515583,bbbb,376.604,0,TIC,NaN,16,32400.9,237152,21.957,,,,,0,17.34,0,1,1,17.251,17.668,16705.4,0,,0,,,,,0,268.015,0,2.47651,6.60403,0,
10,"4,4 DCB",70,19.014,0,6141.26,45480,0.18954,bbbb,15.4133,0,TIC,NaN,16,32400.9,237152,21.957,,,,,0,18.954,0,1,1,18.864,19.254,6141.26,0,,0,,,,,0,421.104,0,0.43319,1.15517,0,
11,"2,7 DCF",106,18.739,0.975,1238.53,7233,0.038225,bbbb,8.54629,0,TIC,NaN,16,32400.9,237152,21.957,,,,,0,18.716,0.988,1,1,18.589,19.039,1238.53,12,,0,,,,,0.22,154.205,0,0.436138,1.16303,0,
12,"2,6 DCF",138,19.219,0,8007.62,45372,0.247142,bbbb,56.7152,0,TIC,NaN,16,32400.9,237152,21.957,,,,,0,19.226,0,1,1,19.039,19.579,8007.62,0,,0,,,,,0.22,154.205,0,0.447659,1.19376,0,
13,"2,6 DCA",93,19.889,0,358.883,1870,0.0577144,bbbb,3.2526,0,TIC,NaN,16,32400.9,237152,21.957,,,,,0,19.645,0,1,1,19.709,20.104,1870,0,,0,,,,,0,311.333,0,0.221956,0.591881,0,
14,"4,4 DCS",63,21.061,0,1767.26,13942,0.0545433,bbbb,6.30672,0,TIC,NaN,16,32400.9,237152,21.957,,,,,0,21.103,0,1,1,20.929,21.302,1767.26,0,,0,,,,,0,302.556,0,0.442551,1.18013,0,
15,MCF,46,20.814,0,581.984,4697,0.0179619,bbbb,2.96873,0,TIC,NaN,16,32400.9,237152,21.957,,,,,0,20.757,0,1,1,20.694,20.979,581.984,0,,0,,,,,0,193.591,0,0.40476,1.07936,0,
16,MCA,69,21.957,0,32400.9,237152,32400.9,bbbb,0.85756,0,TIC,-14.244,0,,,,,,,,0,21.999,0,1,1,21.778,22.19,32400.9,0,,0,,,,,0,263.585,0,0.00285943,0.00762515,32400.9,
VOR_5354,439907 STD,,,,,,24-Nov-04,15:51:35
1,TMA,223,2.935,0,41809.2,159778,1.41191,bbbb,623.317,0,TIC,NaN,16,29611.7,214846,21.957,,,,,0,3,0,1,1,2.746,3.666,41809.2,0,,0,,,,,0,339.605,0,2.09348,5.58261,0,
2,HBA,57,4.917,0,1766.8,10830,0.0596656,bbbb,7.44196,0,TIC,NaN,16,29611.7,214846,21.957,,,,,0,4.951,0,1,1,4.682,5.237,1766.8,0,,0,,,,,0,412.089,0,0.717709,1.91389,0,
3,PA,46,4.871,0,1406.67,5617,0.0475037,MMMM,10.6873,0,165.13,NaN,16,29611.7,214846,21.957,29-Nov-04,13:58:21,,,0,5.274,0,1,1,4.754,5.207,1406.67,0,,0,,,,,0,842.059,0,2.64532,7.05418,0,
4,HMBA,50,4.796,0,7446.18,41756,0.251461,bbbb,14.166,0,TIC,NaN,16,29611.7,214846,21.957,,,,,0,4.712,0,1,1,4.51,5.148,7446.18,0,,0,,,,,0,246.916,0,0.194231,0.51795,0,
5,TCBP,202,14.553,0,475.691,2113,0.0160643,bbbb,0,0,313.25,NaN,16,29611.7,214846,21.957,,,,,0,13.672,0,1,1,14.318,14.833,475.691,0,,0,,,,,0,239.325,0,0,0,0,
6,TCB,373,14.104,0,19483.9,116198,0.657981,bdbd,282.533,0,TIC,NaN,16,29611.7,214846,21.957,,,,,0,14.163,0,1,1,13.906,14.455,19483.9,0,,0,,,,,0,234.418,0,1.40553,3.74809,0,
7,BA,80,13.567,0,55403.3,370849,1.87099,bbbb,762.975,0,TIC,NaN,16,29611.7,214846,21.957,,,,,0,13.426,0,1,1,13.393,13.874,55403.3,0,,0,,,,,0,1537.66,0,8.7557,23.3485,0,
8,Acet-HMBA,78,15.131,0,8381.41,61482,0.283044,bbbb,110.885,0,TIC,NaN,16,29611.7,214846,21.957,,,,,0,15.274,0,1,1,14.971,15.411,8381.41,0,,0,,,,,0,204.016,0,1.11604,2.9761,0,
9,p-TA,214,17.401,0,44564.8,341214,1.50497,bbbb,1097.49,0,TIC,NaN,16,29611.7,214846,21.957,,,,,0,17.34,0,1,1,17.258,17.667,44564.8,0,,0,,,,,0,759.863,0,7.73753,20.6334,0,
10,"4,4 DCB",70,19.013,0,89871.1,654447,3.03498,bbbb,246.398,0,TIC,NaN,16,29611.7,214846,21.957,,,,,0,18.954,0,1,1,18.833,19.298,89871.1,0,,0,,,,,0,518.365,0,0.587639,1.56704,0,
11,"2,7 DCF",107,18.753,0.9758,2827.48,17280,0.0954853,bbbb,21.3134,0,TIC,NaN,16,29611.7,214846,21.957,,,,,0,18.716,0.988,1,1,18.588,19.008,2827.48,12,,0,,,,,0.22,167.254,0,0.521299,1.39013,0,
12,"2,6 DCF",138,19.218,0,50226.8,271232,1.69618,bbbb,388.608,0,TIC,NaN,16,29611.7,214846,21.957,,,,,0,19.226,0,1,1,19.023,19.668,50226.8,0,,0,,,,,0.22,167.254,0,0.53507,1.42685,0,
13,"2,6 DCA",93,19.888,0,2264.86,10391,0.350909,bbbb,19.7436,0,TIC,NaN,16,29611.7,214846,21.957,,,,,0,19.645,0,1,1,19.663,20.224,10391,0,,0,,,,,0,184.602,0,0.145032,0.386751,0,
14,"4,4 DCS",64,21.074,0,4294.6,33142,0.145031,bbbb,16.742,0,TIC,NaN,16,29611.7,214846,21.957,,,,,0,21.103,0,1,1,20.899,21.33,4294.6,0,,0,,,,,0,331.404,0,0.534195,1.42452,0,
15,MCF,47,20.829,0,3494.8,28285,0.118021,bbbb,19.4743,0,TIC,NaN,16,29611.7,214846,21.957,,,,,0,20.757,0,1,1,20.694,20.979,3494.8,0,,0,,,,,0,322.439,0,0.742924,1.98113,0,
16,MCA,69,21.957,0,29611.7,214846,29611.7,bbbb,0.783737,0,TIC,-21.6263,0,,,,,,,,0,21.999,0,1,1,21.792,22.19,29611.7,0,,0,,,,,0,304.481,0,0.00333215,0.00888573,29611.7,
VOR_5355,Howard Jenkins 01,,,,,,24-Nov-04,16:24:51

and so on and on and on......

 

[yacyac]

From the early part of this thread, it appears that the only issue you were having with using split was the quotation marks messing you up.

I split a lot of csv files and have run into the same issue. IF you do not need the quotation marks (and most times you do not) just replace them before you split the string. If you want the quotation marks - replace them with a single quote

line input #1, d$
''If don't need quotes
d$ = replace(d$,chr$(34),"")

'If quotes are desired
d$ = replace9d$,chr$(34),"'")

''Now Split
temparray = split(d$,",")

'If you want quotes back then
temparray(1) = replace(temparray(1),",",chr$(34))

 

[yacyac]

Code error
last line of code

temparray(1) = replace(temparray(1),",",chr$(34))


should be

temparray(1) = replace(temparray(1),"'",chr$(34))

 

[koie]

Wonderfull...I am learning a lot here. There are certainly of lot of nice tricks to do.
I have been working on the Code TallOne provided and made some progress.
Perhaps not the best way to write code but this is what I have so far.

    Debug.Print .Fields(0).UnderlyingValue, .Fields(1).Value
    If .Fields(0).UnderlyingValue <> "" Then
        
        If Left$(.Fields(0).UnderlyingValue, 3) = "VOR" Then
           GetSampleID
           .MoveNext
           
           Do
           GetComponentData
           .MoveNext
           
           Loop Until Left$(.Fields(0).UnderlyingValue, 3) = "VOR" Or .Fields(0).UnderlyingValue = ""
        .MovePrevious
        
        
        End If



    End If
.MoveNext

    Loop
    
    

End With
End Sub
Private Sub GetSampleID()
'Get your SampleID info here
Debug.Print "SampleID=" & rs.Fields(1).UnderlyingValue


End Sub
Private Sub GetComponentData()
'Get your component info here
    Debug.Print "Component: " & rs.Fields(1).UnderlyingValue & vbTab & _
        "Concentration: " & rs.Fields(9).UnderlyingValue
End Sub

It is not finished yet but getting there.
Again... Thanks all!!!

 

[TallOne]

Hi Koie,
Glad you found my example useful. I got it to work with your changes tweaked a little. I don't think you have to use the .UnderlyingValue property though. I think that's in case someone else connects to your data and changes it since last read by your client. Anyways here's what I got.

Option Explicit

Private cn As ADODB.Connection
Private rs As ADODB.Recordset
Private bolStartComponentData As Boolean
Private strCSVPath As String
Private strCSVFileName As String
Private Sub Form_Load()

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
With CommonDialog1
            .Filter = "CSV Files |*.csv"
            .DefaultExt = "csv"
            .Flags = cdlOFNHideReadOnly Or cdlOFNFileMustExist Or _
                cdlOFNNoReadOnlyReturn
            .DialogTitle = "Select Your File!"
            .CancelError = True
            .ShowOpen
            End With
            
            
            strCSVPath = ExtractPath(CommonDialog1.FileName)
            strCSVFileName = ExtractFileName(CommonDialog1.FileName)
With cn
    .CursorLocation = adUseClient
    .Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
        & "Data Source=" & strCSVPath & ";" _
        & "Extended Properties=""text;FMT=Delimited;HDR=NO"""
End With

With rs
    .Open "Select * From [" & strCSVFileName & "]", cn, _
            adOpenStatic, adLockPessimistic, adCmdText
        
    If .BOF And .EOF Then
        MsgBox "Recordset Empty"
        .Close
        Exit Sub
    End If
    
    .MoveFirst
    Do Until .EOF = True
    
      
        Debug.Print .Fields(0).Value, .Fields(1).Value
        If .Fields(0).Value <> "" Then
            If Left$(.Fields(0).Value, 3) = "VOR" Then
               GetSampleID
               .MoveNext
                 Do
                     If .EOF Then Exit Sub
                     GetComponentData
                     .MoveNext
                 Loop Until Left$(.Fields(0).Value, 3) = "VOR" Or _
            .Fields(0).Value = ""
            
            .MovePrevious
            End If
        End If
        .MoveNext
Loop
End With

Set cn = Nothing
Set rs = Nothing

End Sub
Private Sub GetSampleID()
'Get your SampleID info here
Debug.Print "SampleID=" & rs.Fields(1).Value


End Sub
Private Sub GetComponentData()
'Get your component info here
    Debug.Print "Component: " & rs.Fields(1).Value & vbTab & _
        "Concentration: " & rs.Fields(9).Value
End Sub

Private Function ExtractPath(path As String)
Dim i As Integer
For i = Len(path) To 1 Step -1
    If Mid$(path, i, 1) = "\" Then
        ExtractPath = Left$(path, i)
        Debug.Print Left$(path, i)
        Exit Function
    End If
Debug.Print Mid$(path, i, 1)
Next
End Function
Private Function ExtractFileName(path As String)
Dim i As Integer
For i = Len(path) To 1 Step -1
    If Mid$(path, i, 1) = "\" Then
        ExtractFileName = Right$(path, (Len(path) - i))
        Debug.Print Right$(path, (Len(path) - i))
        Exit Function
    End If
Next

End Function

Hope this helps.
Later

 

[jebenson]

Well, I think I'll just throw in my 2 cents worth!

koie, the reason the code using the Split function failed is not because of the quotes, it is because of the comma between the quotes. If I am reading the data correctly, it is at least in part chemical compounds and their concentration in...well, I'm not sure I want to know what! Well, if I remember my college chemistry (an iffy proposition at best, I assure you!) then
this data - "4,4 DCB" - is a notation for a chemical compound. The split is reading the comma in the compound's name and treating it as a delimeter, which is throwing off the split for that line and producing the errors you see in the output. A solution is to test the input line for quotes, and if found replace the comma between the quotes with some character other than a comma (my code example below uses the "pipe" character: |). Then do the split and replace the pipe with the original comma before displaying the chemical name. I know its rather convoluted, but I just wanted you to know that it is not the quotes that is causing the error, but the extra comma. Here's your code from above, modified to do what I have discussed. Also, I made the While loops into Do While loops and replaced the GoTo lines with Exit Do lines (GoTo is baaaaad and should be avoided as worse than the plague!).

Dim tmpArray() As String
Dim FirstPart As String
Dim ChemName As String
Dim LastPart As String
Dim FirstQuoteLocation As Long
Dim LastQuoteLocation As Long
Dim tmpstr2 As String
Dim ChemNameLen As Integer

Open "D:\Temp\Test.txt" For Input As #1
look_for_sampleid:

Do While Not EOF(1)
Line Input #1, tmpstr
If Left$(tmpstr, 3) = "VOR" Then
    tmpArray = Split(tmpstr, ",")
    sampleid = tmpArray(1)
    
    Debug.Print "SampleID=" & sampleid
    
    
    
    Exit Do
End If
Loop


Do While Not EOF(1)
Line Input #1, tmpstr
If InStr(tmpstr, """") > 0 Then
    FirstQuoteLocation = InStr(tmpstr, """")
    FirstPart = Mid(tmpstr, 1, FirstQuoteLocation)
    tmpstr2 = Mid(tmpstr, FirstQuoteLocation + 1)
    LastQuoteLocation = InStr(tmpstr2, """")
    LastPart = Mid(tmpstr2, LastQuoteLocation)
    ChemNameLen = LastQuoteLocation - 1
    ChemName = Mid(tmpstr, FirstQuoteLocation + 1, ChemNameLen)
    ChemName = Replace(ChemName, ",", "|")
    tmpstr = FirstPart & ChemName & LastPart
End If
If Left$(tmpstr, 3) = "VOR" Then Exit Do
tmpArray = Split(tmpstr, ",")
component = Replace(tmpArray(1), "|", ",")
component = Replace(component, """", "")
concentration = tmpArray(9)
Debug.Print "Component:" & component & "    Concentration:" & concentration


Loop

End

Anyway, use this or not, since you have stated that you are learning I just wanted you to know the actual reason the code wasn't working.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[koie]

Mission Accomplished!!

Thanks guys for all your great input.
I am certain that the code below could be rewritten in a neater way but it does what it has to do and that's what counts for me.

There is one big no-no I did and that was an error I encountered on a blank line at the bottom of the resultfile.
I jumped out of a loop using a error routine.
I was banging my head all night on this problem so this was the easiest way for me.

Keep up the good work!

Private Sub Form_Load()


On Error GoTo Extract_Complete
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
With CommonDialog1
            .Filter = "CSV Files |*.csv"
            .DefaultExt = "csv"
            .Flags = cdlOFNHideReadOnly Or cdlOFNFileMustExist Or _
                cdlOFNNoReadOnlyReturn
            .DialogTitle = "Select Your File!"
            .CancelError = True
            .ShowOpen
            End With
            
            
            strCSVPath = ExtractPath(CommonDialog1.FileName)
            strCSVFileName = ExtractFileName(CommonDialog1.FileName)
With cn
    .CursorLocation = adUseClient
    .Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
        & "Data Source=" & strCSVPath & ";" _
        & "Extended Properties=""text;FMT=Delimited;HDR=NO"""
End With

With rs
    .Open "Select * From [" & strCSVFileName & "]", cn, _
            adOpenStatic, adLockPessimistic, adCmdText
    rs.ActiveConnection = Nothing
    
    If .BOF And .EOF Then
        MsgBox "Recordset Empty"
        .Close
        Exit Sub
    End If
    
    .MoveFirst
    
    Do While .EOF <> True
    
    'Debug.Print .Fields(0).Value, .Fields(1).Value
    If .Fields(0).Value <> "" Then
        
        If Left$(.Fields(0).Value, 3) = "VOR" Then
           GetSampleID
           Debug.Print SampleID
           
           .MoveNext
                     
           Do
           If .EOF Then Exit Sub
           
           GetComponentData
           .MoveNext
           'Debug.Print .Fields(0).Value
           'If .Fields(0).Value <> "" Then Exit Do
           Debug.Print CompoundNumber, CompoundName, CompoundConcentration
           
           [red]Loop Until Left$(.Fields(0).Value, 3) = "VOR"[/red] 'this is were it gives an error if it encounters the blank line at the end.
        
        Close #myfile 'closing still open limsfile
        
        .MovePrevious
        End If
    End If
    .MoveNext

    Loop

    

End With

Extract_Complete:
MsgBox ("Extraction complete")
End
End Sub
Private Sub GetSampleID()
'Get your SampleID info here
'Debug.Print "SampleID=" & rs.Fields(1).Value
SampleID = rs.Fields(1).Value

'start writing header information

myfile = FreeFile

Dim ExportLimsFile As String
ExportLimsFile = "C:\resultfile\limsfile\" & SampleID & "_LCMS.lim"
Open ExportLimsFile For Output As #FreeFile
Print #myfile, "PGMGETLAB!PROD!"; SampleID; "!"; "TPA_LCMS"; "!LCMS1!16!"

End Sub
Private Sub GetComponentData()
'Get your component info here
    'Debug.Print "Component: " & rs.Fields(1).Value & vbTab & _
        "Concentration: " & rs.Fields(9).Value

CompoundNumber = rs.Fields(0).Value
CompoundName = rs.Fields(1).Value
CompoundRetTime = rs.Fields(3).Value
CompoundArea = rs.Fields(5).Value
CompoundHeight = rs.Fields(6).Value
CompoundConcentration = rs.Fields(9).Value
CompoundMass = rs.Fields(21).Value
Print #myfile, CompoundName & "_Conc"; "!1!"; CompoundConcentration; "!"
Print #myfile, CompoundName & "_RetTime"; "!1!"; CompoundRetTime; "!"
Print #myfile, CompoundName & "_Area"; "!1!"; CompoundArea; "!"
Print #myfile, CompoundName & "_Height"; "!1!"; CompoundHeight; "!"
Print #myfile, CompoundName & "_Mass"; "!1!"; CompoundMass; "!"


End Sub

 

[bjd4jc]

Try

Loop Until Left$(.Fields(0).Value & vbNullString, 3) = "VOR"

 

[koie]

that easy...Looks like time for bed.

 

[MichaelRed]

WOW. What a lot of stufffffff.


Somewhere buried (behind the Search) in these fora is a simple basGrabFile routine to import delimited files into an array. While it doesn't attack the issue of parsing the individual lines, it does present a highly efficient method of getting the information into an array in (occassionally Virtual) memory.

Since I/O remains the 'bane' of many routines it may be useful to incorporate this for the data retrievial process, and consider the line field parseing seperatly.

There are at least several threads which include the specific routine, with some of the threads also including redimentary examples of its use and even some further processing of hte seperated fields (as array elements).





MichaelRed