Create bat file to remove first two lines of text file and resave under same name

Public Function Import_Fixed_Width_File()
 
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
Dim I As Integer
 
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
 
'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("MyTableName")    '<-- Change to your tablename
 
'Opening the file to read from
FileNum = FreeFile()
InputFile = "C:\MyFolder\MyTextFile"        '<-- Change to your folder and path
Open InputFile For Input Access Read Shared As #FileNum
 
I = 0
'Dump the first two lines
Line Input #FileNum, InputString
Line Input #FileNum, InputString
 
Do Until EOF(FileNum) = True
    Line Input #FileNum, InputString            'Read the data in
    If Len(InputString) > 50 Then               'A loop to skip bad lines
        Do Until Len(InputString) > 50
            Line Input #FileNum, InputString
        Loop
    End If
    
    With RS                                 'the input is an empty string write it
        .AddNew
        !MyFieldName = Left(InputString, 10) '<-- Change to your field name
        !Field1 = Mid(InputString, 11, 5)
        !Field2 = Mid(InputString, 16, 5)
        .Update
    End With
 
 
    I = I + 1
Loop
 
Close FileNum
Set RS = Nothing
Set DB = Nothing
 
 
End Function

Select allOpen in new window

@ECHO OFF
(for /f "skip=8 Tokens=* delims=" %%a in ('type "%1"') do echo %%a))>"%~n1.tmp"
move "%~n1.tmp" "%1"

Select allOpen in new window

Public Function Import_Fixed_Width_File()
 
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
Dim I As Integer
 
Dim StringArray() As String
 
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("MyTableName")    '<-- Change to your tablename
 
'Opening the file to read from
FileNum = FreeFile()
InputFile = "C:\MyFolder\MyTextFile"        '<-- Change to your folder and path
Open InputFile For Input Access Read Shared As #FileNum
 
I = 0
'Dump the header lines
Do Until I > 7
    Line Input #FileNum, InputString
    I = I + 1
Loop
 
 
Do Until EOF(FileNum) = True
    Line Input #FileNum, InputString            'Read the data in
    If Right(InputString, 1) <> Chr(9) Then              'A loop to skip bad lines
        Do Until Right(InputString, 1) = Chr(9)
            Line Input #FileNum, InputString
        Loop
    End If
    StringArray = Split(InputString, Chr(9))
    With RS                                 'the input is an empty string write it
        .AddNew
        !Created_on = StringArray(1)
        !Plan = StringArray(2)
        !Group = StringArray(3)
        !Material = StringArray(5)
        !Qty = StringArray(6)
        !SU = StringArray(7)
        !BATCH = StringArray(8)
        !Sorg = StringArray(9)
        !Customer = StringArray(10)
        !Group = StringArray(11)
        !Document = StringArray(12)
        !Di = StringArray(13)
        !VENDOR = StringArray(14)
        !Purchase_order_no = StringArray(15)
        !Item = StringArray(16)
        !Material_Description = StringArray(17)
        !Sales_Value = StringArray(18)
        !Curr = StringArray(19)
        !Goods_Is_D = StringArray(20)
        !Haz = StringArray(21)
        !Product_hierarchy = StringArray(22)
        !Compliance = StringArray(23)
        !CDl = StringArray(24)
        .Update
    End With
Loop
 
Close FileNum
Set RS = Nothing
Set DB = Nothing
 
End Function

Select allOpen in new window

Public Function Import_Delimited_File()
 
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
Dim I As Integer
 
Dim StringArray() As String
 
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
Dim BreakLoop As Boolean
 
'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("Eurobo_step1")    '<-- Change to your tablename
 
'Opening the file to read from
FileNum = FreeFile()
InputFile = "C:\Documents and Settings\All Users\Desktop\Misc Access DB\Q_24181017_EuroboTestCenter\eurobo.txt"        '<-- Change to your folder and path
Open InputFile For Input Access Read Shared As #FileNum
 
I = 0
'Dump the header lines
Do Until I > 7
    Line Input #FileNum, InputString
    I = I + 1
Loop
 
BreakLoop = False
Do Until EOF(FileNum) = True
    I = I + 1
    Line Input #FileNum, InputString            'Read the data in
    If Right(InputString, 1) <> Chr(9) Then              'A loop to skip bad lines
        Do Until Right(InputString, 1) = Chr(9) Or EOF(FileNum) = True
            If InputString = "Select-options entered:" Then BreakLoop = True
            I = I + 1
            Line Input #FileNum, InputString
        Loop
    End If
    If BreakLoop = True Or EOF(FileNum) = True Then Exit Do
 
    StringArray = Split(InputString, Chr(9))
    With RS                                 'the input is an empty string write it
        .AddNew
        !Created_on = StringArray(0)
        !Plant = StringArray(1)
        !Group = StringArray(2)
        !Material = StringArray(3)
        !Qty = StringArray(4)
        !UOM = StringArray(5)
        !BATCH = StringArray(6)
        !Sales_org = StringArray(7)
        !Customer = StringArray(8)
        !Code = StringArray(9)
        !Document = StringArray(10)
        !Division = StringArray(11)
        !VENDOR = StringArray(12)
        !Purchase_order = StringArray(13)
        !Item = StringArray(14)
        !Description = StringArray(15)
        !Value = StringArray(16)
        !Curr = StringArray(17)
        !Goods_Issue_Date = StringArray(18)
        !Haz = StringArray(19)
        !Product_hierarchy = StringArray(20)
        !Compliance = StringArray(21)
        !CDl = StringArray(22)
        .Update
    End With
Loop
 
Close FileNum
Set RS = Nothing
Set DB = Nothing
 
End Function

Select allOpen in new window

Public Function Import_using_Bat_file()
 
'Call Shell("c:\uts\striplines.cmd " & K:\Lombard\eurobo.txt & " " & 7, vbNormalFocus) '7 is lines to skip
Call Shell("cmd /c more +" & 7 & " " & K:\Lombard\eurobo.txt & " > " & K:\Lombard\eurobo.txt & ".$$$ & move /y " & K:\Lombard\eurobo.txt & ".$$$ " & K:\Lombard\eurobo.txt, vbNormalFocus)
 
End Function

Select allOpen in new window

Public Function Import_Delimited_File()
 
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
Dim I As Integer
 
Dim StringArray() As String
 
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
Dim BreakLoop As Boolean
 
'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("Eurobo_step1")    '<-- Change to your tablename
 
'Opening the file to read from
FileNum = FreeFile()
InputFile = "K:\Lombard\eurobo.txt"        '<-- Change to your folder and path
Open InputFile For Input Access Read Shared As #FileNum
 
I = 0
'Dump the header lines
Do Until I > 7
    Line Input #FileNum, InputString
    I = I + 1
Loop
 
BreakLoop = False
Do Until EOF(FileNum) = True
    'I = I + 1
    Line Input #FileNum, InputString            'Read the data in
    If Left(InputString, 1) <> Chr(9) Then              'A loop to skip bad lines
        Do Until Right(InputString, 1) = Chr(9) Or EOF(FileNum) = True
            If InputString = "Select-options entered:" Then BreakLoop = True
            I = I + 1
            Line Input #FileNum, InputString
        Loop
    End If
    If BreakLoop = True Or EOF(FileNum) = True Then Exit Do
    'Debug.Print Right("0000000" & I, 5) & ":  " & InputString
    
    StringArray = Split(InputString, Chr(9))
    With RS                                 'the input is an empty string write it
        .AddNew
        !Created_on = StringArray(1)
        !Plant = StringArray(2)
        !Group = StringArray(3)
        !Material = StringArray(5)
        !Qty = StringArray(6)
        !UOM = StringArray(7)
        !BATCH = StringArray(8)
        !Sales_org = StringArray(9)
        !Customer = StringArray(10)
        !Code = StringArray(11)
        !Document = StringArray(12)
        !Division = StringArray(13)
        !VENDOR = StringArray(14)
        !Purchase_order = StringArray(15)
        !Item = StringArray(16)
        !Description = StringArray(17)
        !Value = StringArray(18)
        !Curr = StringArray(19)
        !Goods_Issue_Date = StringArray(20)
        !Haz = StringArray(21)
        !Product_hierarchy = StringArray(22)
        .Update
    End With
Loop
 
Close FileNum
Set RS = Nothing
Set DB = Nothing
 
End Function

Select allOpen in new window

Option Compare Database
Option Explicit
 
 
Public Function Import_Delimited_File()
 
Dim FileName As String
Dim FilePath As String
 
 
 
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
Dim I As Integer
 
Dim StringArray() As String
 
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
Dim BreakLoop As Boolean
 
'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("Eurobo_step1")    '<-- Change to your tablename
 
FilePath = "K:\Lombard\"
FileName = "eurobo.txt"
 
FileCopy FilePath & FileName, Environ("temp") & "\" & FileName
Clear_Disk_Buffer
 
'Opening the file to read from
FileNum = FreeFile()
InputFile = Environ("temp") & "\" & FileName   '<-- Change to your folder and path
Open InputFile For Input Access Read Shared As #FileNum
 
I = 0
'Dump the header lines
Do Until I > 7
    Line Input #FileNum, InputString
    I = I + 1
Loop
 
BreakLoop = False
Do Until EOF(FileNum) = True
    'I = I + 1
    Line Input #FileNum, InputString            'Read the data in
    If Left(InputString, 1) <> Chr(9) Then              'A loop to skip bad lines
        Do Until Right(InputString, 1) = Chr(9) Or EOF(FileNum) = True
            If InputString = "Select-options entered:" Then BreakLoop = True
            I = I + 1
            Line Input #FileNum, InputString
        Loop
    End If
    If BreakLoop = True Or EOF(FileNum) = True Then Exit Do
    'Debug.Print Right("0000000" & I, 5) & ":  " & InputString
    
    StringArray = Split(InputString, Chr(9))
    With RS                                 'the input is an empty string write it
        .AddNew
        !Created_on = StringArray(1)
        !Plant = StringArray(2)
        !Group = StringArray(3)
        !Material = StringArray(5)
        !Qty = StringArray(6)
        !UOM = StringArray(7)
        !BATCH = StringArray(8)
        !Sales_org = StringArray(9)
        !Customer = StringArray(10)
        !Code = StringArray(11)
        !Document = StringArray(12)
        !Division = StringArray(13)
        !VENDOR = StringArray(14)
        !Purchase_order = StringArray(15)
        !Item = StringArray(16)
        !Description = StringArray(17)
        !Value = StringArray(18)
        !Curr = StringArray(19)
        !Goods_Issue_Date = StringArray(20)
        !Haz = StringArray(21)
        !Product_hierarchy = StringArray(22)
        .Update
    End With
Loop
 
Close FileNum
Set RS = Nothing
Set DB = Nothing
Kill Environ("temp") & "\" & FileName
 
End Function
 
Public Function Clear_Disk_Buffer()
'This is called beecause sometimes the modern CPUs try to fire the FTP script before the _
 file has actually written it all to disk.  This writes at least an 8K file to the drive _
 to flush the buffers.
Dim I As Integer
 
Dim FileName As String
Dim FileNum As Integer
Dim OutputLine As String
Dim Directory As String
 
Directory = "C:\TEMP"
FileNum = FreeFile()
 
 
If UCase(Dir(Directory, vbDirectory)) <> "TEMP" Then
    MkDir "C:\TEMP"
End If
I = 0
FileNum = FreeFile()
FileName = Environ("temp") & "\Buffer_Run.txt"
Open FileName For Output Access Write Lock Write As FileNum
OutputLine = "123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890"
Do Until I > 1000
    Print #FileNum, OutputLine
    I = I + 1
Loop
Close #FileNum
Debug.Print FileName & " size =" & FileLen(FileName)
If Dir(FileName) = "Buffer_Run.txt" And FileLen(FileName) > 0 Then
    Debug.Print Dir(FileName)
    Kill FileName
End If
 
End Function

Select allOpen in new window