Split File Help

[bbrendan]

Hi all,

im just i need of help with splitting a file

The file looks like this


-------------------------------------------------------------------------------
OrderID|BillFirstname|BillLastname|BillCompany|BillAddress
1143658|Chris|Bell|Strutt & Parker|13 Hill St
1143659|Chris|Bell|Strutt & Parker|13 Hill St
1143660|Chris|Bell|Strutt & Parker|13 Hill St
--------------------------------------------------------------------------------



What I would like to do is split the file into the rows to insert into the database. The thing is I dont know how to:
1. Remove the header row
2. Tell the split function to treat each line as 1 row
3. Then split each field based on the pipe

any help would be greatly appreciated

 

[ADoozer]

Dim ff As Integer
dim tmpStr as string
dim tmpArray() as string

ff=freefile

Open txtPath For Input As #ff //where txtPath is the path to your list
While Not (EOF(ff))
DoEvents
Line Input #ff, tmpStr
if not instr(1,tmpstr,"OrderID|BillFirstname|BillLastname|BillCompany|BillAddress")
tmpArray=split(tmpstr,"|")
debug.print tmparray(0)
debug.print tmparray(1)
debug.print tmparray(2)
debug.print tmparray(3)
debug.print tmparray(4)
//write to your db
wend
close #ff

or something similar

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

 

[zemp]

You can try to split on the vbCRLF character to separate the header and the records, then split again (the results of the first split) on the pipe to separate the fields.

zemp

 

[bbrendan]

Hiya

Thanks for your help, still trying to get my head round this

OK, so I have the following code that splits each line the splits inside that that as shown below.

But it doesnt loop through each line only the first line.

any help would be great
[vb]
Dim strTemp() As String
Dim intLine As Integer
Dim strDelen() As String
Dim strNummers() As String
Dim i As Integer

Open "C:\Development\WebImport\downloads\test.txt" For Input As #1

Do While Not EOF(1)
intLine = intLine + 1
ReDim strTemp(1 To intLine)
Line Input #1, strTemp(intLine)

strDelen = Split(strTemp(intLine), vbCrLf)
strNummers = Split(strDelen(0), "|")

' here is where i loop through the second split line, but it only loops once
For i = 1 To intLine

Debug.Print strNummers(0)
Debug.Print strNummers(1)
Debug.Print strNummers(2)
Debug.Print strNummers(3)

Next i

Loop

Close #1

[/vb]

 

[ADoozer]

try my code as it is.

do a quick msdn search for "open statement" "instr" and "split" and all will become clear

good luck

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

 

[gmmastros]

strNummers is being reset each time through the "big" loop. So for each line you read, strNummers is being reset. You don't really need to have your "For I" loop.

Take a look at this to see if it makes sense.

Dim strTemp() As String
Dim intLine As Integer
Dim strDelen() As String
Dim strNummers() As String
Dim i As Integer

Open "C:\test.txt" For Input As #1

    Do While Not EOF(1)
        intLine = intLine + 1
        ReDim strTemp(1 To intLine)
        Line Input #1, strTemp(intLine)
        
            strDelen = Split(strTemp(intLine), vbCrLf)
                strNummers = Split(strDelen(0), "|")
 
' here is where i loop through the second split line, but it only loops once
        Debug.Print "Record"
        Debug.Print strNummers(0)
        Debug.Print strNummers(1)
        Debug.Print strNummers(2)
        Debug.Print strNummers(3)
        Debug.Print " "
        
   Loop
   
Close #1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ADoozer]

oops just realised i missed an end if

Dim ff As Integer
dim tmpStr as string
dim tmpArray() as string

ff=freefile

Open txtPath For Input As #ff //where txtPath is the path to your list
    While Not (EOF(ff))
        DoEvents
        Line Input #ff, tmpStr
        if not instr(1,tmpstr,"OrderID|BillFirstname|BillLastname|BillCompany|BillAddress")
            tmpArray=split(tmpstr,"|")
            debug.print tmparray(0)
            debug.print tmparray(1)
            debug.print tmparray(2)
            debug.print tmparray(3)
            debug.print tmparray(4)
            //write to your db
        end if
    wend
close #ff

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

 

[strongm]

Have you considered the ODBC text driver?

 

[ADoozer]

in my defence i know very little about db's.. and i was answering questions 1,2,3

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

 

[bbrendan]

Hi All,

thanks for you help on this really!. I will try your suggestions out. Regarding the text odbc driver, yes went down that path, but unless your using a common delimter like "," you hit some brick walls

thanks again

 

[bbrendan]

Hi gmmastros,

Ive tried your change to the code but im still only getting the first row of data.

Man this is thrustrating me

 

[strongm]

>your using a common delimter like "," you hit some brick walls

Eh? Delimiter can be whatever you like. All you need is a simple schema.ini file

 

[gmmastros]

Looking at your code, I noticed a couple things I would like to point out.

1) You should use FreeFile when working with the Open command.
2) You don't need all those array variables. 1 will do. (unless you want to keep the data in memory after the loop finishes).

3) Performance will be improved (if there are many records in the file) by using the File Sysem Object.

I re-wrote your code. I hope this works for you.

    Dim iFile As Integer
    Dim strTemp As String
    Dim strNummers() As String
    
    iFile = FreeFile
    Open "C:\test.txt" For Input As #iFile
    
        Do While Not EOF(iFile)
            Line Input #iFile, strTemp
            
            strNummers = Split(strTemp, "|")
     
            Debug.Print "Record"
            Debug.Print strNummers(0)
            Debug.Print strNummers(1)
            Debug.Print strNummers(2)
            Debug.Print strNummers(3)
            Debug.Print strNummers(4)
            Debug.Print " "
            
       Loop
       
    Close #iFile

I changed the name of the file (C:\Test.text).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[strongm]

Here, have an example (assuming target database is an mdb):

1. Create an empty text file called schema.ini file in the same folder as your text data file
2. Paste the following into schema.ini

[tektips.txt]
ColNameHeader=True
Format=Delimited(|)
MaxScanRows=25
CharacterSet=OEM

(throughout this example you should replace tektips.txt with the name of your text data file and replace c:\tektips.mdb with the path and filename of your database)

3. Create a new VB project, and add a reference to ADO

4.

[blue]Option Explicit

Private Sub Command1_Click()
    Dim sConTxt As String
    Dim cnn As Connection
    Dim rs As Recordset
    
    [green]' build the connection string[/green]
    sConTxt = "Driver={Microsoft Text Driver (*.txt; *.csv)}; "
    sConTxt = sConTxt & "DEFAULTDIR=" & "c:\" & "; "
    sConTxt = sConTxt & "Persist Security Info=False"
    
    Set cnn = New Connection
    Set rs = New Recordset
    
    cnn.Open sConTxt [green]'open the connection
    
    ' Create new table with data in target database
    ' rs.Open "Select *  INTO [Table1] IN 'c:\tektips.mdb' FROM [" & "tektips.txt" & "]", cnn
    
    ' or append to existing table in target database[/green]
    rs.Open "INSERT INTO [Table1] IN 'c:\tektips.mdb' Select *  FROM [" & "tektips.txt" & "]", cnn

End Sub
[/blue]

 

[JerryKlmns]

strongm,

How much faster is your version against a file open, readline to recordset (UpdateBatchOptimistic) and update(batch)? For just 53.000+ records.

I use the schema.ini with record layout info, link as table to .mdb and execute a similar append bulk transaction.

(I was waiting for that post of yours since the beggining!)