Avatar of dabdowb
dabdowb
 asked on

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

I am having all sorts of fun trying to figure this out, but have not embraced victory yet...hence my plea for help.  I would think this would be simple, but I digress.  I have a text file (see attached) and because the header data (aka Column names) come in to the file, it will not allow me to properly create import specs that will work using a tab delimited option, as opposed to fixed width.  Using fixed width, as you can see in the file, causes problems because about 60 records out of the ~30,000 in the file somehow get out of place in the data dump from the master software system SAP.

As of right now, I simply lose those data points, which is never good.  Now, I can open the file, delete the first two lines manually, save the file and then go in to do a tab delimited import into Access 2000, and it works pretty well.  There are a handful of messed up records, but that is due to header data sporadically mixed into the output.  Those can be deleted after I import them in Access easy enough.  Unfortunately, I cannot change the file output from SAP, so I am stuck trying to figure out if I can create a simple bat file or VBA code to just delete the first two lines in this text file and then save it again under the same name and directory.  Then the import specs would work with the tab delimited option and most of my headache would go away.

The reason to use a bat file or VBA code is because VBA code is currently importing this data from the network, so if I could just add a line to run the bat job and then keep on cooking, I think it work.  If a VBA solution exists, even better.

Help?
Microsoft AccessMicrosoft DOS

Avatar of undefined
Last Comment
Jim P.

8/22/2022 - Mon
Rey Obrero (Capricorn1)

where is the text file?

you can do the cleaning before importing using vba..
Surone1

if you use vba allready i would use vba for that too:
dim fs,fname

Set fs = CreateObject("Scripting.FileSystemObject")
Set fname = fs.OpenTextFile("c:\test.txt", ForReading)
i = 0
Do While fname.AtEndOfStream <> True
linetext = fname.ReadLine
If i > 1 Then mytext = mytext & linetext & vbCrLf
i = i + 1
Loop
fname.Close
Set fname = fs.OpenTextFile("c:\test.txt", ForWriting)
fname.WriteLine mytext
fname.Close
Set fname = Nothing
Set fs = Nothing

End Sub
Surone1

sorry about the end sub line there :-P
Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
bluntTony

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Surone1

in my code replace "c:\test.txt" with the (network) path of yor file
bluntTony

Both bits of code basically do the same thing, but I would use Surone1's. Just realised mine is unnecessarily long-winded. :( I need some sleep.
Jim P.

I would do it slightly differently -- and it will probably save time. Just use a VBA function to write the data directly to a table.

Something like below.

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

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
dabdowb

ASKER
Sorry about the file attachment...it was too big.  I have zipped it.

eurobo.zip
dabdowb

ASKER
It also looks like I am losing my mind (too many of these files I am trying to correct for this project).  So it is the first 8 lines actually, and truthfully, the last 11 lines I don't need...but again, those are pretty easy to clean up afterward.  I am assuming I would adjust code to look for i>7 on surone1's suggestion and creat 6 extra "strline" statements for bluntTony's idea.  I imagine I would add a few extra "LineInput" lines to jimpen's code, but I am unsure about the >50 delimiter as the file is pretty large...is that assuming no more than 50 characters for each field?  It is fixed width import as well, which won't work for the outliers, so just a bit confused.

Thanks,
Matt
AmazingTech

Give this batch file a try. Try the batch in the same folder first as your text file. I don't think it should matter though.

Pass in your file name.

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

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
SOLUTION
AmazingTech

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jim P.

Warning -- Aircode below. Test on backup.

Anyway the updated code. The >50 was guessing without seeing the file.

Updated code below needing some playing with. It looks like it is tab delimited Chr(9), not fixed width, but it works out pretty much the the same.

If it doesn't have the a tab at the beginning of the line then it gets skipped.
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

Open in new window

Qlemo

Another DOS short one, with %1 as the filename (like AmazingTech did show):

more +3 "%1" > "%1.$$$" & move "%1.$$$" "%1" /y
AmazingTech

Wow. That's awesome Qlemo!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
bluntTony

Ha! That truly is very good. When I tested this though, I could only get it to work by changing the syntax slightly:

more +3 "%1" > "%1.$$$" & move /y "%1.$$$" "%1"
Qlemo

Yes, that's right, I did not test the /y switch. It's typical for pseudo-DOS utilities to interpret switches ony as prefix before file arguments. I should have thought of that.
t0t0

Qlemo

I am speechless.... Another great one-liner.

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
t0t0

Here is my batch file based on traditional batch methods. To use, type either:

   DELLINE filename

to remove the first line from the file or say for example:

   DELLINE filename 3

to remove 3 lines from the file. You can specify any number of lines to delete.


@rem DELLINE.BAT
@echo off

if "%1"=="" echo No file specified & exit /b 1
if not exist "%1" echo Cannot find file & exit /b 1

if "%2"=="" (
   (for /f "skip=1 tokens=* delims=" %%a in ('type "%1"') do echo %%a)>"%~n1.tmp"
) else (
   (for /f "skip=%2 tokens=* delims=" %%a in ('type "%1"') do echo %%a)>"%~n1.tmp"
)

move "%~n1.tmp" "%1"
exit /b %errorlevel%
t0t0

Qlemo...

It amazes me that even after years and years of experience, I learn something every day.... and it's people like you and AmazingTech to name just a few, who inspire me and push me to discovering new limits. You have my full respect as a programmer.
t0t0

Having said that, here's my NEW IMPROVED batch file.....

@rem DELLINE.BAT
@echo off

if "%1"=="" echo No file specified & exit /b 1
if not exist "%1" echo Cannot find file & exit /b 1

if "%2"=="" (more +1 "%1" > "%~n1.tmp") else (more +%2 "%1" > "%~n1.tmp")

move /y "%~n1.tmp" "%1"
exit /b %errorlevel%
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Surone1

if you have many files which all need to be stripped of the same number of lines we could have the filesystemobject loop through all files in that folder and strip them
Surone1

Set fs = CreateObject("Scripting.FileSystemObject")
Set fld = fs.GetFolder("c:\mpt\")
MsgBox fld.Name
For Each fl In fld.Files
fpath = fl.Path
Set fname = fs.OpenTextFile(fpath, ForReading)
i = 0
Do While fname.AtEndOfStream <> True
linetext = fname.ReadLine
If i > 1 Then mytext = mytext & linetext & vbCrLf
i = i + 1
Loop
fname.Close
Set fname = fs.OpenTextFile(fpath, ForWriting)
fname.WriteLine mytext
fname.Close
Set fname = Nothing
Next fl
Set fld = Nothing
Set fs = Nothing
t0t0

Surone1

The following does the same but in a batch file:

@echo off
for /f "tokens=*" %%a in ('dir ^"%1^" /b /a-d') do more +3 "%%a" > "%%a.$$$" & move /y "%%a.$$$" "%%a"

This will process all files in the argument specified on the command line.
 
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
dabdowb

ASKER
Holy Cow!!!  I have a wide assortment of answers here, and it looks like most of them will work.
I like the simplicity behind some of the bat file statements, but I don't have alot of familiarity with how to execute a bat file from within VBA code.  Does anyone know how to do this?
As well, I noticed the praise around the one-liner Qlemo created...as much as I admire the expertise, I am uncertain how to implement it.  Is it a one-line bat file?  Where do I specify what lines to delete, or number of lines rather...or is that built into the line somehow?
Thanks,
Matt
t0t0

dabdowb...

if you're struggling to understand Qlemo's one-liner then I strongly urge you to use my excellent batch file which I am sure even you will quickly be able to understand what's going on.... Qlemo's batch file is intended for gurus...His one-liner is like the gold plating on a set of bath taps.

Nope, I would stick to something simple if I were you.... brass taps if you like.... and leave us to marvel at Qlemo's one-liner especially as it may be 'wasted' in your non-batch file solution. Oh, and please don't forget to award Qlemo a share of the points....

To use the following batch file, call it by passing it the number of lines you want to delete ie:

   DELLINE filename 2

would delete the first 2 lines from file: filename.


@rem DELLINE.BAT
@echo off
if "%1"=="" echo No file specified & exit /b 1
if not exist "%1" echo Cannot find file & exit /b 1

if "%2"=="" (
   more +1 "%1" > "%~n1.tmp"
) else (
   more +%2 "%1" > "%~n1.tmp"
)

move /y "%~n1.tmp" "%1"
exit /b %errorlevel%

t0t0

Just kidding Qlemo.....

You deserve to clinch this one for that great work.... I will add this knowledge to my set of tools.....
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Surone1

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Qlemo

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Surone1

notice mine now also strips the footer part.
t0t0

Nice work Qlemo..... You've certainly exceeded the initial requirements of this question....
Jim P.

The batch file route is great, but since the ultimate destination is an Access DB, Using my VBA approach to load the data directly to the destination table will probably be easier to work with.

You also avoid the issue of shelling out to a DOS prompt and hoping the batch file works.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Qlemo

jimpen, I agree. If I were in Access or Excel, I would use VBA as far as possible. If you are not interested in intermediate results, the direct approach is the best.
dabdowb

ASKER
hey there,
Just wanted to drop a quick line that I have not left this question.  I am testing all the different methods to find out which one works best for my situation before I finalize everything.  Even still, I think there are multiple valid solutions, and I would like to give credit to each one that works, even if it is not my final choice.  I am also smack in the middle of monthend at my work, so the next few days are chaos.  I will get a reply out there as soon as possible, but thank you SOOOO much for all the help and advice so far.  I think the bat file solutions can be used in some other jobs not based in Access, so even if I don't use it for this situation, I will definitely use it for those.  I don't think I have had such a complete array of working solutions before.  My hats off to you all.

Cheers,
matt
t0t0

I will come back to this one tomorrow....
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Surone1

good luck matt, and i must say this is/was a fun question to have participated in.
Jim P.

If you need help with the coding, let me know.

I didn't do a massive amount of commenting, so if it doesn't make sense, please ask.
dabdowb

ASKER
OK, so I tried the surone1's solution because I liked that it deleted the top and bottom lines that were bad, but I kept getting compile errors.  After going back and fixing the code to create declarations for the variables, I ran into a compile error on the line: Set fname = fs.OpenTextFile(fpath, ForReading) with the "ForReading" portion being highlighted.  I wasn't sure what that was all about, so I abandoned that solution temporarily and moved to jimpen's solution.
I understand this solution the most out of all of them (basic VBA skills is all I really have right now...still very much learning), but it ran into a run-time error '62': Input past end of file.  Choosing the debug option, it highlighted this line in the Do Until EOF loop: Line Input #FileNum, InputString.
My assumption here is that it made it to the end of the file where the last 11 lines all have data in the first field of the line instead of a tab or Char(9) as it is in the statement, so it never makes it out of the loop.
I am sure there is a workaround for that, but being a basic VBA user at this point, I haven't figured it out.
Thoughts?
Thanks,
Matt
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
dabdowb

ASKER
Sorry...just to clarify, it highlights the line inside of this particular Do Until loop
Do Until Right(InputString, 1) = Chr(9)
            Line Input #FileNum, InputString
        Loop
Thanks,
Matt
AmazingTech

Try it this way.

      Do Until EOF(FileNum) = True
           Line Input #FileNum, InputString
       Loop

OR

       Do Until EOF(FileNum)
           Line Input #FileNum, InputString
       Loop
dabdowb

ASKER
well, here's the thing....it is already nested inside a loop that goes to EOF (see below)
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
I like the premise here quite a bit...maybe the easier thing would be to set it up like this:
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
        Code needed to tell program to go to next line
        Loop
    End If
I just don't know what the coding would be to skip to the next line since this isn't on a counter or array of any type.  If I am reading the code correctly, and a skip to next line could be inserted, wouldn't that read all the lines into the database that have the first character setup in the line as a tab?
Thanks,
Matt
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
AmazingTech

You can add an or but you will still need to re-evaluate the InputString because the last line might not begin with Chr(9).

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) or EOF(FileNum) = True
           Line Input #FileNum, InputString
       Loop
   End If
AmazingTech

Opps. last line might not end with Chr(9).
SOLUTION
t0t0

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
dabdowb

ASKER
Ok Amazing Tech, the EOF trick seems to have worked because it moved on to the StringArray, but now it bombs out with a Run-Time error '9': Subscript out of range in the StringArray.  I took a look at the String Array and found that it was numbered 1-24, but the number 4 was missing and that is where the Debug took me first.  So I renumbered all the String arrays to 1-23, making sure to put the 4 in.  Re-ran, same error, different line, now it pointed to step 4.  Thought I recalled that arrays started with zero, so I renumbered, 0-22, re-ran, same error, different line again.  Now it is pointing to number 5.  What the heck?
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)
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jim P.

Just so we're on the same lines -- can you zip up the file you are trying to import and a copy of the DB and attach it?

If nothing else, at least the post current code and the text file.

I suspect that if you look at the InputString it is a short line or has a blank at the fifth column. You might need to test for blanks/nulls.

When you get the error -  with the debug mode hit <Ctrl>+G and in the immediate window put

?InputString

And hit enter. It should print out the line that it is on. It might be juste error handling.
dabdowb

ASKER
Here is the output I got
?InputString
Plan                ALL
That doesn't make the slightest bit of sense to me at all, so I am way confused on that being the input string.
The original file is in a zip format further up this question.  I am trying to zip the database, but it is pretty big, so I think I will just make a fake database with only the key elements in play and post it here so we can all be on the same playing field with it.
I will also include the table on the database "eurobo" which gives an idea of how this should ultimately load into the database, without losing any data.  ideally, I would like to load the results of this code straight into that table, but for testing purposes, and concerning on formatting issues with changing field types and so forth, I just set the code up to dump straight into the "eurobo_step1" table, which has all text.  I just wanted to get to a point where the table construction would work before trying to get the correct formatting in place.
Thanks,
Matt
dabdowb

ASKER
Here is a copy of what I am working with, simplified to just the table I am working with and the code.
Thanks,
Matt

EuroboTestCenter.zip
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jim P.

Found the issue -- it is occurring in the     'A loop to skip bad lines at the bottom of the file.

Try this on.



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

Open in new window

dabdowb

ASKER
Hi jimpen,
I inserted the last solution you posted, and while it did compile with no errors and ran with no errors, it didn't populate the table "Eurobo_step1" with any data.  When you were working with it, did it post data into your version?  I wonder if I could possibly be missing some references or something different.

Thoughts?
Thanks,
Matt
ASKER CERTIFIED SOLUTION
Jim P.

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
dabdowb

ASKER
I want to understand the batch/command file approach, but it keeps giving me error messages on the : after K, and then it gives me syntax errors as well.  I am trying to understand how to do it...any input?  I don't have much experience with the cmd or batch files, but would like to learn for multiple applications this code could be used on in my databases.
Below is what I have so far in the Module portion of the database.  The first line that is commented out is the attempt I made at running it with the cmd file in C:\UTS\striplines.cmd, but I can't get past the errors in Access to even run it yet.
K:\Lombard\eurobo.txt is where the source file is (there is an attached version above) and 7 is the lines I want to delete/skip at the beginning of the file.
 

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

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
dabdowb

ASKER
jimpen,
I downloaded your database and re-ran it, but even though it ran, the data was in the wrong places on the file.  I went back in and did some troubleshooting and got rid of a couple columns that were causing me issues, but I really didn't need anyway.  The working version I am using is below.
I am still trying the other code snippets so that if there are other correct ones, I give points accordingly, so I won't quite close this yet, but thank you very much for your help and patience.
Thanks,
Matt

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

Open in new window

dabdowb

ASKER
I have also tried this batch file approach in a VBA module in Access
Set objShell = WScript.CreateObject("WScript.Shell")
    objShell.Run ("%comspec% /K DELLINE.BAT K:\Lombard\eurobo.TXT 7"), 1, True
But it states a compile error stating that the WScript.CreateObject is not defined.
Again, no idea what I am doing here either.
Thanks,
matt
Surone1

Set objShell = CreateObject("WScript.Shell")
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Surone1

probably the option explicit is what "broke" my code..
Surone1

to get my code to work you would have to remove the "option explicit" line and in the code window goto tools>references..
then select "microsoft scripting runtime"
dabdowb

ASKER
I don't see where  "option explicit" line is?  I checked the reference you mentioned and tried to compile this again:

Set objShell = WScript.CreateObject("WScript.Shell")
   objShell.Run ("%comspec% /K DELLINE.BAT K:\Lombard\eurobo.TXT 7"), 1, True
But it still had an issue with the Wscript definition (see in bold)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Surone1

Set objShell = CreateObject("WScript.Shell")
  objShell.Run ("%comspec% /K DELLINE.BAT K:\Lombard\eurobo.TXT 7"), 1, True



the "option explicit" line is line 2 of modile imports of your database
Surone1

for that to work yo would also need to add a reference to:
"Windows Script Host Object Model"
dabdowb

ASKER
Hello all,
I ended up using jimpen's solution as it worked best in this situation.  I split up the points as best I could trying to give everyone credit for their help, since all the ones I tested worked, just not the best solution for this particular situation.  Hopefully everyone is okay with my point division...I wish I had more to give each of you as your help was instrumental in the success of my database needs.
It has been a pleasure to work with all of you and I appreciate your patience with me as well.  I hope to achieve your level of knowledge as my exposure to programming increases.
Thanks,
Matt
PS -> Not sure why it is pausing on my request to close the question, but hopefully this all works out right.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
t0t0

The user indicates he wishes to accept jimpen's solution and split points among other contributors. It is my belief the user does not intend to close this question. It is my belief the user is not sure how to accept a solution, how to accept assisted solutions and split points.
dabdowb

ASKER
You are right t0t0...I have done this before, but maybe not with this newer version...it has been a spell since I had open questions to close.  I notice some state 'Accept Multiple Solutions' and some say 'Accept and Aware Points' and then even after it is asking me what I want to 'Accept as Solution'.  In the past, I thought I could only Accept as Solution or Accept Multiple Solutions, so I must have done something wrong.  My bad!
dabdowb

ASKER
The catch is, the one I was trying to post as the ultimate solution, is a post that I made, because it had some corrections to what jimpen had stated, but it IS the working solution.  When I went to that, it would only allow, Accept as Solution, and Accept and Award Points, so I chose Accept and Award Points, which led down the path I am on.  I thought if I hit just the Accept as Solution it would prevent me from splitting points and I certainly don't want any points out of my own question!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Surone1

so what you should do is accept jimpen's as the answer and add a comment that the code needs changes with the # of your post
Surone1

as an assisted solution by the way
dabdowb

ASKER
Hopefully this works this time.  Sorry for all the confusion, but I very much appreciate the help.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
dabdowb

ASKER
Accepted Solution needed slight modification to work in the database.  See post ID: 23902673 for the final working solution.
Thanks,
Matt
Jim P.

Glad to be of assistance. May all your days get brighter and brighter.
dabdowb

ASKER
hey jimpen,
do you know of a reason why this would run ALOT longer on my company's network drives, as opposed to my C: drive?  I mean, I know that if you are not battling network traffic (on a meager 100Mbps connection) that things will run slower than if everything was on my C: drive, but when I ran testing, the database was on my C: drive, but I was still getting the file from the network and it finished within about 10 seconds.  With both the database and the file on the network drive, I can't get it to finish.  I finally kill it after about 10 minutes.  Does the storage of the file contents take place where the database resides?
Thanks again,
Matt
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jim P.

You have both your database and your file on the network drive?

I've run into this issue before. What is happening is the file is being read from the network and then trying to write it back to the network. To see what is happening start your Task Manager (Start --> Run --> taskmgr) and then select the Networking tab -- Turn on the Network Adapter History --> Bytes Received/Sent like the image below and then run the app.

You should see about a 50/50 split and your adapter overall trying to go through the roof.

The work around to this is copy the file local -- process it and then wipe it out.  Added code below.
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

Open in new window

TaskMgr-Networking.jpg